Table Partitioning in PostgreSQL: When You Need It, Partition Pruning, Dropping Old Data and Lock Pitfalls

Once a table reaches hundreds of millions of rows, the pain is usually not query speed but maintenance. Removing old data becomes a DELETE that runs for hours, autovacuum then spends its time cleaning up the dead rows it left behind, indexes bloat, and every maintenance task grows because it has to cover the whole table. PostgreSQL's declarative partitioning keeps the table as one logical object while splitting it into physical pieces. Set up well, throwing away a month of data costs as much as a DROP TABLE. Set up badly, queries get slower, inserts start failing, and locks stall traffic at the worst possible moment.

When partitioning actually pays off

The strongest reason to partition is data lifecycle: events, audit trails, metrics, order history, anything that accumulates over time and is deleted or archived after a retention period. The win is clear here. Detaching and dropping a finished month produces no dead rows and no vacuum work.

The second reason is that nearly every query filters on the partition key. For "the last seven days" style queries the planner skips irrelevant partitions entirely, which is called partition pruning.

If neither applies, try other tools first:

• The right index plus batched deletes. If query speed is your only problem, a well chosen composite index usually gets you the same result with far less complexity. Deleting in small batches is also enough for many medium sized tables.
• A BRIN index. On append-mostly tables where rows arrive in time order, a BRIN index is tiny and gives a real boost to range queries.

To take a side: if a table grows with time and you drop old data on a schedule, use range partitioning. Do not partition just because "the table is big and it might get faster". Queries that do not use the partition key run slower after partitioning, because the planner has to visit each partition separately.

Hash partitioning by tenant is often suggested and rarely worth it. Removing a tenant is still a row by row delete, and large tenants skew the partitions. If you need tenant isolation, that is an architecture decision, not a partitioning one.

Basic setup

With range partitioning the parent holds no data. It only defines the structure and the routing rule:

CREATE TABLE events (
    id          bigserial,
    created_at  timestamptz NOT NULL,
    tenant_id   bigint      NOT NULL,
    payload     jsonb,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_11 PARTITION OF events
    FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');

CREATE TABLE events_2026_12 PARTITION OF events
    FOR VALUES FROM ('2026-12-01') TO ('2027-01-01');

The lower bound is inclusive and the upper bound is exclusive, so consecutive partitions neither overlap nor leave gaps.

Note that the primary key is (id, created_at). That is not a style choice, it is required; the pitfalls section explains why.

To confirm that pruning really happens:

EXPLAIN SELECT count(*) FROM events
WHERE created_at >= '2026-11-10' AND created_at < '2026-11-17';

Only events_2026_11 should appear in the plan. For parameterized queries pruning happens at execution time, and EXPLAIN ANALYZE reports it as a "Subplans Removed" line.

Dropping old data

This is the real payoff:

ALTER TABLE events DETACH PARTITION events_2025_10 CONCURRENTLY;
DROP TABLE events_2025_10;

The CONCURRENTLY option arrived in PostgreSQL 14 and detaches without holding a heavy lock on the parent. It has two restrictions: it cannot run inside a transaction block, and it is not allowed while the parent has a default partition. If you want an archive before dropping, the detached partition is an ordinary table you can pg_dump on its own.

Creating future partitions safely

CREATE TABLE ... PARTITION OF takes a stronger lock on the parent than ATTACH PARTITION does. Under load the command waits for any long running query touching the parent, and every query arriving behind it queues up too. Two habits fix this: create partitions weeks before they are needed, and put a lock_timeout in front of every DDL statement.

On a busy table the lighter path is to build the partition separately and attach it:

SET lock_timeout = '3s';

CREATE TABLE events_2027_01
    (LIKE events INCLUDING DEFAULTS INCLUDING CONSTRAINTS);

ALTER TABLE events_2027_01 ADD CONSTRAINT events_2027_01_range
    CHECK (created_at >= '2027-01-01' AND created_at < '2027-02-01');

ALTER TABLE events ATTACH PARTITION events_2027_01
    FOR VALUES FROM ('2027-01-01') TO ('2027-02-01');

ALTER TABLE events_2027_01 DROP CONSTRAINT events_2027_01_range;

When a CHECK constraint matching the partition bound already exists, PostgreSQL skips the validation scan during attach. Without it, the whole partition is read.

Do not create partitions by hand. A scheduled job, such as the pg_partman extension or a small script of your own, should keep several future partitions ready. When a row fits no partition, PostgreSQL raises no partition of relation "events" found for row, and your application stops writing in the first minute of the new month.

Pitfalls

Unique constraints must include the partition key. PostgreSQL has no global unique index across partitions; each partition's index only knows its own rows. So the primary key and every UNIQUE constraint must contain the partition key columns. As a consequence, id alone is not guaranteed unique across the table. With ids from a sequence or UUIDs this rarely matters in practice, but a business rule like "email must be unique" cannot be enforced by a constraint on a partitioned table. Foreign keys that reference this table also need to carry the partition key, because the referenced columns must be unique.

Expressions on the partition key break pruning. With conditions like WHERE created_at::date = '2026-11-10' or WHERE date_trunc('day', created_at) = ... the planner cannot work out which partitions to read and scans all of them. Always write the condition as a range on the bare column.

A default partition quietly piles up debt. A default partition added "just in case" accepts rows when partition creation fails, turning an error into silence. Later, when you create the real partition for that range, PostgreSQL scans the default partition for conflicting rows and refuses if it finds any. A default partition also rules out DETACH ... CONCURRENTLY. Keep it only as a monitored safety net, or skip it. An error is better than data landing in the wrong place.

Too many partitions slow down planning. Every query spends planning and locking effort on the partitions it may touch. Daily partitions kept for years mean thousands of partitions, and every query that cannot prune pays for that. Size partitions to match your retention granularity: if you drop data monthly, monthly partitions are enough.

Building indexes concurrently needs a detour. CREATE INDEX CONCURRENTLY does not work on a partitioned parent. The workaround:

CREATE INDEX events_tenant_idx ON ONLY events (tenant_id, created_at);

CREATE INDEX CONCURRENTLY events_2026_11_tenant_idx
    ON events_2026_11 (tenant_id, created_at);

ALTER INDEX events_tenant_idx ATTACH PARTITION events_2026_11_tenant_idx;

Repeat the last two steps for each partition. Once every partition's index is attached, the parent index becomes valid.

Autovacuum does not analyze the parent. Partitions get their own statistics, but autovacuum never refreshes the aggregate statistics of the partitioned parent. Queries spanning several partitions can get poor estimates as a result. Add a regular ANALYZE events; at the end of your partition maintenance job.

You cannot convert an existing table in place. There is no ALTER TABLE that turns a plain table into a partitioned one. The usual route is to create the new partitioned table, attach the old table as one large partition (with the CHECK trick above to avoid a scan), and let new data flow into new partitions. The old partition then ages out on its own when its retention period ends.

When not to use it

If the table is below a few tens of millions of rows, if your queries do not filter on the partition key, or if you never delete data, partitioning mostly buys you complexity. If your uniqueness rules depend on columns other than the partition key, partitioning takes away your ability to enforce them. In those cases a good index, BRIN, and batched deletes are the better tools. Partitioning is a maintenance tool for tables whose data is born and dies by time; evaluate it as that first.