maintenance_work_mem

maintenance_work_mem is the memory budget for maintenance work rather than ordinary queries — vacuuming, building indexes, and adding foreign keys. More memory lets these operations do more in a single pass, so index builds and vacuums finish noticeably faster. The memory is released as soon as the operation ends.

Because it's short-lived and used by few concurrent operations, you can be generous — often much larger than work_mem (commonly 256 MB to a few GB on a well-resourced server). Note autovacuum workers each draw from autovacuum_work_mem (which defaults to this value), so multiply by autovacuum_max_workers when estimating peak use.

Why it can be generous

Unlike work_mem, which is multiplied by every operation in every connection, maintenance_work_mem is used by a small number of maintenance operations: VACUUM, CREATE INDEX, REINDEX, and ALTER TABLE … ADD FOREIGN KEY. The memory is allocated for the duration of the operation and released the moment it finishes. Because few of these run at once, you can afford to be generous — often 256 MB to a few GB on a well-resourced server.

Where it actually helps

• Index builds keep more of the sort in memory, so CREATE INDEX and REINDEX finish faster with fewer disk merges.
• VACUUM can track more dead tuples per pass, meaning fewer passes over the indexes of a large, bloated table.

The autovacuum caveat

Autovacuum workers don't read maintenance_work_mem directly — they read autovacuum_work_mem, which defaults to -1, meaning "use maintenance_work_mem." So if you set maintenance_work_mem = 2GB and run autovacuum_max_workers = 5, autovacuum alone could use up to ~10 GB at peak.

If that's a concern, set autovacuum_work_mem to a smaller, explicit value so interactive index builds can stay large while autovacuum stays bounded:

maintenance_work_mem = 2GB     -- big index builds
autovacuum_work_mem  = 256MB   -- bounded per autovacuum worker

PostgreSQL 17+

Recent versions improved VACUUM's dead-tuple memory management (a more compact structure), so very large maintenance_work_mem values matter a bit less for vacuuming than they used to — but they still help index builds.

• Index Only Scan

Is it safe to set maintenance_work_mem high?

Generally yes — it's reclaimed when the operation finishes and only a handful of maintenance operations run at once. The main caveat is autovacuum: each worker can use up to autovacuum_work_mem (default = maintenance_work_mem), so account for autovacuum_max_workers.

• autovacuum_work_mem
• work_mem
• autovacuum_max_workers
• vacuum_buffer_usage_limit