Temp files and work_mem

Question

My Postgres instance is logging things like this:

2021-11-16 22:18:52.491 UTC [1808765]: [3-1] db=xxx,user=xxx LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp1808765.0", size 262144

My understanding is that if work_mem is too low, Postgres can use a temporary file for sorting. But if I run show work_mem; I see that the current value is 86000kB, which is much bigger than the size of the temp files I see mentioned in the logs. Is there something else I should be looking at here?

Answer 1

Be sure to understand what the work_mem setting actually does. The manual:

work_mem (integer)

Sets the base maximum amount of memory to be used by a query operation (such as a sort or hash table) before writing to temporary disk files. If this value is specified without units, it is taken as kilobytes. The default value is four megabytes (4MB). Note that for a complex query, several sort or hash operations might be running in parallel; each operation will generally be allowed to use as much memory as this value specifies before it starts to write data into temporary files. Also, several running sessions could be doing such operations concurrently. Therefore, the total memory used could be many times the value of work_mem; it is necessary to keep this fact in mind when choosing the value. Sort operations are used for ORDER BY, DISTINCT, and merge joins. Hash tables are used in hash joins, hash-based aggregation, result cache nodes and hash-based processing of IN subqueries.

Hash-based operations are generally more sensitive to memory availability than equivalent sort-based operations. The memory available for hash tables is computed by multiplying work_mem by hash_mem_multiplier. This makes it possible for hash-based operations to use an amount of memory that exceeds the usual work_mem base amount.

(hash_mem_multiplier was added in Postgres 13.)

If Postgres needs more memory than work_mem allows for any given operation, it starts spilling to disk. So what you observed is the excess:

I see that the current value is 86000kB, which is much bigger than the size of the temp files I see mentioned in the logs.

Increase your work_mem setting (at least) by the size of the observed temporary file and it should go away.

However, don't set work_mem too high, either, as that might starve out cache and concurrent transactions. One can always set a higher work_mem locally for exceptionally big operations. See:

• Slow query performance due to temporary file?
• Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries