What VACUUM does · PostgreSQL

Part VII established why dead versions exist. This part is about the process that removes them, which is the single largest source of operational work on a PostgreSQL cluster.

Start by discarding the mental model that vacuum is periodic tidying you could skip on a quiet week. Vacuum is the mechanism that makes the storage design viable. Without it a table grows without bound, indexes never reclaim entries, index-only scans stop working, and the cluster eventually refuses to accept transactions.

What one pass does

A single VACUUM on a table does five things.

Removes dead tuples whose deleting transaction is older than the horizon, and marks their line pointers reusable.
Removes the matching index entries for those tuples, in every index. This is the expensive part, and it requires a full scan of each index unless there was nothing to remove.
Updates the free space map so the reclaimed space is advertised.
Updates the visibility map, setting all-visible and, where the transaction ids are old enough, all-frozen.
Freezes old tuples, advancing the table’s relfrozenxid.

Then, and only under one specific condition, it truncates the file.

The condition

Two tables, identical: 200,000 rows of a 200-byte payload, 46 MB each. 100,000 rows deleted from each. The only difference is which rows.

Read-only / Safecase A: the deleted rows are a contiguous tail— Captured on 18.6. DELETE FROM vac_demo WHERE id > 100000, then VACUUM VERBOSE.

$ psql -U postgres -c "DELETE FROM vac_demo WHERE id > 100000" -c "VACUUM (VERBOSE) vac_demo"

INFO:  vacuuming "postgres.public.vac_demo"
INFO:  launched 1 parallel vacuum worker for index vacuuming (planned: 1)
INFO:  table "vac_demo": truncated 5883 to 2942 pages
INFO:  finished vacuuming "postgres.public.vac_demo": index scans: 1
pages: 2941 removed, 2942 remain, 2942 scanned (50.01% of total), 0 eagerly scanned
tuples: 100000 removed, 99989 remain, 0 are dead but not yet removable
index "vac_demo_pkey": pages: 551 in total, 271 newly deleted, 271 currently deleted, 0 reusable
index "vac_demo_payload_idx": pages: 239 in total, 115 newly deleted, 115 currently deleted, 0 reusable

-- 46 MB -> 23 MB

Read-only / Safecase B: the deleted rows are scattered— Same table definition, same row count deleted. DELETE FROM vac_scatter WHERE id % 2 = 0.

$ psql -U postgres -c "DELETE FROM vac_scatter WHERE id % 2 = 0" -c "VACUUM (VERBOSE) vac_scatter"

pages: 0 removed, 5883 remain, 5883 scanned (100.00% of total), 0 eagerly scanned
tuples: 100000 removed, 100000 remain, 0 are dead but not yet removable
index "vac_scatter_pkey": pages: 551 in total, 0 newly deleted, 0 currently deleted, 0 reusable

-- 46 MB -> 46 MB

Truncation removes a contiguous run of empty pages at the end of the relation, and nothing else. Case A left 2,941 empty pages in a row at the end. Case B left every page half full and none empty.

Both vacuums succeeded. Both removed exactly 100,000 dead tuples. One returned 23 MB to the filesystem and the other returned nothing, and neither did anything wrong.

Note the index lines too. Case A removed 271 index pages; case B removed zero, because a half-empty index page is not an empty index page.

Why this is the right design, not a limitation

Truncating a relation requires an ACCESS EXCLUSIVE lock — the same lock that made a plain SELECT fail in lesson VIII-06’s measurement of VACUUM FULL. Routine vacuum takes that lock only briefly, only to truncate, and gives up quickly if it cannot get it.

Deferring to that constraint is correct. A maintenance process that stopped all access to a table in order to reclaim space would be unusable on production, and a table under steady churn does not benefit from shrinking anyway: it would grow straight back.

The design point is that a table under a stable workload reaches a stable size and stays there. Space is recycled rather than returned, and that is cheaper for everyone.

In PostgreSQL 18 the behaviour is controllable directly: vacuum_truncate is a server variable as well as a per-table storage parameter, and setting it off suppresses the truncation attempt entirely — useful on a standby-heavy cluster, where the brief exclusive lock replays as a conflict that can cancel queries on replicas.

Reading VERBOSE output

Every line answers a question you will eventually need answered.

pages: 2941 removed, 2942 remain, 2942 scanned (50.01% of total), 0 eagerly scanned

removed is truncation. scanned against of total shows how much the visibility map let it skip — a low percentage on a large table means the map is doing its job. eagerly scanned is new in PostgreSQL 18: normal vacuums now freeze some all-visible pages opportunistically, governed by vacuum_max_eager_freeze_failure_rate, to spread the cost of freezing rather than concentrating it in one aggressive pass.

tuples: 100000 removed, 99989 remain, 0 are dead but not yet removable

The third number is the one from lesson VII-05. Anything other than zero means something is holding the horizon.

