maintenance_work_mem

For DBAs and operators

Used by VACUUM (the dead-tuple TID store), CREATE INDEX / REINDEX (sorting), ALTER TABLE ADD FOREIGN KEY, CLUSTER, and by autovacuum workers unless autovacuum_work_mem is set. A session runs one such command at a time, so it is safe to set much higher than work_mem (256 MB – 2 GB is common).

VACUUM and index passes

VACUUM collects dead TIDs while scanning the heap. When the store reaches the limit it vacuums all indexes, then continues. Each extra pass means reading every index again. The autovacuum log (and VACUUM VERBOSE) shows the number of passes: LOG: automatic vacuum of table "app.public.orders": index scans: 4. Since PostgreSQL 17 the TID store is a compact radix tree, so the same memory holds many more TIDs and the old 1 GB cap for VACUUM no longer applies. Watch it live:

SELECT relid::regclass, phase, index_vacuum_count,
       pg_size_pretty(dead_tuple_bytes) AS used,
       pg_size_pretty(max_dead_tuple_bytes) AS cap
  FROM pg_stat_progress_vacuum;

Pitfalls

• Each autovacuum worker may use the full amount: up to autovacuum_max_workers × this value.
• Parallel CREATE INDEX / VACUUM share one allowance across all workers.
• For index builds, also check pg_stat_progress_create_index; large restores (pg_restore) benefit from a temporarily high value.

How to change it

Any user can change it for their session with SET.