skip to content

In PostgreSQL, why can maintenance_work_mem safely be set much larger than work_mem, and when should autovacuum_work_mem be set separately?

level: middleimportance: should knowfreq 42%

answer

  1. one maintenance command per session
  2. VACUUM, CREATE INDEX, ADD FOREIGN KEY
  3. autovacuum workers multiply it
  4. -1 means fall back

basics

~20 s

maintenance_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
ini
# 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 autovacuum

go deeper

for a junior

Know that maintenance_work_mem (64MB by default) is used by VACUUM and CREATE INDEX and is usually set higher than work_mem.

for a middle

Explain why one-command-per-session makes a big value safe, and how autovacuum workers break that rule unless autovacuum_work_mem caps them.

for a senior

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.

for a principal

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.