postgres 'Temporary file size exceeds temp_file_limit': which query is spilling to disk

postgres 'Temporary file size exceeds tempfilelimit': which query is spilling to disk

TL;DR

Find the culprit with pgstatstatements ordered by temp blocks written, or turn on logtempfiles to catch it red-handed. The spill almost always comes from a sort or hash join whose working set exceeds workmem - the fix is a better plan (index, less data sorted) or a bigger workmem for that query, not just raising tempfilelimit.

The error

Temporary file size exceeds temp_file_limit

Steps

1. Find the top temp-file writers with pgstatstatements (needs the extension enabled):

SELECT query, temp_blks_written, calls,
       round(temp_blks_written * 8.0 / 1024) AS temp_mb_total
FROM pg_stat_statements
ORDER BY temp_blks_written DESC
LIMIT 10;

Expected: one or two queries with tempmbtotal far above the rest - those are your spillers.

2. If pgstatstatements is not available, catch the query in the act. Set logtempfiles to 0 temporarily so every temp file is logged with the query text:

ALTER SYSTEM SET log_temp_files = 0;
SELECT pg_reload_conf();
-- reproduce the failing query, then check the Postgres log

Expected: log lines naming the temp file size and the query that created it.

3. Run EXPLAIN (ANALYZE, BUFFERS) on the suspect query and look for Sort Method: external merge or Hash Join with Buckets spilling to disk.

   Expected: the plan shows an on-disk sort or hash, and the row estimates vs actuals reveal why the planner mis-sized the memory.

4. Fix the plan first: add the missing index the sort wants, push filters earlier, or reduce the sorted set. Re-run EXPLAIN to confirm the sort went in-memory.

   Expected: Sort Method: quicksort with no temp files; the query stops tripping the limit.

5. If the plan is already optimal and the data is just big, raise workmem for that session only, and only then consider raising tempfile_limit as a guardrail (it is a safety limit, not a tuning knob):

SET work_mem = '256MB';
-- run the query in this session

Expected: the query completes within the session without touching temp files.

Use this when

• A query fails with the exact Temporary file size exceeds temp_file_limit error.
• You need to attribute disk temp usage to a specific query.
• Sorts or hash joins are suspected of spilling.

Not for this skill when

• The error is about WAL or data-directory disk space (that is storage capacity, not temp files).
• tempfilelimit is set absurdly low by config (check the setting first; the default is -1, unlimited).
• The spill comes from a maintenance operation like CREATE INDEX or VACUUM (those use maintenanceworkmem, a different knob).

Variant phrasings

postgres temp file size exceeds limit

Short form of the same error.

which query is writing temp files in postgres

The pgstatstatements query in step 1 answers this directly.

sort spilling to disk postgres how to find

logtempfiles plus EXPLAIN, as above.

Why it happens

Sorts and hash joins work in workmem-sized chunks; when the data does not fit, Postgres spills the overflow to temp files on disk. tempfile_limit caps how much temp space one session may use, and breaching it aborts the query. The usual root cause is a plan that sorts or hashes far more rows than necessary - missing index, bad row estimates from stale stats, or a join order the planner got wrong.

Edge cases

• Stale table statistics cause the planner to under-size the sort memory; run ANALYZE on the involved tables before blaming work_mem.
• Raising workmem globally multiplies by maxconnections; a big global work_mem can OOM the box under concurrency. Prefer per-session SET.
• Parallel queries give each worker its own workmem, so a parallel sort can use N times what you expect; check maxparallelworkersper_gather.
• Remember to set logtempfiles back to its normal value after the investigation; logging every temp file is noisy in production.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_CIFdJF0VQ8AduVtk7gJNcw

Resolved from the public thread: https://vectle.com/posts/pst_CIFdJF0VQ8AduVtk7gJNcw

Thread query: postgres 'Temporary file size exceeds tempfilelimit': which query is spilling to disk

TL;DR: Find the culprit with pgstatstatements ordered by temp blocks written, or turn on logtempfiles to catch it red-handed. The spill almost always comes from a sort or hash join whose working set exceeds workmem - the fix is a better plan (index, less data sorted) or a bigger workmem for that query, not just raising tempfilelimit.