Skip to content

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

Last updated

View as Markdown

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 as a Hash node with more than one batch. Log every case with log_temp_files = 0. Size work_mem from those observed sizes: keep the global setting conservative, then raise it per session or role for heavy queries.

What does “spilling to disk” actually mean?

Section titled “What does “spilling to disk” actually mean?”

PostgreSQL executes sorts, hash joins, hash aggregates, and some other operations in memory when possible. 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 or allocate more memory. It uses 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. The query still works, but the operation now writes and rereads potentially gigabytes.

This is primarily an I/O cost, not a CPU cost. The extra time usually comes from writing and reading temp files. There is also a secondary CPU cost from extra merge passes in a sort and extra hashing passes across batches. If you are diagnosing a spill as “high CPU,” check disk throughput and temp-file volume first.

You need ANALYZE. Estimated-only EXPLAIN will not tell you whether a sort fits, because that 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)The planner expected it to fit, but it did not. This is a misestimate.

The Disk: figure is useful, but it is temp-file bytes written during the spill, not the amount of memory the operation would have needed to stay in RAM. Those numbers differ. The on-disk representation is not the in-memory representation, and an external merge sort works in bounded chunks rather than holding everything at once. The memory required to avoid the spill is generally larger than the bytes written, not equal to them.

Use it as a sizing signal with headroom, not as a direct conversion. Keep these caveats in mind:

  • 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 actual 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.

EXPLAIN ANALYZE tells you about one query you already suspect. To find queries 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 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.

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 measure is the rate of change, not the absolute value. Rising temp_bytes with no corresponding reporting job is worth investigating.

You will find two camps online: one gives you an equation like work_mem = (RAM × 0.25) / max_connections, while the other says formulas are useless and that you should raise the setting until spilling stops. Both focus on the wrong denominator.

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 can require 8 to 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 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. An idle connection uses none of it.

So work_mem × max_connections is not a worst case. It is too pessimistic for typical load, because most connections are not sorting, and too optimistic for the bad moment, when several concurrent parallel reporting queries can exceed it. That mismatch is how teams set work_mem to 256 MB using a formula and then get an OOM kill during the next month-end report.

Instead of computing a value, observe one and apply it narrowly:

  1. Measure first. Set log_temp_files = 0 for a few days of representative traffic. You will see which operations spill and how much data they write. That gives you a measured starting point.

  2. Keep the global setting conservative. The global work_mem should cover the ordinary OLTP path, where a small spill is harmless and concurrency is high. Sizing it for the worst report can cause 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 it from 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 reports Sort Method: quicksort Memory: afterward. Raise the setting 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 must be concurrent too.

If you have no logs and need a starting point today, leave the global setting in the low tens of megabytes on a mixed workload, turn on log_temp_files, and fix specific offenders as they appear. This applies the iterate approach while accounting for where memory actually multiplies.

maintenance_work_mem covers maintenance operations (VACUUM, CREATE INDEX, REINDEX, ALTER TABLE, adding a foreign key) rather than ordinary query execution. The two settings are tuned separately because their concurrency patterns differ.

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 give each one more memory. A CREATE INDEX with enough maintenance_work_mem sorts in memory instead of spilling, and vacuum scans its indexes 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 sizing it.

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

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

More memory is often the wrong fix. A spill is a symptom, and “the sort needed 4 GB” may be a statement about the query rather than the configuration. Before raising anything, check whether the operation should be that large:

  • 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 needed. A SELECT * that hauls wide columns (long text, JSONB) through a sort inflates the row width and therefore the memory used for data the application discards. Select only 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 the hash would fit. That is a statistics problem. Fix the estimate with ANALYZE, a higher statistics target, or extended statistics, and the memory problem may go away. See why did my PostgreSQL query suddenly get slow?
  • Deep OFFSET pagination, which sorts and discards rows. Use keyset pagination.
  • A genuinely large analytical sort. Sometimes the report really does aggregate 200 million rows. Then a session-scoped work_mem increase is the right fix, after you rule out the other causes.

A useful order is to fix the estimate, then the query or index, and then raise memory narrowly.

Why does AI-generated code produce spilling queries?

Section titled “Why does AI-generated code produce spilling queries?”

Your AI coding agent may write a GROUP BY with an ORDER BY, or a join across three tables. That SQL can be correct and idiomatic, but the agent 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 depends on your data volume, server configuration, and concurrency. The source file does not contain that information.

  • Leave log_temp_files on with a sensible threshold, not off, so new spills appear in the logs instead of being discovered during an incident.
  • Alert on the growth rate of temp_bytes in pg_stat_database.
  • Add EXPLAIN (ANALYZE, BUFFERS) when reviewing new reporting or export queries, and check the Sort Method and Batches lines.
  • Give heavy analytical work its own role with its own work_mem, so the setting does not reach the web tier.
  • Re-check after data growth. A sort that fit last quarter at 3 million rows may not fit at 9 million, even if the configuration is unchanged.
  • What does "Sort Method: external merge" mean in EXPLAIN ANALYZE?

    It means the sort did not fit in work_mem, so PostgreSQL wrote the data to temporary files and merged them from disk. The Disk: NkB value tells you how much temp space that sort used. The in-memory equivalent is "Sort Method: quicksort" with a Memory: NkB value.

  • Is work_mem per query or per connection?

    Neither. work_mem is a limit per sort or hash node per execution. One query with three sorts and a hash join can allocate it four times, and each parallel worker running those nodes allocates its own. That is why multiplying work_mem by max_connections underestimates worst-case memory and why naive sizing formulas cause out-of-memory kills.

  • How do I choose a safe work_mem value?

    Measure instead of calculating. Set log_temp_files = 0 to log every temp file with its size, collect a few days of real workload, and size from the observed distribution. Keep the global value conservative and raise it per session or per role for the specific heavy reporting queries that need it.

  • What is the difference between work_mem and maintenance_work_mem?

    work_mem limits memory for sorts and hashes inside ordinary queries, and many of them can run at once. maintenance_work_mem limits memory for maintenance operations such as VACUUM, CREATE INDEX, and ALTER TABLE, which usually run a few at a time. Because the concurrency is so different, maintenance_work_mem is normally set much higher and tuned separately.