Autovacuum Isn't Enough: How Dead Tuples Silently Degrade Postgres

PostgreSQL uses multiversion concurrency control. An UPDATE does not overwrite a row; it writes a new version and leaves the old one in place. A DELETE marks a row as gone without immediately removing it. These obsolete row versions are dead tuples, and they remain in the table until vacuum reclaims them.

This design is what lets readers and writers avoid blocking each other. The tradeoff is that every table with write activity continuously produces garbage that must be collected.

Autovacuum reclaims space occupied by dead tuples, updates the visibility map so index-only scans stay fast, and advances freeze information to hold off transaction ID wraparound. It is triggered per table when the number of dead tuples exceeds a threshold defined by autovacuum_vacuum_threshold plus a scale factor times the table's estimated row count.

The default scale factor is twenty percent. On a small table that fires often. On a hundred-million-row table it means twenty million dead tuples can accumulate before autovacuum even starts, by which point damage is already done.

Autovacuum is deliberately throttled by cost-based delay so it does not overwhelm production I/O. Under heavy write load, the rate of dead-tuple creation can simply outpace the rate of collection. Worse, a single long-running transaction holds back the point below which tuples can be removed, so vacuum runs but reclaims nothing.

The result is a table where autovacuum appears to be working while dead tuples keep climbing. The default configuration is a safe baseline, not a tuned one.

The relevant evidence is available cheaply and read-only. The cumulative statistics views expose the live and dead tuple counts per table, the timestamp of the last autovacuum, and how many rows have changed since. A dead-tuple ratio that trends upward, or a last-autovacuum timestamp that never advances, tells you cleanup is not keeping pace.

None of this requires scanning the table. It comes from statistics the engine already maintains, which is why it can be watched continuously without measurable overhead.

The safe responses are tuning per-table thresholds so busy tables are vacuumed sooner, and running a regular VACUUM to catch up. Both are low-risk and reversible, which is why an automated DBA can perform them under policy. What it must not do automatically is anything that takes a heavy lock or rewrites the table.

Regular VACUUM reclaims dead space for reuse within the table; it does not aggressively return space to the operating system, and it does not block normal reads and writes. That distinction is what separates a routine action from a structural one, a line the next post examines directly.