What spilling actually costs

Why does spilling 5% of the data make a query several times slower?

There are four components. They are ordered by how often people forget them.

1. The write. N bytes, sequential, predictable. At about 1 GB/s of local NVMe, 100 GB takes 100 seconds. This is the only term most people budget.

2. The read-back. Another N bytes, read later, competing with whatever else is spilling by then. The cost is 2N, not N.

3. The pipeline break. A pipeline is a chain of operators where each row flows to the next operator immediately, so the operators work at the same time. A spilling operator breaks this. The partitioning pass must complete before any output is produced. So the operator now blocks where it may not have blocked before, and its full duration lands on the critical path (the chain of steps that determines total time) instead of overlapping with downstream work.

This has a consequence that is not obvious and that closes a loop back to Rationing. The operator's residence time W (how long a query stays in the system) jumps. By Little's Law (average number in the system = arrival rate × average time in the system), with arrival rate λ unchanged, the number of queries in flight L = λW rises in the same proportion. Every one of those queries holds a memory grant.

The relief mechanism feeds the condition it relieves. Whole-system overload behaviour is a topic of its own, but the mechanism belongs here, and the important fact is that it is structural, not a tuning error.

4. The resource shift. This is where the cliff comes from. A spilling operator stops being memory-bandwidth-bound and becomes I/O-bound. These two resources differ by roughly 50× per node: of the order of 100+ GB/s of memory bandwidth against 1–2 GB/s of local NVMe. So the slowdown is not proportional to the fraction spilled. It is governed by the bandwidth ratio applied to that fraction.

Let f be the spill fraction, and r = B_mem / B_disk the bandwidth ratio. Counting the read-back:

(The (1 − f) part is the data that stays in memory. The 2·f·r part is the data that goes to disk and back, each byte costing r times more, twice.)

At r = 50:

This one calculation explains every "it was fine and then it fell off a cliff" report. Nobody spills 100% and is surprised. People spill 5% and are astonished.

Honest caveat. Real engines overlap I/O with computation, so this formula is an upper bound, and well-tuned systems do better. But the shape is right, and the shape is what matters: the penalty is set by the ratio of two bandwidths, not by the fraction of the data that crossed between them.

This is not a contradiction of hybrid hash's "ramp, not a cliff". Hybrid hash makes the extra I/O grow in proportion to the spilled fraction, a ramp when measured against the cost of a fully spilled join; measured against the in-memory join, each spilled byte costs r times more, which is the cliff.

One-line compression: the slowdown from spilling is governed by the memory-to-disk bandwidth ratio, not the spill fraction, which is why 5% spilled feels like a cliff.