PostgreSQL Temp File Spills: The Performance Bug Hiding Behind work_mem

The dashboard got slow after a customer imported a year of data. The query still worked. The index still existed. CPU was not maxed out. Then the plan showed it: a sort had spilled to disk, and the spill was doing more work than the actual business logic.

Temp file spills are a normal part of PostgreSQL execution when an operation needs more memory than it was allowed to use. They become a production problem when sorts, hash joins, aggregates, or materialized intermediates start using disk as a routine part of request handling.

The dangerous reaction is to raise work_mem globally and move on. Sometimes that helps. Sometimes it creates a memory incident because work_mem is per operation, not a cluster-wide budget.

Find spills from the workload view

pg_stat_statements can show query shapes that write temp blocks. That gives you a target before you dig into individual plans.

SELECT
  queryid,
  calls,
  temp_blks_read,
  temp_blks_written,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  left(query, 180) AS query_sample
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC
LIMIT 20;

Make spills visible in logs

For a focused investigation, log_temp_files is useful. Set a threshold that catches meaningful spills without filling logs with noise.

log_temp_files = '64MB'

Read the plan before touching memory

External sorts and multi-batch hashes are symptoms. The cause might be that the query fed too many rows into the operation. If a WHERE clause fails to prune or a join explodes row counts, more memory only makes the bad shape more comfortable.

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, count(*)
FROM events
WHERE occurred_at >= now() - interval '90 days'
GROUP BY customer_id
ORDER BY count(*) DESC
LIMIT 50;

work_mem math is where teams get hurt

work_mem can be used by each sort or hash operation, and a single query can have several. Multiply that by concurrent sessions and parallel workers before changing a global setting.

BEGIN;
SET LOCAL work_mem = '128MB';
-- Run the report query here.
COMMIT;

• Use a session-level setting to test the query first.
• Prefer role-level or workload-specific settings when only a job needs more memory.
• Use statement timeouts and concurrency limits for heavy reports.
• Do not size memory from one query in isolation.

The fixes I check before global memory changes

1. Can an index satisfy the ORDER BY and LIMIT?
2. Can the input be filtered earlier?
3. Can aggregation happen on a smaller time window?
4. Can a report use a summary table?
5. Can the query run in a background job instead of a request?
6. Does only this role need higher work_mem?

The production habit

The teams that get good at PostgreSQL performance do not chase every knob. They turn a vague complaint into a named failure mode, collect one clean measurement, make one change, and then compare the next measurement against the first. That rhythm is slower than guessing for the first hour and much faster by the end of the incident.