Lab 14: Make a query spill to disk, then find out whether that actually mattered

Objective

“Increase work_mem” is standard advice, usually given without measurement, and this lab is about measuring it.

You will make a sort, a hash join and an aggregate all spill to disk, learn to recognise each from its plan line, and then run each one across a range of work_mem settings with a stopwatch.

The results are not the ones the advice predicts. In one case the on-disk version was faster than the in-memory version. In another, giving the query more memory made the planner choose a worse plan. In the third, spilling genuinely cost 25% — and every setting above the one that stopped the spill bought exactly nothing.

Those three outcomes are the real content of this lab, and they are why the answer to “how much work_mem” is a measurement rather than a number.

Architecture

Two large tables, enough to exceed any sensible work_mem, and three query shapes that each hold memory differently.

flowchart TD
    E["events, 2,000,000 rows, 266 MB"] --> Q1["full sort\n-> Sort Method"]
    E --> Q2["hash join\n-> Batches"]
    E --> Q3["group by 2M groups\n-> HashAggregate or GroupAggregate"]
    C["customers, 1,000,000 rows"] --> Q2
    Q1 --> S1["external merge Disk: 184864kB"]
    Q2 --> S2["Batches: 8 -> 1"]
    Q3 --> S3["planner picks a different node entirely"]
    S1 --> T["temp files:\nlog_temp_files, pg_stat_database"]
    S2 --> T

Requirements

• A PostgreSQL 18 cluster with superuser access. The lab creates and drops a database called lab14.
• Roughly 1 GB of free disk. The data is about 400 MB and the temporary files reached 1109 MB across the lab run.
• log_temp_files = 0 for Task 7. The lab sets and resets it.

Scenario

A nightly report is slow. Its plan shows Sort Method: external merge and somebody has proposed raising work_mem from 4 MB to 1 GB.

Before agreeing you want to know two things: how much faster the query actually gets, and how much memory the server would be committing if every session did the same.

Tasks

Task 1 — Build the data and read the settings

LAB="$HOME/rbpg-lab-14"
mkdir -p "$LAB"

docker exec -i -u postgres rbpg-lab01 psql -X -c "CREATE DATABASE lab14;"
docker exec -i -u postgres rbpg-lab01 psql -X -d lab14 <<'SQL'
CREATE TABLE events(id int PRIMARY KEY, customer_id int, category text,
                    amount numeric, note text);
INSERT INTO events SELECT g, 1+(g%50000), 'cat-'||(g%2000), (g%997)*1.37, repeat('n',60)
  FROM generate_series(1,2000000) g;
SQL
docker exec -u postgres rbpg-lab01 psql -X -d lab14 -c "VACUUM ANALYZE events;"

docker exec -u postgres rbpg-lab01 psql -X -c "ALTER SYSTEM SET log_temp_files = 0;"
docker exec -u postgres rbpg-lab01 psql -X -c "SELECT pg_reload_conf();"

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT name, setting, unit, context FROM pg_settings
  WHERE name IN ('work_mem','hash_mem_multiplier','maintenance_work_mem',
                 'temp_file_limit','log_temp_files','max_parallel_workers_per_gather')
  ORDER BY name;"

$ psql -X -c "SELECT name, setting, unit, context FROM pg_settings WHERE name IN (...) ORDER BY name;"

              name               | setting | unit |  context  
---------------------------------+---------+------+-----------
hash_mem_multiplier             | 2       |      | user
log_temp_files                  | 0       | kB   | superuser
maintenance_work_mem            | 65536   | kB   | user
max_parallel_workers_per_gather | 2       |      | user
temp_file_limit                 | -1      | kB   | superuser
work_mem                        | 4096    | kB   | user

work_mem is 4 MB, and hash_mem_multiplier is 2 — hash operations get twice the budget of sorts. temp_file_limit = -1 means a single query may write unlimited temporary data, which Task 7 revisits.

Task 2 — A first attempt that does not spill

docker exec -u postgres rbpg-lab01 psql -X -d lab14 \
  -c "SET max_parallel_workers_per_gather = 0;" \
  -c "EXPLAIN ANALYZE SELECT * FROM events ORDER BY amount, id LIMIT 10;"

