maintenance_work_mem
Sets the maximum memory to be used for maintenance operations.
How you change it
Any session (SET)
Defaults and ranges on this page were read from pg_settings on live PostgreSQL 18.4. Treat older majors as needing their own check before you copy a value.
This includes operations such as VACUUM and CREATE INDEX.
What this means
Sets the memory ceiling for maintenance operations -- CREATE INDEX, VACUUM, ALTER TABLE ADD FOREIGN KEY, and similar. Unlike work_mem it is used by one maintenance task at a time per session, so a larger value here is far safer than the same increase on work_mem. Index builds and vacuum's dead-tuple collection both go faster when they can stay in memory instead of spilling.
When it matters
Raise well above the default before large index builds, bulk loads, or when autovacuum is slow on big tables.
| Default | 64 MB |
|---|---|
| Range | 64 – 2147483647 |
| Type | integer |
| Change scope | Any session (SET) |
| Category | Memory |
SHOW maintenance_work_mem;Tradeoffs
- • Too low: index builds and VACUUM spill to temp files and run slower.
- • Too high with many concurrent autovacuum workers: each worker can use up to autovacuum_work_mem (which defaults to this value), so multiply by the worker count when budgeting RAM.
- • A single session doing a one-off CREATE INDEX can safely SET it high for that session only.
Workload profiles
Bulk load / index rebuild
Set 1-2GB per session for the duration of the build.
Use SET LOCAL so it reverts automatically.
Steady-state OLTP
A few hundred MB is usually plenty.
Remember autovacuum workers inherit it via autovacuum_work_mem.