temp_file_limit

Definition PG 18 manual

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.

How it works

temp_file_limit limits the total instantaneous size of temporary files owned by one PostgreSQL process, including sort and hash spill files and storage for held cursors. Exceeding it cancels the transaction.

The limit is per process, not cluster-wide, so concurrent backends and parallel workers can collectively consume multiples of it. The default -1 means no limit.

Explicit temporary-table storage is excluded from this limit. log_temp_files and pg_stat_database.temp_bytes observe related temporary-file activity but do not change the enforcement boundary.

Operational considerations

Treating a per-process limit as a cluster-wide disk cap.

Expecting it to constrain explicit temporary tables, which are excluded.

Leaving -1 on a filesystem where one runaway query can exhaust shared storage.

Setting the cap below normal spill sizes and discovering through transaction cancellations.

Workload guidance

OLAP: Allow a larger but still finite budget for known large joins and sorts, and coordinate it with query concurrency and parallelism. Test cancellation behavior before relying on the limit in production.

OLTP: Use a finite guardrail sized from filesystem headroom and worst-case concurrent spillers. Monitor temp_bytes and log_temp_files so the cap blocks pathological queries rather than routine bursts.

SMALL: Choose a modest fraction of the data filesystem and preserve emergency free space. Pair a lower limit with conservative work_mem and query timeouts.