Skip to main content

Why are my queries spilling to disk, and how do I size work_mem safely?

Quick answer: A sort or hash needing more memory than work_mem writes temp files to disk. Find it in EXPLAIN ANALYZE as Sort Method: external merge Disk: NkB, or a Hash node with more than one batch, and log every case with log_temp_files = 0. Size work_mem from those observed sizes: keep the global conservative, raise it per session or per role for heavy queries.

What does "spilling to disk" actually mean?​

PostgreSQL executes sorts, hash joins, hash aggregates, and a few other operations in memory when it can. work_mem is the ceiling for how much memory one such operation may use. When the data does not fit under that ceiling, PostgreSQL does not fail and does not allocate more. It falls back to an on-disk algorithm:

  • A sort writes sorted runs to temporary files and merges them back (an external merge sort).
  • A hash join or hash aggregate partitions its input into multiple batches, spilling all but the current batch to temp files and processing them one at a time.

Those temp files live in the database's temp tablespace and are deleted when the query finishes. Functionally, everything still works. The cost is that a step that should have been pure in-memory work now writes and re-reads potentially gigabytes.

This is primarily an I/O cost, not a CPU cost. The extra time is dominated by writing and reading temp files. There is a real but secondary CPU cost as well: extra merge passes in a sort, extra hashing passes across batches. So it is not purely I/O either. If you are diagnosing a spill as "high CPU," you are usually chasing the wrong resource; look at disk throughput and temp-file volume first.

How do I see that a query is spilling?​

In EXPLAIN ANALYZE​

You need ANALYZE. The estimated-only EXPLAIN will not tell you, because whether a sort fits is a runtime fact.

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, sum(total)
FROM orders
GROUP BY customer_id
ORDER BY sum(total) DESC;

Then read the node annotations:

What you seeWhat it means
Sort Method: quicksort Memory: 2048kBFit in memory. Fine.
Sort Method: top-N heapsort Memory: 1024kBFit in memory, LIMIT-aware. Fine.
Sort Method: external merge Disk: 412032kBSpilled. That sort wrote ~400 MB of temp files.
Buckets: 4096 Batches: 1 Memory Usage: 3072kBHash fit in memory. Fine.
Buckets: 4096 Batches: 32 Memory Usage: 3072kBSpilled. Batches > 1 means the hash partitioned to disk.
Batches: 16 (originally 1)Worse: the planner expected it to fit and it did not. A misestimate.

The Disk: figure is the most useful number in this whole article, but be precise about what it is: temp-file bytes written during the spill, not the amount of memory the operation would have needed to stay in RAM. Those are different numbers. The on-disk representation is not the in-memory one, and an external merge sort deliberately works in bounded chunks rather than holding everything at once, so the memory required to avoid the spill is generally larger than the bytes written, not equal to them.

Treat it as a sizing signal with headroom, not a conversion. Two caveats before you use it:

  • On a parallel plan the reported numbers are per worker, check the Workers Launched line before assuming the figure is the total.
  • Whatever you set has to be affordable at your real concurrency (see below), since work_mem is per sort or hash node, not per query.

For a walkthrough of the rest of the plan output, see how to read a PostgreSQL EXPLAIN ANALYZE plan.

In the logs, across your whole workload​

EXPLAIN ANALYZE tells you about one query you already suspect. To find the ones you do not know about, turn on temp-file logging:

-- log every temp file, regardless of size
ALTER SYSTEM SET log_temp_files = 0;
SELECT pg_reload_conf();

log_temp_files takes a size threshold in kilobytes: 0 logs every temp file, a positive value logs only files at least that large, and -1 (the default) disables it. Each log line names the file and its size, alongside the statement that produced it. Set it to 0 while you are investigating; on a busy server you may want a threshold instead so the log stays readable.

One gotcha: the threshold is in kilobytes, but the size in each log line is in bytes. Do not read a logged 188743680 as kilobytes. That is 180 MB, not 180 GB.

Counters, for a quick check​

SELECT datname, temp_files, temp_bytes
FROM pg_stat_database
WHERE datname = current_database();

