Skip to main content
Time-Series Postgres: Declarative Partitioning in Practice

Time-Series Postgres: Declarative Partitioning in Practice

KU
· Founder & Chief Editor
• 7 min read•Updated •

An events table where every query scanned years of history to read one day. Range partitioning by month makes recent-data queries prune to one partition and turns retention into a DROP TABLE.

Key takeaways

  • Partition a time-series table with PARTITION BY RANGE on its timestamp, one partition per month.
  • Queries that filter on the bare timestamp column prune to the matching partitions, and retention becomes DROP TABLE or DETACH PARTITION instead of a bloating DELETE.
  • Automate future partitions with pg_partman, and include the timestamp in every primary key.

Partition a time-series table with PARTITION BY RANGE (created_at), one child table per month. Queries filtered on the bare timestamp only scan matching partitions, and deleting old data becomes DROP TABLE on a partition instead of a slow, bloating DELETE. Let pg_partman create future partitions, and put the timestamp in every primary key.

Verified againstChecked against official docs

PostgreSQL 18 (18.6 current; 19 at release candidate), pg_partman 5.5.0, against the postgresql.org table partitioning docs, PostgreSQL 17 release notes and the pg_partman README. Checked October 2026.

The pattern we keep seeing: an events table that has grown to hundreds of gigabytes over several years. A dashboard query for "yesterday's signups by source" walks an index covering the entire history, and even with a good index on created_at the planner chews through bloated btree pages for rows nobody will look at again. Deleting old data is worse: a DELETE ... WHERE created_at < now() - interval '1 year' holds locks for ages and leaves behind bloat vacuum can't keep up with. Declarative partitioning fixes both problems at once.

Range partitions by month

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

migrations/001_events.sql
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_10 PARTITION OF events
    FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
CREATE TABLE events_2026_11 PARTITION OF events
    FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');

The primary key includes created_at because the docs require it: a unique or primary key constraint on a partitioned table must include all partition key columns, with no expressions. People who want a bare PRIMARY KEY (id) get an error and live with the composite key. Identity columns on partitioned tables arrived in PostgreSQL 17; on 16 and older, use a bigserial or a sequence default instead.

Partition pruning is the whole point

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

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

The plan shows a single child table (events_2026_10) instead of the whole history, so a dashboard query that took seconds typically drops well under one. 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 during execution (the docs cover runtime pruning, and EXPLAIN marks skipped partitions as never executed), but WHERE date_trunc('day', created_at) = '2026-10-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 drop or detach a whole partition, which is near-instant because it removes the table's files rather than rewriting rows:

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

Space comes back immediately, with no vacuum and no bloat. That alone usually justifies the migration. A plain DETACH PARTITION takes an ACCESS EXCLUSIVE lock on the parent; DETACH ... CONCURRENTLY (PostgreSQL 14+) needs only SHARE UPDATE EXCLUSIVE, so you can pull a partition out to back it up with pg_dump before dropping it. If you are fighting bloat on unpartitioned tables in the meantime, our guide to detecting and fixing Postgres index bloat covers the stopgaps.

Automating the monthly rollover

Nobody wants to remember to create next month's partition. Use pg_partman or a small scheduled function. With pg_partman 5.x:

SQL
CREATE SCHEMA partman;
CREATE EXTENSION pg_partman SCHEMA partman;

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

UPDATE partman.part_config
SET retention = '18 months', retention_keep_table = false
WHERE parent_table = 'public.events';

p_premake keeps future partitions ready (4 is the default), 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. Retention lives in the part_config table; by default pg_partman only detaches expired partitions, so set retention_keep_table = false if you want them dropped. Maintenance runs from pg_partman's background worker (add it to shared_preload_libraries) or from run_maintenance() on a cron or pg_cron schedule. Nightly is enough for most tables.

The migration itself

Converting a live table isn't free. The docs are blunt: it is not possible to turn a regular table into a partitioned table in place. The path we take: 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 double storage during the copy. The other route is attaching the old table as one big historical partition and partitioning only new data, which avoids the copy but keeps the old bloat. Treat the cutover like any schema change and stage it with the expand-contract migration pattern.

Questions people ask

When should I partition a Postgres table?

Partition when a table is time-ordered, grows without bound, and you mostly query recent data while deleting old data. Do it before the table reaches a few hundred gigabytes, because converting a large live table means copying it. Small tables gain little: an index on the timestamp is enough, and partitioning adds planning overhead and composite primary keys.

Why is partition pruning not working in my query?

Usually because the query wraps the partition column in a function or casts it, such as date_trunc('day', created_at) = ..., which the planner cannot match against partition bounds. Filter on the bare column with a range instead. Also check that enable_partition_pruning is on, and that parameters are stable values. EXPLAIN (ANALYZE) shows which partitions were scanned or never executed.

Do I need pg_partman for time-series partitioning?

No, but you need something that creates future partitions before data arrives. pg_partman handles premade partitions, retention and maintenance through a background worker, and is offered on many managed Postgres services. A scheduled function that creates next month's table works too. The failure mode without either is inserts failing at midnight on the first of the month.

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 operation, 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.

Sources

Explore topics:Infrastructure
React
Share this post
Cite this article
Plain text

Kiril Urbonas (2026). Time-Series Postgres: Declarative Partitioning in Practice. DevOpsNess. Updated 2026-11-20. https://www.devopsness.com/blog/time-series-postgres-declarative-partitioning

Markdown

[Time-Series Postgres: Declarative Partitioning in Practice](https://www.devopsness.com/blog/time-series-postgres-declarative-partitioning) (Kiril Urbonas, DevOpsNess, updated 2026-11-20)

KU

Kiril Urbonas

Founder & Chief Editor

608 articles
View all articles by Kiril Urbonas

You might have missed

Evergreen posts worth revisiting.