Spill to disk and memory budgets

For a hash build, row payload is only a starting point. Bucket arrays, entry headers, alignment, pointers or offsets, load-factor choices, and allocator metadata add space. A sort similarly retains keys, references, or tuples depending on implementation. An estimated row width can omit representation-specific overhead, and a cardinality underestimate can make the expected fit wrong before the first allocation. Distinguish logical input bytes from retained algorithm state. The exact accounting belongs to the operator implementation, so a payload formula should be labeled as an estimate or lower category, not a guaranteed allocation size.

Some database systems negotiate centralized memory grants for a query. PostgreSQL's work_mem has a different contract: it is a base per-operation allowance used by relevant sorts and other operations, not one reservation for the entire query or connection. Hash-based operations can use the documented hash_mem_multiplier with that base. Multiple plan nodes, sessions, and parallel execution contexts can use memory concurrently. Shared versus private parallel state also matters, so multiplying a worker count blindly may overcount shared state or miss per-worker copies. Inspect the actual plan and deployment version.

An external sort can form sorted runs that fit its memory budget and then merge them. A partitioned hash operation writes and later processes groups that cannot remain together in memory. Skew can leave one partition too large, requiring more work than an even-distribution estimate suggests. Temporary data adds storage traffic and competes with other queries, but a correct spill path preserves the requested result. A spill is therefore neither automatically a bug nor automatically harmless; judge its effect on latency, throughput, temporary capacity, and resource contention.

Count the actual phases in an I/O estimate. Writing D bytes once and reading that temporary representation once contributes 2D transferred bytes before accounting for initial input and final output. Multiple merge passes, different representations, compression, read-ahead, and a final pass piped directly to a consumer can change the total. Do not infer a universal number of passes from the word external. Observe sort methods, hash batches, temporary reads or writes, and available timing information, then compare those measurements with the phase model you proposed.

Draw a timeline of retained states rather than adding every node regardless of lifetime or assuming only one node matters. A hash build can remain live while its probe side invokes another memory-using operation. A blocking sort may retain state while a consumer reads it. Admission control, lower concurrency, narrower intermediates, better cardinality estimates, or a selective earlier filter can help without raising every operator's limit. Increasing a global default may reduce one spill while creating a much larger concurrent memory peak elsewhere.

Use separate queue-lab runs to model one admission-pressure mechanism. Reset, set both λ=4 and μ=3, predict growth from the initial four jobs at one per second, then Play or Step. Separately Reset, set both λ=2 and μ=3, predict drainage at t=4, then Play or Step. Jobs are equal normalized retained tasks, not bytes or operators; the instrument contains no memory grants, spill files, or scheduler. Its M/M/1 mean needs separate stochastic assumptions. State the measured per-query overlap and rejection policy required before applying this analogy to a real service.