Parameter · Resource Usage / Disk
temp_file_limit
Limits the total size of all temporary files used by each process. -1 means no limit.
- Default:
-1 - Type: integer (kB)
- Range: -1 … max (2147483647)
- Applies: Session (superuser)
In plain words
Big sorts and hashes that do not fit in work_mem write temporary files. One bad query can fill the disk this way and take the whole server down. temp_file_limit caps how much temporary file space a single process may use at the same time; a query that goes over is cancelled.
For DBAs and operators
Counts all temporary files of one process at any instant: sort/hash spill files, materialised CTEs, held cursors, tuplestores. Parallel workers are separate processes with their own limit. It does not count temporary tables (those are relation files). The error is:
ERROR: temporary file size exceeds "temp_file_limit" (1048576kB)
Temp files go to base/pgsql_tmp or the tablespaces in temp_tablespaces. When the filesystem itself fills you get ERROR: could not write to file "base/pgsql_tmp/pgsql_tmp1234.5": No space left on device, and if the WAL disk is the same, much worse things follow. A limit of e.g. 10–50% of free disk space is cheap insurance.
-- who is spilling now (PG12+):
SELECT * FROM pg_ls_tmpdir();
SELECT datname, temp_files, pg_size_pretty(temp_bytes)
FROM pg_stat_database;Only superusers (or roles granted SET on it) can change it, so it can be enforced per role: ALTER ROLE analyst SET temp_file_limit = '20GB'. Combine with log_temp_files to find the queries.
How to change it
A superuser (or a role granted SET) can change it per session with SET.
See it in context
Related parameters
work_mem log_temp_files temp_tablespaces hash_mem_multiplier