$ EXPLAIN ANALYZE on an ORDER BY with a LIMIT

 Limit  (cost=91791.28..91791.31 rows=10 width=83) (actual time=178.790..178.792 rows=10.00 loops=1)
 ->  Sort  (cost=91791.28..96791.28 rows=2000000 width=83) (actual time=178.788..178.789 rows=10.00 loops=1)
       Sort Key: amount, id
       Sort Method: top-N heapsort  Memory: 26kB
       ->  Seq Scan on events  (cost=0.00..48572.00 rows=2000000 width=83) (actual time=0.082..75.242 rows=2000000.00 loops=1)
Execution Time: 178.825 ms

top-N heapsort Memory: 26kB. Two million rows sorted, and the sort used 26 kilobytes.

ORDER BY ... LIMIT 10 never needs to hold more than ten rows. This is worth knowing before you tune anything: the presence of a large sort in a query does not mean the query needs memory, and adding LIMIT to a report is often a far better fix than adding memory.

Task 3 — Force a real sort, and watch it spill

docker exec -u postgres rbpg-lab01 psql -X -d lab14 \
  -c "SET max_parallel_workers_per_gather = 0;" \
  -c "EXPLAIN ANALYZE
      WITH s AS MATERIALIZED (SELECT * FROM events ORDER BY amount, id)
      SELECT count(*) FROM s;" | tee "$LAB/sort-spill.txt"

$ EXPLAIN ANALYZE on a materialised CTE that sorts every row

 Aggregate  (cost=499295.69..499295.70 rows=1 width=8) (actual time=1244.377..1244.378 rows=1.00 loops=1)
 CTE s
   ->  Sort  (cost=449295.69..454295.69 rows=2000000 width=83) (actual time=838.679..1011.649 rows=2000000.00 loops=1)
         Sort Key: events.amount, events.id
         Sort Method: external merge  Disk: 184864kB
         ->  Seq Scan on events  (cost=0.00..48572.00 rows=2000000 width=83) (actual time=0.186..78.396 rows=2000000.00 loops=1)
 ->  CTE Scan on s  (cost=0.00..40000.00 rows=2000000 width=0) (actual time=838.685..1185.513 rows=2000000.00 loops=1)
       Storage: Disk  Maximum Storage: 184416kB
Execution Time: 1279.642 ms

Sort Method: external merge Disk: 184864kB is the signature. Note also Storage: Disk Maximum Storage: 184416kB on the CTE scan — the materialised result spilled too, which is a second, separate consumer of memory in the same query.

The four sort methods you will meet:

Task 4 — Now measure whether the spill mattered

for WM in 4MB 64MB 256MB 512MB; do
  echo "work_mem = $WM:"
  docker exec -u postgres rbpg-lab01 psql -X -d lab14 \
    -c "SET max_parallel_workers_per_gather = 0;" \
    -c "SET work_mem = '$WM';" \
    -c "EXPLAIN ANALYZE
        WITH s AS MATERIALIZED (SELECT * FROM events ORDER BY amount, id)
        SELECT count(*) FROM s;" \
    | grep -E "Sort Method|Execution Time"
done | tee -a "$LAB/sort-spill.txt"

$ the same sort at four work_mem settings

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

This is not the expected result and it is worth sitting with.

Going from 4 MB to 256 MB eliminated the spill entirely — 180 MB of temporary file writes became zero — and the query got slower, from 1295 ms to 1689 ms. Sixty-four times the memory, for a 30% regression.

Task 5 — A case where less memory produces a better plan

for WM in 4MB 1GB; do
  echo "work_mem = $WM:"
  docker exec -u postgres rbpg-lab01 psql -X -d lab14 \
    -c "SET max_parallel_workers_per_gather = 0;" \
    -c "SET work_mem = '$WM';" \
    -c "SET enable_sort = off;" \
    -c "EXPLAIN ANALYZE SELECT id, sum(amount) FROM events GROUP BY id;" \
    | grep -E "Aggregate|Index Scan|Batches|Memory Usage|Execution Time"
done | tee "$LAB/aggregate.txt"

$ the same GROUP BY at 4MB and 1GB

work_mem = 4MB:
GroupAggregate  (cost=0.43..115519.43 rows=2000000 width=36) (actual time=2.421..600.638 rows=2000000.00 loops=1)
 Group Key: id
 ->  Index Scan using events_pkey on events  (cost=0.43..80519.43 rows=2000000 width=10) (actual time=0.030..214.883 rows=2000000.00 loops=1)
Execution Time: 660.146 ms

