Time-Series Postgres: Declarative Partitioning in Practice
KU
Kiril Urbonas
last week • 4 min read•
A 900GB events table where every query scanned four years to read one day. Range partitioning by month cut our dashboard queries from 8s to under 200ms.
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.
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.
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.
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.
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.
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.
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.
Get the latest tutorials, guides, and insights on AI, DevOps, Cloud, and Infrastructure delivered directly to your inbox.
Both put SQLite near your users, but they solve replication and write latency very differently. We ran the same schema on both for a month and picked one.
We had long-lived AWS keys sitting in a datacenter we don't own. IAM Roles Anywhere let us delete every one of them. Here's the real setup.
Explore more articles in this category
The Backstage demo always wows leadership. Then six months later the catalog has 400 stale entries and nobody trusts it. Here's what got ours to actually stick.
We inherited 200-odd AWS resources built by hand over four years, with no state file anywhere. Here's how import blocks and a generation workflow got them under Terraform without a rebuild.
A single ALTER TABLE took a lock and stalled every write for 40 seconds during peak traffic. Expand-contract is how we stopped shipping outages.
Evergreen posts worth revisiting.
