PostgreSQL Partitioning

Declarative partitioning splits a table into child tables by a partition key. The main operational payoff is dropping a partition instead of deleting rows.

Creating a partitioned table

CREATE TABLE events (
    id          bigint GENERATED ALWAYS AS IDENTITY,
    tenant_id   bigint      NOT NULL,
    event_type  text        NOT NULL,
    payload     jsonb       NOT NULL DEFAULT '{}'::jsonb,
    created_at  timestamptz NOT NULL,
    -- The partition key must be part of every unique constraint.
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);
 
CREATE TABLE events_2026_07 PARTITION OF events
    FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');
CREATE TABLE events_2026_08 PARTITION OF events
    FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');
 
-- Indexes created on the parent are created on every partition.
CREATE INDEX events_tenant_created_idx ON events (tenant_id, created_at DESC);

Range partitioning suits time series. List partitioning suits a small fixed set such as region. Hash partitioning spreads writes evenly when there is no natural range key.

Verifying pruning

Pruning is the reason to partition, so confirm it rather than assume it:

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM events
WHERE created_at >= '2026-07-01' AND created_at < '2026-07-15';
-- The plan should reference only events_2026_07.

If the plan shows every partition, the predicate is not on the partition key in a form the planner can use — usually because it is wrapped in a function, or because the value arrives from a join.

Retention by detach and drop

-- Detach first: the table becomes independent and can be archived or inspected.
ALTER TABLE events DETACH PARTITION events_2025_07 CONCURRENTLY;
 
-- Then drop, after the archive copy is verified.
DROP TABLE events_2025_07;

DETACH ... CONCURRENTLY avoids the exclusive lock that a plain detach takes on the parent. Verify the archived copy before dropping — a drop is only recoverable from backup.

Costs to plan for

• Planning time grows with partition count. Keep the total in the tens or low hundreds and measure planning time on your real queries.
• Unique constraints must include the partition key; global uniqueness on another column is not enforceable.
• Converting an existing table copies the data. Build the partitioned table alongside and migrate — see Zero-Downtime Migrations.
• Foreign keys referencing a partitioned table are supported in recent versions; confirm behaviour for the version you run before designing around them.