Setting Up Declarative Table Partitioning in PostgreSQL for Time-Series Data

Once a time-series table passes a few hundred million rows, routine operations degrade: indexes stop fitting in memory, VACUUM runs for hours, and deleting old data with DELETE generates enormous write-ahead log (WAL) traffic. Declarative partitioning, available since PostgreSQL 10, addresses this by splitting one logical table into many physical child tables, so queries scan only relevant partitions and old data is removed with an instant DROP TABLE instead of a slow DELETE.

Desired outcome

A table partitioned by range on a timestamp column, with one child partition per month. Queries that filter on the timestamp use partition pruning (the planner skips irrelevant partitions at plan time), and retiring old months is a metadata-only operation.

Prerequisites and the decision you cannot easily undo

• PostgreSQL 10 or later; the examples assume PostgreSQL 14+ syntax, which is stable across current supported versions.
• Permission to create tables in the target schema, and a maintenance window if you are migrating an existing table (partitioning cannot be added to an existing table in place — you create a new partitioned table and move data).
• A partition key chosen before loading data. Changing the key later requires a full table rewrite. Pick a column that appears in most of your queries' WHERE clauses, typically the event timestamp.
• Enough selectivity in the key. If a typical query spans most partitions, pruning buys little and you pay planning overhead instead.

Procedure: create the partitioned table and monthly children

Run these in psql or any SQL client as a role with CREATE privilege on the schema. Replace measurements, recorded_at, and the date ranges with your own names and boundaries.

CREATE TABLE measurements (
    id          bigint GENERATED ALWAYS AS IDENTITY,
    recorded_at timestamptz NOT NULL,
    device_id   int NOT NULL,
    value       double precision,
    PRIMARY KEY (recorded_at, id)
) PARTITION BY RANGE (recorded_at);

Note the primary key must include the partition key — PostgreSQL enforces uniqueness only within each partition, so the key column has to be part of any unique constraint.

CREATE TABLE measurements_2026_09 PARTITION OF measurements
    FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');
CREATE TABLE measurements_2026_10 PARTITION OF measurements
    FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
CREATE TABLE measurements_2026_11 PARTITION OF measurements
    FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');

Bounds are inclusive at the low end and exclusive at the high end. Create the next partition before the current one fills; an insert with no matching partition fails with an error rather than spilling somewhere safe. Many teams script this with a cron job or pg_partman. Indexes created on the parent are propagated to children automatically, so define them once:

CREATE INDEX ON measurements (device_id, recorded_at);

Checks: confirm pruning and partition layout

Insert representative data, then verify two things. First, the partition list:

SELECT inhrelid::regclass AS partition
FROM pg_inherits
WHERE inhparent = 'measurements'::regclass;

(On PostgreSQL 15+, pg_partition_tree('measurements') gives a cleaner tree view.) Second, confirm pruning with EXPLAIN — run this where your application runs its queries:

EXPLAIN (COSTS OFF)
SELECT count(*) FROM measurements
WHERE recorded_at >= '2026-10-10' AND recorded_at < '2026-10-11';

The plan should show a scan only on measurements_2026_10. If you see all partitions listed, the predicate is the problem: pruning works with simple equality and range comparisons against the key, but often fails when the key is wrapped in a function (for example date_trunc('day', recorded_at) = ...). Rewrite such predicates as explicit ranges.

Retiring old data and recovering from mistakes

To drop a month, run DROP TABLE measurements_2026_01;. This is fast and permanent — archive first with pg_dump or COPY if retention policy requires it. A safer intermediate step is ALTER TABLE measurements DETACH PARTITION measurements_2026_01;, which turns the partition into a standalone table you can archive or re-attach later with ATTACH PARTITION.

If rows landed in the wrong partition (possible after attaching a pre-filled table with mismatched bounds), detach the partition, fix the data with ordinary UPDATE/INSERT ... SELECT, then re-attach with corrected bounds. ATTACH PARTITION validates existing rows against the new bounds and takes a stronger lock if no matching CHECK constraint exists, so add a CHECK matching the bounds before attaching to keep the scan lock-free-ish and fast.

Limitations to plan around

• VACUUM, ANALYZE, and REINDEX run per partition; the parent does not cascade maintenance. Autovacuum treats each partition as its own table, which usually works, but large partitions may need tuned thresholds.
• Thousands of partitions inflate planning time. Tens to a few hundred partitions is the comfortable range; prefer monthly over daily if daily would explode the count.
• Foreign keys referencing a partitioned table are supported only from PostgreSQL 12 onward, with restrictions on earlier versions.

A practical final check: run your application's five most common queries with EXPLAIN ANALYZE against the partitioned table and compare scanned partitions and buffer hits against the old monolithic table. If pruning appears in every plan and old-data cleanup is a DROP instead of a DELETE, the migration did its job.