PostgreSQL VACUUM and Autovacuum

The idea: MVCC leaves dead rows, VACUUM takes them away

PostgreSQL never changes a row in place. An UPDATE writes a new version of the row and marks the old one with xmax = the updating transaction; a DELETE only sets xmax. Once no transaction can see the old version any more, it is a dead tuple: it still takes space in its page, and its index entries still point to it. VACUUM finds dead tuples, removes them and their index entries, and marks their space free for new rows. Tables that are not vacuumed often enough keep growing with dead rows: this is bloat.

The page models one table, orders, with one index, orders_updated_at_idx. Every UPDATE sets updated_at, an indexed column, so no update can be a HOT update: each new version gets a new index entry, and VACUUM has index work to do. (HOT updates and the page pruning they allow are on Inside a PostgreSQL Heap Page.)

The phases of one VACUUM pass

A plain (lazy) VACUUM reports its phase in pg_stat_progress_vacuum, and the canvas shows the same six boxes.

Phase	What it does
scanning heap	Reads every page the visibility map does not mark all-visible. It prunes each page: dead tuples lose their data and leave an LP_DEAD line pointer (a stub), and the stub's TID (page, line pointer) goes into dead_items.
vacuuming indexes	Reads each whole index once and deletes every entry whose TID is in dead_items (ambulkdelete).
vacuuming heap	Visits the pages with stubs again and turns each LP_DEAD into LP_UNUSED. Records the free space in the free space map and sets the all-visible bit.
cleaning up indexes	One last call per index (amvacuumcleanup): statistics, empty index pages.
truncating heap	Cuts empty pages off the end of the file, if it can take a short ACCESS EXCLUSIVE lock without waiting.
performing final cleanup	Writes reltuples, relpages, n_dead_tup, last_vacuum / last_autovacuum.

Why go back to the heap? The stub cannot be freed while an index entry still points to it: a new row put in that line pointer would be found through the old entry. So the order is always heap, then indexes, then heap. Demo: one VACUUM pass, phase by phase updates 4 rows and deletes 4 more, then removes all 8 dead tuples with one index pass.

dead_items and maintenance_work_mem

The TIDs collected in phase 1 are kept in memory, limited by maintenance_work_mem (or autovacuum_work_mem for autovacuum workers; default 64 MB). When it is full, VACUUM stops the heap scan, runs a full index pass and heap pass for what it has, empties the list, and resumes. Each extra round reads every index from start to end again, which is the expensive part on a big table with many indexes. index_vacuum_count counts the rounds. Demo: small maintenance_work_mem makes room for only 4 TIDs and needs 2 index passes for 8 dead tuples. Since PostgreSQL 17 the TIDs are stored in a compact radix tree (TidStore), so far more fit in the same memory.

When autovacuum starts

The autovacuum launcher wakes every autovacuum_naptime (1 minute) and, for each table, compares the dead-tuple count from pg_stat_user_tables.n_dead_tup with a threshold. If it is over, it has an autovacuum worker run the same VACUUM pass as above.

Setting	Default	This page (per-table)
autovacuum_vacuum_threshold	50	2
autovacuum_vacuum_scale_factor	0.2	0.2
vacuum when n_dead_tup >	50 + 0.2 × reltuples	2 + 0.2 × 16 = 5.2
autovacuum_naptime	1min	one press of Autovacuum wakes
autovacuum_max_workers	3	one worker

The page uses a small per-table threshold (ALTER TABLE orders SET (autovacuum_vacuum_threshold = 2)) so that 16 rows are enough to see it. Demo: when autovacuum starts: after one UPDATE, 4 dead tuples are below 5.2 and the launcher goes back to sleep; after the second, 8 > 5.2 and a worker runs.

The scale factor is the usual problem on big tables: with 0.2, a table of 100 million rows waits for 20 million dead rows. Busy large tables get a smaller per-table autovacuum_vacuum_scale_factor (for example 0.01) or a fixed threshold.

A long transaction holds VACUUM back

VACUUM may remove a dead tuple only if no snapshot can still see it. The oldest snapshot's xmin is the xmin horizon: tuples deleted at or after it are "recently dead" and must stay. A session that sits in an open transaction (often idle in transaction), a long report query, a replication slot, or a standby with hot_standby_feedback can hold the horizon back for hours. VACUUM then runs, reads everything, and frees nothing; its VERBOSE output says N are dead but not yet removable, oldest xmin: …. Demo: long transaction blocks cleanup removes 0 tuples while session B is open, and all 4 after it commits.

Find the culprit with SELECT pid, state, xact_start, backend_xmin FROM pg_stat_activity ORDER BY age(backend_xmin) DESC;, and limit it with idle_in_transaction_session_timeout.

Bloat: VACUUM frees space but rarely shrinks the file

Space freed by VACUUM is reused by later inserts and updates of the same table, through the free space map. The file itself only gets smaller when the pages at its very end are empty. Demo: bloat, then space reuse runs three UPDATEs without VACUUM: there is no free line pointer, so the table grows from 4 to 7 pages. After VACUUM the next UPDATE fits into the freed line pointers and the table does not grow.

• VACUUM FULL (or CLUSTER) rewrites the table into a new, compact file, but holds an ACCESS EXCLUSIVE lock for the whole time: no reads, no writes. Tools like pg_repack do the same online.
• The cure for bloat is prevention: let autovacuum run often enough (lower scale factor, more workers, a higher autovacuum_vacuum_cost_limit so it is not throttled) and keep transactions short.
• Watch n_dead_tup, last_autovacuum and autovacuum_count in pg_stat_user_tables, and pg_stat_progress_vacuum while one runs.

See also How PostgreSQL Runs a Query (heap tuples, MVCC, HOT and VACUUM in one flow), the free space map and visibility map (how VACUUM skips pages and records free space), XID wraparound and freezing (the other job of VACUUM), the PostgreSQL process architecture (the launcher and its workers) and MVCC and isolation levels.

What the page leaves out

• HOT updates, heap-only tuples, redirect line pointers and pruning outside VACUUM (a normal read or update can prune a full page too).
• Freezing: VACUUM also freezes old rows and advances relfrozenxid; aggressive and anti-wraparound VACUUMs.
• The index-cleanup bypass: since PostgreSQL 14, VACUUM skips the index pass when fewer than 2% of the pages have dead items; and INDEX_CLEANUP off, PARALLEL index vacuuming.
• Cost-based delay (autovacuum_vacuum_cost_delay, _cost_limit), insert-triggered autovacuum (autovacuum_vacuum_insert_threshold), auto-ANALYZE, and the B-tree's own deletion of dead entries during inserts.
• Real VACUUM skips only runs of 32 or more all-visible pages, and truncates only when enough trailing pages are empty; the model skips every all-visible page and truncates any empty tail.
• Pages hold 4 line pointers and rows have no size; real pages are 8 KB with up to 291 line pointers.

References

Routine Vacuuming (PostgreSQL documentation)

VACUUM Progress Reporting: pg_stat_progress_vacuum phases

Automatic Vacuuming settings

src/backend/access/heap/vacuumlazy.c (PostgreSQL source)