Plan for running out of memory
Queries sometimes need more memory than they were given. Then an operator spills (writes part of its data to disk and reads it back) or, if it cannot, the query is killed. Spilling a little sounds cheap; it rarely is. What will happen when memory runs out, and what will it cost?
You will end with what happens to each operator when memory runs out, what it costs, and the policies that stop it becoming an outage.
Work through it
List what cannot spill
Window frames, holistic aggregates, state inside user-defined functions, broadcast join sides. That list is your kill inventory. Everything else is a latency problem.
Compute what spilling costs on your real hardware
Let r = memory bandwidth ÷ the bandwidth of the device you actually spill to (the real one, not the one in the architecture diagram). With a fraction f spilled, slowdown ≈ (1 − f) + 2 × f × r, since spilled data goes out and comes back. At r = 50 (local disk), spilling 5% makes the query about 6 times slower. On network-attached disk r can be about 500. That number tells you whether "a little spilling" is acceptable or fatal.
Ask whether one key can outgrow memory
If skew can produce one group larger than memory: hash-based operators are unsafe there whatever the memory, because no amount of repartitioning splits a single key. Use a sort-based fallback, a skew-aware plan, or an aggregate that can be computed in pieces.
Check the aggregate's algebra before anything else
If the aggregate can be combined from partial results (sum, count, min, max): skew stops mattering, because each piece can be reduced as it goes. Rewriting the operation this way is cheaper than every other mechanism here.
Under uncertainty, choose the operator with the better worst case
Memory estimates are wrong by multiples, with a heavy upper tail, so plan for the tail, not the average. Hash is faster in memory but fragile outside it; sort is slower but cannot fail. Choose by how each degrades.
Check whether spill bandwidth is governed
Almost certainly it is not. If many queries spill at once: they compete for the same disk, and that is where the system actually fails, not in the memory manager you tuned.
Make a killed query retry with more memory
If it only retries with the same grant: it fails again the same way, and you have a loop that never finishes.