VACUUM

For DBAs and operators

The phases

1. Scanning heap: reads every page not marked all-visible in the visibility map (all-visible pages are only skipped in runs of at least 32, to keep OS read-ahead effective). It first prunes each page (removes dead tuples' data, leaving short "dead item identifiers"), freezes old tuples, and records the TIDs (block, offset) of dead items.
2. Vacuuming indexes: every index is scanned once and entries that point to a collected TID are deleted. Several indexes can be processed by parallel workers (VACUUM (PARALLEL n), manual VACUUM only).
3. Vacuuming heap: the dead item identifiers are set to unused, space is recorded in the free space map, pages are marked all-visible (and all-frozen) where possible.
4. Cleaning up indexes and truncating heap: empty pages at the end of the file are returned to the OS. Truncation needs a short ACCESS EXCLUSIVE lock and is skipped if it cannot get it; disable it with vacuum_truncate = off (a server setting since PostgreSQL 18) or the vacuum_truncate table option (replicas replay the lock too).

The TID store (PostgreSQL 17+)

Dead TIDs are kept in memory limited by maintenance_work_mem (or autovacuum_work_mem). Before 17 it was a flat array of 6-byte TIDs capped at 1GB, so big tables often needed several index passes ("index scans: 3"). Since 17 it is a compact radix tree (TidStore) that needs far less memory (the PostgreSQL 17 announcement cites up to 20× less) and is no longer capped at 1GB. If it fills up anyway, VACUUM pauses the heap scan, vacuums all indexes, empties the store and continues: every extra pass rereads every index.

VACUUM vs VACUUM FULL

For online shrinking use pg_repack or pg_squeeze; better still, keep autovacuum frequent enough that bloat never builds up.

Watching it

SELECT pid, relid::regclass, phase, heap_blks_total, heap_blks_scanned,
       heap_blks_vacuumed, index_vacuum_count,
       dead_tuple_bytes, max_dead_tuple_bytes, num_dead_item_ids,
       indexes_total, indexes_processed, delay_time
  FROM pg_stat_progress_vacuum;

delay_time (PostgreSQL 18) is only filled when track_cost_delay_timing is on. An autovacuum run logged because log_autovacuum_min_duration was exceeded looks like this (captured on PostgreSQL 18.6; the final system usage: CPU: … line is omitted):

Read it as: only 21.66% of pages were scanned (the rest were all-visible), one index pass was enough, 19 empty tail pages were truncated. "eagerly scanned" is new in PostgreSQL 18 (see eager freezing). VACUUM (VERBOSE) prints the same block.

"dead but not yet removable" greater than zero means some snapshot (a long transaction, an idle-in-transaction session, a replication slot or hot_standby_feedback) still needs those versions. VACUUM cannot fix that; ending the old transaction can.