# Postgres: MVCC and vacuum ## 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](transaction-isolation.md)). 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: you'll watch a row physically move, watch a 5 MB table triple in size without gaining a single row, and then find out which kind of idle transaction in another terminal stops vacuum reclaiming anything at all — which is *not* the kind almost everyone names. ## 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](https://www.postgresql.org/docs/16/mvcc-intro.html) 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](https://www.postgresql.org/docs/16/transaction-iso.html) 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](https://www.postgresql.org/docs/16/transaction-id.html) 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](https://www.postgresql.org/docs/16/storage-page-layout.html) — 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 Transaction ids (`xmin`, `xmax`, `removable cutoff`, `backend_xmin`) are run-variable and depend on how much the cluster has done — they will not match yours, and they need not even be consecutive, since background activity consumes ids too. So are exact dead-tuple counts, which shift with opportunistic HOT pruning. The **relationships** between them are the lesson and those reproduce exactly: sizes, `ctid` movement, `0 removed` vs `N removed`, and `removable cutoff` matching the blocking backend's `backend_xmin`. ## 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 *`: | Column | What it is | | --- | --- | | `ctid` | The row version's *physical address*: `(page, line_pointer)`. Page 0, slot 3 is `(0,3)`. Not a stable identifier — it changes every time the row is updated, which is the point. | | `xmin` | The transaction ID that *created* this row version. | | `xmax` | The transaction ID that *deleted* (or superseded) it. `0` means still live. | A row version is visible to your transaction if its `xmin` committed before your snapshot and its `xmax` hasn't. That single rule is all of MVCC. An `UPDATE` is then just: write a new version with `xmin = me`, and set `xmax = me` on the old one. Both versions are physically present on disk; your snapshot picks which one you see. Once no live snapshot can see a dead version, it's garbage. Four things can collect it, and they are not interchangeable: | Mechanism | What it actually does | Reach for it when... | | --- | --- | --- | | **HOT pruning** (automatic, no config) | When a query happens to touch a page, Postgres opportunistically frees dead tuple *data* on that page. Cheap and constant, but it can't free the tuples' line pointers or their index entries, so it only recovers part of the space. | Never — it's always on. Worth knowing only because it explains why `VACUUM VERBOSE` sometimes reports suspiciously few "tuples removed" alongside a huge "dead item identifiers removed". | | **autovacuum** (the default, on by default) | A background worker that runs a normal `VACUUM` on a table once its dead-tuple count crosses a threshold (default: 20% of the table plus 50 rows). | This is the answer in production ~95% of the time. When a table bloats anyway, the fix is almost always to make autovacuum run *more aggressively* on that table (`autovacuum_vacuum_scale_factor`), not to schedule manual vacuums. | | **`VACUUM`** (manual, non-blocking) | Marks dead tuples' space free *for reuse by that same table*, and clears their index entries. Takes only a `SHARE UPDATE EXCLUSIVE` lock — reads and writes continue normally. Does **not** return disk to the OS (except by chance, if the free space happens to be at the very end of the file). | Right after a large bulk `DELETE`/`UPDATE`, when you don't want to wait for autovacuum's threshold. Also as `VACUUM ANALYZE` after a bulk load, to refresh planner statistics at the same time. | | **`VACUUM FULL`** (manual, blocking) | Rewrites the entire table into a brand-new file containing only live rows, then swaps it in and deletes the old one. Genuinely returns disk to the OS and rebuilds the indexes compactly. Takes an `ACCESS EXCLUSIVE` lock — *every* reader and writer blocks for the duration — and needs room for a second full copy of the table while it runs. | Rarely, and never casually on a live system. Justified after a one-off deletion of most of a large table, during a maintenance window. If you need the space back *without* the downtime, `pg_repack` does the same job online. | The distinction that trips people up: **`VACUUM` almost never makes the table smaller.** It makes the space inside the table reusable, so the table stops *growing*. Those are different outcomes, and Part 3 below shows both. ## Setup Two terminals, side by side. Everything below assumes you have Docker. ```bash docker run --name pg-mvcc --rm -e POSTGRES_PASSWORD=postgres -d postgres:16 ``` Terminal A: ```bash docker exec -it pg-mvcc psql -U postgres ``` Terminal B (a second, independent connection — not needed until Part 4, but open it now): ```bash docker exec -it pg-mvcc psql -U postgres ``` In **A**, create the schema: ```sql 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); ``` Two things to note about that `CREATE TABLE`. `pageinspect` is a contrib extension (bundled with the official image) that lets you read raw heap pages — normally invisible internals. And `autovacuum_enabled = off` is deliberate: autovacuum would otherwise clean up behind your back mid-exercise and make the bloat you're trying to observe vanish. This is a lab setting only; **never** do this to a real table. ## Steps ### 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. 1. **A**: `SELECT ctid, xmin, xmax, * FROM items;` ``` ctid | xmin | xmax | id | name | qty -------+------+------+----+--------+----- (0,1) | 733 | 0 | 1 | widget | 10 (1 row) ``` Page 0, slot 1. Created by transaction 733, never superseded (`xmax = 0`). Your `xmin` will differ — transaction IDs depend on how much the cluster has done. 2. **Predict**: you're about to update `qty` on this single-row table. Does `ctid` stay `(0,1)`? 3. **A**: `UPDATE items SET qty = qty + 1 WHERE id = 1;` then the same select again. 4. **Observe**: ``` ctid | xmin | xmax | id | name | qty -------+------+------+----+--------+----- (0,2) | 745 | 0 | 1 | widget | 11 (1 row) ``` The row *moved* — slot 1 → slot 2 — and its `xmin` is the new transaction. This isn't the same row edited; it's a new row version. (733 → 745 rather than 733 → 734: background activity consumed the ids in between. Transaction ids are cluster-wide, not per-table.) 5. **A**: run that same `UPDATE` twice more. `ctid` becomes `(0,4)`. 6. **Predict**: the table has one row. How many tuples are physically on page 0? 7. **A**: read the raw page: ```sql SELECT lp AS line_ptr, lp_off, t_xmin, t_xmax, t_ctid FROM heap_page_items(get_raw_page('items', 0)); ``` 8. **Observe**: ``` line_ptr | lp_off | t_xmin | t_xmax | t_ctid ----------+--------+--------+--------+-------- 1 | 8152 | 733 | 745 | (0,2) 2 | 8112 | 745 | 746 | (0,3) 3 | 8072 | 746 | 747 | (0,4) 4 | 8032 | 747 | 0 | (0,4) (4 rows) ``` Four tuples for one logical row. Read the `t_xmax`/`t_ctid` columns as a linked list: version 1 was killed by txn 745 and *points forward* to `(0,2)`; `(0,2)` was killed by 746 and points to `(0,3)`; and so on until `(0,4)`, whose `t_xmax = 0` and which points at itself — the live version. That forward chain is how a transaction holding an old snapshot walks to the version it's allowed to see. Note `lp_off` decreasing (8152 → 8032): new tuples are written from the end of the 8 KB page backwards, while line pointers grow from the front. A page fills when they meet. Every new version here landed on the *same* page, which means these were **HOT updates** — the optimization Postgres applies when the updated column isn't indexed and the page has room. Confirm it: `SELECT n_tup_upd, n_tup_hot_upd FROM pg_stat_user_tables WHERE relname = 'items';` reports `3 | 3`. It matters from Part 2 on, where full-table updates fill pages, new versions are forced onto *different* pages, and this optimization mostly stops applying. ### 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. 9. **A**: grow the table and check its size: ```sql INSERT INTO items SELECT g, 'item-' || g, g FROM generate_series(2, 100000) g; SELECT pg_size_pretty(pg_relation_size('items')); ``` → `5096 kB` for 100,000 rows. 10. **Predict**: you're about to `UPDATE` every row once — no inserts, no deletes, the row count stays exactly 100,000. What happens to the 5096 kB? 11. **A**: `UPDATE items SET qty = qty + 1;` then check the size again. Then do it a second time. 12. **Observe**: ``` 5096 kB -- 100,000 rows 10192 kB -- after one full-table UPDATE 15 MB -- after two ``` Exactly linear: each `UPDATE` writes a full second copy of every row and the dead copies stay. `SELECT count(*)` still returns `100000` — the table is 3× the size for the same data. Two-thirds of it is garbage. 13. **A**: confirm the garbage is counted: ```sql SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'items'; ``` ``` n_live_tup | n_dead_tup ------------+------------ 100000 | 199997 (1 row) ``` (These statistics are reported asynchronously — if you run this immediately after the updates you may catch stale numbers. Wait a second and re-run. The `199997`, not `200000`, is Part 1's three dead tuples already having been pruned opportunistically; that exact shortfall is HOT-dependent and will vary.) ### 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. 14. **Predict**: after `VACUUM`, does `pg_relation_size` drop back toward 5096 kB? 15. **A**: `VACUUM (VERBOSE) items;` 16. **Observe** (trimmed to the table's own output; vacuum also reports on the table's TOAST relation, and on per-run I/O and WAL counters): ``` INFO: vacuuming "postgres.public.items" INFO: finished vacuuming "postgres.public.items": index scans: 1 pages: 0 removed, 1911 remain, 1911 scanned (100.00% of total) tuples: 27 removed, 100000 remain, 0 are dead but not yet removable removable cutoff: 751, which was 0 XIDs old when operation ended new relfrozenxid: 750, which is 18 XIDs ahead of previous value frozen: 0 pages from table (0.00% of total) had 0 tuples frozen index scan needed: 1274 pages from table (66.67% of total) had 199970 dead item identifiers removed index "items_pkey": pages: 623 in total, 0 newly deleted, 0 currently deleted, 0 reusable ``` Then the size — still `15 MB`. **`pages: 0 removed, 1911 remain`** is vacuum telling you outright that it handed nothing back to the OS. Read those two lines together and the HOT-pruning row of the mental model table pays off. Only `27` *tuples* were removed here, because the full-table `UPDATE`s had already scanned every page and pruned the dead tuple *bodies* on the way past. What they couldn't touch was the dead tuples' line pointers, because index entries still pointed at them — freeing those requires the index pass, which is the `199970 dead item identifiers removed` line. That's why vacuum needs to scan indexes at all. (The `27` is opportunistic and run-variable; what reproduces is that it is tiny next to the ~200,000 line pointers.) 17. **Predict**: so if the 15 MB didn't shrink, does a *third* full-table `UPDATE` push it to 20 MB? 18. **A**: `UPDATE items SET qty = qty + 1;` twice more, checking the size after each. 19. **Observe**: `15 MB`, then `15 MB`. Flat. The space vacuum freed was handed back to *this table's* free space map, and the new row versions were written into it rather than extending the file. That's the actual payoff: vacuum doesn't shrink the table, it stops the table growing. ### 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. 20. **A**: `VACUUM items;` (clean slate). Size is `15 MB`. 21. **B**: open a transaction, read the table, and just leave it sitting there: ```sql BEGIN; SELECT count(*) FROM items; ``` ``` count -------- 100000 (1 row) ``` Don't commit. This is the textbook idle-in-transaction session — an app that forgot to close one, or a developer who typed `BEGIN` and went to lunch. Note that no isolation level was specified, so it is **Read Committed**, the Postgres default. 22. **A**: `UPDATE items SET qty = qty + 1;` 23. **Predict**: A now has 100,000 fresh dead tuples, and B is sitting in an open transaction that read the table *before* those updates. Does `VACUUM` clean them up? 24. **A**: `VACUUM (VERBOSE) items;` 25. **Observe**: ``` INFO: vacuuming "postgres.public.items" INFO: finished vacuuming "postgres.public.items": index scans: 1 pages: 0 removed, 1911 remain, 1275 scanned (66.72% of total) tuples: 100000 removed, 144357 remain, 0 are dead but not yet removable removable cutoff: 755, which was 0 XIDs old when operation ended index scan needed: 637 pages from table (33.33% of total) had 99837 dead item identifiers removed index "items_pkey": pages: 682 in total, 0 newly deleted, 0 currently deleted, 0 reusable ``` **All 100,000 removed, and `0 are dead but not yet removable`.** The idle transaction did not block vacuum at all. 26. **A**: look at why: ```sql SELECT pid, state, xact_start, backend_xmin, left(query, 40) AS query FROM pg_stat_activity WHERE datname = 'postgres'; ``` ``` pid | state | xact_start | backend_xmin | query -----+---------------------+-------------------------------+--------------+------------------------------------------ 85 | idle in transaction | 2026-07-25 16:09:13.463856+00 | | SELECT count(*) FROM items; 86 | active | 2026-07-25 16:09:16.768425+00 | 755 | SELECT pid, state, xact_start, backend_x (2 rows) ``` B (pid 85) is genuinely `idle in transaction` with an `xact_start` minutes in the past — and its `backend_xmin` is **null**. It is holding no snapshot. This is §13.2.1 in action: Read Committed "starts each command with a new snapshot," and that snapshot is released when the command ends. Between statements a read-only Read Committed transaction pins nothing at all. The only row with a `backend_xmin` here is A itself, running this very query. 27. **B**: now hold a snapshot for real: ```sql COMMIT; BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT count(*) FROM items; ``` 28. **Predict**: same idle session, same query, one clause changed. Does `VACUUM` still clean up? 29. **A**: `UPDATE items SET qty = qty + 1;` then `VACUUM (VERBOSE) items;` 30. **Observe**: ``` INFO: vacuuming "postgres.public.items" INFO: finished vacuuming "postgres.public.items": index scans: 0 pages: 0 removed, 1911 remain, 1275 scanned (66.72% of total) tuples: 0 removed, 248043 remain, 100000 are dead but not yet removable removable cutoff: 755, which was 1 XIDs old when operation ended index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed ``` **`0 removed ... 100000 are dead but not yet removable`.** Vacuum ran, did its scan, and reclaimed nothing. Under Repeatable Read the snapshot is taken once, at the first statement, and held until commit (§13.2.2) — so every one of those old versions is still potentially visible to B, and vacuum is not allowed to touch them. Note also `index scans: 0`: with nothing removable there is no point walking the index at all. 31. **A**: name the blocker: ```sql SELECT pid, state, xact_start, backend_xmin, left(query, 40) AS query FROM pg_stat_activity WHERE backend_xmin IS NOT NULL; ``` ``` pid | state | xact_start | backend_xmin | query -----+---------------------+------------------------------+--------------+------------------------------------------ 85 | idle in transaction | 2026-07-25 16:09:38.59458+00 | 755 | SELECT count(*) FROM items; 86 | active | 2026-07-25 16:10:02.18174+00 | 756 | SELECT pid, state, xact_start, backend_x (2 rows) ``` Same session, same idle state — but now `backend_xmin = 755`, and vacuum reported `removable cutoff: 755`. Identical. That's the whole causal chain in two numbers. (Row 86 is A itself; its `backend_xmin` is one higher and irrelevant. Filtering out `pid = pg_backend_pid()` makes this a cleaner production query.) 32. **Predict**: what happens to the table's size if writes continue while vacuum is stuck? 33. **A**: `UPDATE items SET qty = qty + 1;` twice more, then `VACUUM items;` and check the size. 34. **Observe**: `20 MB`. It grew straight through the vacuum, because there was no reusable space to write into. This is exactly how a forgotten `BEGIN` turns into a disk-full page at 3 a.m. — nothing is failing, nothing is locked, the table simply grows forever. 35. **B**: `COMMIT;` 36. **A**: `VACUUM (VERBOSE) items;` 37. **Observe**: ``` INFO: finished vacuuming "postgres.public.items": index scans: 1 pages: 0 removed, 2548 remain, 2548 scanned (100.00% of total) tuples: 300000 removed, 100000 remain, 0 are dead but not yet removable removable cutoff: 758, which was 0 XIDs old when operation ended index scan needed: 1911 pages from table (75.00% of total) had 299711 dead item identifiers removed index "items_pkey": pages: 825 in total, 0 newly deleted, 0 currently deleted, 0 reusable ``` All 300,000 collected the instant the blocker went away — the same command that reclaimed nothing 30 seconds ago. Vacuum was never broken. Size is still `20 MB`, per Part 3. ### 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. 38. **A**: check both the table and its index, and note where row 1 lives: ```sql 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; ``` ``` heap | pkey -------+--------- 20 MB | 6600 kB ctid ------------ (1910,132) ``` 39. **Predict**: `VACUUM FULL` on a table that is 100,000 live rows inside a 20 MB file. Final size? And what happens to `ctid`? 40. **A**: `VACUUM FULL items;` then re-run both queries. 41. **Observe**: ``` heap | pkey ---------+--------- 5096 kB | 2208 kB ctid ------- (0,1) ``` Back to the Part 2 baseline exactly — 20 MB → 5096 kB, and the index fell from 6600 kB to 2208 kB as a bonus, since it was rebuilt from scratch rather than incrementally cleaned. Row 1 is back at the front of page 0. That `ctid` change is the tell for what just happened: this wasn't cleanup, it was a full copy into a new file. Which is also the cost — it held an `ACCESS EXCLUSIVE` lock the whole time (every reader blocked, not just writers), and it needed room for both copies at once. On a 200 GB table you need 200 GB free and a maintenance window. ### 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. 42. **A**: dirty half the table, then look at a plan: ```sql 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; ``` 43. **Observe**: ``` Aggregate (actual rows=1 loops=1) -> Bitmap Heap Scan on items (actual rows=50000 loops=1) Recheck Cond: ((id >= 1) AND (id <= 50000)) Heap Blocks: exact=640 -> Bitmap Index Scan on items_pkey (actual rows=100000 loops=1) Index Cond: ((id >= 1) AND (id <= 50000)) ``` Two things are wrong here. The index scan returns `100000` rows to produce `50000` — the index still carries an entry for every dead version. And Postgres must visit 640 heap blocks *just to check visibility*, even though `id` is in the index and the query needs nothing else. 44. **Predict**: `VACUUM` (plain, not `FULL`) and re-run. Does the plan change? 45. **A**: `VACUUM items;` then the same `EXPLAIN`. 46. **Observe**: ``` Aggregate (actual rows=1 loops=1) -> Index Only Scan using items_pkey on items (actual rows=50000 loops=1) Index Cond: ((id >= 1) AND (id <= 50000)) Heap Fetches: 0 ``` A different plan node, `50000` index rows instead of `100000`, and `Heap Fetches: 0` — the table was not touched at all. Nothing about the query, the schema, or the statistics changed; vacuum marked the pages all-visible and unlocked a strictly better access path. This is why a badly-vacuumed table gets *slow*, not just fat. ### 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. 47. **A**: `CREATE TABLE scratch (x int);` 48. **B**: an ordinary Read Committed transaction that *writes* — to a completely different table: ```sql BEGIN; INSERT INTO scratch VALUES (1); ``` Leave it idle. No `SELECT`, no snapshot held, and it never touches `items`. 49. **Predict**: Part 4 said Read Committed holds no snapshot between statements. Does this one block vacuum on `items`? 50. **A**: `UPDATE items SET qty = qty + 1;` then `VACUUM (VERBOSE) items;` 51. **Observe**: ``` INFO: vacuuming "postgres.public.items" INFO: finished vacuuming "postgres.public.items": index scans: 0 pages: 0 removed, 1274 remain, 1274 scanned (100.00% of total) tuples: 0 removed, 200000 remain, 100000 are dead but not yet removable removable cutoff: 761, which was 2 XIDs old when operation ended index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed ``` Blocked just as hard. One `INSERT` into an unrelated table was enough. 52. **A**: and the diagnostic from Part 4 would have found nothing: ```sql SELECT pid, state, backend_xmin, backend_xid, left(query, 32) AS query FROM pg_stat_activity WHERE datname = 'postgres'; ``` ``` pid | state | backend_xmin | backend_xid | query -----+---------------------+--------------+-------------+---------------------------------- 85 | idle in transaction | | 761 | INSERT INTO scratch VALUES (1); 86 | active | 761 | | SELECT pid, state, backend_xmin, (2 rows) ``` B's `backend_xmin` is still null — but the moment it wrote, it was assigned a real transaction id, `backend_xid = 761`, and a running transaction id is itself below the horizon. Vacuum's `removable cutoff: 761` is exactly that id. So the production query is **both columns**: a session holds vacuum back if it has a `backend_xmin` (it is holding a snapshot) *or* a `backend_xid` (it has written and not yet ended). Sorting by the smaller of the two and looking at `xact_start` is the real playbook. 53. **B**: `ROLLBACK;` **A**: `DROP TABLE scratch;` ## 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 `UPDATE`s 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`). That arithmetic is why the folk rule fails. "Idle in transaction" is a *state*, not a contribution. A Read Committed transaction that has only read holds neither a snapshot (§13.2.1: a new one per statement, released when the statement ends) nor an id (ids are assigned lazily, on first write), so it contributes nothing and vacuum sails past it — Part 4 step 25. Change one clause to `REPEATABLE READ` and the snapshot is taken once and held (§13.2.2), so `backend_xmin` pins the horizon and vacuum removes exactly zero. Insert one row into an unrelated table instead and `backend_xid` pins it just as hard. Same "idle in transaction", three different outcomes, decided entirely by which of those two fields is populated. 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: ```sql 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 wherever the live version is. 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](../ddia/ch08/update-bloat-mvcc.md) 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](../ddia/ch08/mvcc-no-blocking.md) 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. - Read the vacuum docs and Part 4's failure mode written up formally: https://www.postgresql.org/docs/16/routine-vacuuming.html — the 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). ## 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. ```bash docker stop pg-mvcc ```