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 then find out which kind of idle transaction stops vacuum cleaning up anything at all — it is not the kind everyone names.

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.

Provenance

PostgreSQL 16 documentation, §13.1 “Introduction” (Chapter 13, Concurrency Control) for what MVCC is:

This means that each SQL statement sees a snapshot of data (a database version) as it was some time ago, regardless of the current state of the underlying data.

and §25.1.2 “Recovering Disk Space” (Chapter 25, Routine Database Maintenance Tasks) for the two claims Parts 3–4 test:

In PostgreSQL, an UPDATE or DELETE of a row does not immediately remove the old version of the row. This approach is necessary to gain the benefits of multiversion concurrency control (MVCC, see Chapter 13): the row version must not be deleted while it is still potentially visible to other transactions.

The standard form of VACUUM removes dead row versions in tables and indexes and marks the space available for future reuse. However, it will not return the space to the operating system, except in the special case where one or more pages at the end of a table become entirely free and an exclusive table lock can be easily obtained. In contrast, VACUUM FULL actively compacts tables by writing a complete new version of the table file with no dead space.

The load-bearing phrase is “still potentially visible to other transactions.” Part 4 is entirely about pinning down what potentially visible means in practice — and the docs' own §13.2.1/§13.2.2 answer it, because Read Committed “starts each command with a new snapshot” while Repeatable Read “sees a snapshot as of the start of the first non-transaction-control statement in the transaction.” Those two sentences are the difference between a vacuum that reclaims everything and one that reclaims nothing.

Prerequisites

Parts 1–3 are self-contained. Part 4 is where the exercise's surprise lives, and it leans on three things:

1. The MVCC visibility rule — a row version is visible to you if its xmin committed before your snapshot and its xmax hasn't. Used everywhere, but decisive in Part 1 when you read raw tuples off the page. → PostgreSQL docs §13.1, Introduction
2. Snapshot lifetime per isolation level — ⚑ carries most of the result. Read Committed takes a new snapshot for every statement and drops it when the statement ends; Repeatable Read takes one snapshot for the whole transaction and holds it until commit. This single difference decides Part 4 entirely. → PostgreSQL docs §13.2, Transaction Isolation
3. The xmin horizon — ⚑ carries most of the result. Vacuum computes the oldest transaction id any backend in the cluster could still need, and refuses to remove anything newer. pg_stat_activity.backend_xmin exposes each backend's contribution; backend_xid exposes the other contribution people forget (Part 7). → PostgreSQL docs §74.1, Transactions and Identifiers

Helpful but not load-bearing: heap page layout (ctid, line pointers, lp_off) for reading Part 1's raw page dump — §73.6, Database Page Layout — and HOT updates, which explain Part 3's oddly small “tuples removed” count.

Environment

• Host: macOS 26.5.2 (build 25F84), Apple Silicon (arm64)
• Docker: Docker Desktop, Engine 29.6.2 — Linux VM kernel 6.12.76-linuxkit, 16 GiB allocated to the VM
• Postgres image: postgres:16 — PostgreSQL 16.14 (Debian 16.14-1.pgdg13+1), digest sha256:33f923b05f64ca54ac4401c01126a6b92afe839a0aa0a52bc5aeb5cc958e5f20
• Captured: 2026-07-26

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 -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, and §25.1.2's “it will not return the space to the operating system.” 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 — which idle transaction stops vacuum?

What this part tests: vacuum's one hard constraint, from §25.1.2 — a dead version “must not be deleted while it is still potentially visible to other transactions.” Everyone knows the folk version of this rule: a forgotten BEGIN in another session blocks vacuum. This part needs both terminals, and it is where that folk version turns out to be wrong.

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 datname = 'postgres';

COMMIT;
BEGIN ISOLATION LEVEL REPEATABLE READ;
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 §25.1.2 it does so by “writing a complete new version of the table file with no dead space,” 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

Part 7 — the case the playbook query misses

Part 4 concluded that a Read Committed reader pins nothing. That is true, and it is also a trap: backend_xmin is not the only way a session holds back the horizon.

BEGIN;
INSERT INTO scratch VALUES (1);

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

SELECT pid, state, backend_xmin, backend_xid, left(query, 32) AS query
FROM pg_stat_activity WHERE datname = 'postgres';

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.

