PostgreSQL work_mem Tuning: Temp File Spills Measured

When a sort or hash table needs more memory than work_mem allows, PostgreSQL writes the excess to temporary files and finishes the work on disk. The usual advice is to raise work_mem. Sometimes that is right. But each sort or hash node in each query can use that much memory, and in this lab, removing every spill made the queries at most 8% faster. Measure what the spills cost first.

The lab used PostgreSQL 18.6 in Docker on a laptop, with parallel query turned off to keep the plans simple. The script and full output are public.

Three spills at the default 4 MB

An orders table with 2 million rows (115 MB) and customers with 500,000. Three common query shapes, each at the default work_mem of 4 MB:

Sort (actual rows=2000000.00 loops=1)
  Sort Key: amount DESC, created_at
  Sort Method: external merge  Disk: 82280kB
  Buffers: shared hit=11965 read=2747, temp read=20560 written=20595

HashAggregate (actual rows=500000.00 loops=1)
  Group Key: customer_id
  Batches: 33  Memory Usage: 8209kB  Disk Usage: 63296kB

Hash (actual rows=500000.00 loops=1)
  Buckets: 262144  Batches: 4  Memory Usage: 7904kB
  Buffers: shared read=3677 written=2700, temp written=1647

The signals to look for in a plan:

• Sort: external merge Disk: instead of quicksort Memory:.
• Hash aggregate: Batches above 1 and a Disk Usage figure.
• Hash join: Batches above 1 on the Hash node.
• Buffers: temp read and temp written, in 8 kB blocks. From PostgreSQL 18, EXPLAIN ANALYZE shows buffers without asking.

Hash nodes get work_mem times hash_mem_multiplier, which defaults to 2 from PostgreSQL 15. That is why the hash aggregate used 8 MB at a 4 MB setting.

Find spills without running EXPLAIN

On a production system you start from the workload, not a single plan. Three sources, from the lab:

-- Per statement: pg_stat_statements
 query                                          | calls | temp_blks_written | temp_written | mean_ms
------------------------------------------------+-------+-------------------+--------------+--------
 SELECT id, customer_id, amount FROM orders ... |     1 |             20595 | 161 MB       |   1820
 SELECT customer_id, count(*), sum(amount) ...  |     1 |             14921 | 117 MB       |    441
 SELECT c.region, o.amount FROM orders o ...    |     1 |              7454 | 58 MB        |    335

-- Per database: pg_stat_database
 datname  | temp_files | temp_bytes
----------+------------+------------
 postgres |          8 | 200 MB

-- In the server log, with log_temp_files = 0
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp62.0", size 84254720
STATEMENT:  EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) ...

Order pg_stat_statements by temp_blks_written to find the statements that write most to temporary files, and divide by calls for the amount per execution. log_temp_files takes a size in kB; 0 logs every file, which is fine in a lab but noisy in production. Start at a few MB. The log entry is written when the file is removed, so a query that is still running does not appear yet; on PostgreSQL 12 and later, pg_ls_tmpdir() lists the temporary files that exist now. Its file names contain the process ID of the session that created them. The two counters disagree because the sort read and wrote its data more than once while merging; temp_bytes counts file sizes.

What more memory bought

The same queries with SET LOCAL work_mem = '256MB':

Sort Method: quicksort  Memory: 142902kB
HashAggregate ... Batches: 1  Memory Usage: 204817kB
Hash ... Buckets: 524288  Batches: 1  Memory Usage: 27534kB

No temp files. Then five runs of each query at each setting, execution time in milliseconds:

The hash aggregate used 200 MB of memory to avoid 63 MB of temp files and was no faster; its run-to-run spread is wider than the difference. The temp files here were small enough to stay in the operating system's page cache on a laptop SSD, so spilling cost little. The queries spent their time reading rows and building sorts and hash tables, not on temp file I/O.

That will not hold everywhere. Spills cost more when:

• Storage is network-attached and throttled. On RDS without Optimized Reads, temp files go to EBS and share its IOPS and throughput limits with table reads and WAL.
• Many sessions spill at once and their temp files no longer fit in the page cache.
• Temp space is limited. On Aurora, temp files share each instance's local storage with log files, so watch the FreeLocalStorage metric. On RDS instance classes with local NVMe (Optimized Reads), temp files go to rds_temp_tablespace on that disk, and AWS notes that transactions can fail when it fills.

So the measurement that matters is your own: the same statement's time and temp usage at two settings, on the instance class it runs on, at normal concurrency.

Why not raise work_mem for everyone

work_mem is a limit per sort or hash node, per process, not per query or per server. A plan with three hash joins and a sort can use four times the setting, hash nodes can use twice that, and each parallel worker gets its own. As an illustration, 200 connections each running one query with two hash nodes at a 256 MB setting and the default multiplier could ask for up to 200 GB. Most of the time far less is used, but the out-of-memory event happens at the busiest moment, and on Linux the out-of-memory killer can restart the whole server.

Give memory to the statements that need it instead:

-- For one transaction (reports, batch jobs, migrations)
BEGIN;
SET LOCAL work_mem = '256MB';
-- the query
COMMIT;

-- For a role that only runs reports
ALTER ROLE reporting SET work_mem = '64MB';

The role setting applies to new sessions of that role; existing pooled connections keep the old value until they reconnect. In the lab, the hash aggregate still used 5 batches at 64 MB because it needed about 200 MB, so check the plan after the change rather than assuming it fits. A SET outside a transaction lasts for the session; with PgBouncer in transaction mode, it can leak to other clients that reuse that server connection, so use SET LOCAL.

Often the better fix is not memory

• Avoid the sort. An index on (amount DESC, created_at) let ORDER BY ... LIMIT 50 read 50 rows from the index: 0.18 ms instead of sorting 2 million rows. A sort with LIMIT uses a top-N heap sort that rarely spills anyway; the expensive case is sorting everything.
• Aggregate less. Filter by date before grouping, or keep a summary table for dashboards that group the whole history on every refresh.
• Fix the estimate. A hash join sized for 1,000 rows that receives 5 million spills because the planner picked the plan for the wrong number of rows. Stale statistics after an upgrade or a bulk load cause this; see slow after a major upgrade.
• Cap the damage. temp_file_limit cancels a query that writes more than the limit, which protects disk space from one bad query.

Checklist

1. Rank statements by temp_blks_written in pg_stat_statements; check temp_files and temp_bytes per database.
2. Set log_temp_files to a threshold of a few MB to see the statements and sizes as they happen.
3. For the top statements, read the plan: sort method, batches, temp buffers, and estimated against actual rows.
4. Try an index, a query change or a statistics fix first.
5. If memory is the answer, set it per transaction or per role, and measure the time at both settings on your own platform.

For the full method, from ranking to validation, see how to profile slow PostgreSQL queries.