S7: Temporary file: path ..., size ... (temp file created)
Example Postgres Log Output:
LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp15967.0", size 200204288 STATEMENT: alter table pgbench_accounts add primary key (aid)
This log event is emitted when a temporary file was written, used as part of normal operations, and then deleted again.
Commonly you would see this when a query could not allocate enough memory due to
work_mem restrictions, and had to use a file on disk to perform a sort or hash
Whether this log line is output or not, is determined by the
By default this setting is disabled, but it can often make sense to simply set
log_temp_files = 0, which will log every temporary file that got written.
If you see this infrequently its usually not a problem, as its probably just an analytics query that requires more data to be processed than could fit into RAM.
You may also see this a lot on a system that is designed for data warehousing,
where again this would be expected, and you may want to raise the
byte limit to reduce noise in the logs.
However, what can often happen on OLTP type systems that are not well tuned, is
work_mem is configured way too low, and a lot of temporary files get created
for seemingly simple queries. You will also see I/O spikes in such situations,
since the temporary files need to be written to disk, read in again, etc.
In such cases it makes sense to raise
work_mem based on these log messages,
and see whether they then disappear.
Couldn't find what you were looking for or want to talk about something specific?
Start a conversation with us →