Temp File Usage and log_temp_files #

The database writes 400 GB of temporary files a day. No individual query looks slow enough to explain it, storage latency is fine, and the only symptom users notice is that reports are erratic — sometimes four seconds, sometimes ninety. Temporary files are the trace of nodes that exceeded their memory budget, and they are logged only if you ask.

Ranking nodes by I/O is covered in using BUFFERS to find I/O hotspots. This page is about the spills specifically.

The Condition #

Sorts, hashes, materialises and CTE scans that exceed their memory allowance write temporary files. The relevant budgets are work_mem for sorts and materialises, and work_mem × hash_mem_multiplier for hash-based nodes, as covered in hash_mem_multiplier and the hash memory budget.

Three ways to see them:

Node-level details name the mechanism directly: Sort Method: external merge Disk: 1402208kB, Batches: 33 Disk Usage: 3204410kB on a hash aggregate, and Batches: 64 on a hash join.

Spills are not automatically a problem. An external merge sort of a large report is a reasonable way to sort more data than fits in memory. They become a problem when they are avoidable — a spill caused by an underestimate, or by a work_mem far below what the workload needs — or when they are so frequent that the temporary tablespace becomes a bottleneck.

Finding and attributing a spill Four stages. Enable log_temp_files to record spills with their statements. Identify the statements writing the most temporary bytes. Capture EXPLAIN with ANALYZE and BUFFERS to attribute the spill to a node. Fix the node with memory, an index, or a better estimate. log_temp_files spills with statements rank by bytes worst statements first EXPLAIN + BUFFERS attribute to a node fix the node memory, index, estimate only the node level tells you which fix applies

Annotated Evidence #

Logged spills:

ALTER SYSTEM SET log_temp_files = '1MB';
SELECT pg_reload_conf();
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp41204.0", size 1436880896
STATEMENT:  SELECT customer_id, sum(total) FROM orders GROUP BY customer_id ORDER BY 2 DESC LIMIT 100

Cumulative counters:

SELECT temp_files, pg_size_pretty(temp_bytes) FROM pg_stat_database WHERE datname = current_database();
 temp_files | pg_size_pretty
------------+----------------
    1840220 | 402 GB

Attribution:

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, sum(total) FROM orders GROUP BY customer_id ORDER BY 2 DESC LIMIT 100;

Limit  (actual time=88204.6..88204.8 rows=100 loops=1)
  ->  Sort  (actual time=88204.6..88204.7 rows=100 loops=1)
        Sort Key: (sum(total)) DESC
        Sort Method: top-N heapsort  Memory: 32kB
        ->  HashAggregate  (actual time=41204.2..86104.8 rows=3812044 loops=1)
              Group Key: customer_id
              Planned Partitions: 4  Batches: 37  Memory Usage: 262193kB  Disk Usage: 1402208kB
              Buffers: shared hit=412008 read=102204, temp read=175276 written=175276
              ->  Seq Scan on orders  (actual rows=94022110 loops=1)
Execution Time: 88310.2 ms
-- the top-N sort is tiny; the HashAggregate spilled 1.4 GB across 37 batches
-- Planned Partitions 4 vs Batches 37: the group estimate was far too low
Reading a spill at the node level Three items are annotated. Batches far above planned partitions shows an underestimated group count. Disk Usage gives the spilled volume for the node. Temp read and written in the buffers line confirm the I/O belongs to this node rather than a child. Planned Partitions: 4 Batches: 37 estimate far too low Disk Usage: 1402208kB 1.4 GB spilled by this node temp read=175276 written=175276 the node's own temp I/O fix the estimate first, memory second

Step-by-Step Resolution #

  1. Turn on logging with a threshold — log_temp_files = '1MB' keeps the log useful without recording every trivial spill.

  2. Rank statements by spilled bytes from the log, or by temp_blks_written in pg_stat_statements:

    SELECT temp_blks_written, calls, left(query, 60)
