maintenance_work_mem
Memory for VACUUM and CREATE INDEX. Too low: many slow index rebuild passes. Too high × workers: OOM kills the whole cluster.
Tier 2 — Production Guide — Operational guidance is available; evidence scope is stated where relevant.
What We Observed
box
-17 (PG 16-17), mwm_test 500k rows + mwm 2M dead tuples for VACUUM
vacuum passes
- evidence
VACUUM index passes vs maintenance_work_mem (2M dead tuples, 2026-06-19)
- 1MB passes
12
- 2MB passes
6
- 4MB passes
3
- 8MB passes
2
- 16MB passes
1
- note
Exponential improvement: each 2× increase halves the index passes until 1 pass achieved at 16MB
- source file
create index timing
- container
, mwm_test 500k rows
- 1MB ms
959
- 64MB ms
839
- 256MB ms
852
- note
Small table: diminishing returns after 64MB. Large tables see bigger gains.
- source file
buildindex sort method
- evidence
mwm_buildindex.txt (2026-06-19, large table)
- 1MB
external sort (disk), 1365ms
- 128MB
internal sort (memory), 1693ms
- 256MB
internal sort (memory), 1488ms
- note
Internal sort at 128MB still slower than external at 1MB on this table — small table effect; large tables benefit more from internal sort
curve note
threshold behavior: below the threshold that fits index build in memory → many slow passes; above → single in-memory pass.
What Was Expected
- framing
maintenance_work_mem × autovacuum_max_workers is a separate memory pool from work_mem. Setting maintenance_work_mem=200MB with autovacuum_max_workers=3 = 600MB peak just for autovacuum. On a 768MB container: OOMKilled=true.
- formula
peak_vacuum_mem = maintenance_work_mem × autovacuum_max_workers
- example
200MB × 3 workers = 600MB → exceeds 768MB container → OOMKilled=true
- lab evidence
- container spec
768MB cap (from mwm_oom_inspect.txt)
- result
OOMKilled=true Status=running ExitCode=0
- log line
server process (PID 125) was terminated by signal 9: Killed
- source file
- source file
Common Pitfalls
- Treating maintenance_work_mem as a GLOBAL cap — it is per-operation; multiply by concurrent ops, especially autovacuum_max_workers (default 3).
- Setting it very high in postgresql.conf on a box with many tables and active autovacuum — 3 concurrent autovacuum workers each grab it and OOM the instance (reproduced: 8× builds bounced the whole server).
- Forgetting autovacuum uses it (autovacuum_work_mem=-1 by default) — a value chosen for one-off index builds silently triples under autovacuum.
- On PG 17+, raising it hoping to cut VACUUM index passes — the TidStore already does one pass at tiny memory; the lever now mainly speeds index-build sorts.
- Over-provisioning past the operation's sort size — build speed is FLAT once it fits in memory (lab: identical at 128 MB and 256 MB); extra is wasted RAM.
- Expecting an in-memory sort with parallel index builds on — parallel CREATE INDEX always spills to tapes; disable parallel maintenance if you specifically want the in-memory path.
- Assuming 'SET maintenance_work_mem=.; VACUUM .' works in one statement — it errors ('VACUUM cannot run inside a transaction block'); set it separately or via PGOPTIONS.
Tuning Guidance
Unlock Incident Pro
The free sections tell you what to do. Pro is the measured proof — the exact failure signature, safe-ceiling formula, and per-workload defaults, each captured on real PG 14–18 runs.
- Tuning Guidance
maintenance_work_mem is the per-operation memory CEILING for PostgreSQL's heavyweight maintenance work: building or rebuilding indexes (CREATE INDEX, REINDEX), the dead-tuple bookkeeping in VACUUM, table rewrites (some ALTER TABLE), and foreign-key validation. It defaults to 64 MB — 16x the 4 MB query work_mem default — because these operations are infrequent but benefit from a big sort/bookkeeping buffer. A LARGER value lets an index build keep its whole sort in memory (instead of spilling to temp files) and lets VACUUM remember more dead tuples per heap pass (so it scans each index fewer times). The critical word is PER-OPERATION: this is NOT a global budget. Every concurrent maintenance operation — a manual CREATE INDEX you run, plus EACH of the up-to-autovacuum_max_workers autovacuum workers — can allocate up to maintenance_work_mem of its own. So the real memory exposure is maintenance_work_mem multiplied by how many maintenance operations run at once, and setting it too high turns concurrent maintenance into an out-of-memory event.
- name
maintenance_work_mem
- setting default
65536
- unit
kB (65536 kB = 64 MB default)
- vartype
integer
- category
Resource Usage / Memory
- context
user
- min val
64 on PG 17-18; 1024 on PG 14-16 (the minimum was lowered from 1 MB to 64 kB in PG 17)
- max val
2147483647
- boot val
65536
- reset val
65536
- pending restart
false
- short desc
Sets the maximum memory to be used for maintenance operations.
- ground truth note
Captured literally from pg_settings on PG 14-18 (-18) on 2026-06-19. setting=65536, boot_val=65536, reset_val=65536 (= 64 MB) on EVERY version — default_changed=false, boot_fallback_changed=false. The ONE thing that differs across versions is min_val: 1024 (1 MB) on PG 14/15/16, lowered to 64 (64 kB) on PG 17/18. context=user, vartype=integer, unit=kB, max_val=2147483647 everywhere.
Default 65536 kB (64 MB) and context=user, vartype=integer, unit=kB, max 2147483647 are IDENTICAL on PG 14, 15, 16, 17, 18. The only version difference is min_val: PG 14/15/16 = 1024 kB (1 MB) floor, PG 17/18 = 64 kB floor. The far more consequential 14→18 change is not in pg_settings at all: PG 17 replaced VACUUM's dead-tuple storage (old fixed-size 6-byte-per-TID array, hard-capped at ~1 GB) with a compact TidStore radix tree — which drastically reduces how much maintenance_work_mem VACUUM needs to avoid multiple index passes (see _guc_effect F2). Per-version detail in _guc_matrix_machine.
context=user is the most flexible scope PostgreSQL offers — no reload, no restart, and a non-superuser can change it for their own session. Best practice is a MODERATE global value plus per-operation overrides: (1) SET maintenance_work_mem = '2GB'; right before a big CREATE INDEX / pg_restore / bulk load, so just that session gets the large sort buffer. (2) PGOPTIONS="-c maintenance_work_mem=2GB" psql . to inject at connect time (how this lab drove every measurement — note that 'SET maintenance_work_mem=.; VACUUM .' in ONE statement fails with 'VACUUM cannot run inside a transaction block', so set it separately or via PGOPTIONS). (3) ALTER ROLE loader SET maintenance_work_mem = '2GB'; to scope it to an ETL role. (4) maintenance_work_mem = '256MB' in postgresql.conf (or ALTER SYSTEM) + SELECT pg_reload_conf; for the server default — keep THIS value conservative because it is multiplied by concurrent autovacuum workers. The change takes effect on the next maintenance operation. BOUNDARY PROOF (captured literal): an out-of-range / invalid value is rejected at set time -- ALTER SYSTEM SET maintenance_work_mem = 63; -> ERROR: 63 kB is outside the valid range for parameter "maintenance_work_mem" (64 kB .. 2147483647 kB). It is context=user, so a plain SET maintenance_work_mem = ... in a session succeeds (session-scoped, no restart); change it cluster-wide via postgresql.conf or ALTER SYSTEM + reload.
References
- PostgreSQL 18 docs — Resource Consumption / Memory: maintenance_work_mem, autovacuum_work_mem (runtime-config-resource.html)
- PostgreSQL 18 docs — Automatic Vacuuming: autovacuum_max_workers (runtime-config-autovacuum.html)
- PostgreSQL 17 release notes — VACUUM dead-tuple storage replaced with TidStore (radix tree), removing the prior ~1 GB maintenance_work_mem cap and the multi-pass-at-low-memory behavior
- PostgreSQL source — src/backend/access/heap/vacuumlazy.c and src/backend/access/common/tidstore.c (dead-TID storage); src/backend/utils/sort/tuplesort.c (trace_sort, external vs internal sort)
- PostgreSQL 18 docs — CREATE INDEX (maintenance_work_mem and parallel index builds), VACUUM
- Lab ground truth: pg_settings on -18 , 2026-06-19 — default 65536 kB (64 MB) unchanged 14→18; min_val 1024→64 at PG 17
- Lab captures: params/maintenance_work_mem/captures/ (mwm_meta, mwm_passes, mwm_versions, mwm_buildindex, mwm_oom_*)
Technical Reference
- Category
- Resource Usage / Memory
- Type
- integer (kB (65536 kB = 64 MB default))
- Blast radius
- cluster
- Severity
- High
- Context
- user
- Versions
- 14, 15, 16, 17, 18
- Reviewed
- 2026-06-19
Severity scale: 1 Minimal · 2 Low · 3 Moderate · 4 High · 5 Critical.