PostgreSQL Table Partitioning: Range, List, and Hash Partitioning

PostgreSQL table partitioning splits one logical table into smaller physical pieces called partitions, each holding a subset of rows defined by a partition key. To queries and applications it still looks like a single table - PostgreSQL routes reads and writes to the right partition automatically. Partitioning helps when a table grows past tens of millions of rows: the planner can skip partitions that cannot match a query, and you can archive old data by detaching a partition instead of running a large DELETE.

PostgreSQL has supported native declarative partitioning since version 10, and it is the only approach worth using on PostgreSQL 17 and 18. The older patterns - table inheritance with triggers, or a UNION ALL view over manually maintained tables - are legacy workarounds that predate declarative syntax and should not be used for new schemas.

Range, List, and Hash Partitioning Compared

PostgreSQL offers three declarative partitioning strategies, set with the PARTITION BY clause when you create the parent table. Each suits a different access pattern.

A range partition's bounds are inclusive at the lower end and exclusive at the upper end. If one partition covers FROM ('2025-01-01') TO ('2025-02-01') and the next covers FROM ('2025-02-01') TO ('2025-03-01'), then 2025-02-01 belongs to the second partition, not the first. This convention lets adjacent ranges meet without gaps or overlaps.

List partitioning assigns each partition an explicit set of key values. Hash partitioning assigns each partition a modulus and remainder; a row lands in the partition where hash(key) % modulus = remainder. Hash gives roughly uniform distribution but no ability to prune by value range, so it suits load spreading rather than time-based archiving.

Creating a Partitioned Table

Define the parent table with PARTITION BY, then create each partition as a child table. Partition the table on the raw column you query on - order_date here - not on an expression like EXTRACT(YEAR FROM order_date). Partitioning on a bare column is what lets the planner prune partitions from ordinary WHERE order_date >= ... filters.

-- Parent table partitioned by the raw date column
CREATE TABLE orders (
    order_id    bigint GENERATED ALWAYS AS IDENTITY,
    order_date  date NOT NULL,
    customer_id bigint NOT NULL,
    amount      numeric(12,2),
    PRIMARY KEY (order_id, order_date)
) PARTITION BY RANGE (order_date);

-- One partition per year
CREATE TABLE orders_2024 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
CREATE TABLE orders_2025 PARTITION OF orders
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

-- Catch-all for rows outside every defined range
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

The partition key column must be part of any primary key or unique constraint on a partitioned table, which is why the key above is (order_id, order_date). A DEFAULT partition (available since PostgreSQL 11) catches rows that match no other partition's bounds; without one, inserting an out-of-range row raises an error.

List and hash tables follow the same shape with different bound syntax:

-- LIST: route by a discrete column value
CREATE TABLE customers (id bigint, region text) PARTITION BY LIST (region);
CREATE TABLE customers_emea PARTITION OF customers FOR VALUES IN ('EU', 'UK', 'ME');
CREATE TABLE customers_na   PARTITION OF customers FOR VALUES IN ('US', 'CA');

-- HASH: spread evenly across 4 partitions
CREATE TABLE events (id bigint, payload jsonb) PARTITION BY HASH (id);
CREATE TABLE events_p0 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE events_p1 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 1);
-- ...p2, p3

Indexes created on the partitioned parent cascade to every partition and to partitions added later - a feature available since PostgreSQL 11. There is no single global index spanning all partitions; each partition gets its own local index. CREATE INDEX CONCURRENTLY cannot run against the parent table, so to build an index without blocking writes you create it concurrently on each partition, then attach them. See creating indexes in PostgreSQL for the concurrent build workflow.

How Partition Pruning Works

Partition pruning is the optimization that makes partitioning pay off: PostgreSQL skips partitions that cannot contain matching rows. It is controlled by enable_partition_pruning, which defaults to on. A query like SELECT count(*) FROM orders WHERE order_date >= '2025-01-01' touches only orders_2025, not every partition.

-- The plan should show only the matching partition(s), not a scan of all
EXPLAIN SELECT count(*) FROM orders
WHERE order_date >= '2025-06-01' AND order_date < '2025-07-01';

Pruning happens in two phases. Plan-time pruning eliminates partitions when the filter values are constants known at planning. Run-time pruning (added in PostgreSQL 11) handles values not known until execution - parameters in prepared statements, subquery results, or the inner side of a nested-loop join - and shows up in EXPLAIN ANALYZE as Subplans Removed: N.

The rule that trips people up: the query must filter on the partition key itself for pruning to apply. If you partition on an expression such as EXTRACT(YEAR FROM order_date), then a plain WHERE order_date BETWEEN '2025-01-01' AND '2025-12-31' will not prune, because the planner does not connect that range to the year expression - it scans every partition. The fix is not to add the expression to the query; it is to partition on the raw order_date column so normal date filters prune naturally. Partition on what you query.

