How does a hash join handle memory overflow?

WHAT THIS TESTS The interviewer wants to see whether you understand that hash joins are not magic and that databases have concrete spill-to-disk algorithms for when the build side exceeds available work memory. It probes your knowledge of execution internals beyond the happy path.

A GOOD ANSWER COVERS The canonical solution is the Grace hash join. Apply a hash function to the join key to split the build relation into partitions small enough that each fits in memory, writing those partitions to disk. Apply the identical hash function to the probe relation so matching keys land in corresponding partitions. Then load one build partition into memory, build its hash table, stream the matching probe partition, and emit results, repeating per partition pair. A refinement is the hybrid hash join, which keeps the first partition entirely in memory to avoid one round trip of I/O. If a single partition is still too large because of key skew, the engine recurses, repartitioning that partition with a different hash function.

COMMON WRONG ANSWERS Saying the query simply fails, or that the planner always switches to a sort-merge or nested loop join. Real planners may pick those at plan time, but at runtime the hash operator spills rather than aborts. Another miss is forgetting that the probe side must be partitioned by the same function, or ignoring skew handling entirely.

LIKELY FOLLOW-UPS What happens with severe data skew where one bucket dominates? How does the optimizer estimate whether a hash join fits before execution? How does work_mem or equivalent settings influence the choice? What is the I/O cost compared to a sort-merge join?

ONE CONCRETE EXAMPLE PostgreSQL building a hash table on a 4 GB orders table with 256 MB of work memory will batch the build side into multiple on-disk partitions, increasing nbatch dynamically as it discovers it underestimated rows. EXPLAIN ANALYZE will show Batches greater than one and a Disk usage figure, signaling the hash join spilled rather than staying purely in memory.