Why Dead Tuples Exist

Chapter 6 stated the rule and named the bill: every update writes a new row version and leaves the previous one in place for transactions that still need it, every delete only marks a version expired, and every rolled-back insert leaves behind a version that was never visible to anyone. inventory is where that bill has been accumulating. Twelve thousand live rows occupy 900 MB on pg-primary — 115,200 pages of 8 KB, in a table that has never had a row deleted from it.

Vacuum is the process that reclaims those versions, and it is not a maintenance chore bolted onto the engine. It is the second half of the design. Postgres buys reads that never wait for writers by leaving debris behind, and vacuum is the part of the bargain that agrees to clear the debris up. A cluster where it cannot keep up gets slower first and stops accepting writes eventually, and this chapter walks the whole distance between those two states.

The Four Jobs of One Command

The manual lists four reasons to vacuum, and it is worth reading them as four separate jobs that happen to share one scan of the table. The first is to make the space held by dead row versions available for reuse. The second is to update the visibility map, which is what lets a later vacuum skip pages and what makes an index-only scan possible at all. The third is to freeze old transaction ids so they never fall off the back of the id space — the subject of the last topic in this chapter. The fourth is statistics: a plain vacuum refreshes the row-count and page-count estimates in pg_class, while the column statistics the planner reasons with come from ANALYZE, which autovacuum schedules on its own separate thresholds.

Because the four jobs ride on one scan, a table that stops being vacuumed loses all four at once. It bloats, it loses index-only scans, its freeze age climbs toward a hard deadline, and its size estimate in the catalogue drifts away from reality.

The Shape of a Vacuum Run

A run has three repeating phases. Vacuum scans the heap, pruning pages and collecting the item identifiers of dead tuples into memory. Then it visits every index on the table in full and removes the entries pointing at those identifiers. Then it returns to the heap and frees the line pointers themselves, which is only safe once no index can still lead a scan to them. The index step is the expensive one, and it is proportional to the number of indexes rather than to the number of dead rows: orders carries nine indexes, so a vacuum of that table is one heap scan plus nine index scans, and the eighth index someone added for a report costs on every vacuum forever.

SELECT p.phase, p.heap_blks_scanned, p.heap_blks_total,
       p.index_vacuum_count, p.indexes_processed, p.indexes_total,
       pg_size_pretty(p.dead_tuple_bytes)     AS collected,
       pg_size_pretty(p.max_dead_tuple_bytes) AS budget,
       a.query
  FROM pg_stat_progress_vacuum p
  JOIN pg_stat_activity a USING (pid);

That view carries one row per running vacuum, autovacuum workers included, and the phase column names exactly where it is: scanning heap, vacuuming indexes, vacuuming heap, cleaning up indexes, truncating heap, or performing final cleanup. Two numbers there answer most questions. heap_blks_scanned against heap_blks_total is honest progress. And index_vacuum_count above 1 means the run has already been through the indexes more than once. The cause is maintenance_work_mem, not anything about the table.

Memory and the Number of Index Passes

The dead item identifiers vacuum collects during the heap scan have to live somewhere, and that somewhere is sized by maintenance_work_mem, which defaults to 64 MB. When the collection fills the budget, vacuum cannot simply carry on: it has to stop scanning, make a complete pass over every index, free the heap line pointers, and only then resume the scan where it left off. Every one of those cycles rereads all nine of orders's indexes. A too-small budget therefore turns one vacuum of a large table into three or four, and pg_stat_progress_vacuum reports it directly through dead_tuple_bytes against max_dead_tuple_bytes.

Older advice on this setting is now misleading, because two things have changed. Version 17 replaced the flat array of identifiers with a far more compact structure and removed the silent one-gigabyte ceiling that used to cap this memory no matter how high you set it, so a large setting now actually buys fewer index passes. And autovacuum workers take their budget from autovacuum_work_mem, which defaults to -1, meaning "use maintenance_work_mem" — so raising the shared setting to 1 GB for migrations and index builds authorizes three concurrent workers to take 1 GB each. On pg-primary the pair is set deliberately: maintenance_work_mem at 1 GB for the operations a human starts, and autovacuum_work_mem pinned at 256 MB, so three background workers between them cannot take the three gigabytes the shared setting would otherwise authorize.

What Vacuum Does Not Do

It does not shrink the file. The standard form marks space available for reuse inside the same table and returns nothing to the operating system, with one narrow exception: if pages at the very end of the table are entirely free and an exclusive lock can be obtained without waiting, vacuum truncates them off. On a table with continuous traffic that lock is rarely free for the asking, and the pages that are empty are usually not the ones at the end. It also does not reorder rows, does not compact half-full pages by moving tuples between them, and does not shrink indexes.

This is the sentence that explains inventory. Measured directly, in the bloat topic later in this chapter, 99.79% of those 900 MB is free space, and the dead tuple count at any given moment is a few thousand. Vacuum is not failing on that table today. It failed for a stretch of months, the file grew to hold the versions produced during that stretch, and when vacuum caught up it handed all of it back to the table rather than to the filesystem. For a table whose live set is 12,000 rows, that free space will never be refilled by ordinary traffic.

Manual VACUUM and Its Few Correct Uses

Running vacuum by hand is right in a small number of places: after a bulk load or a mass delete, at the end of a migration that rewrote most of a table, and before a benchmark whose numbers you intend to trust. In every one of those the point is that the thresholds have not caught up with a change you already know about. VACUUM (ANALYZE) is the form to use, because the statistics matter as much as the space. The command cannot run inside a transaction block, and it needs the MAINTAIN privilege on the table — database owners can vacuum everything in their own database apart from shared catalogues, and anything the calling role lacks permission for is silently skipped rather than raising an error.

What manual vacuum is not is a schedule. The next topic is where the per-table settings live. A nightly cron job that vacuums the busy tables is a workaround written down as a policy, and it usually means those settings were never adjusted.

Zero Removed, Millions Not Yet Removable

The previous chapter's symptom chain ended on a vacuum log line, and the same line on a later morning is worth reading from vacuum's own side, because it is the difference between a vacuum problem and something that is not a vacuum problem at all. Setting log_autovacuum_min_duration to 0 on the tables you care about records every automatic run with what it actually did; the default is 10 minutes, which on a small hot table means nothing is ever logged.

INFO:  finished vacuuming "cartwheel.public.inventory": index scans: 0
pages: 0 removed, 115200 remain, 115200 scanned (100.00% of total), 0 eagerly scanned
tuples: 0 removed, 12000 remain, 1840244 are dead but not yet removable
removable cutoff: 1988304551, which was 5312041 XIDs old when operation ended
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item
                       identifiers removed

Read the middle line first. Nothing was removed, twelve thousand rows are live, and 1.8 million dead versions could not be touched — not because vacuum was throttled or short of memory, but because some transaction elsewhere in the cluster still has a snapshot old enough that those versions might be visible to it. The removable cutoff line quantifies exactly how far back that snapshot reaches: 5.3 million transaction ids, on a cluster that assigns about ten million a day, so it has been open for something like half a day. No setting on this page changes that number, and Chapter 6 owns the runbook for finding who is holding it.