MVCC and Vacuum

An UPDATE is really an INSERT plus a tombstone. Watch a row physically move, watch a table triple in size without gaining a row, and watch one idle transaction stop vacuum from cleaning up anything at all.

Concept

Postgres never modifies a row in place. An UPDATE writes a brand-new copy of the row and marks the old copy dead — which is how two transactions can see two different versions of the same row at the same time (that's MVCC, the machinery underneath transaction isolation). The dead copies don't disappear on their own. They pile up as bloat, and VACUUM is the process that reclaims them. This exercise makes all of that visible.

Mental model: the vocabulary, and who cleans up

Three system columns are the whole story. Every table has them; they're just hidden from SELECT *:

Once no live snapshot can see a dead version, it's garbage. Four things can collect it, and they are not interchangeable:

Setup

Two terminals, side by side. Everything below assumes you have Docker.

docker run --name pg-mvcc --rm -e POSTGRES_PASSWORD=postgres -p 5432:5432 -d postgres:16

Terminal A:

docker exec -it pg-mvcc psql -U postgres

Terminal B (a second, independent connection — not needed until Part 4, but open it now):

docker exec -it pg-mvcc psql -U postgres

In A, create the schema:

CREATE EXTENSION pageinspect;
CREATE TABLE items (id int PRIMARY KEY, name text, qty int)
  WITH (autovacuum_enabled = off);
INSERT INTO items VALUES (1, 'widget', 10);

Part 1 — an UPDATE is an INSERT

What this part tests: whether an UPDATE modifies the row where it sits. Per the mental model, it can't — it writes a new version elsewhere and marks the old one superseded. ctid is the direct evidence, since it's the row version's physical address.

SELECT ctid, xmin, xmax, * FROM items;

UPDATE items SET qty = qty + 1 WHERE id = 1;

SELECT ctid, xmin, xmax, * FROM items;

SELECT lp AS line_ptr, lp_off, t_xmin, t_xmax, t_ctid
FROM heap_page_items(get_raw_page('items', 0));

Part 2 — bloat you can measure

What this part tests: whether that pile of dead versions costs anything real. Part 1 leaked three dead tuples; at scale, the same mechanism means a table can grow without gaining a single row.

INSERT INTO items SELECT g, 'item-' || g, g FROM generate_series(2, 100000) g;
SELECT pg_size_pretty(pg_relation_size('items'));

UPDATE items SET qty = qty + 1;   -- check size, then run it again

SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'items';

Part 3 — VACUUM reclaims, but doesn't shrink

What this part tests: the distinction flagged at the end of the mental model. VACUUM frees the dead space — the question is who gets it back, your table or your filesystem.

VACUUM (VERBOSE) items;

UPDATE items SET qty = qty + 1;

Part 4 — the thing that stops vacuum working

What this part tests: vacuum's one hard constraint. It may only remove a dead version if no snapshot anywhere in the cluster could still need it — and a snapshot is held for as long as a transaction stays open, whether or not that transaction is doing anything. This part needs both terminals.

BEGIN;
SELECT count(*) FROM items;

UPDATE items SET qty = qty + 1;
VACUUM (VERBOSE) items;

SELECT pid, state, xact_start, backend_xmin, left(query, 40) AS query
FROM pg_stat_activity WHERE backend_xmin IS NOT NULL;

UPDATE items SET qty = qty + 1;   -- twice
VACUUM items;
SELECT pg_size_pretty(pg_relation_size('items'));

VACUUM (VERBOSE) items;

Part 5 — VACUUM FULL, and what it costs

What this part tests: the one mechanism that actually returns disk. Per the mental model, it does so by rewriting the table wholesale, which is why it's the option you reach for last.

SELECT pg_size_pretty(pg_relation_size('items')) AS heap,
       pg_size_pretty(pg_relation_size('items_pkey')) AS pkey;
SELECT ctid FROM items WHERE id = 1;

VACUUM FULL items;

Part 6 — vacuum isn't only about space

What this part tests: the other thing vacuum maintains. Alongside freeing tuples it updates the visibility map, which marks pages where every tuple is visible to everyone — and that map is what makes index-only scans possible.

UPDATE items SET qty = qty + 1 WHERE id <= 50000;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
  SELECT count(*) FROM items WHERE id BETWEEN 1 AND 50000;

VACUUM items;   -- plain, not FULL

What you should see

An UPDATE that visibly relocates a row (ctid (0,1) → (0,2)), leaving a forward-linked chain of dead versions on the page. A 100,000-row table that triples from 5096 kB to 15 MB across two full-table updates without gaining a row. A VACUUM that reclaims all of it and shrinks the file by exactly zero bytes — but stops further growth dead. A single idle BEGIN in another terminal that reduces that same VACUUM to 0 removed … 100000 are dead but not yet removable, with the table growing to 20 MB regardless. And VACUUM FULL returning it to 5096 kB by rewriting the file, at the price of locking out every reader.

Why

Because Postgres implements MVCC by keeping old row versions in the table itself. Other databases make the opposite choice — Oracle and MySQL/InnoDB update rows in place and push the old versions into a separate undo/rollback segment. Neither is free: Postgres pays with bloat and a vacuum process to manage, InnoDB pays with rollback-segment growth and slower reads for old snapshots. Postgres's choice is what makes its UPDATEs and rollbacks cheap (a rollback is nearly free — the new versions just never become visible) and what makes vacuum a permanent operational concern.

Given that design, VACUUM's "removable" test falls out with no room for cleverness: a dead version can only be dropped once no snapshot in the cluster could still need it. One idle transaction pins the horizon for the entire cluster, and vacuum's only honest response is to scan the whole table and remove nothing — which is precisely what you saw in Part 4. Not a bug, not a tuning problem, and not fixable from vacuum's side at all; the fix is always to close the transaction.

And because VACUUM only marks space free within the file, the file itself never shrinks. Handing pages back to the OS requires proving no live tuple sits in them, which in the general case means relocating live tuples — a rewrite. VACUUM FULL does exactly that rewrite, and the ACCESS EXCLUSIVE lock is the direct consequence: while rows are moving to new physical addresses, nobody else can be reading the old ones.

Go deeper

• Turn autovacuum back on (ALTER TABLE items SET (autovacuum_enabled = on)) and repeat Part 2. Watch last_autovacuum in pg_stat_user_tables and see how much bloat accumulates before the 20% threshold trips.
• Follow the HOT chain from Part 1 through a vacuum. On a fresh single-row table, do the three updates, then VACUUM and re-read the page asking for lp_flags this time: SELECT lp, lp_flags, t_ctid FROM heap_page_items(get_raw_page('items', 0));. Line pointer 1 becomes flag 2 (REDIRECT) still occupying its slot, pointers 2 and 3 become 0 (UNUSED, fully reclaimed), and 4 stays 1 (NORMAL). The redirect is the trick that makes HOT work: the index entry still points at slot 1, so it never had to be rewritten, and slot 1 now just forwards to the live version. Contrast with Part 2's full-table updates, which weren't HOT — which is exactly why the vacuum in Part 3 had to do an index pass.
• Run Part 5's VACUUM FULL while another session sits in a plain SELECT loop, and watch the reader block in pg_stat_activity — proof that ACCESS EXCLUSIVE isn't just about writers. Then try REINDEX TABLE CONCURRENTLY on the same table to see the online alternative.
• DELETE 90% of the table, VACUUM, and check the size. Then insert 50,000 fresh rows and check again. The free space map means the file shouldn't grow — you can watch space get recycled instead of allocated.

Sources: PostgreSQL Docs: Routine Vacuuming — its transaction-ID-wraparound section explains the other reason vacuum is non-optional, one this exercise doesn't reach (a cluster that stops accepting writes entirely) · Database Page Layout for what heap_page_items is showing you

Cleanup

docker stop pg-mvcc