work_mem = 1GB:
HashAggregate  (cost=58572.00..83572.00 rows=2000000 width=36) (actual time=696.969..1391.731 rows=2000000.00 loops=1)
 Batches: 1  Memory Usage: 761873kB
Execution Time: 1472.613 ms

At 4 MB the planner concluded a hash table for two million groups was unaffordable and chose a GroupAggregate over the primary key index — which is already sorted, so it needs essentially no memory at all. 660 ms.

At 1 GB it concluded the hash table was affordable, built one using 761 MB, and took 1472 ms.

Task 6 — A case where spilling genuinely costs

docker exec -i -u postgres rbpg-lab01 psql -X -d lab14 <<'SQL'
CREATE TABLE customers(id int PRIMARY KEY, name text, tier text, blurb text);
INSERT INTO customers SELECT g, 'name-'||g, 'tier-'||(g%5), repeat('b',120)
  FROM generate_series(1,1000000) g;
SQL
docker exec -u postgres rbpg-lab01 psql -X -d lab14 -c "VACUUM ANALYZE customers;"

for WM in 1MB 8MB 64MB 512MB; do
  echo "work_mem = $WM:"
  docker exec -u postgres rbpg-lab01 psql -X -d lab14 \
    -c "SET max_parallel_workers_per_gather = 0;" \
    -c "SET work_mem = '$WM';" \
    -c "SET enable_mergejoin = off;" -c "SET enable_nestloop = off;" \
    -c "EXPLAIN ANALYZE SELECT count(*) FROM events e
        JOIN customers c ON c.id = e.customer_id WHERE c.tier='tier-2';" \
    | grep -E "Batches|Execution Time"
done | tee "$LAB/hash-batches.txt"

$ the same hash join at four work_mem settings

work_mem = 1MB:
 Buckets: 65536  Batches: 8  Memory Usage: 1390kB
 Execution Time: 413.476 ms
work_mem = 8MB:
 Buckets: 262144  Batches: 1  Memory Usage: 9080kB
 Execution Time: 332.442 ms
work_mem = 64MB:
 Buckets: 262144  Batches: 1  Memory Usage: 9080kB
 Execution Time: 332.864 ms
work_mem = 512MB:
 Buckets: 262144  Batches: 1  Memory Usage: 9080kB
 Execution Time: 334.487 ms

Here the spill genuinely costs: 413 ms with 8 batches against 332 ms with one, a 25% penalty.

And here is the number that matters for tuning: 8 MB was enough. 64 MB and 512 MB produced identical plans, identical memory usage of 9080 kB, and identical times. Sixty-four times the memory bought nothing whatsoever.

Task 7 — hash_mem_multiplier, and predicting the batch count

for HM in 1 2 4; do
  echo "work_mem = 1MB, hash_mem_multiplier = $HM:"
  docker exec -u postgres rbpg-lab01 psql -X -d lab14 \
    -c "SET max_parallel_workers_per_gather = 0;" \
    -c "SET work_mem = '1MB';" -c "SET hash_mem_multiplier = $HM;" \
    -c "SET enable_mergejoin = off;" -c "SET enable_nestloop = off;" \
    -c "EXPLAIN ANALYZE SELECT count(*) FROM events e
        JOIN customers c ON c.id = e.customer_id WHERE c.tier='tier-2';" \
    | grep -E "Batches|Execution Time"
done | tee -a "$LAB/hash-batches.txt"

$ the same hash join at three hash_mem_multiplier values

work_mem = 1MB, hash_mem_multiplier = 1:
 Buckets: 32768  Batches: 16  Memory Usage: 695kB
 Execution Time: 427.246 ms
work_mem = 1MB, hash_mem_multiplier = 2:
 Buckets: 65536  Batches: 8  Memory Usage: 1390kB
 Execution Time: 416.389 ms
work_mem = 1MB, hash_mem_multiplier = 4:
 Buckets: 131072  Batches: 4  Memory Usage: 2778kB
 Execution Time: 412.170 ms

16, 8, 4. Memory usage doubles each time and the batch count halves, exactly as the arithmetic predicts.

hash_mem_multiplier exists because hash tables and sorts have different appetites: a hash table that does not fit degrades sharply, whereas a sort that does not fit degrades gently — which is precisely what Tasks 4 and 6 measured. Raising the multiplier gives hash operations more room without giving every sort in the system the same increase.

