Dead Tuples, Bloat, and What VACUUM Actually Reclaims

A database that updates rows in place accumulates garbage by design. The update does not overwrite the old tuple, because some concurrent transaction may still be reading it under its own snapshot; it writes a new tuple and marks the old one’s header with the transaction that superseded it. Every update therefore costs one new tuple, and every delete leaves one behind. The rows stop being visible to queries long before the storage they occupy stops being used.

Vacuum is the process that closes that gap. It is not a defragmentation tool, it is not an optimizer, and it does not make a table smaller. Understanding precisely what it does — and what it deliberately leaves alone — explains nearly every operational surprise around bloat.

Why the old tuple cannot simply be overwritten

Multi-version concurrency control keeps readers from blocking writers by giving each transaction a snapshot of which transaction identifiers were committed when it started. A tuple carries the identifier of the transaction that created it and, after an update or delete, the identifier that removed it. A reader sees the tuple if the creator is visible in its snapshot and the remover is not.

Overwriting the tuple’s bytes would violate that: a reader holding a snapshot from before the update would suddenly see the new value. So the storage layer writes a second copy and links them, and the old copy becomes dead only once no snapshot that could see it remains. That condition is not “the updating transaction committed” — it is “every transaction older than the updating transaction has finished”, which is why long-running transactions cause garbage to pile up.

The vacuum process uses that exact test. A tuple is dead if its deleting transaction identifier is older than the oldest snapshot any live transaction might still need. The engine maintains that horizon, and a single idle-in-transaction session that has not touched a table for an hour holds it back for the entire database. This is the first and most common cause of unexplained bloat.

What a vacuum pass does to a page

Vacuum walks the table’s pages and, for each, performs four distinct actions that are easy to conflate.

It removes dead tuples. Where a page holds dead tuples whose space is not needed by any live tuple’s line pointer, the tuple identifier is marked unused and the space is returned to the page’s free space. This is the only step that recovers space, and it recovers it to the page, not to the filesystem.

It freezes old tuples. Every tuple stores a 32-bit transaction identifier, which wraps. To keep the identifier space usable, tuples whose creator is older than a threshold are rewritten with a special frozen marker that is visible to every snapshot. Freezing is what allows the engine to advance the horizon without losing the ability to interpret old rows, and it is why vacuum must eventually visit pages that contain no dead tuples at all.

It updates the visibility map. Each heap page has a bit saying whether it contains only tuples visible to all transactions. A page with that bit set can be skipped by future vacuum passes and, more importantly, can be read by index-only scans without consulting the heap. Vacuum sets the bit; any write to the page clears it. The map’s existence is why a table that has never been vacuumed forces index-only scans to visit the heap for every row.

It updates the free space map and the statistics. Free space is recorded so that inserts can find a page with room, and the planner’s estimates are refreshed by the analyze step that usually follows. Neither affects the table’s on-disk size.

A pass that finds no dead tuples still does the freezing and map maintenance work, so vacuum cost is driven by how many pages it must read rather than by the amount of garbage it removes. An ordinary pass consults the visibility map and skips pages marked all-visible, so a clean table whose map is current is cheap to vacuum. A freeze sweep, and a first pass over a table whose map is empty, must read every page regardless. That distinction is the second surprise: a large, mostly clean table is expensive to vacuum exactly when its visibility map is missing or stale.

VACUUM (VERBOSE, ANALYZE) orders;

The verbose form reports pages scanned, pages newly set all-visible, tuples removed, and tuples frozen. Those four numbers, read together, distinguish “the table is bloated” from “the table is clean but the visibility map was stale”.

Why the file does not shrink

Removing a tuple from a page returns space to that page’s free-space region. The page stays in the file. Truncation happens only at the tail of the file: if vacuum finds a run of completely empty pages at the end, it can shorten the file, and it does so. Interior empty pages cannot be removed without renumbering every tuple identifier after them, and tuple identifiers are referenced by every index entry in the database.

