PostgreSQL 13 Beta 2 clarifies disk spilling for aggregation
Beta 2 changes hash aggregation controls and improves reporting of worker resource use. Its implementation shows how spilling bounds new groups while continuing work on existing groups.
A planner setting that discourages disk use cannot promise that execution will avoid disk. PostgreSQL 13 Beta 2 makes that distinction visible in its hash aggregation controls. The June 25 announcement reports a renamed setting, removal of another aggregation setting and better reporting when parallel hash aggregates spill. These are details of a feature still undergoing beta testing, with application workloads needed to expose problems before the final release.
The release also requires a major-version-style upgrade procedure when moving from Beta 1. This is a useful reminder that two prereleases sharing a future major version number do not necessarily share an installation procedure. The announcement names pg_upgrade or dump and restore as appropriate strategies and advises against production deployment of the beta.
I value PostgreSQL's EXPLAIN output because it makes a planner's decisions inspectable. Hash aggregation that can write temporary data needs the same visibility. Without it, an apparently ordinary aggregate can conceal a substantial change in resource use once its groups exceed the memory available during execution.
Hash aggregation keeps state for groups
The Beta 2 aggregate executor explains how spilling works. When the hash table exceeds its limit, the executor enters spill mode. Rows belonging to groups already in the table continue updating those groups. Rows that would require new entries go to disk for later processing.
The executor partitions the spilled rows so that later batches are smaller. A batch that remains too large can spill recursively. This distinction between existing groups and new groups explains why spilling does not mean that every incoming row immediately takes the same route to temporary storage. Some aggregate states continue progressing in memory while other input waits for a later batch.
The source also identifies a remaining difficulty. Aggregate transition states can grow after their group has entered the table. An array aggregate is one example. Consequently, the implementation can still exceed work_mem substantially in such cases. It estimates what will fit and limits group count separately from current memory consumption. Spilling provides a way to process excess groups, but it cannot make every aggregate state's growth predictable.
A planning preference does not constrain every execution
The configuration reference shipped with Beta 2 documents hashagg_avoid_disk_plan. Enabling it asks the planner to avoid hash aggregation plans expected to use disk. A chosen hash aggregate may still require disk during execution. Its default is off.
That wording separates an estimate from an observed resource requirement. The planner decides using the information available before execution. The executor then encounters the actual groups and transition states. A preference about expected disk use cannot convert that estimate into a guarantee about the values a query will encounter.
For someone comparing this beta's choices, an explicit setting can make the intended planning preference clear:
SET hashagg_avoid_disk_plan = on;
This is the name in Beta 2, not a promise about the final release's configuration. The announcement says the earlier enable_hashagg_disk name has changed and that enable_groupingsets_hash_disk has been removed. Configuration copied from an earlier beta therefore needs interpretation, even when the application SQL is identical.
I prefer that the setting's documented meaning admits the difference between planning and execution. An operator needs to know whether a control changes plan selection or imposes a runtime prohibition. Those are different contracts, especially when memory consumption depends on the number and size of groups actually encountered.
EXPLAIN exposes the execution that happened
The performance documentation in the beta source describes a plan as a tree of nodes. Scans produce rows, while higher nodes can join, sort or aggregate them. Estimates belong to that planned structure. Reading an aggregate's behaviour therefore requires locating it within the plan, including the nodes that produce its input.
The Beta 2 announcement adds maximum memory and disk usage per worker to EXPLAIN ANALYZE output for parallel plans with spilling hash aggregates. Per-worker reporting matters because a parallel query is not fully described by one undifferentiated resource number. The detail lets a reader associate observed use with the workers involved in executing the aggregate.
The performance documentation also offers machine-readable EXPLAIN formats, including JSON. That gives an application investigation a way to retain the plan structure without parsing the spacing of its text presentation. A stored plan can preserve estimates alongside node relationships, which is useful when explaining exactly which part of a query a beta report concerns.
That makes a beta report more informative than a statement that a query became slower. Its plan can identify the selected aggregation method, and the execution output can describe disk use where it occurred. PostgreSQL's beta process can then work with the query's actual behaviour, while the final configuration and implementation remain open to change during testing.