Every Row Is a Stack of Ghosts: MVCC Explained

Here is a question that sounds simple and isn’t. One session runs a long report over the orders table. Halfway through, another session updates a row that report is reading. What happens? In a naive database, one of them waits: either the reader blocks the writer, or the writer blocks the reader. Postgres does neither. Both run at full speed, and both get a consistent answer. The mechanism that makes this possible is MVCC — Multi-Version Concurrency Control — and once you see it, half of Postgres’s behavior stops being mysterious.

Everything below was run against PostgreSQL 18, including the two-session demo at the end.

An UPDATE doesn’t update

Start with the claim that gives MVCC its name. When you UPDATE a row, Postgres does not overwrite it in place. It writes a new version of the row and leaves the old one sitting on disk. Every row carries two hidden system columns that record which transactions it’s visible to. xmin is the transaction ID that created this version; xmax is the one that deleted or superseded it (0 means still live). There’s also ctid, the physical (page, slot) address. Let’s watch them.

CREATE TABLE acct (id int PRIMARY KEY, owner text, bal numeric);
INSERT INTO acct VALUES (1,'Ada',100),(2,'Bo',100);
SELECT xmin, xmax, ctid, id, bal FROM acct ORDER BY id;

 xmin | xmax | ctid  | id | bal
------+------+-------+----+-----
 1084 |    0 | (0,1) |  1 | 100
 1084 |    0 | (0,2) |  2 | 100

Both rows were created by transaction 1084, neither is superseded (xmax=0), and they sit at slots 1 and 2 of page 0. Now update one:

UPDATE acct SET bal = bal + 50 WHERE id = 1;
SELECT xmin, xmax, ctid, id, bal FROM acct ORDER BY id;

 xmin | xmax | ctid  | id | bal
------+------+-------+----+-----
 1085 |    0 | (0,3) |  1 | 150
 1084 |    0 | (0,2) |  2 | 100

Row 1 is now a different physical row: ctid moved from (0,1) to (0,3), and xmin advanced to 1085, the transaction that did the update. The old version at (0,1) didn’t vanish. It’s still on the page, now marked with an xmax pointing at 1085 — invisible to anyone starting after that commit, but still readable by any transaction old enough to predate it. The UPDATE appended; it didn’t overwrite.

The version chain, seen directly

You can see all the versions on the page at once with the pageinspect extension. Two updates in a row, so three versions of one logical row:

CREATE TABLE ver (id int, bal int) WITH (autovacuum_enabled=false);
INSERT INTO ver VALUES (1,100);
UPDATE ver SET bal=150 WHERE id=1;
UPDATE ver SET bal=200 WHERE id=1;
SELECT lp AS slot, t_xmin AS xmin, t_xmax AS xmax, t_ctid AS ctid
FROM heap_page_items(get_raw_page('ver',0));

 slot | xmin | xmax | ctid
------+------+------+-------
    1 | 1134 | 1135 | (0,2)
    2 | 1135 | 1136 | (0,3)
    3 | 1136 |    0 | (0,3)

Read it as a linked list. Slot 1 was created by 1134 and killed by 1135, and its ctid points forward to (0,2) — the next version. Slot 2 was created by 1135, killed by 1136, points to (0,3). Slot 3 was created by 1136, has xmax=0, and points at itself: it’s the live one, bal=200. Three physical rows for a single logical row, chained oldest to newest by xmax and ctid. Only the tail of the chain is alive; the rest are corpses that haven’t been cleaned up yet.

A snapshot is what you can see

Now the payoff. Every transaction runs against a snapshot: a fixed idea of which transaction IDs had committed at the moment the snapshot was taken. When a transaction reads a row, it walks the version chain and picks the one version visible to its snapshot — created by a transaction it can see, not yet superseded by one it can see. Two transactions reading the same logical row at the same instant can legitimately see two different physical versions, and both are correct.

This is why readers and writers don’t block each other. A writer creating a new version doesn’t disturb the old one, so a reader on an older snapshot reads straight through. Let’s prove it with two sessions. Session A opens a REPEATABLE READ transaction — one fixed snapshot for its whole life — reads the balance, waits three seconds, and reads again. One second into that wait, session B updates the same row and commits.

# Session A (background): one snapshot, two reads three seconds apart
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT 'A first read', bal FROM acct WHERE id=1;   -- t=0
SELECT pg_sleep(3);
SELECT 'A second read', bal FROM acct WHERE id=1;  -- t=3
COMMIT;

# Session B (t≈1): update and commit while A is mid-transaction
UPDATE acct SET bal = bal + 100 WHERE id=1;

Session A:

 A first read  | 100
 ...
 A second read | 100

Session B:

UPDATE 1
Time: 2.256 ms
 B sees | 200

Two things happened at once. Session B’s UPDATE completed in 2.3 milliseconds — it was not blocked for even an instant by A’s open transaction. And session A, reading the same row B had just changed, saw 100 both times, because its snapshot was fixed at BEGIN and B’s new version isn’t visible to it. B wrote a new version; A kept reading the old one. Nobody waited. That is the whole promise of MVCC delivered in one interleaving: readers don’t block writers, and writers don’t block readers.

(A detail for later: whether A’s second read sees B’s change depends on A’s isolation level. Under REPEATABLE READ, as here, the snapshot is frozen and it sees 100. Under the default READ COMMITTED, each statement takes a fresh snapshot and the second read would see 200. That difference is the next chapter.)

The price: dead tuples

MVCC isn’t free. Every UPDATE and DELETE leaves old versions on disk — dead tuples — and they pile up until something cleans them out. Watch them accumulate. A table with autovacuum turned off, and 5,000 updates to its two rows:

CREATE TABLE acct2 (id int PRIMARY KEY, bal numeric) WITH (autovacuum_enabled = false);
INSERT INTO acct2 VALUES (1,100),(2,100);
DO $$ BEGIN FOR i IN 1..5000 LOOP UPDATE acct2 SET bal=bal+1 WHERE id IN (1,2); END LOOP; END $$;
SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname='acct2';

 n_live_tup | n_dead_tup
------------+------------
          2 |      10000

Two live rows. Ten thousand dead ones — 5,000 iterations times two rows, every prior version still on disk. The table’s heap grew from a handful of kilobytes to 440 kB to hold versions that no transaction will ever read again. That’s bloat, and it’s the standing cost of never overwriting anything.

The cleanup crew is VACUUM, which finds dead tuples no snapshot can still see and reclaims their space:

VACUUM acct2;
SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname='acct2';

 n_live_tup | n_dead_tup
------------+------------
          2 |          0

Dead count back to zero. (The heap stays 440 kB — vacuum frees the space for reuse inside the table but doesn’t hand it back to the operating system, which is a distinction worth its own chapter.) In normal operation you don’t call this by hand; autovacuum runs it automatically, and on the earlier acct table it had already fired before I could even catch the dead tuples growing. That background reaping is the invisible tax that buys you lock-free reads.

Final thoughts

MVCC is one idea with large consequences: a row is not a cell you overwrite but a chain of versions, and each transaction sees the version its snapshot allows. From that single decision flows everything — readers and writers that never block, xmin/xmax bookkeeping on every row, and dead tuples that VACUUM exists to sweep. The concept to hold onto is the snapshot: what you can see is fixed by when you looked. And that immediately raises the question the demo above hinted at — how fixed, exactly? What is one transaction allowed to see of another’s work? That’s isolation, and it’s next.

Next: What one transaction sees of another: isolation — read committed, repeatable read, serializable, and the anomalies each one stops.