How PostgreSQL MVCC creates dead tuples

PostgreSQL 18 scope. The SQL in this article was run on PostgreSQL 18.6 against synthetic fixtures.

An update in PostgreSQL does not simply overwrite the only copy of a row. Multiversion concurrency control allows different transactions to see versions appropriate to their snapshots. That behavior makes concurrent reads and writes practical, but it also creates a lifecycle for old row versions that must eventually be cleaned up.

The phrase “dead tuple” is often used too loosely. A version superseded by an update may still be visible to an older snapshot. It is not yet removable just because the updating transaction committed. Understanding that distinction is essential before blaming vacuum for retaining data.

Establish a reproducible two-session timeline

Use PostgreSQL 18 in a disposable database with two independent connections. Do not run the session blocks sequentially in one connection. First create the fixture and commit it:

CREATE TABLE mvcc_balance (
  id integer PRIMARY KEY,
  amount integer NOT NULL
);
INSERT INTO mvcc_balance VALUES (1, 100);

In session A, establish a repeatable-read snapshot by actually reading the table:

-- Session A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT id, amount, xmin, xmax, ctid
FROM mvcc_balance WHERE id = 1;

Leave session A open. In session B, update the balance and commit:

-- Session B
BEGIN;
UPDATE mvcc_balance SET amount = 120 WHERE id = 1;
COMMIT;
SELECT id, amount, xmin, xmax, ctid
FROM mvcc_balance WHERE id = 1;

Now repeat the select in session A. It should continue to see the balance from its established snapshot, while a new statement in session B sees the committed newer value. Run with two real connections on PostgreSQL 18.6, session A still read 100 after session B had committed 120.

The explicit first read matters: merely issuing BEGIN is not the same as establishing the relevant data snapshot. Keep the session ordering precise or the demonstration can accidentally teach the wrong lesson.

Observe cleanup while the snapshot remains

In session B, outside a transaction block:

VACUUM (VERBOSE) mvcc_balance;

Vacuum must respect versions potentially visible to session A. It cannot discard the old balance merely because session B no longer needs it. After session A commits, run vacuum again from session B:

-- Session A
COMMIT;

-- Session B, after A commits
VACUUM (VERBOSE) mvcc_balance;

The second vacuum has a different visibility horizon available. Verbose output depends on the environment. In a PostgreSQL 18.6 run, the first vacuum reported one tuple "dead but not yet removable"; after session A committed, the second vacuum removed it.

Reference timeline. Session A's established snapshot can still require the old version after session B commits an update.

Do not turn system columns into application identifiers

xmin, xmax, and ctid help explain tuple versions, but they are not durable business identifiers. ctid identifies a physical tuple location and can change after updates or table rewrites. Transaction identifiers wrap and have special interpretation rules. xmax can also carry locking-related information, so a nonzero value is not a universal “this row is deleted” flag.

Use the primary key for application identity. Inspect system columns as diagnostic evidence within a controlled exercise, and avoid building synchronization protocols around assumptions that those values remain stable forever.

Contrast read committed behavior

Repeat the exercise with session A using ordinary read committed isolation. Each statement gets a new snapshot, so a select issued after session B commits can see the newer value. That does not mean read committed transactions can never retain cleanup-relevant state: active statements, assigned transaction IDs, locks, and other session behavior still matter.

The distinction is about snapshot lifetime. A long-running statement can hold a snapshot even without a long sequence of application queries. A repeatable-read transaction can intentionally preserve one across many statements. Diagnose the actual state rather than assuming every old connection has the same effect.

Understand HOT without expecting magic

Heap-only tuple updates can avoid some index maintenance when indexed values do not change and the new version can fit appropriately on the same heap page. They still create row versions. HOT reduces certain costs; it does not remove the need for visibility checks and cleanup.

Changing an indexed column, including an included payload column where relevant, can prevent that optimization. Page free space also matters. A lower fillfactor may reserve room for updates, but it increases the initial table footprint. That is a workload-specific tradeoff, not a universal setting to apply after seeing dead tuples.

Ordinary vacuum generally makes space reusable inside the relation. It is not a promise that the operating system file shrinks after every update cycle. A stable file size alongside continued writes can be a successful reuse pattern rather than evidence of failure.

Turn the mechanism into an operational checklist

When old versions accumulate, ask how quickly the application creates them, whether vacuum runs often enough, whether it can keep up, and whether a retained horizon prevents removal. Those are different failure modes with different remedies. Running vacuum more frequently cannot make a still-needed version unnecessary.

Check application transaction boundaries, particularly requests that open a transaction and then wait for user input or external services. Keep read snapshots bounded. For reporting that genuinely needs a consistent long snapshot, budget its impact and schedule it deliberately.

MVCC is a trade between concurrent visibility and deferred cleanup. Once the team can draw the version timeline accurately, dead-tuple monitoring becomes easier to interpret: an old version may be required, removable, or already reclaimed for reuse. Treating all three states as the same “bloat” number obscures the action that would actually help.

Sources and review scope

• MVCC introduction
• Transaction isolation
• System columns
• HOT updates