Task 8 — work_mem is per operation, and the arithmetic that follows

docker exec -u postgres rbpg-lab01 psql -X -d lab14 \
  -c "SET max_parallel_workers_per_gather = 0;" -c "SET work_mem = '4MB';" \
  -c "EXPLAIN ANALYZE
      SELECT c.tier, count(DISTINCT e.category), count(*)
      FROM events e JOIN customers c ON c.id = e.customer_id
      GROUP BY c.tier ORDER BY 1;"

$ EXPLAIN ANALYZE on a query with a join, a distinct aggregate and an ordering

 GroupAggregate  (cost=400721.40..420721.45 rows=5 width=23) (actual time=1438.619..1618.232 rows=5.00 loops=1)
 Group Key: c.tier
 ->  Sort  (cost=400721.40..405721.40 rows=2000000 width=15) (actual time=1394.013..1511.628 rows=2000000.00 loops=1)
       Sort Key: c.tier, e.category
       Sort Method: external merge  Disk: 49880kB
       ->  Nested Loop  (cost=0.43..123043.72 rows=2000000 width=15) (actual time=4.369..500.473 rows=2000000.00 loops=1)
             ->  Seq Scan on events e  (cost=0.00..48572.00 rows=2000000 width=12) (actual time=0.102..88.531 rows=2000000.00 loops=1)
             ->  Memoize  (cost=0.43..0.50 rows=1 width=11) (actual time=0.000..0.000 rows=1.00 loops=2000000)
                   Cache Key: e.customer_id
                   Cache Mode: logical
                   Hits: 1950000  Misses: 50000  Evictions: 0  Overflows: 0  Memory Usage: 5469kB
                   ->  Index Scan using customers_pkey on customers c  (cost=0.42..0.49 rows=1 width=11) (actual time=0.001..0.001 rows=50000)
Execution Time: 1633.911 ms

Two nodes are holding memory at the same time: the Sort (spilling 49880 kB) and the Memoize cache (5469 kB). Both are governed by work_mem, and each gets its own allowance.

That is the arithmetic nobody does before raising the setting:

peak memory ≈ work_mem
              × memory-hungry nodes per query
              × (1 + parallel workers per node)
              × concurrent queries

At work_mem = 1GB, a plan with three such nodes, two parallel workers each, in ten concurrent sessions, has a theoretical ceiling of 90 GB. Nothing enforces that ceiling; the server will attempt it and the kernel will decide how it ends.

Task 9 — Account for what spilled

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT datname, temp_files, pg_size_pretty(temp_bytes) AS temp_bytes
  FROM pg_stat_database WHERE datname='lab14';" | tee "$LAB/accounting.txt"

docker exec rbpg-lab01 grep "temporary file" \
  /var/log/postgresql/postgresql-18-main.log | tail -3 | tee -a "$LAB/accounting.txt"

$ pg_stat_database temp counters, then the log lines log_temp_files produced

 datname | temp_files | temp_bytes 
---------+------------+------------
lab14   |          7 | 1109 MB
(1 row)

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

Two complementary views:

pg_stat_database.temp_files and temp_bytes are cumulative counters per database since the last statistics reset. Graphing temp_bytes over time is the cheapest possible way to notice that spilling has started happening, without inspecting a single plan.

log_temp_files logs each temporary file with its size and the statement that created it. Set to 0 it logs everything, which is noisy; a threshold in the tens of megabytes catches the queries worth looking at and ignores the rest.

Validation

test -s "$LAB/sort-spill.txt"   && echo "OK sort-spill"
test -s "$LAB/hash-batches.txt" && echo "OK hash-batches"
test -s "$LAB/aggregate.txt"    && echo "OK aggregate"
test -s "$LAB/accounting.txt"   && echo "OK accounting"

grep -q "external merge"  "$LAB/sort-spill.txt"   && echo "OK sort spill captured"
grep -q "Batches: 8"      "$LAB/hash-batches.txt" && echo "OK hash spill captured"
grep -q "Batches: 1"      "$LAB/hash-batches.txt" && echo "OK plateau captured"
grep -q "GroupAggregate"  "$LAB/aggregate.txt"    && echo "OK plan change captured"
grep -q "temporary file"  "$LAB/accounting.txt"   && echo "OK temp files logged"

Questions to answer without looking anything up:

