Parameter · Reporting and Logging / What to Log
log_temp_files
Log the use of temporary files larger than this number of kilobytes. -1 disables logging temporary files. 0 means log all temporary files.
- Default:
-1 - Type: integer (kB)
- Range: -1 … max (2147483647)
- Applies: Session (superuser)
In plain words
When a sort, hash or similar operation needs more memory than work_mem allows, it spills to temporary files on disk, which is slow. This setting logs every temporary file at least this large, with the query that created it. 0 logs them all.
For DBAs and operators
Real lines (18.6). The size is in bytes, even though the setting is in kB:
LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp4621.0", size 2800000 STATEMENT: insert into t select g, md5(g::text) from generate_series(1,200000) g;The line is written when the file is deleted (end of the operation or transaction), and the STATEMENT line comes from log_min_error_statement (LOG ranks above the default ERROR). The file name contains the PID (pgsql_tmp4621), and the suffix counts files per backend; parallel workers create their own, under a shared fileset directory.
Use it to find candidates for a larger work_mem (per role or per query) or better indexes. Compare with pg_stat_database.temp_files / temp_bytes for totals and EXPLAIN (ANALYZE, BUFFERS) ("Sort Method: external merge Disk: …", "temp read/written") per query. A threshold like 10MB avoids noise from tiny spills. Temporary space can be capped with temp_file_limit.
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 hash_mem_multiplier temp_file_limit temp_tablespaces log_min_error_statement