PostgreSQL has a powerful ability to parallelize queries, making use of multiple CPU cores to dramatically improve performance — especially for aggregations on large datasets.
What is Parallel Aggregation?
When you write a query like:
SELECT department_id, SUM(salary)
FROM employees
GROUP BY department_id;
PostgreSQL has a choice: it can either process everything in a single backend process (the default), or — if conditions are right — it can split the work across multiple CPU cores using parallel aggregation.
In Parallel Aggregation:
- Multiple worker processes each scan a chunk of the table and compute partial results (e.g., partial SUM()s).
- A leader process then combines the results from the workers to produce the final output.
This approach is extremely efficient for large datasets, where sequential scanning and full aggregation would be slow.
How PostgreSQL Executes It (Conceptually)
PostgreSQL builds a plan like this:
Finalize Aggregate
?
Gather
?
Partial Aggregate (in workers)
?
Parallel Scan of the table
Each worker process:
- Scans part of the table (in parallel)
- Groups by the required keys
- Computes intermediate aggregates like partial sums or counts
Then:
- The leader process collects those intermediate results (via Gather)
- Performs a final aggregation to get accurate SUM(), AVG(), COUNT() results
How to Enable Parallel Aggregation
PostgreSQL doesn’t always enable parallelism by default, especially for small tables or low-cost queries. Here’s how you can encourage it.
Step 1: Tweak Planner Settings
You can override the defaults to reduce the cost thresholds for using parallel workers:
SET max_parallel_workers = 4;
SET max_parallel_workers_per_gather = 2;
SET parallel_setup_cost = 0;
SET parallel_tuple_cost = 0;
SET min_parallel_table_scan_size = 0;
SET min_parallel_index_scan_size = 0;
This forces PostgreSQL to consider parallel execution even for small datasets (great for learning or experimentation).
Step 2: Create a Sample Dataset
CREATE TABLE employees (
id SERIAL,
department_id INT,
salary INT
);
INSERT INTO employees
SELECT i % 10, (random() * 10000)::INT
FROM generate_series(1, 1000000) AS i;
Step 3: Run and Analyze the Query
EXPLAIN (ANALYZE, VERBOSE)
SELECT department_id, SUM(salary)
FROM employees
GROUP BY department_id;
You should see output like:
Finalize GroupAggregate
Group Key: department_id
-> Gather
Workers Planned: 2
-> Partial GroupAggregate
Group Key: department_id
-> Parallel Seq Scan on employees
This confirms that:
- The table was scanned in parallel
- Aggregates were computed in parts
- The final result was assembled by the leader process
Why It Matters
Parallel aggregation can lead to massive performance improvements, particularly for analytics-style queries that involve large datasets. For example, aggregating over one million rows might take between 2 to 5 seconds without parallelism, but with parallel processing, the same operation can be completed in about a second or less. When dealing with 100 million rows, query times that would normally take minutes can be reduced to just seconds. This efficiency gain comes from PostgreSQL’s ability to fully utilize multiple CPU cores for compute-intensive operations such as SUM(), COUNT(), and AVG(), making parallel aggregation a powerful optimization for high-volume data analysis.
Parallel aggregation is a powerful optimization in PostgreSQL. While it’s automatically applied based on planner cost estimates, you can force or encourage it by:
- Adjusting your planner cost settings
- Using simple GROUP BY + aggregate queries
- Avoiding clauses that prevent parallelism
It’s an excellent tool for high-performance analytics, and something every PostgreSQL user should be aware of.