Partition-wise Joins and Aggregates
Discover how PostgreSQL can join and aggregate partitioned tables partition-by-partition for big performance gains.
Partition-wise Joins and Aggregates is a free Advanced PostgreSQL: Indexing, Partitioning, Replication lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the Advanced PostgreSQL: Indexing, Partitioning, Replication learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
What Are Partition-wise Operations
When two tables are partitioned the same way, PostgreSQL can join matching partitions to each other instead of joining the whole tables. This is a partition-wise join.
The same idea applies to grouping: a partition-wise aggregate.
Enabling the Feature
These optimizations are off by default because they increase planning cost. Turn them on per session or in postgresql.conf.
SET enable_partitionwise_join = on;
SET enable_partitionwise_aggregate = on;Matching Partition Boundaries
For a partition-wise join the tables must share the same partition key type and identical bounds. Otherwise the planner cannot pair partitions.
Example Setup
Two tables partitioned by the same range on customer_id.
CREATE TABLE orders (customer_id int, total numeric)
PARTITION BY RANGE (customer_id);
CREATE TABLE refunds (customer_id int, amount numeric)
PARTITION BY RANGE (customer_id);The Join
With matching partitions, this join runs as several small joins, each fitting in memory more easily.
SELECT o.customer_id, sum(o.total), sum(r.amount)
FROM orders o
JOIN refunds r USING (customer_id)
GROUP BY o.customer_id;Why It Is Faster
Smaller per-partition joins mean:
- Smaller hash tables that fit in
work_mem - Better cache locality
- Opportunities for parallel workers per partition
Reading the Plan
Use EXPLAIN to confirm. You will see an Append node over several join nodes rather than one giant join.
EXPLAIN
SELECT * FROM orders o JOIN refunds r USING (customer_id);Partition-wise Aggregate
If you group by the partition key, each partition is aggregated independently and the results are concatenated. No global sort or hash across the whole table is needed.
SELECT customer_id, sum(total)
FROM orders
GROUP BY customer_id;Planning Cost Trade-off
With many partitions, considering each one increases planning time. Enable these settings only when partition counts are moderate and the query benefits clearly.
Combining with Pruning
Partition-wise joins combine well with partition pruning: pruning removes irrelevant partitions first, then the remaining ones are joined pair-by-pair.
When It Does Not Apply
If only one table is partitioned, or the bounds differ, PostgreSQL falls back to a normal join over the appended partitions.
Quick Check
What is required for a partition-wise join?
Recap
You learned to speed up queries on partitioned tables with partition-wise joins and aggregates. Enable the settings, ensure matching bounds, verify with EXPLAIN, and combine with pruning for the best results.
Frequently asked questions
Is the “Partition-wise Joins and Aggregates” lesson free?
Yes — the full text of “Partition-wise Joins and Aggregates” is free to read here on the web, and the Advanced PostgreSQL: Indexing, Partitioning, Replication course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the Advanced PostgreSQL: Indexing, Partitioning, Replication course, upgrade to CoddyKit PRO.
What will I learn in “Partition-wise Joins and Aggregates”?
Discover how PostgreSQL can join and aggregate partitioned tables partition-by-partition for big performance gains. You practise Advanced PostgreSQL: Indexing, Partitioning, Replication with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start Advanced PostgreSQL: Indexing, Partitioning, Replication?
No prior experience is required. Advanced PostgreSQL: Indexing, Partitioning, Replication on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Partition-wise Joins and Aggregates” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this Advanced PostgreSQL: Indexing, Partitioning, Replication lesson?
Yes. Every Advanced PostgreSQL: Indexing, Partitioning, Replication lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- Query Optimization with Partitioning
- Attaching and Detaching Partitions
- Partition Pruning and Exclusion
- Partition-wise Joins and Aggregates