Memory for maintenance operations (VACUUM, CREATE INDEX).
At a glance
| Property | Value |
|---|---|
| Parameter | maintenance_work_mem |
| Category | Memory |
| Default | 64MB |
| Value type | memory (kB/MB/GB) |
| Change scope | Per-session (SET) |
What it does
Maximum memory used by maintenance operations such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. Only a few such operations run at once, so it can be much larger than work_mem.
How to apply a change
Can be set per session with SET, per role/database with ALTER ROLE/DATABASE ... SET, or globally in postgresql.conf.
Inspect the current value and source with SHOW maintenance_work_mem; or SELECT name, setting, unit, context, source FROM pg_settings WHERE name = 'maintenance_work_mem';.
Tuning guidance
Raise to 256MB-1GB+ to speed up index builds and vacuums. Note autovacuum workers use autovacuum_work_mem if set, otherwise this value times the worker count.