| Type: | integer |
| Default: | -1 (-1) |
| Min: | -1 (-1) |
| Max: | 2147483647 (2147483647kB) |
| Unit: | KB |
| Context: | superuser |
| Restart: | false |
Controls logging of temporary file names and sizes. Temporary files can be created for sorts, hashes, and temporary query results. If enabled by this setting, a log entry is emitted for each temporary file when it is deleted. A value of zero logs all temporary file information, while positive values log only files whose size is greater than or equal to the specified amount of data. If this value is specified without units, it is taken as kilobytes. The default setting is -1, which disables such logging. Only superusers can change this setting.
Recommendations
This logger is used for troubleshooting sorts and other activities which are spilling to disk. If you use it at all, it's probably good to set it a something low like 1kB so that you know each query that spilled to disk, since any disk spill at all causes a dramatic slowdown in the query. Can be used to see if you need more work_mem, temp_mem or maintenance_work_mem.
On StackOverflow
- How do I tune work_mem based on logs of temp files?
- Why are my Postgresql sessions writing directly on disk?
- is a reduction in free disk space a good overall indicator of a `work_mem` setting that is too low?
- How to customize the configuration file of the official PostgreSQL Docker image?
- PostgreSQL query written millions of times to the log file when using temp files