1. A plan shows Sort Method: top-N heapsort Memory: 26kB over two million rows. Does this query need more work_mem?
2. A hash node reports Memory Usage: 9080kB and Batches: 1 at work_mem = 8MB. What do you gain by setting it to 512 MB?
3. Raising work_mem made a query slower without changing the data. Give two distinct mechanisms that could explain it.
4. Why is there no setting that caps PostgreSQL’s total query-execution memory, and what two settings limit the damage indirectly?
5. Sort Method: external merge Disk: 184864kB. Is 185 MB of work_mem enough to stop the spill?

Expected Outcome

You have made three different node types spill, measured each across a range of settings, and found that the effect ranged from a 25% penalty to no penalty at all to a negative penalty.

The procedure that follows from that:

1. Find the spilling node in the plan — Sort Method, Batches, or Storage: Disk.
2. Note the reported size. For a hash node, Memory Usage; for a sort, the Disk: value plus a substantial margin, since the in-memory form is larger — 184 MB on disk needed 252 MB in memory here.
3. Set work_mem for that session or role, and measure. The spill disappearing is not the same as the query getting faster.
4. Find the plateau and stop there.
5. Multiply by the plan’s memory-hungry nodes, its parallel workers and your concurrency, and check the total is one the server can survive.

Troubleshooting

The query does not spill. It is not sorting enough. A LIMIT may allow a top-N sort, an index may already provide the order, or the data is simply smaller than work_mem. Check the Sort Method line: top-N heapsort and quicksort are in memory; external merge is the spill.

Sort Method: external merge Disk: 184MB and setting work_mem to 184MB still spills. The in-memory representation is larger than the on-disk one — measured here, 184 MB on disk needed 252 MB in memory. Size from the disk figure plus a substantial margin, then confirm.

Raising work_mem made the query slower. That happened here, twice, and it is a real result rather than an error: a hash aggregate was more than twice as slow at 1 GB (1473 ms) as at 4 MB (674 ms), and an on-disk external merge sort beat an in-memory quicksort (1295 ms against 1689 ms). Measure the query, not the presence of the spill.

A hash node still reports Batches: 8 at a work_mem you calculated as sufficient. Hash nodes get work_mem × hash_mem_multiplier, which defaults to 2.0. Multiply before you predict the batch count.

log_temp_files produces nothing. It is a size threshold in kilobytes, and -1 disables it. Set it to 0 to log every temporary file regardless of size.

Total memory use is far higher than work_mem. It is per operation, not per query — Task 8’s arithmetic. A plan with three memory-hungry nodes and two parallel workers can allocate many multiples of the setting, and that is before concurrency.

Cleanup

docker exec -u postgres rbpg-lab01 psql -X -c "ALTER SYSTEM RESET log_temp_files;"
docker exec -u postgres rbpg-lab01 psql -X -c "SELECT pg_reload_conf();"
docker exec -u postgres rbpg-lab01 psql -X -c "DROP DATABASE IF EXISTS lab14;"

Production notes

• Set work_mem on the role, not globally. A reporting role that runs three large sorts a day and an OLTP role running thousands of small statements want different values, and one global number is chosen for whichever complained most recently.
• Size from measurement and stop at the plateau. Spilling disappearing is not the goal; the query getting faster is, and this lab found cases where those are not the same thing.
• Do the concurrency arithmetic before you raise it: memory-hungry nodes in the plan, times parallel workers, times concurrent sessions. A setting that is safe for one session can exhaust the host for fifty.
• Alert on pg_stat_database.temp_bytes as a rate. A workload that has always spilled is a tuning question; a workload that has started spilling is a change worth explaining.
• temp_file_limit is enforced per process and is a real safety net against one query filling the data volume — but a query that hits it fails, so choose the value knowing that.

What You Learned

• work_mem is per operation, per worker — not per query and not per connection.
• Hash nodes get work_mem × hash_mem_multiplier, which defaults to 2.0, and that is what determines the batch count.
• The in-memory form is bigger than the on-disk form. 184 MB of external merge needed 252 MB of work_mem to stay in memory.
• Spilling is not automatically bad. Measured here, an on-disk sort beat the in-memory one, and a hash aggregate was twice as slow with more memory.
• log_temp_files = 0 logs every temporary file, and pg_stat_database.temp_bytes aggregates them per database.
• The right setting is found by measuring the query at several values and stopping at the plateau, not by calculating one number and applying it.