These are cumulative since the last stats reset, so the useful thing is the rate of change, not the absolute value. Rising temp_bytes with no corresponding reporting job is a signal worth chasing.

Should I use a formula or just iterate?​

You will find two camps online: one hands you an equation like work_mem = (RAM × 0.25) / max_connections, the other says formulas are useless and you should just raise it until things stop spilling. Both are wrong in the same way. They are arguing about the wrong denominator. Here is the resolution.

work_mem is a limit per sort or hash node per execution. Not per query, and not per connection. That means:

  • One query with three sorts and a hash join can allocate work_mem four times.
  • Under parallel query, each parallel worker running those nodes gets its own allocation. A plan with 4 workers and 2 spilling nodes is up to 8–10 allocations for one statement.
  • Hash-based nodes are governed by work_mem multiplied by hash_mem_multiplier, which in current versions defaults to a value greater than 1, so a hash node's real ceiling is higher than the work_mem you set. Check SHOW hash_mem_multiplier; on your server before doing any arithmetic.
  • It is a limit, not a reservation. A connection sitting idle uses none of it.

So work_mem × max_connections is not a worst case. It is a number that is simultaneously far too pessimistic for typical load (most connections are not sorting) and far too optimistic for the bad moment (a handful of concurrent parallel reporting queries can blow straight through it). That mismatch is exactly how teams set work_mem to 256 MB using a formula, feel safe, and get an OOM kill at the next month-end report.

The resolution: do not compute a value, observe one, then apply it narrowly.

  1. Measure first. log_temp_files = 0 for a few days of representative traffic. Now you have the real distribution of which operations spill and how much they spill by, a measured starting point instead of a guess.

  2. Keep the global conservative. The global work_mem should cover the ordinary OLTP path, where a small spill is harmless and the concurrency is high. Sizing the global for your worst report is how you get OOM.

  3. Raise it where the heavy work is, not everywhere:

    -- for one session / one job
    SET work_mem = '256MB';

    -- for a dedicated reporting role
    ALTER ROLE analytics SET work_mem = '256MB';

    -- for one database
    ALTER DATABASE warehouse SET work_mem = '128MB';

    A reporting role running 3 concurrent queries at 256 MB is a bounded, reasoned risk. The same value applied to 400 web connections is not.

  4. Size to what you saw, with headroom. If the log says a nightly rollup wrote 180 MB of temp files, a session-level 256 MB is a reasonable first try, not a computed answer. Temp-file bytes are a floor, not the memory figure, so confirm with EXPLAIN ANALYZE that the node actually reports Sort Method: quicksort Memory: afterwards, and raise again if it still spills. Do not jump straight to 1 GB "to be safe". That is the same mistake at a different scale.

  5. Re-check under real concurrency, not on a quiet box. The failure mode is concurrent, so the test has to be.

If you have no logs and need a starting point today: leave the global in the low tens of megabytes on a mixed workload, turn on log_temp_files, and fix the specific offenders as they appear. That is the iterate camp's advice, applied with the formula camp's arithmetic discipline about where the memory actually multiplies.

How is maintenance_work_mem different?​

maintenance_work_mem covers maintenance operations.VACUUM, CREATE INDEX, REINDEX, ALTER TABLE, adding a foreign key, rather than ordinary query execution. They are tuned separately for one reason: concurrency.

work_memmaintenance_work_mem
Applies toSorts, hashes in queriesVACUUM, CREATE INDEX, REINDEX, ALTER TABLE ADD FOREIGN KEY
How many at onceMany nodes × many sessions × parallel workersUsually a small number
Typical settingModest globally, raised per role/sessionMuch higher

Because only a few maintenance operations run at a time, you can afford to give each one a lot of memory, and it pays off directly: a CREATE INDEX with enough maintenance_work_mem sorts in memory instead of spilling, and vacuum scans the table fewer times. The catch is autovacuum: autovacuum_max_workers workers can each use up to autovacuum_work_mem (which falls back to maintenance_work_mem when unset), so that setting does multiply. Count the workers when you size it.

Raising maintenance_work_mem for a one-off index build is a normal, low-risk move:

