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)

Explanation:

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 operation.

Whether this log line is output or not, is determined by the log_temp_files configuration setting.

By default this setting is disabled, but it can often make sense to simply set this to log_temp_files = 0, which will log every temporary file that got written.

Recommended Action:

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 log_temp_files byte limit to reduce noise in the logs.

However, what can often happen on OLTP type systems that are not well tuned, is that 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.

Learn More:

• pganalyze Guide: Adjusting work_mem based on temporary file creation