Partitioning

Partitioning divides one table into several physical child tables according to a partition key. The table still behaves as one table to queries; the storage and maintenance are split.

The two reasons to partition

Retention. Dropping a partition is a metadata operation that frees space instantly. Deleting the equivalent rows is a long-running write that generates dead tuples, bloat, WAL and replication lag. If you expire data on a schedule, partition by time and drop — this is the strongest single argument for partitioning.

Pruning. If the planner can prove a query only needs some partitions, it skips the rest. A query for last week's data on a table partitioned by month reads one or two partitions instead of sixty.

Everything else — smaller indexes, cheaper vacuum, parallel maintenance — is a secondary benefit.

What pruning requires

Pruning only works when the query filters on the partition key directly. These do not prune:

-- Does not prune: the function hides the partition key from the planner.
SELECT * FROM events WHERE date_trunc('day', created_at) = '2026-07-30';
 
-- Prunes: a plain range predicate on the partition key.
SELECT * FROM events
WHERE created_at >= '2026-07-30' AND created_at < '2026-07-31';

A join whose partition-key predicate comes from the other table often cannot prune at plan time either. PostgreSQL can do runtime pruning for some of these cases; verify with EXPLAIN rather than assuming. See Query Plans.

Choosing granularity

Partition count is a balance. Too few and you get no pruning benefit; too many and planning time, open file handles and memory per query grow.

The number that matters is partitions per query and total partitions per table — both in the tens or low hundreds, not thousands. Validate against your own planning times: a query that plans in 20 ms and executes in 5 ms has too many partitions.

Common mistakes

Uniqueness and indexes

In PostgreSQL, a unique constraint on a partitioned table must include the partition key. There is no way to enforce global uniqueness on a non-partition-key column across partitions — the same constraint that appears in sharding, for the same reason.

Indexes are created per partition. Creating an index on a partitioned parent creates it on every child, which can be a long operation on a large table; PostgreSQL supports building children concurrently and attaching them to keep the lock window short.