SET maintenance_work_mem = '2GB';
CREATE INDEX CONCURRENTLY ON orders (customer_id);

When is more memory the wrong fix?​

Often. A spill is a symptom, and "the sort needed 4 GB" is frequently a statement about the query, not about your configuration. Before you raise anything, check whether the operation should be that big at all:

  • A missing index causing a huge sort. If the query has ORDER BY created_at LIMIT 20 and PostgreSQL is sorting 40 million rows to answer it, an index on created_at lets it read the rows in order and skip the sort entirely. No amount of work_mem beats not sorting. See how to find missing indexes in PostgreSQL.
  • Returning far more rows than you need. A SELECT * that hauls wide columns (long text, JSONB) through a sort inflates the row width, and therefore the memory, for data the application throws away. Select the columns you use.
  • Filtering after the sort instead of before it. Push predicates down so the sort operates on the rows you will keep.
  • A row misestimate. Batches: 32 (originally 1) means the planner thought it would fit. That is a statistics problem, fix the estimate with ANALYZE, a higher statistics target, or extended statistics, and the memory question may evaporate. See why did my PostgreSQL query suddenly get slow?
  • Deep OFFSET pagination, which sorts and discards. Use keyset pagination.
  • A genuinely large analytical sort. Sometimes the answer really is "this report aggregates 200 million rows." Then a session-scoped work_mem bump is the right, honest fix, and knowing you ruled out the others is what makes it honest.

A useful ordering: fix the estimate, then fix the query or the index, then raise the memory, narrowly.

Why does aI-generated code produce spilling queries?​

Your AI coding agent (Claude Code, Cursor) writes a GROUP BY with an ORDER BY, or a join across three tables. That is correct and idiomatic. It cannot see that the grouping key has 12 million distinct values, that the joined table is 60 GB, that work_mem on this server is 4 MB, or that the query will run 40 times concurrently at month end. Whether an operation fits in memory is a function of your data volume, your server's configuration, and your concurrency, three things that are invisible in the source file. The SQL is not wrong; it is just unaware of how much memory it is about to ask for.

How do I stop it coming back?​

  • Leave log_temp_files on with a sensible threshold (not off) so new spills announce themselves instead of being discovered during an incident.
  • Alert on the growth rate of temp_bytes in pg_stat_database.
  • Add EXPLAIN (ANALYZE, BUFFERS) to review for any new reporting or export query, and check the Sort Method / Batches lines specifically.
  • Give heavy analytical work its own role with its own work_mem, so the setting cannot leak onto the web tier.
  • Re-check after data growth: a sort that fit last quarter at 3 million rows may not fit at 9 million, and nothing about your config changed.

How DBGorilla helps​

DBGorilla connects read-only and gives your AI coding agent (Claude Code, Cursor) the evidence for this specific problem: the plan showing external merge and the exact Disk: size, the hash batch counts, the real row counts and index coverage behind the sort, and the current work_mem and maintenance_work_mem values. So your agent can tell you whether you are looking at a missing index, a bad row estimate, or a query that genuinely needs more memory, and where to raise it. It surfaces and explains through your agent; it does not change server configuration or tune parameters for you. Get started free →

FAQ​

What does "Sort Method: external merge" mean in EXPLAIN ANALYZE? The sort exceeded work_mem, so PostgreSQL wrote temporary files and merged them from disk. The Disk: NkB value is how much temp space it used; the in-memory equivalent is Sort Method: quicksort with a Memory: value.

Is work_mem per query or per connection? Neither. It is per sort or hash node per execution. One query with several sorts, plus parallel workers, can allocate it many times over, which is why work_mem × max_connections formulas mislead and cause OOM.

How do I choose a safe work_mem value? Measure rather than calculate: set log_temp_files = 0, collect real workload, and size from the observed temp-file sizes. Keep the global conservative and raise it per session or per role for known-heavy queries.

What is the difference between work_mem and maintenance_work_mem? work_mem covers sorts and hashes in ordinary queries, many of which run concurrently. maintenance_work_mem covers VACUUM, CREATE INDEX, and similar operations, which run a few at a time, so it is normally set much higher and tuned separately.