When PostgreSQL hash operations spill to disk

PostgreSQL 18 scope. The SQL in this article was run on PostgreSQL 18.6 against synthetic fixtures.

A query that is comfortable in memory can become much slower when a hash operation needs temporary storage. The plan may still look familiar: a hash join or hash aggregate remains present. The important evidence is in batches, memory usage, and temporary I/O, not just the node name.

Spilling is not automatically a defect. It is a resource-control behavior that lets a query complete without retaining all working data in memory. The operational question is whether the chosen plan and memory budget are appropriate for the workload's concurrency and latency requirements.

Build a controlled hash-join experiment

Use PostgreSQL 18 in a disposable environment with space for temporary files. These queries execute real work. The fixture is intentionally modest, but its effects depend on the host and settings.

CREATE TABLE spill_customer AS
SELECT g::bigint AS id, repeat(md5(g::text), 4) AS label
FROM generate_series(1,100000) AS s(g);
ALTER TABLE spill_customer ADD PRIMARY KEY(id);
CREATE TABLE spill_event AS
SELECT g::bigint AS id, ((g % 100000) + 1)::bigint AS customer_id
FROM generate_series(1,500000) AS s(g);
ANALYZE spill_customer;
ANALYZE spill_event;

BEGIN;
SET LOCAL work_mem = '1MB';
SET LOCAL hash_mem_multiplier = 1.0;
SET LOCAL max_parallel_workers_per_gather = 0;
SET LOCAL enable_mergejoin = off;
SET LOCAL enable_nestloop = off;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT count(*), sum(length(c.label))
FROM spill_event e JOIN spill_customer c ON c.id = e.customer_id;
ROLLBACK;

Disabling alternative joins is a lab technique to expose hash behavior, not a production tuning recommendation. The settings are local to the transaction. The query references label so the hash input carries more than an integer key; projecting fewer columns can materially change memory needs.

Inspect the Hash node's buckets, batches, and memory usage, plus temporary read and write counters. More than one batch indicates partitioned processing rather than a single in-memory hash table. On PostgreSQL 18.6 with work_mem = '1MB', this lab's hash used 32 batches and wrote about 3,600 temporary blocks; your counts depend on settings and data.

Compare one changed budget

Repeat the same transaction with a larger local work_mem, keeping the other settings and dataset unchanged. Record plan shape and temporary activity, not only total runtime. A second execution may benefit from warmed caches, so alternate run order or repeat enough controlled trials to separate cache effects from memory effects.

The hash memory budget is influenced by work_mem and hash_mem_multiplier. It is not a per-query global reservation. Multiple operators, sessions, and parallel workers can consume memory concurrently. A setting that helps one isolated report can create pressure when many copies run at once.

Reference resource flow. A hash build exceeds its available budget, partitions work into batches, and uses temporary storage; concurrency multiplies the broader memory exposure.

Reduce the work before buying memory

Ask whether predicates can reduce rows before the join without changing semantics. Inspect whether the build side includes unnecessarily wide columns. A query that joins full JSON documents when it needs only an identifier may carry avoidable payload through the hash table.

Check estimates. If the planner expects a small build side and receives a much larger one, statistics or parameter-sensitive planning may be the root cause. Raising memory can hide the symptom for one dataset while leaving the wrong plan choice intact.

Join order matters too. A selective filter applied late can force the executor to hash more rows than a different valid plan would require. But moving filters across outer joins can change results. Prove semantic equivalence before rewriting an outer-join condition from ON to WHERE or vice versa.

Distinguish hashes from sorts

A sort can also use temporary files, but its diagnostics and tuning interpretation differ. Look for sort method, memory or disk usage, and temporary buffers. Do not conclude that every temporary write comes from the hash node simply because the plan contains one.

Hash aggregation has its own group-cardinality problem. A query may read a manageable number of rows but create nearly as many distinct groups. Better group-count estimates, a narrower grouping key, or a different aggregation strategy can matter as much as the raw input size.

Parallel plans require careful accounting. Workers can execute memory-consuming operations, and parallel hash behavior is not identical to multiplying every printed value by worker count. Use the plan's worker details and system-level observation to understand actual resource use rather than relying on a single simplistic formula.

Put guardrails around experiments

Use local settings for a targeted report before considering a cluster-wide change. Review temporary-file limits and available disk space. A limit can prevent one query from exhausting storage, but exceeding it can fail the query; that failure behavior must be acceptable to the caller.

If temporary-file logging is enabled through an approved operational change, correlate files with query and session context. Logging is evidence collection, not a remedy. Avoid capturing sensitive query text beyond the organization's observability policy.

The acceptance test should include realistic concurrent executions, not just one fast query in an idle database. Track memory pressure, temporary I/O, queueing, and tail latency. A useful outcome might be that a bounded report receives a larger local budget while interactive traffic retains conservative defaults.

Spill tuning is resource budgeting under uncertainty. The best change is often a combination of better estimates, less intermediate data, and a narrowly scoped memory adjustment, with the concurrency test proving that the improvement survives outside the single-query lab.

Sources and review scope

• Resource consumption settings
• Planner configuration
• EXPLAIN
• Error reporting and logging