Partitioning is one of the most useful tools for big PostgreSQL tables, and one of the most misunderstood. It can make queries faster and turn a slow purge into an instant one, but only when the queries and the partition key fit together.
In this post we build partitioned tables with 3 million rows on PostgreSQL 16 and measure the three planner features that make partitioning fast: partition pruning, partition-wise join and partition-wise aggregation. Every plan and timing below is a real EXPLAIN ANALYZE result, not a copy from the documentation.
In this post
01What partitioning is
A partitioned table is one logical table split into smaller physical tables (partitions) by a partition key, such as a date or a region. The parent table stores no rows itself:
- INSERT into the parent: PostgreSQL routes each row to the right partition.
- SELECT from the parent: the planner reads only the partitions that can contain matching rows.
- UPDATE of the key: the row moves to its new partition automatically (PostgreSQL 11+).
| Method | Rows are split by | Typical use |
|---|---|---|
| RANGE | Ranges of values: FROM ('2026-01-01') TO ('2026-04-01') | Time series, orders, logs, audit data |
| LIST | Explicit values: IN ('IN','SG','AU') | Region, country, tenant, status |
| HASH | Hash of the key: MODULUS 3, REMAINDER 0 | Spreading a big table evenly when there is no natural range |
02When to partition
| Partition when… | Don't partition when… |
|---|---|
| Most queries filter on the same column (usually a date) | Queries rarely filter on any one column |
| You delete old data by period (retention) | The table is small enough that a good index already makes queries fast |
| The table is very large and keeps growing, and maintenance (VACUUM, index rebuilds) is getting slow | You would need thousands of tiny partitions |
| You want to keep hot and cold data apart, for example older partitions on another tablespace | The main goal is speeding up lookups by a column that is not the key; an index does that better |
Rule of thumb: choose the key from your WHERE clauses and your retention policy, not from the table's primary key. Keep the number of partitions in the tens or low hundreds; planning time grows with the partition count.
03Feature timeline
| Version | What it added |
|---|---|
| 10 | Declarative partitioning: PARTITION BY RANGE / LIST |
| 11 | HASH partitioning, DEFAULT partition, indexes and primary keys on the parent, UPDATE moving rows between partitions, partition-wise join and aggregate, pruning during execution |
| 12 | Much faster planning with many partitions, foreign keys that reference partitioned tables, ATTACH PARTITION without blocking queries, pg_partition_tree() |
| 13 | Partition-wise join when partition bounds don't match exactly, logical replication of partitioned tables, BEFORE row triggers |
| 14 | DETACH PARTITION … CONCURRENTLY |
| 17 | Identity columns and exclusion constraints on partitioned tables |
04Create partitioned tables
RANGE: sales by quarter, with a default partition
SQL
CREATE TABLE sales (
id bigint,
product text,
amount int,
sale_date date NOT NULL
) PARTITION BY RANGE (sale_date);
CREATE TABLE sales_2025_q4 PARTITION OF sales FOR VALUES FROM ('2025-10-01') TO ('2026-01-01');
CREATE TABLE sales_2026_q1 PARTITION OF sales FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');
CREATE TABLE sales_2026_q2 PARTITION OF sales FOR VALUES FROM ('2026-04-01') TO ('2026-07-01');
CREATE TABLE sales_default PARTITION OF sales DEFAULT; -- catches anything else
CREATE INDEX ON sales (product); -- created on every partition automatically
The upper bound is exclusive: TO ('2026-01-01') means up to 31 December. Without a default partition, a row that fits nowhere is rejected:
error
ERROR: no partition of relation "t_nodef" found for row DETAIL: Partition key of the failing row contains (d) = (2027-01-01).
LIST: customers by region
SQL
CREATE TABLE customers (id bigint, region text NOT NULL, name text) PARTITION BY LIST (region);
CREATE TABLE customers_apac PARTITION OF customers FOR VALUES IN ('IN','SG','AU');
CREATE TABLE customers_emea PARTITION OF customers FOR VALUES IN ('UK','DE','FI');
CREATE TABLE customers_other PARTITION OF customers DEFAULT;
INSERT INTO customers VALUES (1,'IN','a'), (2,'FI','b'), (3,'US','c');
SELECT tableoid::regclass AS stored_in, * FROM customers;
output
stored_in | id | region | name -----------------+----+--------+------ customers_apac | 1 | IN | a customers_emea | 2 | FI | b customers_other | 3 | US | c
HASH: employees spread over 3 partitions
SQL
CREATE TABLE emp (id int PRIMARY KEY, name text, dept int) PARTITION BY HASH (id); CREATE TABLE emp_0 PARTITION OF emp FOR VALUES WITH (MODULUS 3, REMAINDER 0); CREATE TABLE emp_1 PARTITION OF emp FOR VALUES WITH (MODULUS 3, REMAINDER 1); CREATE TABLE emp_2 PARTITION OF emp FOR VALUES WITH (MODULUS 3, REMAINDER 2); -- emp_info is created the same way, also hashed on id
Primary keys must include the partition key
SQL
ALTER TABLE sales ADD PRIMARY KEY (id);
error
ERROR: unique constraint on partitioned table must include all partitioning columns DETAIL: PRIMARY KEY constraint on table "sales" lacks column "sale_date" which is part of the partition key.
SQL
ALTER TABLE sales ADD PRIMARY KEY (id, sale_date); -- works
Design impact: PostgreSQL can only enforce uniqueness inside each partition, so
id alone cannot be guaranteed unique across the whole table. Plan this before you partition an existing table.05Partition pruning
Partition pruning removes partitions that cannot contain matching rows. It is on by default (enable_partition_pruning = on) and works when the WHERE clause uses the partition key.
The test: the same query on a normal 3-million-row table (sales_flat) and on the partitioned sales table with the same data.
one big table
EXPLAIN (ANALYZE, COSTS OFF) SELECT * FROM sales_flat WHERE sale_date >= '2026-04-01' AND sale_date < '2026-05-01' AND amount = 500;
EXPLAIN ANALYZE
Gather (actual time=79.196..118.421 rows=330 loops=1)
Workers Planned: 2
-> Parallel Seq Scan on sales_flat (actual time=76.147..112.707 rows=110 loops=3)
Filter: ((sale_date >= '2026-04-01'::date) AND (sale_date < '2026-05-01'::date) AND (amount = 500))
Rows Removed by Filter: 999890
Execution Time: 118.492 ms
partitioned
EXPLAIN (ANALYZE, COSTS OFF) SELECT * FROM sales WHERE sale_date >= '2026-04-01' AND sale_date < '2026-05-01' AND amount = 500;
EXPLAIN ANALYZE
Gather (actual time=0.707..41.272 rows=330 loops=1)
Workers Planned: 2
-> Parallel Seq Scan on sales_2026_q2 sales (actual time=0.264..34.837 rows=110 loops=3)
Filter: ((sale_date >= '2026-04-01'::date) AND (sale_date < '2026-05-01'::date) AND (amount = 500))
Rows Removed by Filter: 333223
Execution Time: 41.338 ms
Only sales_2026_q2 was scanned; the other three partitions never appear in the plan. Each worker removed about 333 thousand rows instead of a million. Over three runs the median was 122 ms vs 60 ms, about 2× faster.
Pruning while the query runs (prepared statements)
When the value is only known at execution time (a bind parameter, a subquery), PostgreSQL prunes during execution. The plan shows it as Subplans Removed:
SQL
SET plan_cache_mode = force_generic_plan; -- force the generic plan for the demo
PREPARE q(date) AS SELECT count(*) FROM sales WHERE sale_date = $1;
EXPLAIN (ANALYZE, COSTS OFF) EXECUTE q('2026-02-14');
EXPLAIN ANALYZE
-> Parallel Append (actual time=0.024..42.403 rows=3663 loops=3)
Subplans Removed: 3
-> Parallel Seq Scan on sales_2026_q1 sales_1 (actual time=0.023..42.070 rows=3663 loops=3)
Filter: (sale_date = $1)
Pruning needs the key, as is:
WHERE sale_date = $1 prunes, but wrapping the key in a function, for example WHERE date_trunc('month', sale_date) = …, or comparing it to a different data type can stop pruning. Check with EXPLAIN that the unwanted partitions are gone.06Partition-wise join
When two tables are partitioned the same way and joined on their partition key, PostgreSQL can join them partition by partition: emp_0 with emp_info_0, emp_1 with emp_info_1, and so on. Each small join can use its own indexes and fit in memory.
It is off by default (enable_partitionwise_join = off) because it makes planning more expensive. The test joins two 3-million-row tables, both hash-partitioned on id:
SQL
SET enable_partitionwise_join = off; -- then repeat with "on" EXPLAIN (ANALYZE, COSTS OFF) SELECT e.id, e.name, i.rating FROM emp e JOIN emp_info i ON e.id = i.id WHERE i.rating = 10;
off
Gather (actual time=145.816..616.561 rows=30000 loops=1)
-> Parallel Hash Join (actual time=142.855..605.703 rows=10000 loops=3)
Hash Cond: (e.id = i.id)
-> Parallel Append -- all 3 emp partitions, 3M rows
-> Parallel Seq Scan on emp_1 e_2 ...
-> Parallel Seq Scan on emp_0 e_1 ...
-> Parallel Seq Scan on emp_2 e_3 ...
-> Parallel Hash
-> Parallel Append -- all 3 emp_info partitions
Execution Time: 618.053 ms
on (some timing details trimmed)
Gather (actual time=0.373..194.925 rows=30000 loops=1)
-> Parallel Append
-> Nested Loop
-> Parallel Seq Scan on emp_info_2 i_3 Filter: (rating = 10)
-> Index Scan using emp_2_pkey on emp_2 e_3 Index Cond: (id = i_3.id)
-> Nested Loop
-> Parallel Seq Scan on emp_info_0 i_1 Filter: (rating = 10)
-> Index Scan using emp_0_pkey on emp_0 e_1 Index Cond: (id = i_1.id)
-> Nested Loop
-> Parallel Seq Scan on emp_info_1 i_2 Filter: (rating = 10)
-> Index Scan using emp_1_pkey on emp_1 e_2 Index Cond: (id = i_2.id)
Execution Time: 200.105 ms
With the feature on, the planner built three small joins, each using the matching partition's primary key index. Median of three runs: 563 ms → 272 ms, about 2× faster.
Requirements: the join condition must include all partition key columns, with the same data types, and both tables must use the same partitioning method. Since PostgreSQL 13 the partition bounds no longer have to match exactly. Turn it on for a session or a reporting role (
ALTER ROLE reporting SET enable_partitionwise_join = on;) and measure; don't flip it globally without testing.07Partition-wise aggregation
Partition-wise aggregation runs GROUP BY inside each partition. It is also off by default (enable_partitionwise_aggregate = off).
- If you GROUP BY the partition key, each group lives in one partition, so each partition is aggregated completely and the results are simply appended.
- If you group by another column, each partition produces a partial aggregate and a final step combines them.
SQL
SET max_parallel_workers_per_gather = 0; -- serial, to show the plan shape clearly SET enable_partitionwise_aggregate = off; -- then "on" EXPLAIN (ANALYZE, COSTS OFF) SELECT sale_date, count(*), sum(amount) FROM sales GROUP BY sale_date;
off
HashAggregate (actual time=1121.736..1121.773 rows=273 loops=1)
Group Key: sales.sale_date
-> Append (actual time=0.025..609.211 rows=3000000 loops=1)
-> Seq Scan on sales_2025_q4 sales_1 ...
-> Seq Scan on sales_2026_q1 sales_2 ...
-> Seq Scan on sales_2026_q2 sales_3 ...
Execution Time: 1121.876 ms
on (trimmed)
Append (actual time=224.754..713.379 rows=273 loops=1)
-> HashAggregate (rows=92) Group Key: sales.sale_date
-> Seq Scan on sales_2025_q4 sales (rows=1010991)
-> HashAggregate (rows=90) Group Key: sales_1.sale_date
-> Seq Scan on sales_2026_q1 sales_1 (rows=989010)
-> HashAggregate (rows=91) Group Key: sales_2.sale_date
-> Seq Scan on sales_2026_q2 sales_2 (rows=999999)
Execution Time: 713.606 ms
Three small hash tables instead of one big one. Median of three runs: 904 ms → 681 ms, about 25% less time. With parallel workers on, the gain on the same query was smaller (521 → 421 ms), and grouping by a non-key column (product) gained only 7%. Partition-wise aggregation helps most when you group by the partition key.
Parallel query works with partitions too:
Parallel Append lets workers scan different partitions at the same time. It is on by default (enable_parallel_append); you saw it in the plans above.08Test results
| Test | Before | After | Change |
|---|---|---|---|
| Partition pruning (1 month of data) | 122 ms (one big table) | 60 ms | 2.0× faster |
| Partition-wise join (3M ⋈ 3M rows) | 563 ms | 272 ms | 2.1× faster |
| Partition-wise aggregate (GROUP BY key, serial) | 904 ms | 681 ms | 25% less |
| Remove one quarter of data | 1,058 ms (DELETE) | 67 ms (DETACH + DROP) | 15.8× faster, space freed at once |
Test setup: PostgreSQL 16.15, default settings, 2 vCPU VM, 3 million rows per table, median of three runs (DELETE vs DROP measured once). Your hardware will give different numbers; the direction of the results is what matters.
09Managing partitions
List partitions with bounds and size
SQL
SELECT c.relname AS partition,
pg_get_expr(c.relpartbound, c.oid) AS bounds,
pg_size_pretty(pg_total_relation_size(c.oid)) AS size,
s.n_live_tup AS est_rows
FROM pg_inherits i
JOIN pg_class c ON c.oid = i.inhrelid
LEFT JOIN pg_stat_user_tables s ON s.relid = c.oid
WHERE i.inhparent = 'sales'::regclass
ORDER BY c.relname;
output
partition | bounds | size | est_rows
---------------+--------------------------------------------------+-------+----------
sales_2025_q4 | FOR VALUES FROM ('2025-10-01') TO ('2026-01-01') | 57 MB | 1010991
sales_2026_q1 | FOR VALUES FROM ('2026-01-01') TO ('2026-04-01') | 56 MB | 989010
sales_2026_q2 | FOR VALUES FROM ('2026-04-01') TO ('2026-07-01') | 56 MB | 999999
sales_default | DEFAULT | 16 kB | 0
For multi-level partitioning, SELECT * FROM pg_partition_tree('sales'); shows the whole tree with levels, and SELECT pg_get_partkeydef('sales'::regclass); shows the key.
Remove old data: DROP the partition instead of DELETE
SQL
-- the old way, on a normal table DELETE FROM sales_flat WHERE sale_date < '2026-01-01'; DELETE 1010991 Time: 1058.223 ms -- and the table is still 149 MB until VACUUM reuses the space -- the partitioned way ALTER TABLE sales DETACH PARTITION sales_2025_q4; Time: 1.356 ms DROP TABLE sales_2025_q4; -- or keep it: archive with pg_dump -t, move to cheaper storage Time: 65.597 ms -- space returned to the OS immediately, no bloat, no VACUUM
DETACH CONCURRENTLY (PostgreSQL 14+) and its catch
SQL
ALTER TABLE sales DETACH PARTITION sales_2025_q4 CONCURRENTLY;
error
ERROR: cannot detach partitions concurrently when a default partition exists
CONCURRENTLY avoids blocking queries, but it cannot run inside a transaction block, and not when the table has a DEFAULT partition. If you rely on it for online retention, design without a default partition and create future partitions ahead of time.
Add the next partition, and the default-partition trap
If a row for July 2026 has already landed in sales_default, creating the Q3 partition fails:
SQL
CREATE TABLE sales_2026_q3 PARTITION OF sales FOR VALUES FROM ('2026-07-01') TO ('2026-10-01');
error
ERROR: updated partition constraint for default partition "sales_default" would be violated by some row
fix: move the rows out of the default partition
BEGIN;
ALTER TABLE sales DETACH PARTITION sales_default;
CREATE TABLE sales_2026_q3 PARTITION OF sales FOR VALUES FROM ('2026-07-01') TO ('2026-10-01');
INSERT INTO sales SELECT * FROM sales_default
WHERE sale_date >= '2026-07-01' AND sale_date < '2026-10-01';
DELETE FROM sales_default
WHERE sale_date >= '2026-07-01' AND sale_date < '2026-10-01';
ALTER TABLE sales ATTACH PARTITION sales_default DEFAULT;
COMMIT;
Better: create partitions before data arrives (for example a monthly job that creates next quarter's partition), and alert when
sales_default is not empty. The pg_partman extension automates creating and retiring partitions.Attach a pre-loaded table with minimal locking
SQL
CREATE TABLE sales_2026_q4 (LIKE sales INCLUDING DEFAULTS);
-- load it here (COPY, INSERT ... SELECT) while it is still a normal table
ALTER TABLE sales_2026_q4 ADD CONSTRAINT q4_chk
CHECK (sale_date >= '2026-10-01' AND sale_date < '2027-01-01');
ALTER TABLE sales ATTACH PARTITION sales_2026_q4 FOR VALUES FROM ('2026-10-01') TO ('2027-01-01');
ALTER TABLE sales_2026_q4 DROP CONSTRAINT q4_chk; -- no longer needed
The matching CHECK constraint lets PostgreSQL skip the full-table validation scan during ATTACH.
Row movement
SQL
UPDATE sales SET sale_date = '2026-11-01' WHERE id = 9999999; SELECT tableoid::regclass AS stored_in, id, sale_date FROM sales WHERE id = 9999999;
output
stored_in | id | sale_date ---------------+---------+------------ sales_2026_q4 | 9999999 | 2026-11-01
10Pitfalls checklist
| Check | Why |
|---|---|
| Queries filter on the partition key, unchanged | No key in WHERE (or a function around it) means every partition is scanned |
| Primary/unique keys include the key | Required by PostgreSQL; uniqueness is per partition |
| Partitions exist before the data arrives | Otherwise rows go to DEFAULT, or inserts fail |
| DEFAULT partition is monitored (or not used) | It blocks new partitions and DETACH CONCURRENTLY |
| Tens to a few hundred partitions, not thousands | Planning time and memory grow with the partition count |
| Retention uses DETACH/DROP, not DELETE | Instant, no bloat, no long VACUUM |
| Partition-wise join/aggregate tested before enabling | They cost planning time; enable per session or role where they help |
| Run ANALYZE on the parent after big loads | Autovacuum analyzes partitions, not the partitioned parent's combined statistics |
© 2026 Tamilselvan Perumal · TechStarAdmin. All examples tested on PostgreSQL 16.15; partition-wise features need PostgreSQL 11+, DETACH CONCURRENTLY needs 14+. Please link back instead of copying.
Originally published at techstaradmin.blogspot.com.
Originally published at techstaradmin.blogspot.com.
){kind=link}