MVCC in PostgreSQL¶

What MVCC gives¶

Multi-version concurrency control keeps several versions of a row at once, so each transaction can read a consistent snapshot while others write. In PostgreSQL, readers never block writers and writers never block readers; only writers to the same row wait for each other. The price is paid in storage and cleanup: old versions accumulate and must be removed by VACUUM.

A comparison with InnoDB and LSM engines is in MVCC in Storage Engines. Everything here was measured on PostgreSQL 16.2.

Row versions: xmin, xmax, ctid¶

Each row version (a tuple) in a heap page carries hidden system columns:

An UPDATE does not modify the row. It writes a new tuple and sets the old tuple's xmax. Measured:

after INSERT:                (ctid, xmin, xmax, id, v)
                             ('(0,1)', 781, 0,   1, 10)

after UPDATE SET v = 11 (by transaction 782):
new session sees:            ('(0,2)', 782, 0,   1, 11)
snapshot opened before it:   ('(0,1)', 781, 782, 1, 10)

Both versions physically exist on page 0. A new session sees item 2 (created by 782, not deleted). A transaction whose snapshot predates 782's commit sees item 1 — it was deleted by 782, but 782 is not visible to that snapshot, so the deletion doesn't count yet.

Snapshots and visibility¶

A snapshot records which transactions' effects are visible:

snapshot = (xmin, xmax, xip_list)
  xmin      lowest transaction ID still running when the snapshot was taken
  xmax      first transaction ID not yet assigned
  xip_list  IDs between them that were still running

SELECT pg_current_snapshot(); shows it (e.g. 781:784:782 — 782 was running). A transaction ID is visible to the snapshot if it is below xmax, not in xip_list, and committed according to the commit log (pg_xact).

A tuple is visible when:

1. its xmin transaction is visible (committed before the snapshot), and
2. its xmax is 0, or belongs to a transaction that aborted or is not visible to the snapshot.

When a snapshot is taken depends on the isolation level: once per statement under Read Committed, once per transaction under Repeatable Read and Serializable (see Isolation Levels and Anomalies).

Checking pg_xact for every tuple would be slow, so the first reader that learns a tuple's xmin/xmax is committed or aborted sets hint bits in the tuple. That is why a SELECT right after a large load can write to many pages (and generate WAL with checksums enabled): it is setting hint bits.

What an update costs: new tuples and index entries¶

Because an update writes a full new tuple, each index on the table must point to the new tuple too — unless the update is a HOT update (heap-only tuple): when no indexed column changes and the new tuple fits on the same page, PostgreSQL chains the new version from the old one inside the page, and indexes keep pointing at the old item, which redirects to the new one. No index writes at all.

Measured on 10,000-row tables with a primary key and an index on indexed, updating every row once:

• With the default fillfactor, pages are packed full, so no new version fits on the same page: no HOT updates.
• With 30% free space, updates are HOT until each page's free space runs out (a single statement updating every row exhausts it partway; in a normal workload, pruning reclaims dead versions on the page between updates and the HOT ratio is much higher).
• Changing an indexed column can never be HOT.

So for update-heavy tables: index only columns that queries need, and lower fillfactor (e.g. 70–90) to leave room on each page. pg_stat_user_tables.n_tup_hot_upd / n_tup_upd shows the HOT ratio.

Dead tuples, bloat, and VACUUM¶

A version is dead once no running or future snapshot can see it. Dead tuples occupy space until VACUUM removes them.

Measured on a 100,000-row table with autovacuum disabled:

What it shows:

• Three updates of every row quadrupled the table: each update left a dead copy of every row.
• Plain VACUUM does not shrink the file. It marks dead space as reusable (in the free space map), and the next update reused it — the table stayed at 54 MB instead of growing to ~67 MB. It only returns space to the OS when the trailing pages become empty.
• VACUUM FULL rewrites the table compactly, but holds an ACCESS EXCLUSIVE lock for the whole rewrite — no reads or writes. In production, pg_repack does the same online.

VACUUM also maintains the visibility map (pages where all tuples are visible to everyone), which makes index-only scans possible and lets later vacuums skip clean pages — see Query Optimization.

Autovacuum¶

The autovacuum daemon vacuums a table when its dead tuples exceed autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × rows (defaults: 50 + 20%). For a 100-million-row table that means waiting for 20 million dead tuples — far too late. Large or hot tables usually get per-table settings:

ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.01,
                        autovacuum_vacuum_cost_limit = 2000);

The oldest snapshot holds everything back¶

VACUUM can remove a dead tuple only if no snapshot could still need it. Measured: a Repeatable Read transaction ran one SELECT and stayed open; then all 10,000 rows of a table were updated and vacuumed.

The cutoff is the oldest xmin among all snapshots in the cluster: open transactions (including idle in transaction sessions), long-running queries, replication slots, and standbys using hot_standby_feedback. Find the culprit:

SELECT pid, state, backend_xmin, now() - xact_start AS age, left(query, 50)
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;

Transaction ID wraparound¶

Transaction IDs are 32-bit and compared with modular arithmetic: of the ~4 billion values, about 2 billion are "in the past" and 2 billion "in the future" relative to any given ID. A tuple with an xmin older than ~2 billion transactions would suddenly appear to be in the future and become invisible — data would vanish.

VACUUM prevents this by freezing old tuples: marking them as visible to everyone, regardless of their xmin. An aggressive anti-wraparound autovacuum is forced when a table's oldest unfrozen xmin reaches autovacuum_freeze_max_age (200 million transactions by default), even if autovacuum is disabled for that table. If freezing cannot keep up — usually because a long transaction or abandoned replication slot blocks it — PostgreSQL warns, and as the limit nears, refuses new transactions until a manual VACUUM is run. Monitor it:

SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;

Alert well before 1–1.5 billion.

Trade-offs¶

Real-world usage: ValkeyDB uses a single-threaded model to avoid the complexity of MVCC. Redis avoids it by never allowing concurrent access to the same key — the event loop serializes all commands.

Common pitfall¶

A long-running or forgotten transaction — an analytics query left open for an hour, a session left idle in transaction by an application bug — holds its snapshot the entire time, and VACUUM can remove no dead tuple created after that snapshot began, in any table of the database: measured above, 10,000 of 10,000 dead tuples survived VACUUM until the old transaction ended. This is the most common real cause of bloat despite autovacuum "running normally," and of approaching wraparound. Check pg_stat_activity for old backend_xmin values before tuning vacuum, and set idle_in_transaction_session_timeout so abandoned transactions are ended automatically.