# 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.

```sql
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 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)` | 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](https://www.dbgorilla.com/learn/postgres/how-to-read-a-postgres-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:

```sql
-- 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

```sql
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:

   ```sql
   -- 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_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 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:

```sql
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](https://www.dbgorilla.com/learn/postgres/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?](https://www.dbgorilla.com/learn/postgres/why-did-my-postgres-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 →](https://app.dbgorilla.com/signup)

## 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.

## Related

- [How to read a PostgreSQL EXPLAIN ANALYZE plan](https://www.dbgorilla.com/learn/postgres/how-to-read-a-postgres-explain-analyze-plan/)
- [How to find missing indexes in PostgreSQL](https://www.dbgorilla.com/learn/postgres/how-to-find-missing-indexes-in-postgresql/)
- [How to fix high CPU on a PostgreSQL database](https://www.dbgorilla.com/learn/postgres/how-to-fix-high-cpu-on-a-postgresql-database/)
- [Why did my PostgreSQL query suddenly get slow?](https://www.dbgorilla.com/learn/postgres/why-did-my-postgres-query-suddenly-get-slow/)
- Product: [DBGorilla docs](https://www.dbgorilla.com/docs/)