Why your index build took four hours — maintenance_work_mem explained

This is the third piece in a series on PostgreSQL memory. Issue #1 covered shared_buffers. Issue #2 covered work_mem and the concurrency math that makes it dangerous to raise globally. Today: maintenance_work_mem, and why your index build on that 500GB table took four hours instead of forty minutes.

The scenario

A team runs CREATE INDEX CONCURRENTLY on a large events table over the weekend. The table is 500GB. The query plan looks fine. The server has 128GB of RAM. shared_buffers is tuned. work_mem is set to a conservative 32MB — reasonable, given the concurrency math from last issue.

The index build takes four and a half hours. Someone checks the server: CPU is mostly idle, disk I/O is moderate, Postgres is not struggling. It's just... slow.

The culprit is one line in postgresql.conf that nobody touched:

maintenance_work_mem = 64MB

Misconception #1: "maintenance_work_mem is just like work_mem — I'll set them the same."

This is the most natural assumption and almost always wrong. work_mem and maintenance_work_mem look similar but operate in completely different regimes.

work_mem is dangerous to raise globally because it multiplies — every concurrent query, every parallel worker, every sort and hash operation all draw from the same budget simultaneously. The math from issue #2: on a busy server, you can exhaust memory fast.

maintenance_work_mem doesn't multiply the same way. It's consumed by maintenance operations — VACUUM, CREATE INDEX, REINDEX, CLUSTER, ALTER TABLE — which are mostly single-threaded, serialized, and deliberately not running hundreds at a time. The number of autovacuum workers is bounded by autovacuum_max_workers (default: 3). You control when you run CREATE INDEX. The concurrency risk is fundamentally different.

The practical consequence: maintenance_work_mem is the one place in PostgreSQL memory tuning where the right answer is often much larger than you'd think. The default is 64MB. On a server with 128GB of RAM, the right value might be 2GB or 4GB. That's not a typo.

Misconception #2: "Autovacuum is running, so vacuum is working."

This is the one that hides for months.

Autovacuum running and autovacuum doing useful work are not the same thing. The setting that controls how efficiently vacuum can reclaim dead tuples is maintenance_work_mem — specifically, it controls how large a chunk of the dead tuple map vacuum can hold in memory at once.

When maintenance_work_mem is too small for the table being vacuumed, vacuum has to make multiple passes over the table to process all the dead tuples. On a 500GB table with heavy write load and 64MB of maintenance_work_mem, vacuum might need a dozen passes where a well-tuned server would need one. Each pass reads the entire table. On a table that size, that's the difference between vacuum finishing in 10 minutes and running for two hours — and potentially getting canceled before it finishes, leaving bloat untouched.

This is also why pg_stat_user_tables can show last_autovacuum with a recent timestamp, everything looks fine, and the table is still accumulating bloat. Autovacuum ran. It just didn't finish the job in one pass.

The diagnostic that actually matters

First, check your current value and what maintenance operations are doing with it:

SELECT name, setting, unit
FROM pg_settings
WHERE name IN (
  'maintenance_work_mem',
  'autovacuum_vacuum_cost_delay',
  'autovacuum_max_workers'
);

Then look at which tables are showing signs of vacuum struggling:

SELECT
  schemaname,
  relname,
  n_dead_tup,
  n_live_tup,
  round(n_dead_tup * 100.0 / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC
LIMIT 20;

A table with high dead_pct and a last_autovacuum timestamp from hours ago is the signature of vacuum running but not keeping up. maintenance_work_mem is the first thing to check.

For index builds specifically, there's no direct visibility into how much memory the sort phase is using — but the timing tells you. A CREATE INDEX on a large table that takes much longer than your disk throughput would predict is almost always memory-constrained. The index build sorts data in memory and writes runs to disk; with more maintenance_work_mem, you get larger in-memory runs, fewer merge passes, and dramatically faster builds.

The empirically reasonable range

The default of 64MB is appropriate for servers with very little RAM or many concurrent maintenance operations. For most production servers:

• 512MB to 1GB: reasonable starting point for servers with 16–64GB RAM
• 1GB to 4GB: appropriate for servers with 64GB+ RAM running large tables
• Hard ceiling: stay below 25% of total RAM, accounting for autovacuum_max_workers running simultaneously

The math for autovacuum: worst case memory for maintenance = autovacuum_max_workers × maintenance_work_mem. With 3 workers and 2GB each, that's 6GB committed to autovacuum at peak. On a 128GB server, that's fine. On a 16GB server, size accordingly.

One important nuance: maintenance_work_mem in postgresql.conf is the global default, including for autovacuum workers. If you want to give manual VACUUM runs more memory without changing the autovacuum budget, set it per session:

SET maintenance_work_mem = '4GB';
VACUUM (VERBOSE, ANALYZE) large_table;

This is especially useful for one-time bloat recovery on a table that got into bad shape — give that manual run all the memory it needs, without permanently changing the autovacuum profile.

The mental model

maintenance_work_mem is the one PostgreSQL memory setting where you should be generous without guilt. It doesn't multiply with concurrency the way work_mem does. The operations that use it are bounded and controlled. Undersizing it doesn't make your server safer — it just makes vacuum slower, index builds longer, and bloat harder to reclaim.

The question isn't "is 2GB too much?" It's "how long are you willing to wait for your index builds, and how much dead tuple debt are you willing to carry?"

Next in the series

Issue #4 will go deeper on autovacuum itself — the threshold math that determines when it fires, why it ignores your biggest tables, and the per-table overrides that actually fix it. maintenance_work_mem gets vacuum into shape; autovacuum tuning keeps it there.

Reply and tell me: what's your maintenance_work_mem value, and have you ever watched an index build crawl on a table you knew should be fast? I read every reply.

— Gio

P.S. If this email landed in your Promotions tab, dragging it to Primary tells Gmail to put future issues there too. Small thing, makes a real difference.