# DDIA Ch. 8: one row, updated 100,000 times — 8 kB or 2.75 MB? ## Concept You have a table with **one row**. You run `UPDATE t SET v = v+1 WHERE id = 1` one hundred thousand times. In Postgres an `UPDATE` is internally a **delete plus an insert** — you can watch the row's physical address (`ctid`) move on every single update, proof that each one wrote a *new* version and abandoned the old. So does the table bloat a hundred-thousand-fold? That depends entirely on **one thing: whether `v` is indexed.** Leave `v` unindexed and the heap stays a single **8 kB page** — HOT pruning reclaims each dead version in place as it goes, so the table never grows. Add an index on `v` and those same 100,000 updates can no longer prune: the heap balloons to **2752 kB**, the index to **2208 kB**, and **~99,421 dead tuples** pile up until `VACUUM`. Identical workload, one live row either way — **344× apart in size**, decided by a single index. ## Provenance *Designing Data-Intensive Applications*, 2nd ed. (Kleppmann & Riccomini, 2025), Chapter 8 — *Transactions*, §"Multiversion concurrency control": > An update is internally translated into a delete and an insert. The book introduces MVCC to explain how a transaction sees a consistent snapshot while others write: instead of overwriting a row in place, the database keeps multiple versions and lets each transaction read the version its snapshot should see. This exercise makes that literal — you watch the versions get created (the moving `ctid`), watch them pile up when Postgres can't prune them, and watch the garbage collector (`VACUUM`) clean them up. ## Prerequisites The surprise is about what an in-place `UPDATE` really does to storage under MVCC — and why two tables under the identical workload end up 344× apart. These help; each line notes where it shows up. 1. **MVCC and row versions** — a database keeps several *versions* of a row so concurrent transactions can each read the one their snapshot should see, rather than blocking or overwriting. It shows up as every `UPDATE` creating a new version instead of mutating the old one. 2. **Heap tuples and `ctid`** — a Postgres table is a *heap* of pages; each row version (a *tuple*) lives at a physical address `ctid = (page, slot)`. It shows up as the `ctid` of `id=1` changing on almost every update. 3. **Dead tuples and bloat** — once no snapshot can see an old version it is *dead*, but its bytes stay in the heap until reclaimed; dead tuples piling up is *bloat*. It shows up as `n_dead_tup` and `pg_relation_size` on the indexed table. 4. **`VACUUM`** — the maintenance pass that finds dead tuples no snapshot needs and marks their space reusable (it does not usually return it to the OS). It shows up as `n_dead_tup` dropping to 0 while the file size stays put. 5. **HOT (Heap-Only Tuple) updates** — an optimization: when an update changes *no indexed column* and the new version fits on the same page, Postgres skips the index and can prune the dead version in place, without a `VACUUM`. It is the whole reason the unindexed table (`t`) stays flat while the indexed one (`t2`) bloats to ~100k dead tuples. ### Where to learn the prerequisites - **MVCC & row versions (#1):** DDIA §"Multiversion concurrency control"; the PostgreSQL manual, "Concurrency Control — Introduction / MVCC." - **Heap, tuples, `ctid`, dead tuples, bloat (#2–#3):** the PostgreSQL manual, "Database Physical Storage"; the `pgstattuple` and `pg_stat_all_tables` reference pages (for `n_dead_tup`). - **`VACUUM` & HOT (#4–#5):** the PostgreSQL manual, "Routine Vacuuming," and the `README.HOT` design note in the source tree (or any "Heap-Only Tuples" write-up). If only one is new, make it #5 — HOT is the difference between the two tables, and understanding it is what turns "an update is a delete and an insert" from a scary-sounding claim into a precise, bounded cost. ## Environment Real measurements from one capture run. Exact dead-tuple counts and transaction ids **vary run-to-run** (HOT pruning is opportunistic and the server's txid counter is shared); the *shapes* reproduce solidly — the unindexed table stays **~1 page (8192 B)**, the indexed table lands at **~2.75 MB** with **~97k–99k** dead tuples. - PostgreSQL 16.14 (Debian, aarch64) in Docker on macOS 26.5.2 (Darwin 25.5.0), arm64; captured via `psycopg[binary]` on Python 3.12 (uv); autovacuum disabled on the tables so any version pile-up is observable; sizes are real measurements. - Two one-row tables, `t(id, v)` with `v` **un**indexed (HOT-eligible) and `t2(id, v)` with an index on `v` (so updates are **non**-HOT). Each row updated 100,000 times. ## Mental model Every `UPDATE` in Postgres is a **new tuple, old one marked dead** — never an in-place edit. The reason is MVCC: a concurrent transaction reading an older snapshot may still need the *old* version, so the database cannot destroy it mid-flight. It writes the new version alongside, stamps the old one with the updating transaction's id (`xmax`), and moves on. The old version becomes *dead* once no live snapshot can see it — and dead versions either get **reclaimed in place** or accumulate as **bloat**, depending on one optimization: - **HOT (Heap-Only Tuple) updates** — if the update touches no *indexed* column and the new version fits on the same page, Postgres links the new version to the old within the page and can *prune* dead versions on ordinary page access, no `VACUUM` required. That is table `t` (v unindexed): the single page keeps getting recycled, so the heap **stays 8 kB** even after 100,000 updates. - **Non-HOT updates** — if an *indexed* column changes (or the page is full), every update must also insert an index entry, the version can't be pruned in place, and dead tuples pile up until `VACUUM`. That is table `t2` (v indexed): ~100,000 dead tuples and a multi-megabyte heap. Both cases still write 100,000 physical versions, so both still move the row's `ctid` — HOT only changes *whether the old versions can be cleaned up as you go*. The whole capture is one script, `code/update_bloat_mvcc.py`; a reader can type the same statements by hand in `psql`. ## Setup Connect to the shared Postgres 16 container and create your own database: ``` psql -h localhost -p 5432 -U postgres -d study -c "CREATE DATABASE ex_ch08_bloat" psql -h localhost -p 5432 -U postgres -d ex_ch08_bloat ``` Then, in that session: ``` CREATE EXTENSION IF NOT EXISTS pgstattuple; CREATE TABLE t (id int PRIMARY KEY, v int); ALTER TABLE t SET (autovacuum_enabled = false); -- so any pile-up is visible INSERT INTO t VALUES (1, 0); ``` Or run the whole capture (drops + recreates `ex_ch08_bloat`, both scenarios): ``` uv run --python 3.12 --with "psycopg[binary]" python3 code/update_bloat_mvcc.py ``` Do **not** read the output yet — make each prediction first. ## Steps ### Step 1 — does an in-place UPDATE move the row? **Predict.** You update the *same* row six times: `UPDATE t SET v=v+1 WHERE id=1`. The row's logical identity (`id=1`) never changes. Will its physical address `ctid = (page, slot)` stay put, or move? **Do.** After each of six updates, read `SELECT ctid, xmin, xmax, v FROM t WHERE id=1`. **Observe.** ``` [1] Initial state of t (one live row): ctid of row id=1 = (0,1) before any updates: live tuples (n_live_tup) = 1 dead tuples (n_dead_tup) = 0 heap size (pg_relation_size) = 8192 bytes (8192 bytes) pgstattuple: tuple_count=1 dead_tuple_count=0 dead%=0.0 free%=99.22 [2] ctid of t's row id=1 over 6 successive UPDATE t SET v=v+1 WHERE id=1: after update 0: ctid = (0,1) after update 1: ctid = (0,2) <- moved after update 2: ctid = (0,3) <- moved after update 3: ctid = (0,4) <- moved after update 4: ctid = (0,5) <- moved after update 5: ctid = (0,6) <- moved after update 6: ctid = (0,7) <- moved live row now: ctid=(0,7) xmin=1401348 xmax=0 v=6 (xmin = txid that created this version; each UPDATE writes a new one) ``` **The `ctid` moves every single time** — `(0,1) → (0,2) → … → (0,7)`. There is no in-place edit: each update wrote a *new tuple* at the next slot on the page and left the old one behind. `xmin=1401348` is the transaction that created the current version; the previous six versions sit at slots 1–6, now dead. The "delete plus insert" is real and it happens every time. ### Step 2 — 100,000 updates, no index on `v` (the surprise) **Predict.** Run `UPDATE t SET v=v+1 WHERE id=1` 100,000 times total. Every one is a delete-plus-insert that moves the `ctid`, as Step 1 showed. So how big does the heap get — roughly 100,000 versions' worth of bloat? **Observe.** ``` [3] Scenario A -- 99,994 more updates of t (100,000 total; v unindexed, HOT-eligible)... done. v = 100000; ctid of row id=1 = (0,124) (was (0,1)) t after 100k HOT-eligible updates: live tuples (n_live_tup) = 1 dead tuples (n_dead_tup) = 158 heap size (pg_relation_size) = 8192 bytes (8192 bytes) pgstattuple: tuple_count=1 dead_tuple_count=123 dead%=48.05 free%=41.26 ``` **Still 8192 bytes — one page. It did not bloat at all.** After 100,000 delete-plus-inserts the heap is the exact same size it started, and the row's `ctid` never left page 0 (it ended at `(0,124)`, a slot on the *first* page). Because `v` isn't indexed, every update is a HOT update, and Postgres prunes the dead versions **within the page** as it reuses it — the same 8 kB gets recycled 100,000 times. `n_dead_tup` reads 158 only because that's how many versions had accumulated since the last opportunistic prune; the count never runs away. The delete-plus-insert absolutely happened 100,000 times (the `ctid` kept moving, `v` reached 100000) — HOT just cleaned up in place so nothing piled up. ### Step 3 — 100,000 updates, with an index on `v` **Predict.** Same 100,000 updates, but now on `t2` where `v` **is** indexed — so each update must add an index entry and can't be a HOT update. Same one live row. How big does the heap (and index) get this time? **Observe.** ``` [4] Scenario B -- 100,000 updates of t2 (v INDEXED, so each UPDATE is NON-HOT)... done. v = 100000; ctid of row id=1 = (343,188) t2 after 100k NON-HOT updates: live tuples (n_live_tup) = 1 dead tuples (n_dead_tup) = 99421 heap size (pg_relation_size) = 2818048 bytes (2752 kB) pgstattuple: tuple_count=1 dead_tuple_count=187 dead%=0.21 free%=0.05 index t2_v size = 2208 kB ``` **99,421 dead tuples, and a heap that ballooned from 8 kB to 2752 kB — 344×.** Same one row, same 100,000 updates — but indexing `v` defeated HOT. Each update had to write a new index entry pointing at the new version, so the old heap version could no longer be pruned in place; it just accumulated, and the row's `ctid` marched all the way to **page 343** `(343,188)`. The index bloated right alongside the heap, to **2208 kB**. `free%` has collapsed to **0.05%** — the pages are packed wall-to-wall with dead versions. (Ignore `pgstattuple`'s own `dead_tuple_count=187`; the two accountings classify differently. The number autovacuum watches, and the one that matters operationally, is `n_dead_tup ≈ 99,421`.) ### Step 4 — VACUUM, the garbage collector **Predict.** Run `VACUUM t; VACUUM t2;`. What happens to `n_dead_tup`? And to `pg_relation_size` on the bloated table — does the 2752 kB file shrink back? **Observe.** ``` [5] VACUUM both tables (the garbage collector removes dead tuples): t after VACUUM: live tuples (n_live_tup) = 1 dead tuples (n_dead_tup) = 0 heap size (pg_relation_size) = 8192 bytes (8192 bytes) pgstattuple: tuple_count=1 dead_tuple_count=0 dead%=0.0 free%=93.21 t2 after VACUUM: live tuples (n_live_tup) = 1 dead tuples (n_dead_tup) = 0 heap size (pg_relation_size) = 2818048 bytes (2752 kB) pgstattuple: tuple_count=1 dead_tuple_count=0 dead%=0.0 free%=99.59 ``` **Dead tuples → 0 on both, but the bloated file does not shrink.** `VACUUM` found every dead version and marked its space **reusable** — `free%` on `t2` jumps from 0.05% back to **99.59%** — but `pg_relation_size` still reads **2752 kB**. Plain `VACUUM` reclaims space *for future rows in this table*, it does not hand pages back to the OS. (`VACUUM FULL` rewrites the table and would shrink it, at the cost of an `ACCESS EXCLUSIVE` lock.) The unindexed `t` was never bloated, so its `VACUUM` is a no-op on size — it just clears the 158 stray dead tuples. ## What you should see - Updating one row moves its `ctid` **every time** — no in-place edit. - `v` **unindexed** (HOT): the heap **stays one 8 kB page** (1×) after 100,000 updates; the `ctid` never leaves page 0; `n_dead_tup` stays tiny (~150). HOT pruning reclaims in place, so the table does not bloat. - `v` **indexed** (non-HOT): **~99,421** dead tuples, heap **~344×** (8 kB → 2752 kB), plus a **2208 kB** index — the book's "delete + insert" made literal. - `VACUUM` drives `n_dead_tup` to **0** and restores `free%` (to ~99.6% on the bloated table), but the file size is **unchanged** — space is reusable, not returned to the OS. ## Why From first principles, the root cause is a single design decision: **Postgres never updates a row in place, because a concurrent transaction on an older snapshot may still need to read the old version.** Overwriting the bytes would corrupt that reader's view of the world. So MVCC keeps versions: an `UPDATE` *inserts* a new tuple carrying the new values and stamps the old tuple's `xmax` with the updating transaction's id, meaning "deleted by this txn." The old version stays physically present, visible to snapshots that started before the update and dead to everyone after. That is why the `ctid` moves on every update in Step 1 — a new tuple means a new physical address — and why "one row, 100,000 updates" really produces 100,000 physical versions. The cost of that design *would be* runaway write amplification and bloat — each logical update is a physical insert, so versions accumulate — except Postgres has two defenses, and the two tables show them at their extremes. **HOT** is the front-line defense: when an update changes no indexed column, the index still points correctly at the row's *page*, so Postgres can chain the new version to the old inside that page and prune dead versions during ordinary access. On the unindexed table that pruning keeps up perfectly — the single page is recycled 100,000 times and the heap never grows past 8 kB (Step 2). The moment an *indexed* column changes, HOT is off: every update inserts an index entry pointing at the *new* tuple, the old one can no longer be reached through the in-page prune chain, and it must wait for the second defense — **`VACUUM`**, the background garbage collector that scans for tuples no snapshot can see and marks their slots reusable (Step 4). Between the two, dead versions pile up: ~99,421 of them, a 344× heap and a bloated index (Step 3). `VACUUM` then reclaims the space for reuse, though a plain vacuum won't return it to the OS. **The boundary — bloat tracks *un-prunable updates and deletes*, not data.** A table that is only ever `INSERT`ed into and read (append-only) does not bloat: there are no old versions to leave behind. An update-heavy table on *unindexed* columns rides HOT and stays flat without much vacuuming at all — exactly what you just saw. And a workload where autovacuum keeps up — the default, which we deliberately disabled here — holds `n_dead_tup` near zero by vacuuming continuously. Bloat becomes a real problem precisely when you combine high update/delete churn on *indexed* columns (HOT disabled) with vacuuming that can't keep pace — long-running transactions that hold back the snapshot horizon are the classic cause, since they make old versions un-reclaimable no matter how often you vacuum. The lesson the book's one-sentence claim hides: "an update is a delete and an insert" is not a curiosity — it is why *which columns you index* silently decides whether an update-heavy table stays 8 kB or grows to megabytes. ### Go deeper 1. **Hold a snapshot open.** In a second session, `BEGIN; SELECT 1;` and leave it. Now run the 100k updates and `VACUUM` on `t2` in the first. Predict whether `n_dead_tup` drops to 0 — or whether the open transaction pins the old versions and defeats the vacuum (the "long-running transaction" bloat bug). Then check whether it also makes the *unindexed* table `t` finally bloat, because HOT pruning can't remove versions a live snapshot still needs. 2. **`VACUUM FULL` vs plain.** After Step 4, run `VACUUM FULL t2` and re-check `pg_relation_size`. Predict whether the 2752 kB file finally shrinks, and why it needs an exclusive lock to do what plain `VACUUM` won't. 3. **`fillfactor`.** Recreate `t2` (the indexed one) with `WITH (fillfactor = 70)` and re-run Step 3. Predict whether leaving 30% of each page free lets *some* updates stay HOT-prunable even with the index, softening the bloat. ```