A hash join spilled gigabytes. Should we just add memory?

What the hash join actually builds

Think of joining recent orders to their items:

SELECT o.merchant_id, SUM(i.quantity)
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.created_at >= :cutoff
GROUP BY o.merchant_id;

A typical hash join chooses one input to build a hash table, often the smaller one. For each build-side row, it hashes the join key and retains the data needed for matching. Then it scans the probe side, hashes each probe key, and finds matching rows in memory. When the build data fits, the join avoids repeated full scans or individual index lookups for every matching row.

The hash table is not the final grouped result. It is an intermediate structure that makes matching rows cheap. Its real memory need includes hash buckets, pointers, row data, alignment, and executor overhead. Counting only the raw join keys produces an unrealistically small capacity estimate.

A sort, an aggregation, another hash table, and multiple sessions can all hold memory simultaneously. That makes “the server has 128 GB of RAM” a poor reason to grant any one join 100 GB.

The threshold is not a switch from correct to incorrect

When the build-side hash table cannot stay within its memory budget, the executor can split the work into batches. The working batch is handled in memory, while other partitions of build and probe rows are written to temporary storage and revisited. Rows still match correctly. The cost changes because the query does extra writes, reads, bookkeeping, and sometimes repeated partitioning when skew defeats an even distribution.

That explains the sharp performance change. The report may be doing only twice as much logical work but many times as much physical I/O. If ten such reports spill simultaneously, they can interfere with each other, with the database's regular workload, and with background maintenance. This is an overload problem caused by query execution, not a broker or API retry problem.

In PostgreSQL, the Hash node in EXPLAIN ANALYZE reports buckets, batches, and peak memory usage. A hash operation with multiple batches is an important signal of partitioned work. BUFFERS can show temporary block reads and writes. Check the whole plan, because a separate Sort or aggregation can spill independently. PostgreSQL describes these fields in its EXPLAIN documentation.

Measure before increasing memory

Suppose work_mem is 16 MB and hash_mem_multiplier is 2. Under that configuration, the nominal hash-operation budget is about 32 MB. Those are illustrative settings, not a recommendation. PostgreSQL calculates hash memory limits from these two parameters, while sorts and other memory-consuming nodes have their own allocations. The resource configuration guide warns that a query can contain several such nodes and that concurrent sessions multiply the total demand.

A naive fix is to set work_mem to 1 GB globally. Now consider 100 concurrent reports, each with multiple memory-consuming operators. We have traded temp-file pressure for a plausible memory-exhaustion failure. The correct budget depends on query concurrency, operator counts, available memory, and what else must stay healthy.

I would record three things under representative load: the build and probe input sizes, the number of hash batches and temporary bytes, and the concurrent query count. Then examine whether the filter on orders.created_at is applied early, whether columns can be projected away before the join, and whether the query aggregates detail rows far later than necessary. Reducing the material fed to a join is usually more robust than allowing the same expensive shape to consume unlimited memory.

Choose a different join only for a reason

Hash join is not the only option. A nested-loop join with an indexed inner lookup can be excellent when the outer side is small. It can be terrible when millions of outer rows each demand a separate lookup. A merge join can take advantage of already sorted inputs, but sorting unsorted inputs also costs time and memory. The best choice follows the workload and access paths, not a ranking of join algorithms from fastest to slowest.

For this report, I would also consider a pre-aggregated daily order-items projection if the product needs repeated summaries rather than every raw line. The projection requires its own freshness and correction contract. It cannot simply be declared equivalent to the original query if orders can change or refunds revise earlier totals.

If a small number of reports causes the damage, constrain reporting concurrency or move it to isolated resources. Admission control is important even after an optimized query is shipped, because users can request larger windows later. A working query plan is not a capacity guarantee.

The interview discussion should establish the operator's memory model, show why crossing that budget changes I/O, and pick a fix based on evidence. “Add more RAM” can be one evaluated option, but it is not the explanation.

The team gets the join back under control. A different endpoint now uses a covering index and promises not to read the table at all. Its plan says Index Only Scan, yet heap reads remain high. What is still missing from the index?