A HashAggregate node does not require every group to remain in memory for the full query. If the hash table grows past its executor memory limit, PostgreSQL can retain active groups in memory while routing tuples for additional groups into temporary batches. Those batches are processed later, so hash aggregation can complete without allowing an unexpectedly large group set to consume unbounded memory.
This behavior matters because the planner chooses an aggregation strategy from estimates, while the executor has to handle the rows that actually arrive. A cardinality estimate can be imperfect, data can change after statistics were collected, and a grouping key can produce far more distinct groups than a small sample suggests. Disk-backed hash aggregation provides an execution path for those cases.
Hash aggregation stores state per group
For a query such as:
SELECT account_id, count(*), sum(amount)
FROM ledger_entry
GROUP BY account_id;a hash aggregate uses the grouping key to locate an in-memory entry for each account_id. The entry contains the transition state needed by each aggregate. A matching input row advances the existing state; a previously unseen key requires a new group entry.
Memory use therefore depends on more than the number of input rows. A million rows spread across a few hundred groups can require a modest hash table. The same row count with nearly every account_id distinct can require a much larger table because PostgreSQL must maintain separate aggregate state for each group.
The size of each state also matters. count(*) has a compact transition value, while other aggregates can carry larger state. Hash-table bookkeeping and grouping values add further allocations.
The memory limit is specific to hash operations
work_mem is the base memory setting for query operations such as sorts and hash tables. Hash-based nodes derive their memory limit from work_mem multiplied by hash_mem_multiplier.
With settings such as:
work_mem = 16MB
hash_mem_multiplier = 2.0a hash operation receives a memory limit derived from roughly 32 MB rather than the 16 MB base. The limit applies to an operation, not to the entire database session or entire query. A plan can contain several memory-consuming nodes, and concurrent sessions can execute their own nodes at the same time.
That scope makes a large global work_mem setting a coarse response to one spilling aggregate. Raising it affects other operations and sessions too. The execution plan and workload concurrency remain relevant when estimating total memory exposure.
Spill mode partitions future work
Once a hash aggregate reaches its memory boundary, PostgreSQL can enter spill mode. Existing in-memory groups can continue receiving matching tuples. Tuples that would require groups outside the retained working set are written to temporary storage in partitioned form.
After the current in-memory groups are finalized and emitted, PostgreSQL processes saved batches. A batch builds another hash table from its partition of the input. If that batch is still too large for the available memory, further partitioning can occur.
This design avoids treating spill as a simple dump of the entire hash table. Keeping existing groups active lets later tuples continue to update states that are already resident. Partitioning spilled tuples by hash value also gives subsequent passes a smaller subset of grouping keys to process.
The cost shifts from primarily CPU and memory access toward additional temporary-file I/O and repeated batch processing. A spill can still be a valid plan choice, but it is materially different from an aggregate whose complete group set remains resident.
Execution plans expose the spill
EXPLAIN ANALYZE reports runtime details for hash aggregation. A spilling node can include fields for the number of batches, peak memory usage, and disk usage. For example, the shape can resemble:
HashAggregate
Group Key: account_id
Batches: 8
Memory Usage: ...
Disk Usage: ...Exact values depend on the server version, data, aggregate states, and memory settings. The useful distinction is structural: multiple batches and nonzero disk use indicate that the node processed part of its grouping work through temporary storage.
That evidence is more useful than assuming every HashAggregate is memory-resident. The node name describes the aggregation strategy, not the absence of disk I/O.
Temporary I/O can also be observed at broader scopes through PostgreSQL statistics and logging facilities. Those measurements may include other sorts or hash operations, so the node-level execution plan remains the clearest place to associate spill with a specific aggregate during query analysis.
Planner estimates still shape the strategy
The planner estimates the number of groups before execution. That estimate influences the expected size and cost of hash aggregation and its alternatives.
A grouping estimate can be especially sensitive to relationships among multiple grouping columns. If a query groups by several correlated columns, independent per-column distinct counts may not describe the number of distinct combinations well. PostgreSQL extended statistics can provide multivariate distinct-count information for selected column sets, giving the planner better data for group-count estimation.
Better estimates do not guarantee that a hash aggregate stays in memory. They instead improve the cost model used to choose the plan. Runtime memory pressure remains an executor concern, and the actual group count can still differ from the estimate.
A sort-based aggregate has a different execution shape. It orders rows by grouping keys and aggregates adjacent rows. Its sort can also use temporary files after crossing its memory allowance. Replacing a spilling hash aggregate with a sort-based plan therefore does not imply disk-free execution; it changes the operation that manages the intermediate data.
Spill behavior is a capacity signal
A small amount of disk-backed aggregation is not automatically a configuration defect. The executor is using a bounded-memory mechanism designed for a group set that does not fit its current hash memory allowance.
Repeated heavy spilling is more informative. It can indicate a group cardinality that is inherently large, an underestimated group count, a memory allowance that is small relative to the operation, or a query shape that produces substantial intermediate state. Those cases call for different responses, so the execution plan should be read before changing memory settings.
The central boundary is simple: hash aggregation is memory-bounded, not memory-only. Once the resident group table reaches its limit, PostgreSQL can turn the remaining grouping work into partitioned batches and finish it through temporary storage. That distinction makes Batches and Disk Usage part of the normal vocabulary for reading aggregate plans, not signs that the executor abandoned the hash strategy.