Temporary files and spilling to disk · PostgreSQL

When a node exceeds its memory allowance it writes to disk rather than failing. That is the right behaviour, and it means a cluster can be spilling continuously without anyone knowing.

Finding out whether it is happening

The cumulative counter is on by default and nobody looks at it:

SELECT datname,
       temp_files,
       pg_size_pretty(temp_bytes) AS temp_bytes,
       stats_reset
  FROM pg_stat_database
 WHERE datname IS NOT NULL
 ORDER BY temp_bytes DESC;

As with idx_scan in lesson X-07, the number is meaningless without stats_reset. A hundred gigabytes of temporary files since the last restart is a very different statement depending on whether that was yesterday or last year.

Then turn on the per-event logging, which is off by default:

ALTER SYSTEM SET log_temp_files = 0;      -- log every temporary file
SELECT pg_reload_conf();

Read-only / Safewhat a spill looks like in the log— Real log line from 18.6 with log_temp_files = 0, produced by a 2,000,000-row sort at the default work_mem.

$ tail -f /var/log/postgresql/postgresql.log

LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp2684.0", size 52117504

The default of -1 means never. 0 means all. A size threshold such as '10MB' is the sensible compromise on a busy cluster: it records the spills that matter and ignores the trivial ones.

Pair it with log_min_duration_statement so the spilling statement is identifiable, and with a log_line_prefix including %a so it can be attributed to a service.

Bounding it

temp_file_limit caps how much temporary file space a single session may use. Exceeding it raises an error rather than filling the disk.

Service impact possibletemp_file_limit against a sort needing about 51 MB— Real error from 18.6. The session-level limit was set to 8MB.

$ psql -U postgres -c "SET temp_file_limit='8MB'; SELECT count(*) FROM (SELECT * FROM ordersx ORDER BY amount, created_at) s"

SET
ERROR:  temporary file size exceeds "temp_file_limit" (8192kB)

Read-only / Safethe same query with the limit removed— Captured on 18.6 immediately afterwards.

$ psql -U postgres -c "SET temp_file_limit='-1'; SELECT count(*) FROM (SELECT * FROM ordersx ORDER BY amount, created_at) s"

  count
---------
2000000

Set it per role, and understand what it protects

-- an analytics role may spill generously; the application may not
ALTER ROLE analytics SET temp_file_limit = '50GB';
ALTER ROLE app_user  SET temp_file_limit = '2GB';

It is superuser context, so a non-superuser cannot raise their own limit — which is what makes it an effective guard rather than a suggestion.

What it protects against is one runaway query filling the filesystem and taking the cluster down with it, which is a real and common outage. A query that fails with temporary file size exceeds is a query that did not cause an incident.

What it does not do is bound the cluster. The limit is per session, so fifty sessions at 2 GB each can still write 100 GB. Sizing the filesystem remains necessary.

The better answer: remove the spill

Read-only / Safethe same sort, with an index on the leading sort column— Captured on 18.6. An index on (amount) existed at the time, and the planner used an incremental sort.

$ psql -U postgres -c "SET temp_file_limit='8MB'; EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) FROM (SELECT * FROM ordersx ORDER BY amount, created_at) s"

 Full-sort Groups: 1000  Sort Method: quicksort  Average Memory: 27kB  Peak Memory: 27kB
Pre-sorted Groups: 1000  Sort Method: quicksort  Average Memory: 111kB  Peak Memory: 111kB

Compare the three versions of the same query:

Approach	Memory	Disk
Default `work_mem`, no index	—	50,896 kB spilled
`work_mem = 1GB`, no index	111,653 kB	none
Index on `(amount)`, any `work_mem`	111 kB peak	none

An index on the leading sort column let PostgreSQL read rows in approximate order and sort them in small groups — an incremental sort — so it never accumulated the whole result. Peak memory fell by three orders of magnitude, and it succeeded even under the 8 MB limit that had just rejected the same query.

This is the answer to look for first. Raising work_mem accepts the work and buys memory for it. An index avoids the work.

The shapes where it applies:

ORDER BY on an indexed column, or a leading prefix of one.
GROUP BY on an indexed column, which can use a GroupAggregate over ordered input instead of a hash table.
A merge join where both inputs are already ordered by their indexes.

Where the files go

Temporary files live in pgsql_tmp inside each tablespace’s directory, or in base/pgsql_tmp for the default tablespace.

-- put them somewhere with room, and separate failure domains
CREATE TABLESPACE tempspace LOCATION '/mnt/fasttemp';
ALTER SYSTEM SET temp_tablespaces = 'tempspace';
SELECT pg_reload_conf();

Two reasons this is worth doing on a busy cluster:

Failure isolation. Temporary files filling the data directory stops the cluster. Filling a separate filesystem stops the queries that were spilling.

Independent sizing and monitoring. Temporary space is bursty and unrelated to data growth. Watching one number that combines them tells you nothing.

Temporary files are removed at startup, not necessarily at crash time

A backend that dies without cleaning up leaves its temporary files behind. They are removed when the server next starts, which on a cluster that has not been restarted for a year means they can accumulate.

du -sh $PGDATA/base/pgsql_tmp
ls -la $PGDATA/base/pgsql_tmp

Files whose names contain a pid that no longer exists, with old timestamps, are orphans. Do not delete them by hand on a running server — a live backend may own a file whose pid you cannot see from outside. A restart clears them safely.

If a restart is not possible and the filesystem is filling, the supportable order is: identify and terminate the sessions currently writing temporary files, then let their cleanup run, then reconsider.

Why a spill is not one file, and what parallel workers do to the accounting

A spilling sort does not write one file. It writes runs — sorted chunks, each the size of work_mem — and then merges them, potentially in several passes if there are more runs than it can merge at once.

That is why the log shows numbered files (pgsql_tmp2684.0) and why the Buffers line reports both temp read and temp written: a multi-pass merge reads back what it wrote and writes it again.

The reading that matters: temp read substantially exceeding temp written means the merge required more than one pass, which is the case where more memory genuinely would help. temp read roughly equal to temp written means a single-pass merge, which is close to the best a spill can do.

Parallel execution complicates the accounting in a way worth knowing. Each worker sorts its own portion with its own work_mem and writes its own temporary files. So:

temp_file_limit is enforced per process, not per query. A query with two workers can write three times the limit in total, once per participant.
pg_stat_database.temp_bytes aggregates all of them, so the database-level figure and the per-session limit are counting different things.
A query that fails the limit may fail in a worker, and the error the client sees is the worker’s.

None of this changes what to do about a spill. It does change how the numbers add up, and it is the explanation when a query that “should” have been bounded at 2 GB wrote 6 GB.

What to take from this

pg_stat_database.temp_files and temp_bytes are already being collected. Read them with stats_reset.
log_temp_files defaults to never. Set it to 0 or a size threshold.
temp_file_limit is per session and superuser context, so it is a real guard. It does not bound the cluster.
Measured: an index on the sort column reduced peak memory from 111,653 kB to 111 kB and removed the spill. Look for that first.
Site temporary files on their own filesystem for failure isolation.
Orphaned temporary files clear on restart. Do not delete them by hand on a running server.

Cross-course references

Linux for Production Sysadmins — Part XIV (Filesystems) covers the filesystem that fills when a query spills, and Part XLI (Storage Performance) covers whether spilling is expensive on your device or merely different.
Observability for Production Sysadmins — Part LIX (Database observability) covers exporting temp_bytes as a rate, which is what distinguishes a query that always spills from one that started to.