Two related optimizations are off by default and worth enabling for analytical workloads. enable_partitionwise_join lets PostgreSQL join two similarly partitioned tables partition-by-partition, and enable_partitionwise_aggregate pushes grouping down into each partition. Both increase planning time and memory use, so turn them on per session or per workload rather than globally unless you have measured the benefit.

Partitioning in Production: What to Watch For

Partition maintenance is mostly about lifecycle. ALTER TABLE ... DETACH PARTITION removes a partition from the parent while keeping its data as a standalone table, which is how you archive an old time range without a costly DELETE and VACUUM. Use DETACH PARTITION CONCURRENTLY (PostgreSQL 14+) to avoid holding an ACCESS EXCLUSIVE lock on the parent. ATTACH PARTITION adds a populated table back as a partition, after PostgreSQL validates that its rows fall within the declared bounds.

-- Archive an old partition without blocking the live table
ALTER TABLE orders DETACH PARTITION orders_2024 CONCURRENTLY;

The common failure modes are operational, not syntactic. Forgetting to create next month's partition before data arrives sends rows to the DEFAULT partition or raises insert errors - automate partition creation with pg_partman or a scheduled job. Too many partitions (thousands) inflate planning time and relcache memory; partition count in the low hundreds is a healthier target. And a query that omits the partition key in its WHERE clause scans every partition, quietly erasing the benefit - this is the kind of regression that is easy to ship and hard to spot.

That last failure is where continuous monitoring helps. Pulse tracks query plans and per-partition I/O over time, so when a query stops pruning - because someone changed a filter, the partition key dropped out of an ORM-generated query, or a partition grew lopsided - it flags the regression and points to the queries and partitions responsible, rather than leaving you to diff EXPLAIN output after latency has already climbed. See database monitoring best practices for the broader signal set.

Frequently Asked Questions

Q: When should I partition a PostgreSQL table?
A: Partition a PostgreSQL table when it grows large enough that maintenance (VACUUM, index rebuilds, bulk deletes) becomes painful or when you regularly query and archive by a natural key like time. As a rough guide, tables in the tens-to-hundreds of millions of rows with a clear range or list key are good candidates. Small tables rarely benefit, because the planning overhead of many partitions can outweigh the scan savings.

Q: What is the difference between range, list, and hash partitioning in PostgreSQL?
A: Range partitioning assigns rows to partitions by ordered value ranges (lower bound inclusive, upper bound exclusive), ideal for time-series data. List partitioning assigns rows by an explicit set of discrete values, such as region codes. Hash partitioning distributes rows evenly using a modulus and remainder of the key's hash, useful when there is no natural range or list to partition on.

Q: Why is my query scanning all partitions instead of pruning?
A: PostgreSQL only prunes partitions when the query filters on the partition key directly. The most common cause is partitioning on an expression like EXTRACT(YEAR FROM order_date) while filtering on the raw order_date column - the planner cannot connect the two, so it scans every partition. Partition on the raw column you query on, and confirm pruning with EXPLAIN.

Q: How do I add an index to a partitioned table in PostgreSQL?
A: Create the index on the partitioned parent table, and since PostgreSQL 11 it cascades automatically to every existing and future partition as a local index. There is no global index across partitions. Because CREATE INDEX CONCURRENTLY is not allowed on the parent, build indexes concurrently on each partition individually when you need to avoid blocking writes.

Q: How do I archive or drop old data from a partitioned table?
A: Use ALTER TABLE ... DETACH PARTITION to remove an old partition from the parent while retaining it as a standalone table you can back up or drop. This avoids a large DELETE and the VACUUM it triggers. On PostgreSQL 14 and later, add CONCURRENTLY to detach without taking an ACCESS EXCLUSIVE lock on the parent table.

Q: What is a default partition in PostgreSQL?
A: A DEFAULT partition, available since PostgreSQL 11, catches any row that does not match the bounds of any other partition. Without one, inserting an out-of-range value raises an error. It is a useful safety net, but rows accumulating in the default partition usually signal a missing partition that should be created.

Q: Does partitioning improve PostgreSQL query performance automatically?
A: No. Partitioning improves performance only when queries filter on the partition key so the planner can prune partitions, and when partition count stays manageable. A query without the partition key in its WHERE clause scans every partition and can be slower than an equivalent unpartitioned table with a good index. Verify pruning with EXPLAIN rather than assuming it.

Related Reading

• PostgreSQL CREATE TABLE: Table definition syntax, the foundation for declaring partitioned parents.
• PostgreSQL CREATE INDEX: Index types and the concurrent build workflow needed for partitions.
• EXPLAIN ANALYZE in PostgreSQL: Read plans to confirm partition pruning and runtime subplan removal.
• PostgreSQL Performance Tuning: Configuration and query-level levers beyond partitioning.
• Debugging Slow PostgreSQL Queries: Find the queries that fail to prune or scan too much data.
• Database Monitoring Best Practices: Which metrics surface a query that stopped pruning before users notice.