Steampunk-style illustration of ducks operating an industrial factory with copper pipes, steam vents, pressure gauges, and hanging pendant lights.

maintenance_work_mem is the memory parameter that behaves the way people wrongly assume work_mem does: one allocation, for one operation, in one session, and the number you set is the number that gets used. work_mem is a per-sort, per-hash, per-worker allowance that a single query can claim a dozen times over. maintenance_work_mem is a ceiling. The docs say it is “safe to set this value significantly larger than work_mem,” which is one of the few places they give you permission to be generous, and they are mostly right. This post is about the “mostly.”

Default 64MB, context user, so you can SET it in a session, pin it to a role or a database with ALTER ROLE ... SET or ALTER DATABASE ... SET, or hand it to a client through PGOPTIONS. The floor is 64kB; the ceiling is a hair under 2TB on 64-bit builds. It got this name in 8.0, when vacuum_mem was renamed to admit that index builds and foreign-key checks used it too, with a default of 16MB. 9.4 quadrupled that to 64MB in 2014, and nobody has touched it since. On an 8GB machine, 64MB was a fair slice. On the 256GB servers PostgreSQL routinely runs on now, it is a rounding error.

The docs’ list of consumers (VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY) is accurate but abbreviated. In the source, maintenance_work_mem is the budget for:

  • The dead-TID store VACUUM fills during its heap pass, whether the vacuum is manual or an autovacuum worker with autovacuum_work_mem left at -1. When the store fills, VACUUM has to stop and make a complete pass over every index; the multi-pass story, and the PostgreSQL 17 TIDStore that ended the old 1GB cap, is in autovacuum_work_mem.
  • The sort behind every index build that sorts: B-tree, hash, BRIN, GIN, and GiST (presorted since 14 where the opclass allows it, buffered otherwise; both paths spend this). GIN also uses it as the size of the in-memory accumulator it fills before flushing entries into the index, so a smaller budget means more flushes.
  • CLUSTER, when it decides to sort rather than walk the index. (VACUUM FULL never sorts and doesn’t spend it.)
  • Foreign-key validation, at ADD FOREIGN KEY or VALIDATE CONSTRAINT. The check is an ordinary query, so RI_Initial_Check temporarily sets work_mem to maintenance_work_mem for the duration and pins hash_mem_multiplier to 1, because letting a multiplier loose on the maintenance budget would be alarming.
  • Extensions that build big in-memory structures. pgvector’s HNSW build keeps the graph in maintenance_work_mem and tells you, with a NOTICE, the moment it stops fitting.

ANALYZE is not on the list. Its sample is sized by default_statistics_target, and it uses whatever that costs.

Where the ceiling holds, and where it doesn’t

Parallel utility commands are the pleasant surprise. Parallel query multiplies work_mem by the number of workers; parallel CREATE INDEX does the opposite, handing the leader and each worker an even share of one maintenance_work_mem, so the total never exceeds what you set. The catch is the planner’s arithmetic. plan_create_index_workers refuses to give any participant less than 32MB, and it counts the leader as a participant. At the default 64MB that is two participants: the leader plus exactly one worker, whatever max_parallel_maintenance_workers says. 96MB buys a second worker, 128MB a third. (The one way around the arithmetic is the table’s parallel_workers storage parameter, which the planner takes at face value without looking at memory.) I checked this on 18.6 with a 261MB table and max_parallel_maintenance_workers = 4: one worker at 64MB, two at 96MB, three at 128MB, four at 1GB. The default doesn’t merely undersize the sort; it quietly switches off most of the parallelism you thought you had turned on.

Autovacuum is where the ceiling breaks. Every worker gets its own copy, so the real budget is maintenance_work_mem × autovacuum_max_workers, and a global raised for fast index builds becomes something three (or ten) background processes can claim at once. That is the entire reason autovacuum_work_mem exists. Set it, and this parameter goes back to meaning one thing.

The other multiplier is pg_restore -j. Eight jobs is eight sessions, each building indexes with the full budget; PGOPTIONS="-c maintenance_work_mem=2GB" pg_restore -j 8 is a request for 16GB.

What bigger buys you

Less than the folklore says, for the case everyone thinks of first. A serial B-tree build does not get faster with more memory. External sorting has been quicksorted runs plus a merge since 9.6, and quicksorting 64MB runs and merging them is kinder to the CPU cache than quicksorting a gigabyte in one go. On my test box, a 4M-row index on a text column built in 5.5 seconds at 64MB and 7.7 seconds at 1GB, repeatably; Peter Geoghegan reported the same non-effect on -hackers while writing parallel tuplesort. Raising the global to 4GB because the docs said it was safe mostly buys you parallel workers. That’s a fine reason. It isn’t the one people give.

Where more memory is decisive is the structures that either fit or don’t. The same default that ran a B-tree build fine turned a 200k-row, 256-dimension HNSW build from 17 seconds into 64; pgvector announced at tuple 38,590 that the graph no longer fit and things were about to get slow. Foreign-key validation on a large child table gets a hash join that doesn’t batch to disk. GIN builds flush less often, which in my tests was worth a few percent, not a multiple. And on PostgreSQL 16 and earlier, vacuum of a badly bloated table gets one index pass instead of several, up to the 1GB cap; on 17 and later the TIDStore needs so much less that memory is rarely the reason for a second pass anymore.

The diagnostics are ordinary. An index build whose sort spilled appears under log_temp_files as a pgsql_tmp file about the size of the sort. A vacuum that ran out shows index_vacuum_count > 1 in pg_stat_progress_vacuum, with max_dead_tuple_bytes (max_dead_tuples before 17) telling you what the budget was. HNSW tells you in the log. And if you want to know how many workers a build received, count backend_type = 'parallel worker' in pg_stat_activity while it runs; the PG 18 parallel_workers_launched column in pg_stat_database counts queries only, and a CREATE INDEX is not a query.

So: set the global to something the server can afford several copies of. Somewhere between 256MB and 1GB, scaled to RAM (RDS defaults to roughly 1.6% of instance memory, which is the right order of magnitude), and set autovacuum_work_mem explicitly so the global stops multiplying. Then, in the session that is about to build the HNSW index or restore the dump, SET maintenance_work_mem = '4GB' and let it go, remembering that -j 8 means eight of them. The global is for the workers you don’t watch. The session setting is for the build you do.