Specifies the maximum amount of disk space that a process can use for temporary files, such as sort and hash temporary files, or the storage file for a held cursor.
At a glance
| Property | Value |
|---|---|
| Parameter | temp_file_limit |
| Category | Resource Consumption |
| Default | -1 |
| Value type | integer |
| Change scope | Per-session (SET) |
| Available in | PostgreSQL 12, 13, 14, 15, 16, 17, 18, 19 (added in 12) |
What it does
Specifies the maximum amount of disk space that a process can use for temporary files, such as sort and hash temporary files, or the storage file for a held cursor. A transaction attempting to exceed this limit will be canceled. If this value is specified without units, it is taken as kilobytes. -1 (the default) means no limit. Only superusers and users with the appropriate SET privilege can change this setting.
This setting constrains the total space used at any instant by all temporary files used by a given PostgreSQL process. It should be noted that disk space used for explicit temporary tables, as opposed to temporary files used behind-the-scenes in query execution, does not count against this limit.
(Description quoted from the official PostgreSQL documentation.)
How to apply a change
Can be set per session with SET, per role/database with ALTER ROLE/DATABASE ... SET, or globally in postgresql.conf.
Inspect the current value and source with SHOW temp_file_limit; or SELECT name, setting, unit, context, source FROM pg_settings WHERE name = 'temp_file_limit';.
Tuning guidance
Set a per-session cap to stop runaway sorts/hashes filling the disk; a query exceeding it errors out rather than taking the server down. Size it to your largest legitimate temp need plus headroom.