removable cutoff: 683708, which was 1 XIDs old when operation ended

Vacuum stating its own horizon. When this is far behind the current transaction id, you are looking at the problem directly.

index scan needed: 2942 pages from table (50.01% of total) had 100000 dead item identifiers removed
index "vac_demo_pkey": pages: 551 in total, 271 newly deleted, 271 currently deleted, 0 reusable

index scan needed versus index scan not needed is the single largest determinant of how long a vacuum takes. Removing index entries requires reading each index in full.

WAL usage: 9991 records, 2 full page images, 1184722 bytes, 0 buffers full

Vacuum generates WAL. On a large freeze this dominates, as the next lesson but four measures.

VACUUM does not lock out readers or writers, but it does queue behind DDL

Routine VACUUM takes a SHARE UPDATE EXCLUSIVE lock. It is compatible with SELECT, INSERT, UPDATE and DELETE, so ordinary traffic is unaffected.

It is not compatible with ALTER TABLE, CREATE INDEX (non concurrently), REINDEX, CLUSTER, VACUUM FULL, or another vacuum on the same table. Two consequences follow.

A long-running vacuum delays a deployment that includes DDL on that table. And, more awkwardly, a DDL statement waiting for the lock queues ahead of subsequent ordinary queries, so a five-minute vacuum plus one ALTER TABLE can stall an entire table’s traffic. Part IX covers the lock queue in full.

Autovacuum yields: if it is holding the lock and something else needs a conflicting one, the worker is cancelled and logs canceling autovacuum task. An anti-wraparound autovacuum does not yield, which is covered in lesson VIII-05.

Manual vacuum still has a place

Autovacuum handles the routine case, and lesson VIII-02 covers it. Run vacuum by hand for these:

After a bulk load. VACUUM ANALYZE gives the planner statistics and builds the visibility map, which lesson VI-04 measured as worth 645 buffers against 57 on one query.

After a large delete. Autovacuum will get to it, on its own schedule, throttled. If the delete was a tail and you want the space back today, vacuum it now.

Before a maintenance window closes. A table you know is behind is better vacuumed under your supervision than at 09:15 on Monday.

With VERBOSE, to diagnose. It is the cheapest way to find out what vacuum thinks about a table.

VACUUM (VERBOSE, ANALYZE) orders;

-- 18 syntax: several options combine
VACUUM (VERBOSE, ANALYZE, PARALLEL 4, BUFFER_USAGE_LIMIT '256MB') orders;

Manual VACUUM runs with vacuum_cost_delay = 0 by default, so it is not throttled the way autovacuum is. That makes it fast and makes it capable of saturating your storage. On a production system during traffic, set a delay for the session first:

SET vacuum_cost_delay = '2ms';
VACUUM (VERBOSE) orders;

Why maintenance_work_mem decides how many index passes you get

Vacuum collects the identifiers of dead tuples in memory, then scans the indexes once to remove entries for all of them. If the collection fills the available memory before the heap scan finishes, vacuum must stop, scan every index, empty the collection, and resume the heap scan.

That is what index scans: 1 in the VERBOSE output is reporting. A value of 1 means one pass over each index. A value of 4 means the indexes were read four times.

The memory available is maintenance_work_mem, or autovacuum_work_mem if that is set to something other than -1. The default maintenance_work_mem is 64 MB.

The operational shape: on a large table with several indexes and a large number of dead tuples, raising maintenance_work_mem can turn a four-pass vacuum into a one-pass vacuum, cutting the index reading by 75%. It is one of the few settings where a large increase is straightforwardly justified — but note it is allocated per running maintenance operation, so maintenance_work_mem of 4 GB with three autovacuum workers is a 12 GB commitment.

autovacuum_work_mem exists precisely so the two can be set differently: generous for a manual reindex or vacuum you are supervising, modest for the three workers running unattended.

Check index scans: in VERBOSE output before raising anything. If it already says 1, memory is not your constraint.

What to take from this

Vacuum removes dead tuples, cleans indexes, updates both maps and freezes. Truncation is a separate, conditional step.
Truncation only removes empty pages at the end of the relation. Identical delete volumes produce completely different outcomes.
index scan needed versus not needed dominates vacuum duration.
index scans: N greater than 1 means maintenance_work_mem forced extra passes over every index.
Routine vacuum does not block ordinary queries, but conflicts with DDL, and a queued DDL statement blocks everything behind it.
Manual vacuum is not throttled by default. Set vacuum_cost_delay before running one on a busy system.

Cross-course references

Linux for Production Sysadmins — Part XLI (Storage Performance) covers measuring the read and write cost a vacuum imposes, and Part XXXVII (Resource management) covers bounding it with cgroups when the in-database throttle is not enough.
Observability for Production Sysadmins — Part LIX (Database observability) covers exporting vacuum activity as a series.