Why your sort spilled to disk — and what to do about it

This is the second piece in a series on PostgreSQL memory. The first covered shared_buffers and why bumping it to 75% is the wrong instinct. Today: work_mem, and why your sort spilled to disk.

A query that normally runs in 200ms suddenly takes 8 seconds. Same query, same data, same plan. The difference is invisible until you look closely: it had started spilling sorts to disk.

This is the most common work_mem symptom in production, and it's one of the most misunderstood. Let's unpack it.

Misconception #1: "work_mem is per-query."

It isn't. work_mem is the limit per sort, hash, or merge operation — and a single query can use it many times.

A query with one ORDER BY uses one chunk of work_mem. A query with a hash join, a sort, and a hash aggregate uses three. Add parallel workers, and each worker gets its own work_mem for each of those operations. The math gets ugly fast.

Worst case for a single query with 4 parallel workers and 3 memory-intensive operations: work_mem × 4 × 3 = 12 × work_mem for that one query. Multiply by however many of those queries are running concurrently and you have your real memory ceiling. Setting work_mem = 256MB and max_parallel_workers_per_gather = 4 on a busy server is how databases run out of memory at 11am Tuesday.

Misconception #2: "If I see disk spills, I should raise work_mem."

Sometimes. Sometimes not.

Disk spills are the database telling you the truth: this operation needed more memory than you gave it. The instinct to raise work_mem is reasonable but incomplete. Before you change the setting, ask two questions:

First — why is this sort so large? Sometimes a spill is the symptom of a bad plan: a sort over millions of rows that should have been a smaller intermediate result, a hash join estimated wrong because statistics are stale, a sequential scan feeding a sort that an index would have eliminated. Raising work_mem makes the symptom go away without fixing the cause. The query stays expensive; you just gave it more rope.

Second — what's the cost of giving every query that much memory? work_mem is the default per-operation budget across the whole server. Raising it globally because of one slow query means every other query now has more headroom too — including queries that didn't need it and now use more RAM than they should.

The right answer is often query-specific. You have three scopes to choose from, from narrowest to broadest:

• Per session: SET work_mem = '256MB' inside the session that needs it. Affects only that connection, only until it disconnects. Ideal for one-off analytical queries or ad-hoc maintenance work.
• Per role: ALTER ROLE reporting_user SET work_mem = '256MB'. Every session that user opens inherits the higher value automatically. Useful when a specific application or job consistently needs more memory than the default — a reporting service, an ETL job, an analytics user — without exposing your OLTP application user to the same headroom.
• Per database or globally: ALTER DATABASE ... or postgresql.conf. Raise the floor for everything. Use sparingly, and only after the concurrency math checks out.

In my experience, per-role tuning is the most underused option of the three. It lets you give a reporting workload room to breathe without changing the budget for the high-concurrency application traffic on the same instance. Still has to be set wisely — a role with 256MB work_mem opening 50 connections is the same math problem as a 256MB global setting, just scoped to that role. The risk is smaller, not gone.

The empirically reasonable range

Default work_mem is 4MB, which is too low for most modern workloads. A reasonable starting point for OLTP servers is 16MB to 64MB. For analytical workloads with predictable query shape, higher (64MB to 256MB) can be justified — but only with concurrency math behind it.

The rough math: total worst-case memory for sorts = max_connections × work_mem × average_memory_operations_per_query. On a 32GB server with max_connections = 200 and work_mem = 64MB, that's potentially 12.8GB just for sort buffers. Account for shared_buffers, OS page cache, and the connection overhead itself, and you can see how this gets tight quickly.

If the math says you can't afford the work_mem you want, the answer isn't to lie to the math. The answer is to reduce max_connections (use a connection pooler) or raise work_mem only for specific sessions that need it.

The diagnostic that actually matters

EXPLAIN (ANALYZE, BUFFERS) will tell you exactly when a sort spilled and how badly:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events ORDER BY occurred_at DESC LIMIT 1000;

Look for this line in the output:

Sort Method: external merge  Disk: 24736kB

That external merge Disk: XkB is the database telling you the sort exceeded work_mem and used a temp file. Two things matter: the method (quicksort and top-N heapsort are in-memory; external merge is on disk) and the size (how much it spilled).

If the spill is small — a few MB — bumping work_mem for that session is reasonable. If it's hundreds of MB, the query itself probably needs work.

For time-series workloads, there's a third option worth knowing: if your sorts are spilling because you're sorting large time-series tables, consider whether the data shape itself is the problem. Compressed hypertables sort dramatically faster on time-ordered data — because they're already sorted on time. The "fix" is sometimes architectural rather than configuration. That is where TimescaleDB shines.

The mental model

work_mem isn't a per-query budget — it's a per-operation budget that multiplies in ways that aren't obvious. Disk spills aren't necessarily bad; sometimes they're the right outcome for an occasional large query. The job isn't to eliminate spills. The job is to know when they're happening, understand why, and decide whether the answer is more memory, a better query, or different data architecture.

Next in the series

The next issue will cover maintenance_work_mem — why vacuum is crawling on your biggest tables, and what the relationship is between this setting and the autovacuum behavior you've been ignoring.