Dead tuples and bloat
UPDATE creates a new tuple version and DELETE makes a version no longer live. Old versions are not necessarily removed immediately. Reusable space and obsolete versions can remain in the heap file; file growth depends on reuse, pruning, vacuum, fillfactor and workload, not just the number of writes. Routine vacuuming documents the maintenance boundary.
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)This UPDATE of all 1000 rows grows the heap from 5 to 9 pages, not an exact doubling.
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.6 · Run it yourself · Scenario source
For counters, the asserted page contains four tuple versions after three updates, while the ordinary SELECT asserts one live value, 3. For bloat, the asserted heap size grows from five to nine 8 kB pages after updating all 1,000 rows. DELETE leaves zero live rows and nine pages at the next observation. These particular sizes are setup-dependent, not a universal doubling ratio or a promise that a file can never shrink.
The same thing, at file scale
Standard VACUUM can make obsolete space reusable, and can truncate empty trailing pages when its locking conditions permit. VACUUM FULL rewrites the table to compact it. The long-transaction schedule demonstrates an old snapshot preventing reclamation of the inspected versions, not the only possible cause of bloat.
pg_stat_user_tables.n_dead_tup is an estimate, not an exact physical tuple count or a byte-size measurement. Combine it with file size and maintenance observations before diagnosing growth. No workload or performance benchmark is claimed here.