Time-Series Postgres: Declarative Partitioning in Practice

Time-Series Postgres: Declarative Partitioning in Practice#

The events table had grown to 900GB over four years. A dashboard query for "yesterday's signups by source" scanned an index that covered the entire history, and even with a good index on created_at the planner was chewing through bloated btree pages and the visibility map for rows nobody would ever look at again. Response time sat around 8 seconds. Deleting old data was worse: a DELETE ... WHERE created_at < now() - interval '1 year' locked things up and left behind bloat that vacuum couldn't keep up with. Declarative partitioning fixed both problems at once.

Range partitions by month#

Postgres native partitioning (declarative, since version 10, and solid since 12) lets you split one logical table into physical child tables by a key. For time-series, range partitioning on the timestamp is the natural fit.

CREATE TABLE events (
    id          bigint       GENERATED ALWAYS AS IDENTITY,
    created_at  timestamptz  NOT NULL,
    user_id     bigint,
    source      text,
    payload     jsonb,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_06 PARTITION OF events
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');
CREATE TABLE events_2026_07 PARTITION OF events
    FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

Note the primary key includes created_at. That's a partitioning requirement: the partition key must be part of every unique constraint. It surprises people who want a bare PRIMARY KEY (id) and get an error. You live with the composite key.

Partition pruning is the whole point#

The payoff is that the planner skips partitions that can't contain matching rows. A query bounded by time only touches the relevant month:

EXPLAIN (ANALYZE, BUFFERS)
SELECT source, count(*)
FROM events
WHERE created_at >= '2026-07-03' AND created_at < '2026-07-04'
GROUP BY source;

The plan shows a single child table scanned (events_2026_07) instead of the whole 900GB. Our dashboard query dropped from 8s to about 180ms. The catch: pruning only works when the query filters on the partition key with a constant or a stable expression. WHERE created_at >= now() - interval '1 day' prunes at execution time (Postgres does runtime pruning), but WHERE date_trunc('day', created_at) = '2026-07-03' wraps the column in a function and defeats pruning entirely. Keep the partition column bare on the left side of the comparison. That one rule causes most "why is it still scanning everything" tickets.

Retention becomes a metadata operation#

The best part is dropping old data. Instead of a slow, bloat-generating DELETE, you detach or drop a whole partition, which is near-instant because it just unlinks a file:

DROP TABLE events_2022_06;
-- or keep it around cold, out of the parent:
ALTER TABLE events DETACH PARTITION events_2022_06 CONCURRENTLY;

DROP TABLE on a monthly partition took under a second and reclaimed 20GB immediately, no vacuum, no bloat. That alone justified the migration. DETACH ... CONCURRENTLY (Postgres 14+) lets you pull a partition out without a long lock if you want to archive it to cold storage first.

Automating the monthly rollover#

Nobody wants to remember to create next month's partition. You either use pg_partman or a small scheduled function. The pg_partman setup is three lines:

CREATE EXTENSION pg_partman;
SELECT partman.create_parent(
    p_parent_table => 'public.events',
    p_control      => 'created_at',
    p_interval     => '1 month',
    p_premake      => 3
);

p_premake => 3 keeps three future partitions ready, so a burst at midnight on the first of the month never lands in a missing partition (which would otherwise error or fall into a default partition and ruin pruning). Pair it with run_maintenance() on a cron or pg_cron schedule to create new partitions and drop expired ones per a retention setting. We run maintenance nightly at 03:00 and set retention to 18 months.

The migration itself#

Converting a live 900GB table isn't free. You can't turn an existing plain table into a partitioned one in place. The path we took: create the new partitioned table, backfill in monthly batches with INSERT ... SELECT during low traffic, then swap names in a transaction and repoint the app. Budget for the double storage during the copy. For smaller tables, pg_partman's offline conversion or a logical replication cutover are gentler options.

The call we'd make#

If a table is time-ordered, grows without bound, and you routinely query recent data while deleting old data, partition it by range on the timestamp before it hits a few hundred GB, not after. The query speedup from pruning is real but the retention story is the bigger win: dropping a partition is a metadata flick, while DELETE on a giant table is a bloat-making outage waiting to happen. Use pg_partman so you never hand-create a partition, and keep the partition column bare in your WHERE clauses or you'll wonder why nothing got faster.