maintenance_work_mem and the operations that use it · PostgreSQL

maintenance_work_mem is the allowance for operations that are deliberate rather than continuous, and it is set generously by convention on the reasoning that only a few run at once.

That reasoning is sound. The assumption usually attached to it — that a generous value makes those operations faster — is worth testing.

What uses it

Operation	How the memory is used
`CREATE INDEX`, `REINDEX`	The sort workspace for building the index
`VACUUM`	The collection of dead tuple identifiers
`ALTER TABLE` forms that rewrite	Sorting and building the new relation
`ADD FOREIGN KEY`, `VALIDATE CONSTRAINT`	Verification workspace
`CLUSTER`	Sorting the table into index order

autovacuum_work_mem overrides it for autovacuum workers, and defaults to -1, meaning “use maintenance_work_mem”.

Testing the index build claim

Read-only / Safethe same index built three times at different settings— Captured on 18.6. 2,000,000 rows, parallel maintenance workers disabled so the comparison is single-threaded. Index dropped and rebuilt each time.

$ psql -U postgres -c "SET maintenance_work_mem='NNN'; SET max_parallel_maintenance_workers=0; CREATE INDEX ordersx_amount_idx ON ordersx (amount)"

  maintenance_work_mem=4MB  :  874 ms
maintenance_work_mem=64MB : 1004 ms
maintenance_work_mem=1GB  :  946 ms

No useful difference, and the smallest setting produced the fastest run. The spread is within run-to-run variation.

Where it demonstrably matters

Vacuum, and the figure is in the output you are already reading.

INFO:  finished vacuuming "postgres.public.orders": index scans: 4

From lesson VIII-01: vacuum collects dead tuple identifiers in memory, then scans every index once to remove their entries. If the collection fills before the heap scan finishes, vacuum must stop, scan every index, empty the collection, and resume.

index scans: 4 means every index on that table was read four times. On a table with six indexes totalling 200 GB, that is 800 GB of reading instead of 200 GB.

Check index scans before raising anything

ALTER SYSTEM SET log_autovacuum_min_duration = '1s';
SELECT pg_reload_conf();

Then read the log, or run a supervised vacuum:

VACUUM (VERBOSE) orders;

index scans: 1 — memory is not the constraint on this table. Raising maintenance_work_mem will not help it.
index scans: 4 — it is, and raising it could cut the index reading by 75%.

This is a rare case of a tuning question with a direct, unambiguous diagnostic already being printed. Use it.

The aggregate commitment

maintenance_work_mem is allocated per running maintenance operation, not once.

maintenance_work_mem × (autovacuum_max_workers + concurrent manual operations)

At 4 GB with three autovacuum workers, that is a 12 GB commitment before any manual REINDEX is considered. Add a deployment running two concurrent index builds and it is 20 GB.

Set autovacuum_work_mem separately

This is the reason the two settings exist.

-- generous, for operations you are supervising
ALTER SYSTEM SET maintenance_work_mem = '2GB';

-- modest, for the workers running unattended and concurrently
ALTER SYSTEM SET autovacuum_work_mem = '256MB';

maintenance_work_mem alone applies to autovacuum workers too, so a value chosen for a supervised REINDEX becomes a per-worker allowance for three background processes that may all run at once, on a system that may already be under memory pressure.

Setting autovacuum_work_mem explicitly caps that exposure while leaving the supervised value generous. On any cluster where maintenance_work_mem has been raised above a gigabyte, this should be set.

Parallel index builds

max_parallel_maintenance_workers (default 2) lets CREATE INDEX use workers.

Each worker gets its own share of maintenance_work_mem — the total is divided, not multiplied, so the memory commitment does not grow with worker count. CREATE INDEX CONCURRENTLY makes two passes over the table and waits for older transactions between them, so it is substantially slower than a plain build.

The measurement above disabled parallel workers deliberately, to compare memory settings rather than worker counts.

The 1 GB ceiling that used to exist, and what replaced it

For most of PostgreSQL’s history, vacuum’s collection of dead tuple references was capped at approximately 1 GB regardless of what maintenance_work_mem said. Setting 8 GB gave you 1 GB.

The practical consequence was a hard ceiling on how many dead tuples one vacuum pass could handle, after which additional index scans were unavoidable no matter how much memory the server had. On a very large, very churned table this was a real limit.

PostgreSQL 17 changed both halves of that. From the release notes:

Allow vacuum to more efficiently store tuple references … Additionally, vacuum is no longer silently limited to one gigabyte of memory when maintenance_work_mem or autovacuum_work_mem are higher.

So the same maintenance_work_mem now holds more references, and a larger value is no longer ignored. The release notes do not describe the data structure that replaced the old one, and this lesson does not speculate about it — what matters operationally is the two changes above.

Three things follow for an operator.

Advice about the 1 GB ceiling is version-specific. Guidance written before 17 that says “there is no point setting maintenance_work_mem above 1 GB for vacuum” was correct then and is not now.

index scans: N remains the right diagnostic on every version. The question is not what the theoretical capacity is; it is whether this table’s vacuum needed more than one pass.

Memory use for a given maintenance_work_mem is lower, so a value that was previously fully consumed may now leave headroom. That is a reason to re-check after a major version upgrade rather than to assume the previous value is still right.

Always confirm the behaviour of the version you are running rather than carrying a number forward. The diagnostic is stable across versions even where the implementation is not.

What to take from this

Measured: no useful difference in a 2M-row index build across 4 MB, 64 MB and 1 GB. The benefit is not automatic.
index scans: N in VACUUM VERBOSE is the direct diagnostic. Above 1 means memory is the constraint.
The allowance is per operation. At 4 GB with three workers, that is a 12 GB commitment.
Set autovacuum_work_mem separately whenever maintenance_work_mem is large.
Parallel index build workers divide the allowance; CONCURRENTLY does not use them at all.
Vacuum’s memory structure changed in 17, so pre-17 advice about a 1 GB ceiling no longer applies.

Cross-course references

Linux for Production Sysadmins — Part XXXVII (Resource management) covers bounding a maintenance operation’s resource use at the host level, and Part XL (Memory Performance) covers what the peak actually costs.
Ansible for Production Sysadmins — Part XLVIII (Maintenance windows and rollback) covers setting this for the duration of an operation and putting it back.