Dead tuple

A dead tuple is a row version that no current or future transaction can see. PostgreSQL never modifies a row in place: an update writes a new version and marks the old one as ending there, and a delete marks a version as ending with no replacement. The old version stays on the page at its full width until vacuum removes it and every index entry aimed at it. Dead tuples are normal, and every write produces them.

Removed is not the same as reclaimed

The distinction that matters, and that the phrase “cleaning up dead rows” hides, is that vacuum turns occupied space into free space inside the table. It does not return that space to the filesystem. A table that was 400 MB and is now half dead will still be 400 MB after a vacuum, with half of it available for future rows in that same table. That is usually the right outcome, because the alternative is rewriting the whole table under an exclusive lock, and because a table that grew once will probably grow again.

It follows that a dead tuple count falling to zero is not evidence that bloat was fixed. It is evidence that vacuum ran. Bloat is the gap between the space a table occupies and the space its live rows need, and it persists after every dead tuple is gone. The two numbers move together often enough that people treat them as one, and then find that shrinking the count changed nothing on disk.

The count in pg_stat_user_tables has one more property worth knowing: it is an estimate, updated by vacuum and analyze and by the running statistics, and it can be stale or simply wrong on a table that has just been through a burst of activity. It is a trigger for investigation, not a measurement.

Separating the two numbers

pgstattuple reads the heap directly and reports both at once.

CREATE EXTENSION pgstattuple;
CREATE TABLE order_line (id int PRIMARY KEY, qty int);
INSERT INTO order_line SELECT g, 1 FROM generate_series(1, 10000) g;
DELETE FROM order_line WHERE id % 2 = 0;
SELECT dead_tuple_count, dead_tuple_percent, free_percent FROM pgstattuple('order_line');
VACUUM order_line;
SELECT dead_tuple_count, dead_tuple_percent, free_percent FROM pgstattuple('order_line');

CREATE EXTENSION
CREATE TABLE
INSERT 0 10000
DELETE 5000
 dead_tuple_count | dead_tuple_percent | free_percent 
------------------+--------------------+--------------
             5000 |               43.4 |            2
(1 row)

VACUUM
 dead_tuple_count | dead_tuple_percent | free_percent 
------------------+--------------------+--------------
                0 |                  0 |        45.45
(1 row)

The dead count went to zero and the free space went from almost none to nearly half the table. Nothing was returned to the operating system: the same bytes changed category. That second line is the honest measure of what a vacuum achieved, and it is the one number a dead tuple count cannot give you.

pgstattuple scans the whole table to produce this, so it is a diagnostic to run deliberately on one table rather than something to schedule across a fleet.

Why the count sometimes refuses to fall

A vacuum that runs and leaves the dead count where it was has almost always been forbidden from removing anything, and the thing forbidding it is the xmin horizon. When the count does fall and the table keeps growing anyway, the question is which of the three causes of accumulation is at work, which is the subject of autovacuum and table bloat.