How enable_partitionwise_aggregate Optimizes Query Execution on Partitioned Tables

In modern applications, analytical queries are growing both in frequency and complexity. Tables have millions or even billions of entries, and efficiency becomes crucial when generating aggregates like totals, averages, or grouped metrics. Partition-wise aggregation is one of PostgreSQL's most powerful but often ignored optimisation strategies for huge datasets. This functionality is built around a planner setting known as enable_partitionwise_aggregate. When used correctly, it can reduce query time on partitioned tables by distributing work across partitions. Let’s break down how it works, why it matters, and how you can take advantage of it in real workloads.

Why Aggregation Slows Down as Data Grows

Aggregations like SUM(), COUNT(), and GROUP BY might look simple, but they are compute heavy operations. For non-partitioned tables, PostgreSQL must perform the following operations to get the final result :

1. Scan the entire table
2. Collect all required rows
3. Aggregate the results in memory like SUM(), COUNT(), MAX(), etc.
4. And finally produce the final output

On smaller tables, this is going to be fast. But on large datasets, performing a global aggregation over millions of rows becomes expensive in terms of both time and memory.

Table partitioning can be useful in this situation, but only if PostgreSQL is intelligent enough to distribute processing among the partitions.

What Is Partition-Wise Aggregation?

PostgreSQL uses partition-wise aggregation, which means it first computes aggregates inside each partition and then combines those smaller aggregated results to produce the final answer.

For example, let’s imagine having a sales table partitioned by year as given below:

sales_2019
sales_2020
sales_2021
sales_2022

And we have a query like:

SELECT year, SUM(amount)
FROM sales
GROUP BY year;

Instead of aggregating millions of rows at once, PostgreSQL can aggregate within each yearly partition and only merge four small result sets. The difference in performance can be significant, particularly when dealing with big partitions.

The Role of enable_partitionwise_aggregate

By default, PostgreSQL is conservative and may not always use partition-wise aggregation. The parameter “enable_partitionwise_aggregate” is used to control whether the planner should use this optimization.

Possible values we have for the enable_partitionwise_aggregate parameter:

Let PostgreSQL decide whether to use the optimization or not ( This is the default behavior)

Turning it ON encourages PostgreSQL to push aggregates down to partitions whenever possible.

To enable it for a session use the below command:

SET enable_partitionwise_aggregate = on;

In order to enable globally set ( In postgresql.conf)

enable_partitionwise_aggregate = on

How It Improves Performance

When partition-wise aggregation is enabled, the database engine does the following :

1. Runs partial aggregates on each partition
2. Then, produces small, intermediary results (e.g per-partition sums)
3. Finally, performs a lightweight final aggregation step

This approach can:

• Reduce memory consumption
• Lower CPU usage
• Minimize data movement
• Improves parallel execution potential

In many analytical workloads, it leads to 2x–10x faster query execution, especially when partitions are evenly sized and the grouping key aligns with the partition key.

A Quick Example

Let’s assume a table partitioned by month and the table has a million records:

SELECT date_trunc('month', created_at) AS month,
       COUNT(*) AS orders
FROM orders
GROUP BY 1
ORDER BY 1;

With partition-wise aggregation OFF

• PostgreSQL reads all partitions
• Rows are combined into a single aggregation step
• Larger memory footprint

With partition-wise aggregation ON

• Each partition performs its own COUNT
• Global aggregate merges only 12 results (one per month)
• Much faster for large datasets

Checking the execution plan confirms the difference use:

EXPLAIN SELECT ...

In the output produced by above EXPLAIN, check for Partial Aggregate nodes inside each partition scan.

When It Works Best

Partition-wise aggregation is best when:

• The table is partitioned by a logical time-based or category key
• The query filters or groups by the partition key
• Aggregation functions are "combinable" (e.g., SUM, COUNT, MIN, MAX)
• Each partition is sufficiently large to benefit from local computation

But it has a few limitations as well

Although it is powerful, the feature is not universal. It may not be used when:

• Aggregation functions are not decomposable
• Queries involve complex joins that make partition pruning impossible
• The grouping column does not match the partition key
• The planner determines a non-partitioned plan is cheaper

Even when enabled, PostgreSQL may decide the classic approach is faster depending on statistics and cost estimates.

enable_partitionwise_aggregate is one of those PostgreSQL features that gives massive performance improvements for the right workloads. By distributing aggregation across partitions, it reduces query time, minimizes resource usage, and helps large analytical queries scale more gracefully.

If you work with partitioned tables and heavy reporting queries, turning on partition-wise aggregation is one of the simplest, most impactful optimizations you can make.