FROM pg_stat_statements ORDER BY temp_blks_written DESC LIMIT 5;

  3. Attribute the spill to a node with EXPLAIN (ANALYZE, BUFFERS) and read the node’s own temp counters and method lines.

  4. Check the estimate before the memory. Batches far above Planned Partitions, or rows far below actual rows, means the spill is a symptom of a bad estimate; fixing statistics may remove it without extra memory — see overriding n_distinct on large tables.

  5. Size memory for the node, not the server. Raise work_mem or hash_mem_multiplier for the session or role that runs the report, and budget the total against concurrency.

  6. Consider a plan change when the data genuinely does not fit: an index providing order removes the sort entirely; a coarser grouping reduces the hash table.

  7. Re-measure, and confirm the temp counters dropped rather than moved to another node.

Spill volume by remedy The original query spills 1,402 megabytes. Correcting the distinct-count estimate reduces it to 604 megabytes by planning the partitions properly. Raising the hash memory budget for the session removes the spill entirely. An index providing grouped order also removes it while using less memory. original 1,402 MB spilled corrected estimate 604 MB larger hash budget no spill index-provided order no spill the last two also cut execution time by more than half

Before and After #

-- BEFORE: HashAggregate with an underestimated group count
Batches: 37  Disk Usage: 1402208kB  temp written=175276          Execution Time: 88310.2 ms

-- AFTER: n_distinct override + hash_mem_multiplier for the reporting role
Batches: 1  Memory Usage: 892104kB                                Execution Time: 21402.6 ms

Temporary files are also a capacity question #

Temporary files live in the temporary tablespace, which by default shares storage with the data directory. A single large report can consume tens of gigabytes there, and several running concurrently can fill the volume — which fails those queries and, if the volume also holds the data directory, threatens the whole instance. Two mitigations are worth having regardless of tuning: a separate temp_tablespaces location with its own capacity, and temp_file_limit to cap how much one session may write, so a runaway query fails instead of filling the disk.

Monitoring the counters over time tells you whether spills are a normal part of the workload or a regression. A steady daily volume that matches a reporting schedule is expected; a sudden increase usually points at a statistics change or a data-volume threshold being crossed, and correlating it with the deploy or the data growth is faster than re-tuning from scratch.

A last practical note: temporary files are written by the backend running the query, so their I/O competes with the same storage the rest of the workload uses. A report spilling a gigabyte does not only slow itself down; it adds write pressure that every other query feels. That is why the volume of spilled bytes, not just the number of slow queries, is worth tracking as a workload-level metric.

Common Pitfalls #

Logging every temp file. log_temp_files = 0 on a busy system floods the log. Diagnostic signal: log volume growth after enabling it. Fix: a threshold of a few megabytes.

Raising work_mem globally. Every node in every session gains the allowance. Diagnostic signal: memory pressure after a change made for one report. Fix: role or session scope.

Fixing memory when the estimate is wrong. The spill returns as data grows. Diagnostic signal: Batches far above Planned Partitions. Fix: statistics first.

Ignoring temp file location. Reports can fill the data volume. Diagnostic signal: disk alerts during reporting windows. Fix: temp_tablespaces and temp_file_limit.

Frequently Asked Questions #

What causes temporary files in PostgreSQL? #

Sorts, hash joins, hash aggregates and materialised nodes that need more memory than their budget allows write their intermediate data to temporary files. The budget is work_mem, multiplied by hash_mem_multiplier for hash-based nodes.

How do I find which query wrote temporary files? #

Set log_temp_files to a size threshold so spills are logged with their statements, or rank statements by temp_blks_written in pg_stat_statements. Then use EXPLAIN with ANALYZE and BUFFERS to attribute the spill to a node.

Are temporary files always bad? #

No. Sorting more data than fits in memory legitimately uses them. They are a problem when caused by a bad estimate, when the memory budget is far below what the workload needs, or when their volume threatens storage capacity.

Up: Identifying Plan Bottlenecks