A long PostgreSQL snapshot can retain intermediate row versions it never reads. Vacuum uses a conservative transaction horizon to decide what is removable. This walkthrough shows why, then reproduces the behavior with synthetic article rows and node-postgres on a Mac.

The task is to distinguish a snapshot that holds back cleanup from a vacuum that has not run. The example targets PostgreSQL 18 and uses two database connections: one reader holding a Repeatable Read snapshot and one writer updating a single article. PostgreSQL’s documentation currently identifies 18 as the current supported major version; implementation details below are explicitly tied to its 18 branch. PostgreSQL 18 documentation.

Why retain versions the old reader cannot see?

An update creates a new row version. PostgreSQL retains the previous version while another transaction might still need it. Eventually, vacuum can reclaim obsolete versions and make their storage reusable. That is the basic MVCC lifecycle described in Routine Vacuuming.

The subtlety is that snapshot visibility and physical removability answer different questions. Visibility asks whether a particular query should return a version. Cleanup uses a conservative cutoff rather than proving, for every version, exactly which existing snapshots could read it.

In PostgreSQL 18, HeapTupleSatisfiesVacuum compares the transaction that made a version obsolete against OldestXmin. A committed deletion or replacement does not immediately make the previous version removable: versions whose deleting transaction is at or beyond that cutoff remain classified as recently dead. This rule is documented directly in the official tuple visibility implementation.

Consider this illustrative sequence. Each update is committed separately, and no other readers are involved:

Step Action Old reader sees Latest committed version
1 Insert article revision 0 — Revision 0
2 Reader establishes a Repeatable Read snapshot Revision 0 Revision 0
3 Writer commits revision 1 Revision 0 Revision 1
4 Writer commits revision 2 Revision 0 Revision 2
5 Writer commits revision 3 Revision 0 Revision 3

Revision 0 is directly needed by the reader. Revisions 1 and 2 are intermediate versions: this reader cannot see them because they were created after its snapshot. Nevertheless, their replacement transactions are also newer than the retained cleanup cutoff. Applying the documented cutoff rule explains why these versions can remain alongside revision 0. Their retention does not mean the reader can traverse them as historical query results. PostgreSQL 18 tuple visibility implementation.

The horizon is broader than the reader’s selected row. PostgreSQL 18 computes relation-appropriate horizons from backend transaction IDs and snapshot horizons; ordinary table data generally takes sessions in the same database into account. It does not track only the rows returned by a particular SELECT. This explains why a small query can affect cleanup elsewhere in that database. Shared relations and other relation types have distinct rules, so “one snapshot stops all vacuum everywhere” is too broad. PostgreSQL 18 horizon implementation.

Which transaction actually holds a long snapshot?

Repeatable Read establishes its snapshot at the first non-transaction-control statement, then uses that snapshot for subsequent queries. Merely issuing BEGIN is insufficient for this demonstration; the initial article query establishes the view that must be retained. Read Committed, PostgreSQL’s default isolation level, instead takes a new snapshot for each command. Transaction Isolation.

Consequently, an idle Repeatable Read transaction that has queried the table can keep its snapshot after the query finishes. A long-running Read Committed statement can also retain a snapshot while executing, but separate completed statements at that level do not share the same fixed view. Transaction duration alone therefore does not establish which snapshot is being retained. Transaction Isolation.

A read-only transaction can contribute to the horizon without having a top-level transaction ID of its own. The implementation considers both backend transaction IDs and snapshot horizons. In this example, a null backend_xid alongside a non-null backend_xmin is therefore consistent with a reader retaining a snapshot. PostgreSQL 18 horizon implementation.

Reproduce the behavior from Mac Terminal

You need a running local PostgreSQL 18 server, its command-line tools, and Node.js with npm. Node.js 20 or 24 is listed in the node-postgres compatibility documentation. This walkthrough assumes your existing local connection settings work. It creates a separate database and uses only synthetic article text. node-postgres introduction and installation.

In Terminal, create a working directory, install the driver, and create the example database:

mkdir article-snapshot-lab
cd article-snapshot-lab
npm init -y
npm install pg
createdb snapshot_lab

Save the complete program below as snapshot-demo.cjs. Its connections inherit PostgreSQL environment settings, including PGDATABASE, PGHOST, and PGPORT, as documented in node-postgres Connecting. Use the same local connection settings for createdb and the program.

The reader keeps every transaction statement on one dedicated client. node-postgres requires this: a transaction must use the same client throughout, so dispatching its statements through pool.query() is unsuitable. The second client runs the updates and vacuum commands independently. node-postgres Transactions.

The example also indexes revision, which changes on every update. That deliberately prevents these updates from using HOT, making the demonstration easier to interpret. PostgreSQL documents that HOT requires updates to leave columns referenced by ordinary indexes unchanged, along with sufficient space on the original page. Heap-Only Tuples.

const { Client } = require('pg');

