Raising work_mem to stop queries spilling to disk made the reports slower and nearly filled the volume

Reported symptoms

The data volume reaches 94 percent every weekday between 12:00 and 13:00 and recovers by 13:30. pgsql_tmp grows to 180 GB during that hour.

The reporting queries in that window take 40 to 90 seconds each and the business considers them slow.

work_mem was raised from 4 MB to 256 MB cluster-wide at the start of the quarter, to stop the spilling.

After the change the reports got slower, and the host began showing memory pressure at lunchtime. One report now occasionally fails, and the server log records a backend terminated by signal 9.

The temp files did shrink, so the change is regarded as partially successful.

Evidence provided

$ grep 'temporary file' /var/log/postgresql/postgresql-18-main.log | tail -3

2026-08-28 01:24:49.947 UTC [12052] postgres@lab14 LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp12052.0", size 189300736
2026-08-28 01:24:51.667 UTC [12059] postgres@lab14 LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp12059.1", size 188845868
2026-08-28 01:24:51.679 UTC [12059] postgres@lab14 LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp12059.0", size 189235200

And the measurement that the change was based on — the same sort, the same data, four settings:

$ SET work_mem = '...'; EXPLAIN (ANALYZE) SELECT ... ORDER BY ...;

  work_mem = 4MB:
             Sort Method: external merge  Disk: 184864kB
   Execution Time: 1295.160 ms
work_mem = 64MB:
             Sort Method: external merge  Disk: 184800kB
   Execution Time: 1679.936 ms
work_mem = 256MB:
             Sort Method: quicksort  Memory: 252269kB
   Execution Time: 1688.832 ms
work_mem = 512MB:
             Sort Method: quicksort  Memory: 252269kB
   Execution Time: 1675.435 ms

A hash aggregate over two million groups: 674 ms at 4 MB, 655 ms at 64 MB, 1473 ms at 1 GB.

The window runs 60 concurrent reporting sessions. The slowest plan contains four memory-consuming nodes — two sorts and two hash joins. The host has 64 GB of RAM and shared_buffers is 16 GB.

Work the evidence before reading on

1. 60 sessions, four memory nodes each, 256 MB. What is the worst case?
2. The sort at 4 MB was faster than the sort at 512 MB. What does that do to the premise of the change?
3. terminated by signal 9 — who sent that signal, and why?
4. Is the 180 GB of temp files the same problem as the slow reports?

Root cause

work_mem is per operation, not per server

The spilling was not the bottleneck

The hash aggregate is more pointed still: 674 ms at 4 MB and 1473 ms at 1 GB. More than twice as slow with a gigabyte, because the plan changed to one that held 762 MB and was worse.

The 180 GB is real, and it is a different problem

It is a genuine capacity issue and it deserves its own answer. It was never evidence that the queries were slow because they were spilling.

Resolution

Return work_mem to a value the host survives at full concurrency, and set it where it belongs:

-- cluster default: sized for ordinary sessions
ALTER SYSTEM SET work_mem = '8MB';
SELECT pg_reload_conf();

-- the reporting role, deliberately
ALTER ROLE reporting SET work_mem = '64MB';

Write down concurrent_sessions x memory_nodes_per_plan x work_mem against RAM minus shared_buffers minus the operating system’s needs. If nobody can state that number, work_mem has not been chosen — it has been guessed.

For a single heavy query, scope it to the transaction:

SET LOCAL work_mem = '512MB';

SET LOCAL reverts at commit, which is what makes it safe inside a report’s own code.

Measure each query at several settings before deciding:

SET work_mem = '4MB';   EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
SET work_mem = '64MB';  EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
SET work_mem = '256MB'; EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

Read Sort Method, Batches and Memory Usage. A hash join reporting Batches: 1 is not spilling; one reporting many batches is, and that is where extra memory most often pays. A sort reporting external merge may be entirely fine.

Then address the disk, as its own problem:

ALTER ROLE reporting SET temp_file_limit = '20GB';

And give temporary files their own space with temp_tablespaces. A full data volume is a PANIC; a full temp tablespace is a failed query.

Verification

pgsql_tmp no longer approaches capacity at lunchtime, against a threshold expressed in hours of headroom rather than a percentage.

No backend is terminated by signal 9 — checked directly, not waited for:

grep -E 'terminated by signal 9|out of memory' /var/log/postgresql/postgresql-18-main.log

Report durations measured per query, before and after. The claim being verified is that each individual report is at least as fast as it was, which is not what happened last quarter.

SELECT datname, temp_files, temp_bytes, pg_size_pretty(temp_bytes) AS temp_pretty
FROM pg_stat_database WHERE datname = 'reporting';

Host memory has headroom at peak concurrency — measured during the lunchtime window, not at 09:00.

temp_file_limit fires when exceeded. Test it once with a deliberately oversized query and confirm the query fails rather than the volume filling.

Prevention

Write the work_mem arithmetic down. A work_mem nobody can defend with that calculation is a latent out-of-memory incident.

Set work_mem per role. Reporting and OLTP have different needs and different concurrency; one number cannot serve both.

Do not treat spilling as a defect. The external merge was faster here, and the hash aggregate was twice as slow with a gigabyte as with four megabytes.

Measure the query, not the counter. temp_bytes says files were written. It says nothing about whether writing them cost anything.

Set temp_file_limit per role. It converts “the volume filled and the cluster PANICked” into “one query failed” — a far better outcome, needing no operator.

Give temporary files their own tablespace.

Keep log_temp_files on, so files can be attributed to queries.

Alert on pgsql_tmp size and temp_bytes growth, separately from the data-volume alert. They move before the volume does.