That is the whole reason VACUUM and VACUUM FULL are different operations. The plain form is incremental and concurrent; it reclaims space inside pages for reuse by future inserts and updates. The full form rewrites the table into a new file with no gaps and swaps it in, which does shrink the file and does return space to the filesystem — while holding an exclusive lock for the duration and requiring free disk equal to the table’s size. On a large table the full form is an outage, which is why it is reserved for tables that have become mostly empty and are not growing back.

Space recovered inside pages is genuinely reusable. A table that churns steadily reaches a stable size: inserts fill the free space vacuum created rather than extending the file. A table that grew large during a one-off event and then shrank keeps its high-water mark forever without a rewrite, which is why deleting most of a table’s rows leaves the file at its original size. That outcome follows from the reclamation rule rather than from a failure to reclaim.

Autovacuum triggers and their arithmetic

Background vacuuming is driven by two thresholds per table, a base count of dead tuples and a fraction of the table’s live tuple count. The default base is 50 tuples and the default fraction is 0.2, so a table with a million live rows triggers a vacuum after roughly 200,050 dead ones. The same arithmetic applies to inserts for the analyze trigger, with its own pair of thresholds.

The scaling by table size is what makes the defaults wrong for large tables. A table with a hundred million rows tolerates twenty million dead tuples before a pass starts, by which point the dead tuples are scattered across most pages and the pass has far more work to do. Operators lower the fraction and raise the base on big, high-churn tables; the cost is more frequent passes over a table whose pages are mostly clean.

Two other triggers exist. A table that has never been vacuumed gets one at a low absolute threshold, because the visibility map is empty and index-only scans are degraded. And a table approaching the transaction identifier wraparound limit is vacuumed regardless of dead tuple count, because freezing is not optional. The wraparound mechanism is its own subject, covered in the transaction ID horizon and wraparound.

Autovacuum workers are limited in number by a server setting, and each table is handled by at most one worker at a time. A database with many large tables therefore serialises its background work, and a pass that falls behind schedule stays behind. Cost-based delay adds a further throttle: the worker sleeps between pages according to a configured cost per page and a delay per unit of cost, so a pass on a busy system deliberately runs slowly to avoid saturating I/O. That throttle is the reason a vacuum can run for hours without appearing to make progress in the log.

Measuring bloat without guessing

The honest measurement is a comparison of the space the table’s live tuples would need against the space the file occupies. The engine’s own statistics give the live tuple count and the average tuple width, which multiply to an estimate of live bytes; the file size comes from the operating system or from a page-count function. A ratio well above one indicates pages holding free space that is not being reused, which happens when the table is shrinking, or pages holding dead tuples that vacuum cannot remove, which happens when the horizon is held back.

The distinction matters because the remedies differ. Space that vacuum has already reclaimed but that no insert has reused is harmless — it will be consumed by growth. Space held by dead tuples that vacuum refuses to remove is a horizon problem, and no amount of vacuuming fixes it until the long-running transaction ends.

A third case looks like bloat and is not: index bloat. An index over a churned table accumulates dead entries and empty pages just as the heap does, and vacuum cleans them only where it can, because an index page can be removed only if it is entirely empty. Rebuilding an index is the standard remedy, and it interacts with the table rewrite decision described in metadata-only versus rewriting alters, because a rebuild is itself a full scan of the table.

What vacuum does not do

It does not block readers, and it does not block writers for most of its run. It takes a share lock that conflicts with maintenance operations such as index creation but not with ordinary queries.

It does not truncate a table’s file except at the tail, as described above.

It does not remove dead tuples that are still visible to a snapshot, and it cannot override that rule no matter how much bloat results.

It does not clean up everything in one pass on a large table: each pass is bounded by cost limits and by the number of pages it may skip, so a heavily bloated table may need several consecutive passes before the free space map reflects reality. Monitoring should therefore look at the trend across passes rather than at any single run’s report.