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_memwrites temp files to disk. Find it inEXPLAIN ANALYZEasSort Method: external merge Disk: NkB, or as aHashnode with more than one batch. Log every case withlog_temp_files = 0. Sizework_memfrom 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.
How do I see that a query is spilling?
Section titled “How do I see that a query is spilling?”In EXPLAIN ANALYZE
Section titled “In EXPLAIN ANALYZE”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 ordersGROUP BY customer_idORDER BY sum(total) DESC;Then read the node annotations:
| What you see | What it means |
|---|---|
Sort Method: quicksort Memory: 2048kB | Fit in memory. Fine. |
Sort Method: top-N heapsort Memory: 1024kB | Fit in memory, LIMIT-aware. Fine. |
Sort Method: external merge Disk: 412032kB | Spilled. That sort wrote ~400 MB of temp files. |
Buckets: 4096 Batches: 1 Memory Usage: 3072kB | Hash fit in memory. Fine. |
Buckets: 4096 Batches: 32 Memory Usage: 3072kB | Spilled. 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 Launchedline before assuming the figure is the total. - Whatever you set has to be affordable at your actual concurrency (see below),
since
work_memis 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
Section titled “In the logs, across your whole workload”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 sizeALTER 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.
Counters, for a quick check
Section titled “Counters, for a quick check”SELECT datname, temp_files, temp_bytesFROM pg_stat_databaseWHERE 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.
Should I use a formula or just iterate?
Section titled “Should I use a formula or just iterate?”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_memfour 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_memmultiplied byhash_mem_multiplier, which in current versions defaults to a value greater than 1, so a hash node’s ceiling is higher than thework_memyou set. CheckSHOW 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:
-
Measure first. Set
log_temp_files = 0for 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. -
Keep the global setting conservative. The global
work_memshould cover the ordinary OLTP path, where a small spill is harmless and concurrency is high. Sizing it for the worst report can cause OOM. -
Raise it where the heavy work is, not everywhere:
-- for one session / one jobSET work_mem = '256MB';-- for a dedicated reporting roleALTER ROLE analytics SET work_mem = '256MB';-- for one databaseALTER 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.
-
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 ANALYZEthat the node reportsSort 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. -
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.
How is maintenance_work_mem different?
Section titled “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. The two settings are tuned separately because their concurrency
patterns differ.
work_mem | maintenance_work_mem | |
|---|---|---|
| Applies to | Sorts, hashes in queries | VACUUM, CREATE INDEX, REINDEX, ALTER TABLE ADD FOREIGN KEY |
| How many at once | Many nodes × many sessions × parallel workers | Usually a small number |
| Typical setting | Modest globally, raised per role/session | Much 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);When is more memory the wrong fix?
Section titled “When is more memory the wrong fix?”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 20and PostgreSQL is sorting 40 million rows to answer it, an index oncreated_atlets it read the rows in order and skip the sort entirely. No amount ofwork_membeats 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 withANALYZE, a higher statistics target, or extended statistics, and the memory problem may go away. See why did my PostgreSQL query suddenly get slow? - Deep
OFFSETpagination, 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_memincrease 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.
How do I stop it coming back?
Section titled “How do I stop it coming back?”- Leave
log_temp_fileson 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_bytesinpg_stat_database. - Add
EXPLAIN (ANALYZE, BUFFERS)when reviewing new reporting or export queries, and check theSort MethodandBatcheslines. - 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.