Temporary files: the work that spilled

A temporary file is what an executor node writes when it needs more memory than it has been granted and has to finish the job on disk. Sorts, hash joins, hash aggregates and materialised intermediate results all fall back this way. The files are created under the data directory, removed when the statement or the session ends, and counted per database in the statistics, so the evidence of a spill outlives the query that caused it.

A spill is correct behaviour, which is the problem

Nothing fails. The node switches to an algorithm that works in bounded memory, the query returns the same rows, and the only symptom is that it took longer. That is why a server can spill continuously for a year without anyone filing a ticket, and why the first sign is usually a disk alarm rather than a slow query report.

The files are not cached in the buffer pool and are not written to the write-ahead log, so they compete for the same storage bandwidth as everything else while appearing in none of the usual I/O attribution. They are also per backend rather than per database, which means a dozen sessions running the same report each write their own copy of the same spilled data.

Two settings decide how visible this is. log_temp_files is off by default and, set to zero, logs every temporary file with its size and the statement that produced it. temp_file_limit caps how much one session may write before its query is cancelled, which turns a silent disk exhaustion into a failed query naming itself.

Making one on purpose

A sort of half a million rows with a very small memory grant has nowhere to go but disk.

CREATE TABLE reading AS SELECT ((g::bigint * 7919) % 500000)::int AS scattered_key
FROM generate_series(1, 500000) g;
SET work_mem = '64kB';
SELECT count(*) FROM (SELECT scattered_key FROM reading ORDER BY scattered_key) s;
RESET work_mem;
SELECT temp_files, pg_size_pretty(temp_bytes) AS temp_bytes
FROM pg_stat_database WHERE datname = current_database();

SELECT 500000
SET
 count  
--------
 500000
(1 row)

RESET
 temp_files | temp_bytes 
------------+------------
          2 | 12 MB
(1 row)

One statement, two files, twelve megabytes. An external sort writes sorted runs and merges them, so the file count reflects the shape of the algorithm rather than the number of statements, and neither number says which statement was responsible. The counters are cumulative since the last reset of that database’s statistics, so what they are good for is noticing that spilling happens at all and roughly how much; attribution has to come from the log.

Turning the counter into a cause

The useful next step is log_temp_files = 0 for a window, which gives you statements and sizes rather than a total. Then the question is whether the grant was too small or the estimate was wrong, and those want opposite fixes: the first is work_mem, the second is a planner problem covered in query plan regression. A hash aggregate that spills where it used to fit is nearly always the second.