Dead tuples and bloat
If nothing is ever modified in place and nothing is immediately removed, an obvious question follows: where does it all go? Nowhere, is the honest answer. Old versions — dead tuples — stay right in the table's file, and the file only ever grows. That growth is bloat, and it isn't a malfunction: it's the rent MVCC pays for non-blocking reads.
One row, four tuples
A> INSERT INTO counters VALUES (1, 0) RETURNING xmin, n;
xmin | n
------+---
1001 | 0
(1 row)Three updates to one row. Watch each one get a fresh xid — and leave a corpse.
A> UPDATE counters SET n = 1 WHERE id = 1 RETURNING xmin, n;
xmin | n
------+---
1002 | 1
(1 row)
A> UPDATE counters SET n = 2 WHERE id = 1 RETURNING xmin, n;
xmin | n
------+---
1003 | 2
(1 row)
A> UPDATE counters SET n = 3 WHERE id = 1 RETURNING xmin, n;
xmin | n
------+---
1004 | 3
(1 row)SELECT sees one row. The page holds four tuples — a version chain, three of them dead.
A> SELECT n FROM counters;
n
---
3
(1 row)
A> SELECT lp, t_xmin, t_xmax, t_ctid
FROM heap_page_items(get_raw_page('counters', 0)) ORDER BY lp;
lp | t_xmin | t_xmax | t_ctid
----+--------+--------+--------
1 | 1001 | 1002 | (0,2)
2 | 1002 | 1003 | (0,3)
3 | 1003 | 1004 | (0,4)
4 | 1004 | 0 | (0,4)
(4 rows)The same effect at scale: 1000 rows fit in 5 pages of 8 kB.
A> INSERT INTO bloat SELECT g, 'x' FROM generate_series(1, 1000) g;
INSERT 0 1000
A> SELECT (pg_relation_size('bloat') / 8192)::int AS pages;
pages
-------
5
(1 row)One UPDATE of every row = a full second copy of the table.
A> UPDATE bloat SET filler = 'y';
UPDATE 1000
A> SELECT (pg_relation_size('bloat') / 8192)::int AS pages;
pages
-------
9
(1 row)Now delete everything. Zero rows — and not a single byte returned.
A> DELETE FROM bloat;
DELETE 1000
A> SELECT count(*)::int AS live_rows FROM bloat;
live_rows
-----------
0
(1 row)
A> SELECT (pg_relation_size('bloat') / 8192)::int AS pages;
pages
-------
9
(1 row)Verified against PostgreSQL 18.4 · Run it yourself · Scenario source
Each update stamps the current version's t_xmax, writes a fresh copy one slot over, and moves on. SELECT walks the chain and returns exactly one row; the page holds the row's entire biography.
The same thing, at file scale
The same story plays out page by page, and three facts on it are worth reading twice. An UPDATE of every row temporarily doubles the table, so a "harmless" backfill migration like UPDATE users SET new_column = ... rewrites every tuple — plan for the disk, and for the WAL and vacuum work that follows. Then the DELETE frees nothing: zero live rows, nine pages still on disk. That space isn't lost — VACUUM will make it reusable — but it is not returned to the operating system. Which is the third fact: the file never shrinks on its own, and that's exactly where the next lesson picks up.
None of this is pathological. A steady-state churn of dead tuples, continuously recycled by autovacuum, is MVCC working as designed. Bloat becomes a problem only when dead tuples accumulate faster than vacuum can reclaim them, and the usual culprit is a long-running transaction holding an old snapshot open. So update-heavy tables live best with room to recycle: prefer many small transactions over giant UPDATE-everything sweeps, and let autovacuum keep pace. To know whether it is keeping pace, watch n_dead_tup in pg_stat_user_tables — the production chapter builds monitoring on it.