In PostgreSQL, why can maintenance_work_mem safely be set much larger than work_mem, and when should autovacuum_work_mem be set separately?
answer
- one maintenance command per session
- VACUUM, CREATE INDEX, ADD FOREIGN KEY
- autovacuum workers multiply it
- -1 means fall back
basics
~20 smaintenance_work_mem (default 64MB) serves VACUUM, CREATE INDEX and ADD FOREIGN KEY, and a session runs only one such command at a time. Autovacuum may use it once per worker, so autovacuum_work_mem caps those workers separately.
solid answer
~40 s`maintenance_work_mem` (default `64MB`) is the budget for maintenance commands such as `VACUUM`, `CREATE INDEX` and `ALTER TABLE ... ADD FOREIGN KEY`. A session can run only one of them at a time and few run concurrently, so the manual calls it safe to set much higher than `work_mem`; it speeds index builds, dump restores and vacuum's dead-tuple collection. The catch is autovacuum: each of up to `autovacuum_max_workers` (default 3) workers may take the full amount. `autovacuum_work_mem` (default `-1`, meaning use `maintenance_work_mem`) caps those workers on their own, so you can give a manual 4GB index build its memory without letting three background workers each claim 4GB. It is reloadable and does not affect a manual `VACUUM`. Since 17 vacuum can use more than 1 GB of it.
code
ini · 4 lines# postgresql.conf, 64 GB logistics server
maintenance_work_mem = 1GB # manual VACUUM, CREATE INDEX, restores
autovacuum_work_mem = 512MB # each autovacuum worker; reload to apply
autovacuum_max_workers = 3 # default: peak about 1.5GB for autovacuumgo deeper
Know that maintenance_work_mem (64MB by default) is used by VACUUM and CREATE INDEX and is usually set higher than work_mem.
Explain why one-command-per-session makes a big value safe, and how autovacuum workers break that rule unless autovacuum_work_mem caps them.
Show you budget autovacuum_max_workers times autovacuum_work_mem against the host, raise maintenance memory per session for big builds, and know the 17 change.
Weigh faster vacuum and index builds against peak memory overlapping with reporting load, and set policy for who may raise maintenance memory when.
## What maintenance_work_mem is for `maintenance_work_mem` is the memory budget for **maintenance operations**. The manual names `VACUUM`, `CREATE INDEX` and `ALTER TABLE ADD FOREIGN KEY`; in practice the setting matters most for: - **VACUUM**, which keeps the identifiers of dead tuples it found in memory and then removes the matching index entries; a larger budget means fewer passes over every index of a big table. - **CREATE INDEX** and `REINDEX`, which sort the index entries before building the B-tree; with enough memory the sort avoids temporary files. - **Restoring a dump**, which rebuilds every index and foreign key, so a session-level increase shortens `pg_restore` noticeably. The default is `64MB`, and a unitless value is read as kilobytes. ## Why it can be much larger than work_mem `work_mem` is multiplied by every sort and hash node, every parallel worker and every active session. `maintenance_work_mem` is not: a session can execute **only one maintenance command at a time**, and an installation normally has few running at once. The manual therefore calls it safe to set the value significantly larger than `work_mem`. Parallel utility commands keep that property. A parallel `CREATE INDEX` treats `maintenance_work_mem` as a limit for the **whole command**, regardless of the number of workers, which is the opposite of parallel queries, where `work_mem` applies to each worker individually. ## The autovacuum exception Autovacuum breaks the one-per-session reasoning, because it runs several worker processes at once: | setting | default | context | applies to | |---|---|---|---| | `maintenance_work_mem` | `64MB` | user | manual VACUUM, index builds, restores, and autovacuum if not overridden | | `autovacuum_work_mem` | `-1` | reload | each autovacuum worker; `-1` falls back to `maintenance_work_mem` | | `autovacuum_max_workers` | `3` | reload | how many workers may run at once | With `maintenance_work_mem = 4GB` and the default `autovacuum_work_mem = -1`, three autovacuum workers on large tables may each take up to 4GB, a 12GB peak that nobody asked for. Setting `autovacuum_work_mem = 1GB` lets an operator still run `SET maintenance_work_mem = '4GB'` before a big `CREATE INDEX` while background vacuum stays bounded. Two details matter: 1. `autovacuum_work_mem` can only be set in `postgresql.conf` or on the server command line, and a reload applies it. 2. It has **no effect on a manual VACUUM**, which uses the session's `maintenance_work_mem`. ## The 1 GB cap that 17 removed Up to PostgreSQL 16, vacuum stored dead-tuple identifiers in a simple array and was **silently limited to 1 GB** even when `maintenance_work_mem` or `autovacuum_work_mem` were set higher; extra memory only helped index builds and other operations. PostgreSQL 17 replaced that array with a more compact structure (the release notes call it storing tuple references more efficiently) and removed the cap, so on 17 and 18 a vacuum of a very large table can really use a multi-gigabyte budget, and needs fewer index passes even at the same setting. ## Sizing it 1. Keep the server-wide `maintenance_work_mem` moderate, because it is also what every autovacuum worker inherits unless overridden. 2. Set `autovacuum_work_mem` explicitly so `autovacuum_max_workers x autovacuum_work_mem` fits the host's memory budget. 3. Raise `maintenance_work_mem` per session for the big one-off jobs: an index build on the `shipments` table, a restore, a manual vacuum after a mass delete. 4. Remember it adds to the peak in the same window as reporting queries' `work_mem`. ## Mistakes that show up in interviews - **Treating it like `work_mem`.** It is not multiplied by sort nodes or connections; the multiplier that matters is the number of autovacuum workers plus any manual maintenance running at the same time. - **Expecting `autovacuum_work_mem` to cover manual runs.** A `VACUUM` typed in psql, or run by a scheduled job, uses that session's `maintenance_work_mem`. - **Restarting for it.** Both `autovacuum_work_mem` and `autovacuum_max_workers` are applied by a reload; `maintenance_work_mem` can simply be set per session. - **Confusing it with `vacuum_buffer_usage_limit`.** That setting (default 2MB) sizes the ring of shared buffers vacuum may cycle through; it has nothing to do with remembering dead tuples. - **Quoting the 1 GB cap on 18.** It was a real limit up to 16 and is gone from 17 on. When autovacuum runs, how often, and with which thresholds is autovacuum's own subject; this leaf only owns how much memory it may take.
- On PostgreSQL 16, does raising maintenance_work_mem from 1GB to 8GB make VACUUM faster?Not for vacuum: up to 16 vacuum's dead-tuple storage was silently limited to 1 GB, so the extra memory went unused there, although index builds and restores still benefit. From 17 the limit is gone and dead-tuple storage is also more compact, so a vacuum of a large table can use the full budget and make fewer passes over its indexes.
- A parallel CREATE INDEX runs with 4 workers and maintenance_work_mem = 2GB. What is the memory limit?About 2GB for the whole command. Parallel utility commands apply `maintenance_work_mem` to the entire operation, regardless of how many workers it uses; this is the opposite of parallel queries, where each worker applies `work_mem` on its own. The parallel build costs more CPU and I/O, not several times the memory.
- maintenance_work_mem multiplies by max_connections the same way work_mem does.
- autovacuum_work_mem also limits a manual VACUUM started from psql.
- Changing autovacuum_work_mem requires a full server restart.
- On PostgreSQL 18, vacuum still ignores any memory above 1 GB.
- vacuum_buffer_usage_limit is the memory vacuum uses to remember dead tuples.
Assumes PostgreSQL 18; since 17, vacuum can use maintenance_work_mem or autovacuum_work_mem above 1 GB, which 16 and older silently capped.