Concurrencymvccrow versionsnapshotxminxmax

MVCC: Multi-Version Concurrency Control

Instead of overwriting a row, an update writes a new version and marks the old one superseded; each transaction’s snapshot decides which versions it can see — so readers never block writers, and dead versions have to be vacuumed.

▶ Interactive Interview question See how this works internally →

Progress

Versions, not overwrites

A row in PostgreSQL is a chain of versions. Each carries xmin, the id of the transaction that created it, and xmax, the id of the transaction that deleted or superseded it (zero if none). UPDATE does not modify the version in place: it writes a new version with xmin = current and stamps the old one with xmax = current. DELETE just sets xmax. Nothing is physically removed by any of them.

A transaction takes a snapshot: the set of transaction ids that were committed when it started. A version is visible if its xmin is in the snapshot (created by a committed transaction the reader can see) and its xmax is not (not yet deleted, or deleted by something the reader cannot see). That rule, applied per version, is the entire isolation mechanism.

Readers never wait

Because the old version is still there, a reader whose snapshot predates a writer’s commit simply reads the old version. It does not wait for the writer, and the writer does not wait for it. Writers only conflict with writers — two updates to the same row serialise on a row lock. This is why PostgreSQL can run a long analytical query and thousands of small updates at once without either blocking the other.

It also explains Repeatable Read exactly: a snapshot taken once at the start of the transaction, reused for every statement. And it explains why a transaction left open for hours is dangerous: every version newer than its snapshot must be retained for it, even if no one else can see them.

Dead versions and VACUUM

Once every snapshot that could see an old version has ended, the version is dead: invisible to all, yet still occupying space in the page and an entry in every index. VACUUM finds dead versions and marks their space reusable; autovacuum does it in the background when a table’s dead-row count crosses a threshold. A table under heavy update churn where VACUUM cannot keep up grows — bloat — and every scan reads pages that are mostly corpses.

The two things that stop VACUUM from reclaiming: a long-running transaction (its snapshot keeps versions alive) and a replication slot that has fallen behind. Both show up as "table size growing with constant row count". Monitor n_dead_tup in pg_stat_user_tables, and alert on transactions idle for more than a minute.

Seeing the versions and the bloat

1-- the hidden columns on every row
2SELECT xmin, xmax, ctid, * FROM accounts WHERE id = 1;
3 
4-- dead rows waiting for VACUUM
5SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
6FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;
7 
8-- the transaction holding everything back
9SELECT pid, now() - xact_start AS age, state, left(query, 60)
10FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY age DESC LIMIT 5;

Costs of the design

Every update writes a full new row version, so a one-byte change to a wide row copies the whole row and updates every index that contains any changed column — or every index, if the new version cannot fit on the same page (the HOT optimisation avoids index updates when it can). Frequent updates to a hot row generate versions faster than VACUUM removes them. Counters on hot rows, in particular, are the worst case; see Denormalization on Purpose.

Key points

UPDATE writes a new version; old versions stay until vacuumed. xmin/xmax plus a snapshot decide visibility.
Readers never block writers; writers block only writers on the same row.
Dead versions cause bloat; VACUUM reclaims them; long transactions and stale replication slots prevent it.
Updates are expensive on wide rows and hot rows.

Multi-version concurrency control

Why readers never block writers: an UPDATE writes a new row version instead of overwriting the old one, and each transaction's snapshot decides which versions it can see.

-- initial state, written by txid 100

Row versions on the page

version	balance	xmin	xmax
v1	500	100	—

What each transaction sees

txid 205 → balance 500

v1 is visible: created by a committed transaction, not deleted.

txid 207 → balance 500

Same.

A row is not a cell that gets overwritten. It is a chain of versions, each stamped with the transaction that created it (xmin) and the one that superseded it (xmax).

1/5