Troubleshoot high temporary file utilization

When a sort, hash, or similar operation needs more memory than work_mem allows, PostgreSQL spills it to disk as a temporary file. The operation still completes, but it runs far more slowly while consuming storage and I/O that nothing else can use.

Temporary files are a symptom of a memory sizing mismatch. The fix is nearly always either a better query plan or a better-sized work_mem. Rarely more storage.

Symptoms this guide explains

Sudden storage spikes that recover on their own
Queries that are fast on small inputs and disproportionately slow on large ones
Storage utilization climbing during reporting or batch windows
Sort or hash operations that appear slow without an obvious cause

Before you start

Enable Sessions data, Query Store Runtime, and Query Store Wait Statistics; set pg_qs.query_capture_mode to TOP or ALL and pgms_wait_sampling.query_capture_mode to ALL; and enable metrics.collector_database_activity. See Use the troubleshooting guides.

How this guide is organized

Tab	The question it answers
Storage	Is there a storage spike, and when?
Temporary files	Are temporary files responsible for it?
Workload	Did the amount of work increase?
Queries	Which statements generate the temporary files?

This guide is deliberately short. Once you have the query, the work moves to EXPLAIN and work_mem.

Walkthrough: storage spikes every night

Spot the pattern. The Storage tab shows utilization jumping sharply around 02:00 each night and returning to baseline within the hour. Storage that frees itself is characteristic of temporary files, because real data growth doesn't recover.
Confirm the cause. The Temporary files tab shows both file count and total temporary bytes spiking in the same window, confirming the correlation.
Rule out workload. Workload shows no unusual tuple activity. This isn't more work; it's the same nightly job spilling.
Find the query. The Queries tab ranks by total temporary file size. One query ID dominates. Its per-bucket detail shows large temporary blocks written on every execution, with a modest row count returned, so it's sorting a large intermediate result to produce a small answer.
Decide between two fixes. You retrieve the SQL and run EXPLAIN (ANALYZE, BUFFERS). The plan shows an external merge sort on a column with no supporting index. Two options: add the index so the sort disappears entirely, or raise work_mem so it completes in memory. The index is the better fix because it removes the work rather than paying for it, so you add it and set a higher work_mem for just that job's role as a safety net.

Tab reference

Storage and temporary files

Start on Storage and look for spikes. Then check Temporary files for file count and total temporary bytes in the same window.

Spikes that recover indicate temporary files. Continue through this guide.
Growth that persists indicates real data growth or bloat. This guide doesn't help; see Monitor autovacuum.

Many small temporary files and a few enormous ones both produce a spike, but mean different things. Many small files suggest lots of operations each slightly over work_mem. A few large ones suggest a single query far exceeding it.

Workload

Read versus write tuple activity, to check whether the spill accompanies genuinely increased work or the same work spilling.

Queries

Ranks queries by total temporary file size generated. Select a query ID for per-bucket detail: mean, min, and max temporary blocks written and read, mean and total rows, total calls, and execution times.

The ratio to watch is temporary bytes against rows returned. Large spill for a small result set means the query materializes and sorts far more data than it ultimately needs. You can usually fix this problem in the query or with an index, not with memory.

How to act on the query you find:

Run EXPLAIN (ANALYZE, BUFFERS) and look for external merge sorts or hash operations that report disk usage. The plan names the exact operation that spills.
Add an index that provides the needed ordering or grouping, so the sort disappears rather than being paid for.
Select only the columns you need. Wide rows fill work_mem faster, and SELECT * through a sort is a common cause.
Remove joins that don't narrow the result, and check for accidental cross joins.
Only then consider raising work_mem, using the guidance in the next section.
Use statement_timeout or idle_in_transaction_session_timeout to limit the impact so a runaway query can't fill storage.

Size `work_mem`

work_mem is the memory available to each internal sort or hash operation before it spills. The critical word is each. It applies per operation, not per query, and a complex query with several sorts and hash joins can consume it several times concurrently. Multiply by the number of connections running such queries and the true exposure becomes clear.

Situation	Direction
Many short queries, simple joins, little sorting	Keep it low
Few large analytical queries with complex sorts	Set it higher, ideally per-role rather than server-wide
Seeing out-of-memory errors or high memory pressure	Decrease it
Seeing frequent spills while memory has headroom	Increase it gradually

A conservative starting point is Total RAM / max_connections / 16. The PostgreSQL default is 4 MB.

Prefer setting work_mem for the specific role or session that needs it over raising it server-wide. A server-wide increase applies the multiplier to every connection, which is how temporary-file problems turn into out-of-memory problems. See Troubleshoot high memory.

Other mitigations

Temporary tablespaces on local SSD. Enable azure.enable_temp_tablespaces_on_local_ssd to move temporary files onto local SSD, off your provisioned storage. After enabling, grant access:

GRANT CREATE ON TABLESPACE temptblspace TO public;  -- or a specific role

See Edv4 and Edsv4-series for local SSD capacity per SKU. This change relocates the I/O rather than eliminating it. Still worthwhile, but fix the query first.

More storage. The last resort, and only if spills are unavoidable and legitimate.

Note

On a read replica, run EXPLAIN (ANALYZE, BUFFERS) locally, but create missing indexes on the primary, because they replicate. Set work_mem per session or at the replica server level, independently of the primary. Enable azure.enable_temp_tablespaces_on_local_ssd and run the GRANT on the primary.

What good looks like

Signal	Healthy	Investigate
Temporary bytes	Near zero in steady state	Regular or growing spikes
Storage shape	Flat, or slow predictable growth	Sharp spikes that recover
Temporary bytes vs rows returned	Proportionate	Large spill, small result
`work_mem` × concurrent connections	Well within total memory	Approaching it
Spill distribution	Confined to known batch jobs	Spread across OLTP queries

What this guide can't tell you

Which plan node spilled. The guide names the query; EXPLAIN (ANALYZE, BUFFERS) names the operation.
The right work_mem for your workload. It depends on query shape and concurrency. The guidance above is a starting point to measure from, not an answer.
Whether raising work_mem will cause an OOM. Cross-check with Troubleshoot high memory before increasing it.
Temporary files from maintenance. Index builds and similar operations use maintenance_work_mem, which is governed separately.

Feedback

Was this page helpful?

Last updated on 2026-09-16