Then the part worth being wrong about: a textbook idle BEGIN; SELECT … in another terminal that does not block vacuum at all (100000 removed, backend_xmin null), and the same session with ISOLATION LEVEL REPEATABLE READ added that reduces the identical VACUUM to 0 removed … 100000 are dead but not yet removable, with the table growing to 20 MB regardless. And a third case — a Read Committed transaction that merely inserted one row into an unrelated table — that blocks vacuum just as completely while showing a null backend_xmin. Finally VACUUM FULL returning the table 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. Vacuum computes one number — the xmin horizon, the oldest transaction id any backend might still care about — and refuses to remove anything a transaction at or after that id created or killed. Every backend contributes to that number in exactly two ways: the oldest snapshot it currently holds (backend_xmin) and its own transaction id if it has one (backend_xid).

The boundary condition is worth naming: the effect vanishes if the blocking transaction's snapshot is newer than the dead tuples. Vacuum does not care how long a transaction has been open, only how old its horizon is. A session that opened REPEATABLE READ after your updates blocks nothing, and a session opened before them blocks everything — which is why xact_start is a heuristic and backend_xmin is the answer.

And because VACUUM only marks space free within the file, the file itself never shrinks (§25.1.2's “it will not return the space to the operating system”). 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.
• Redo Part 4 with SERIALIZABLE instead of REPEATABLE READ, then with a REPEATABLE READ transaction opened after the updates. Only the first blocks — the boundary condition named in “Why”, made visible.
• 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:

  CREATE TABLE hot_demo (id int PRIMARY KEY, qty int) WITH (autovacuum_enabled = off);
  INSERT INTO hot_demo VALUES (1, 0);
  UPDATE hot_demo SET qty = qty + 1 WHERE id = 1;   -- three times
  SELECT lp, lp_flags, t_ctid FROM heap_page_items(get_raw_page('hot_demo', 0));
  VACUUM hot_demo;
  SELECT lp, lp_flags, t_ctid FROM heap_page_items(get_raw_page('hot_demo', 0));

  Before the vacuum all four line pointers are flag 1 (NORMAL) with a t_ctid. After it:
  lp | lp_flags | t_ctid ----+----------+-------- 1 | 2 | 2 | 0 | 3 | 0 | 4 | 1 | (0,4) (4 rows)
  Line pointer 1 became flag 2 (REDIRECT), still occupying its slot; pointers 2 and 3 became 0 (UNUSED, fully reclaimed); 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 mostly weren't HOT — measured on items at the end of Part 6, this run showed n_tup_upd = 1050003 against n_tup_hot_upd = 1129, about 0.1% — 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.
• The other variable that decides whether bloat happens at all. This exercise holds the schema fixed and varies who else is connected; Part 2's items table triples because a full-table UPDATE on an indexed table can't use HOT (measured at the end of Part 6: 1129 HOT updates out of 1,050,003). ddia/ch08/update-bloat-mvcc runs the complementary experiment — nobody else connected, 100,000 updates to a one-row table, and the only thing that changes is whether the updated column is indexed. Predict the size gap before you look; it is 344×, one 8 kB page against 2752 kB, decided entirely by whether HOT pruning was allowed to fire. Between the two you have both halves of "why did my table bloat": the index that disabled pruning, and the transaction that pinned the horizon.
• Why none of this blocks anyone. Everything above assumes readers and writers don't wait for each other — that's the premise, never tested here. ddia/ch08/mvcc-no-blocking tests it directly, and finds the one knob that revokes it: change a reader's SELECT to SELECT … FOR UPDATE and the concurrent writer blocks after all. Worth running against Part 4's mental model, since a session holding FOR UPDATE is a session that has written — so it pins the vacuum horizon via backend_xid as well as blocking the writer.

Sources: PostgreSQL 16 Docs §13.1: Introduction (MVCC) · §25.1: 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) · §13.2: Transaction Isolation for the snapshot-lifetime rule Part 4 turns on · §73.6: Database Page Layout for what heap_page_items is showing you

Cleanup

The container was started with --rm, so stopping it removes the container, its writable layer, the items table and the extension in one go. Nothing is left on disk and no volume or network was created.

docker stop pg-mvcc