Declarative partitioning in practice

Partitioning makes retention instant and keeps huge time-series tables manageable, if you avoid its traps.

PARTITION BY RANGE | LIST | HASH turns a table into a parent with no storage of its own; rows are routed to child partitions. Practical points beyond the basics:

Create partitions ahead of time (a scheduled job or the pg_partman extension). Inserts with no matching partition fail, unless you add a DEFAULT partition — which then must be scanned when new partitions are attached, so keep it empty.
Indexes created on the parent are created on every partition automatically. A primary key or unique constraint must include the partition key, because uniqueness is only enforced within each partition.
Retention: ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY then DROP TABLE removes a month of data instantly, with no bloat — far better than a mass DELETE.
Loading: build and index a table separately, add a matching CHECK constraint, then ATTACH PARTITION; the check lets PostgreSQL skip validating the rows.
Pruning happens at planning time for constants and at execution time for parameters; it only works when the WHERE clause constrains the partition key directly.

Don't over-partition: thousands of partitions slow planning and use more memory. Partitions of a few GB to tens of GB are typical. Partitioning helps maintenance and pruning; it doesn't make single-row lookups by an indexed key faster.

Example

CREATE TABLE event (
    id         bigint GENERATED ALWAYS AS IDENTITY,
    created_at timestamptz NOT NULL,
    kind       text,
    PRIMARY KEY (id, created_at)            -- must include the partition key
) PARTITION BY RANGE (created_at);
CREATE INDEX ON event (kind);               -- cascades to every partition

CREATE TABLE event_2026_05 PARTITION OF event
    FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');
CREATE TABLE event_2026_06 PARTITION OF event
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');
INSERT INTO event (created_at, kind) VALUES ('2026-05-20', 'login'), ('2026-06-02', 'click');

EXPLAIN SELECT * FROM event WHERE created_at >= '2026-06-01';
-- scans only event_2026_06
ALTER TABLE event DETACH PARTITION event_2026_05 CONCURRENTLY;
DROP TABLE event_2026_05;                   -- instant retention

Remember

Create partitions in advance; keep any DEFAULT partition empty
Unique keys must include the partition key
DETACH ... CONCURRENTLY + DROP is instant, bloat-free retention

Watch out for

Querying by a function of the partition key (date(created_at) = ...), which defeats pruning.

Check yourself

Why must the primary key of a partitioned table include the partition key?

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

Example

Remember

Watch out for

Check yourself

Builds on

Unlocks