# 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](postgres-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 watch one idle transaction in another terminal stop vacuum from cleaning up anything at all. ## 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 -p 5432:5432 -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 ``` 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) | 734 | 0 | 1 | widget | 11 ``` 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. 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 | 734 | (0,2) 2 | 8112 | 734 | 735 | (0,3) 3 | 8072 | 735 | 736 | (0,4) 4 | 8032 | 736 | 0 | (0,4) ``` Four tuples for one logical row. Read the `t_xmax`/`t_ctid` columns as a linked list: version 1 was killed by txn 734 and *points forward* to `(0,2)`; `(0,2)` was killed by 735 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 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 ``` (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.) ### 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. 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): ``` 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 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. 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 — 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. 20. **A**: `VACUUM items;` (clean slate). 21. **B**: open a transaction, read the table, and just leave it sitting there: ```sql BEGIN; SELECT count(*) FROM items; ``` Don't commit. This is a completely ordinary idle-in-transaction session — an app that forgot to close one, or a developer who typed `BEGIN` and went to lunch. 22. **A**: `UPDATE items SET qty = qty + 1;` 23. **Predict**: A now has 100,000 fresh dead tuples, and B is only *reading*, not holding any lock on them. Does `VACUUM` clean them up? 24. **A**: `VACUUM (VERBOSE) items;` 25. **Observe**: ``` INFO: finished vacuuming "postgres.public.items": index scans: 0 pages: 0 removed, 1911 remain, 1275 scanned (66.72% of total) tuples: 0 removed, 233281 remain, 100000 are dead but not yet removable removable cutoff: 753, which was 1 XIDs old when operation ended ``` **`0 removed ... 100000 are dead but not yet removable`.** Vacuum ran, did its full scan, and reclaimed nothing. B's snapshot predates the update, so every one of those old versions is still potentially visible to B, and vacuum is not allowed to touch them. 26. **A**: find out who's responsible: ```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 -----+--------+-------------------------------+--------------+-------------------------- 123 | active | 2026-07-22 13:46:59.877228+00 | 753 | SELECT count(*) FROM items; ``` `backend_xmin` is the oldest transaction ID that backend can still see; the smallest one in the cluster is vacuum's ceiling. Compare it to vacuum's `removable cutoff: 753` — identical. That's the whole causal chain, visible in two numbers. This query is the production playbook for "why is this table bloating": find the smallest `backend_xmin`, look at its `xact_start`, and go ask that session what it's doing. 27. **Predict**: what happens to the table's size if writes continue while vacuum is stuck? 28. **A**: `UPDATE items SET qty = qty + 1;` twice more, then `VACUUM items;` and check the size. 29. **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. 30. **B**: `COMMIT;` 31. **A**: `VACUUM (VERBOSE) items;` 32. **Observe**: ``` tuples: 300000 removed, 100000 remain, 0 are dead but not yet removable index scan needed: 1911 pages from table (75.00% of total) had 299778 dead item identifiers removed ``` 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 the mental model, it does so by rewriting the table wholesale, which is why it's the option you reach for last. 33. **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,97) ``` 34. **Predict**: `VACUUM FULL` on a table that is 100,000 live rows inside a 20 MB file. Final size? And what happens to `ctid`? 35. **A**: `VACUUM FULL items;` then re-run both queries. 36. **Observe**: ``` heap | pkey ---------+--------- 5096 kB | 2208 kB ctid ------- (0,2) ``` 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. 37. **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; ``` 38. **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=639 -> Bitmap Index Scan on items_pkey (actual rows=100000 loops=1) ``` 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 639 heap blocks *just to check visibility*, even though `id` is in the index and the query needs nothing else. 39. **Predict**: `VACUUM` (plain, not `FULL`) and re-run. Does the plan change? 40. **A**: `VACUUM items;` then the same `EXPLAIN`. 41. **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. ## 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 `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. 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. 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: ```sql 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 wherever the live version is. Contrast with Part 2's full-table updates, which *weren't* HOT (`n_tup_hot_upd` barely moves) — 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. - Read the vacuum docs and Part 4's failure mode written up formally: https://www.postgresql.org/docs/current/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 ```bash docker stop pg-mvcc ```