A partitioning optimization, and a notable exception in the enable_* family: it defaults off. Almost every other member of the family defaults on and exists so you can switch a capability off for diagnosis; this one defaults off and exists so you can switch a capability on when you’ve decided it’s worth the cost. That inversion — and the cost that motivates it — is the whole post. Context user. And unlike most of the family, flipping this one is a legitimate tuning decision, not just a probe.
What partitionwise aggregation does
Aggregate a partitioned table the ordinary way — SELECT category, sum(amount) FROM sales GROUP BY category — and PostgreSQL scans every partition, concatenates the rows through an Append, and aggregates the combined stream at the top with a single GroupAggregate or HashAggregate. All the grouping work happens once, at the end, over everything.
Partitionwise aggregation, added in PostgreSQL 11, pushes the grouping down into each partition instead. Each partition is aggregated separately, and the results are combined afterward. There are two forms, and which one you get depends on a single question: does the GROUP BY include the partition key?
If it does — you’re grouping by the column the table is partitioned on — the pushdown is complete. Every group lives entirely within one partition (that’s what partitioning by that key means), so each partition can be aggregated to final results independently, and the plan is just an Append over a per-partition HashAggregate. No finalization needed; the partitions produce disjoint groups that concatenate directly.
If it doesn’t — you’re grouping by some other column — a group’s rows can be spread across many partitions, so each partition can only compute a partial aggregate, and a Finalize Aggregate step above the Append must combine the partials. The plan shows Partial GroupAggregate under each partition feeding a Finalize GroupAggregate at the top — the same two-stage partial/finalize structure parallel aggregation uses. Still often a win, because the partial aggregates run on smaller per-partition inputs and parallelize well, but less clean than the full-pushdown case.
The benefit in both forms is real: smaller aggregation inputs, better cache behavior, and — importantly — it combines with parallel query, so each partition’s aggregate can itself be parallelized. On a large partitioned table with a GROUP BY that lines up with the partition key, it can be a substantial speedup.
Why it defaults off
Here’s the tradeoff that explains the default, and it’s worth stating precisely because it’s the reason this parameter isn’t just on like its siblings. When aggregation is pushed into partitions, the plan gains an aggregate node per partition — and each of those nodes is bounded by work_mem. The docs put the consequence directly: the number of work_mem-restricted nodes in the plan can grow linearly with the number of partitions scanned. A hundred-partition table can turn one aggregate node into a hundred, and if each is entitled to work_mem, the query’s peak memory consumption balloons by that factor. On top of that, considering partitionwise plans makes planning itself significantly more expensive in CPU and memory, because the planner has more paths to evaluate.
So the feature is off by default not because it’s rarely useful, but because turning it on globally would be a memory footgun on databases with many partitions and generous work_mem — exactly the databases most likely to have large partitioned tables. The PostgreSQL developers chose to make you opt in per-workload after verifying it helps, rather than impose the memory multiplication on everyone.
When to turn it on
This is one of the few enable_* parameters you might legitimately set to a non-default value in production, and the reasoning is the mirror image of the rest of the family. You enable it — per-session, per-role, or per-database — when you have large partitioned tables and analytical queries that group by (ideally) the partition key, and you’ve confirmed on your actual data that the pushdown plan is faster. The confirmation matters: SET enable_partitionwise_aggregate = on, run EXPLAIN (ANALYZE), and compare against the default plan. You’re looking for per-partition HashAggregate/Partial GroupAggregate nodes and a lower total time — and you’re watching that the memory cost, multiplied across your partition count, is one you can afford at your concurrency.
The scoping discipline is the opposite of the diagnostic switches. Rather than leaving it on and flipping it off to probe, you leave it off and turn it on for the specific workload that benefits — a reporting role running big grouped scans over a partitioned fact table is the textbook case, set with ALTER ROLE reporting SET enable_partitionwise_aggregate = on. Enabling it cluster-wide is defensible only if you’ve accounted for the worst-case memory across every partitioned query the server runs, which on a mixed workload is a harder thing to be sure of than it sounds. The parameter is genuinely useful; it is off by default because usefulness and safety-by-default are not the same thing, and this is a case where they part ways.