async function main() {
  const reader = new Client({
    application_name: 'article_snapshot_reader',
    connectionTimeoutMillis: 5000
  });
  const writer = new Client({
    application_name: 'article_snapshot_writer',
    connectionTimeoutMillis: 5000
  });
  let readerOpen = false;
  writer.on('notice', notice => console.log(notice.message));

  try {
    await reader.connect();
    await writer.connect();
    const version = await writer.query('SHOW server_version_num');
    const major = Math.floor(Number(version.rows[0].server_version_num) / 10000);
    if (major !== 18) throw new Error('This walkthrough targets PostgreSQL 18.');

    await writer.query(
      'CREATE TABLE public.article_snapshot_demo (' +
      'id integer PRIMARY KEY, revision integer NOT NULL, title text NOT NULL)'
    );
    await writer.query(
      'CREATE INDEX article_snapshot_demo_revision_idx ' +
      'ON public.article_snapshot_demo (revision)'
    );
    await writer.query(
      'INSERT INTO public.article_snapshot_demo VALUES (1, 0, $1)',
      ['Article draft']
    );

    await reader.query('BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY');
    readerOpen = true;
    const selectRow = 'SELECT id, revision, title FROM public.article_snapshot_demo';
    console.log('Reader before:', (await reader.query(selectRow)).rows);

    for (const revision of [1, 2, 3]) {
      await writer.query(
        'UPDATE public.article_snapshot_demo SET revision = $1, title = $2 WHERE id = 1',
        [revision, 'Article revision ' + revision]
      );
    }

    const pid = (await reader.query('SELECT pg_backend_pid() AS pid')).rows[0].pid;
    console.log('Reader activity:', (await writer.query(
      'SELECT state, backend_xid, backend_xmin FROM pg_stat_activity WHERE pid = $1',
      [pid]
    )).rows);
    console.log('Writer latest:', (await writer.query(selectRow)).rows);
    console.log('Reader still:', (await reader.query(selectRow)).rows);

    console.log('Vacuum with snapshot open:');
    await writer.query('VACUUM (VERBOSE) public.article_snapshot_demo');

    await reader.query('COMMIT');
    readerOpen = false;

    console.log('Vacuum after reader commit:');
    await writer.query('VACUUM (VERBOSE) public.article_snapshot_demo');
    console.log('Reader fresh:', (await reader.query(selectRow)).rows);
  } finally {
    if (readerOpen) await reader.query('ROLLBACK').catch(console.error);
    await Promise.allSettled([reader.end(), writer.end()]);
  }
}

main().catch(error => {
  console.error(error.message);
  process.exitCode = 1;
});

Run it with the database selected explicitly:

PGDATABASE=snapshot_lab node snapshot-demo.cjs

The program’s JavaScript syntax was checked with Node.js. The database sequence was inspected against the retrieved documentation; it was not executed against a PostgreSQL server for this article.

How to interpret the two vacuum passes

The expected query results are revision 0 for Reader before, revision 3 for Writer latest, and revision 0 again for Reader still. After the reader commits, its next standalone query should return revision 3. These are expected outcomes of the synthetic sequence, rather than captured test output.

The activity result should identify the reader as idle in transaction while the writer inspects it, with a retained backend_xmin. PostgreSQL defines that state as a transaction waiting for another command, and backend_xmin as the backend’s current horizon. The Cumulative Statistics System.

Read the first vacuum report for obsolete versions that remain nonremovable. After the reader commits, compare the second report for removal of those versions. In an otherwise quiet database, the three committed replacements provide three obsolete versions to examine. Other retained horizons or concurrent maintenance can affect the exact report, so use the before-and-after pattern rather than requiring identical output text.

VACUUM (VERBOSE) produces a detailed activity report. The program prints server notices through the documented node-postgres notice event. Both vacuum calls run outside explicit transaction blocks because PostgreSQL does not allow VACUUM inside one. PostgreSQL VACUUM, node-postgres Client API.

Use this decision aid when the result differs from expectations:

Observation Next check
Reader sees revision 3 before committing Confirm Repeatable Read was started and the first article query ran before the updates.
First vacuum retains obsolete versions Compare it with the pass after reader commit; retention while the snapshot is open is expected.
Versions remain after reader commit Check for another retained horizon; ending this reader removes only its contribution.
A table statistics count seems inconsistent Treat n_dead_tup as an estimate and compare the vacuum reports.
The table file does not become smaller Check whether storage became reusable; file shrinkage is a separate outcome.

PostgreSQL explicitly describes n_dead_tup as an estimated count. A recorded vacuum time also establishes that maintenance ran, without establishing that every obsolete version was removable. These fields support diagnosis, but neither replaces examining the retained snapshot and the vacuum report. The Cumulative Statistics System.

Plain vacuum normally makes space reusable inside the table rather than returning it to macOS. Entirely free trailing pages are a documented exception. A stable file size after cleanup therefore does not establish that cleanup failed. Routine Vacuuming.

What should change in the application?

If several article queries must share one consistent database view, keep the transaction scoped to those queries and end it when they finish. As an application-design consequence of snapshot retention, do unrelated processing and desktop interaction after that transaction ends whenever the task permits.

If each query can independently use the latest committed view, Read Committed supplies command-level snapshots. Changing isolation or committing between batches changes consistency semantics: later queries may see newly committed article revisions. Choose that approach only when those changing results fit the reader task. Transaction Isolation.

Increasing vacuum frequency cannot make a version pass an unchanged removability cutoff. Ending the unnecessary snapshot addresses that contribution to retention. Similarly, VACUUM FULL is a table rewrite requiring an exclusive lock, with additional space requirements; it is not the routine response to this example. PostgreSQL 18 tuple visibility implementation, PostgreSQL VACUUM.

The script leaves its synthetic table available for inspection. It deliberately fails if that table already exists, rather than replacing existing contents. After inspecting it, run the following command in a query session connected to snapshot_lab if you want to remove the example table. If a run failed partway through setup, inspect that same table before removing it and rerunning.

DROP TABLE public.article_snapshot_demo;

The useful diagnostic is the transition: establish an old snapshot, commit several replacements, vacuum, end the snapshot, and vacuum again. Seeing the original revision remain readable while intermediate versions also resist cleanup makes the distinction concrete. PostgreSQL’s visibility rules determine what the query returns; its cleanup horizon determines when obsolete storage can be reused.

Sources