Table partitioning

Partitioning keeps billion-row tables manageable and makes deleting old data instant instead of a day-long DELETE.

Declarative partitioning splits one logical table into child tables, each holding a slice of the rows. Queries and writes go to the parent; PostgreSQL routes rows to the right partition.

CREATE TABLE events (...) PARTITION BY RANGE (created_at);
CREATE TABLE events_2026_03 PARTITION OF events
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

Range bounds include the lower and exclude the upper value. Other strategies: PARTITION BY LIST (region) for discrete values, and PARTITION BY HASH (customer_id) to spread rows evenly. A DEFAULT partition catches rows that fit nowhere else (but it rules out DETACH ... CONCURRENTLY).

What you gain:

Partition pruning: a query with WHERE created_at >= '2026-03-10' skips partitions that cannot match, at plan time or, with parameters, at execution time.
Cheap data retention: dropping a month is DETACH PARTITION plus DROP TABLE, a metadata operation instead of deleting millions of rows and vacuuming afterwards. DETACH ... CONCURRENTLY (PostgreSQL 14+) avoids blocking queries.
Smaller per-partition indexes and per-partition vacuum.

What it costs:

Queries that do not filter on the partition key touch every partition.
A primary key or unique constraint must include the partition key columns, so a globally unique id alone is not enforceable.
Each partition adds planning overhead; thousands of partitions slow down queries. Aim for partitions of a size that matches your retention or access unit, not as many as possible.
New partitions must exist before data arrives; schedule their creation (the pg_partman extension automates it).

Partitioning is not a substitute for indexes. Below roughly hundreds of millions of rows, or without a clear key that queries filter on and data ages by, a well-indexed single table is usually simpler and as fast.

Example

CREATE TABLE events (
    id         bigint GENERATED ALWAYS AS IDENTITY,
    created_at timestamptz NOT NULL,
    kind       text NOT NULL,
    payload    jsonb,
    PRIMARY KEY (id, created_at)        -- must include the partition key
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_02 PARTITION OF events
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE events_2026_03 PARTITION OF events
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

CREATE INDEX ON events (kind);          -- created on every partition

EXPLAIN SELECT count(*) FROM events WHERE created_at >= '2026-03-10';
--  Aggregate
--    ->  Seq Scan on events_2026_03 ...   (February partition pruned)

-- Retention: remove February without a giant DELETE
ALTER TABLE events DETACH PARTITION events_2026_02 CONCURRENTLY;
DROP TABLE events_2026_02;

Remember

RANGE, LIST and HASH partitioning; range upper bounds are exclusive
Pruning skips partitions only when queries filter on the partition key
Detach and drop partitions for instant data retention
Unique keys must include the partition key; too many partitions slow planning

Watch out for

Partitioning on a column queries rarely filter by, so every query scans all partitions.

Check yourself

Why must the primary key of a range-partitioned events table include created_at?
What is the main advantage of DETACH + DROP over DELETE for removing a month of data?

Official docs: www.postgresql.org/docs/current/ddl-partitioning.html

Example

Remember

Watch out for

Check yourself

Builds on

Unlocks