A one-row table, updated 100,000 times. In Postgres an UPDATE is internally a delete plus an insert, so the row's ctid moves every time — yet the heap's size depends entirely on one thing: whether v is indexed. Unindexed, HOT pruning keeps it at a single 8 kB page; indexed, the same updates balloon it to 2752 kB of heap plus 2208 kB of index with ~99,421 dead tuples. Same workload, 344× apart. One VACUUM reclaims the dead space.
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.
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.
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.
UPDATE creating a new version instead of mutating the old one.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.n_dead_tup and pg_relation_size on the indexed table.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.VACUUM. It is the whole reason the unindexed table (t) stays flat while the indexed one (t2) bloats to ~100k dead 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.
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 unindexed (HOT-eligible) and t2(id, v) with an index on v (so updates are non-HOT). Each row updated 100,000 times.
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:
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.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.
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_bloatThen, 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.pyDo not read the output yet — make each prediction first.
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.
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.
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?
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.
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?
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.)
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?
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.
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.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.
INSERTed 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.
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.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.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.Sources: DDIA 2e, Ch. 8, §"Multiversion concurrency control" · PostgreSQL 16 manual, "Concurrency Control (MVCC)", "Routine Vacuuming", "Database Physical Storage", pgstattuple.