Using Declarative Partitioning in PostgreSQL for Time‑Series Data

Problem: Slow queries and costly purges on a growing time‑series table

When a single table stores millions of rows per day, simple SELECT statements that filter by a timestamp begin to scan the entire table, causing high I/O and long response times. Removing old data with DELETE acquires heavy locks and can block writes for minutes or hours. These symptoms indicate that the table has outgrown the monolithic design and needs a strategy that limits the amount of data touched per operation.

How declarative partitioning works in PostgreSQL

PostgreSQL 10 introduced declarative partitioning, where a parent table defines a partition key and PostgreSQL automatically routes each insert to the matching child partition. The parent table holds no data; it only stores metadata. All partitions share the exact same schema, including columns, data types, and indexes. When you create a partition you specify the boundary values for the key, for example a range of dates.

To set up a partitioned table you first create the parent:

CREATE TABLE sensor_data (
    ts          TIMESTAMPTZ NOT NULL,
    device_id   INTEGER NOT NULL,
    value       DOUBLE PRECISION,
    PRIMARY KEY (ts, device_id)
) PARTITION BY RANGE (ts);

Then you create child partitions for the ranges you need, e.g., one per month:

CREATE TABLE sensor_data_2026_09 PARTITION OF sensor_data
    FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');
CREATE TABLE sensor_data_2026_10 PARTITION OF sensor_data
    FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');

Query routing and partition pruning

When a query includes a condition on the partition key, the planner can exclude (prune) partitions that cannot possibly contain matching rows. For a simple range predicate like WHERE ts >= '2026-10-10' AND ts < '2026-10-20', PostgreSQL will scan only the sensor_data_2026_10 partition, ignoring older ones. This reduces the amount of data read from disk and can turn a sequential scan of tens of gigabytes into an index scan of a few megabytes.

To verify pruning, run EXPLAIN (COSTS OFF) on a query that filters by the key. The output should list only the relevant partitions under an Append node. If you see a Seq Scan on the parent or many partitions, pruning did not fire—often because the predicate used a function or complex expression on the key.

Maintenance: adding new partitions and dropping old ones

Adding a partition for the next month requires only a CREATE TABLE … PARTITION OF statement, which takes a negligible lock on the parent. Dropping old data is similarly lightweight: detach the partition and then drop or archive the underlying table.

-- Detach September 2026 data
ALTER TABLE sensor_data DETACH PARTITION sensor_data_2026_09;
-- Now you can safely drop or move the detached table
DROP TABLE sensor_data_2026_09;

Because the detach operation only updates metadata, it completes quickly even when the partition holds billions of rows, allowing a rolling‑window purge with minimal impact on concurrent writes.

Trade‑offs and limitations

While partitioning solves many scaling problems, it introduces constraints you must consider:

• Uniform schema: All partitions must have identical column definitions. Changing a column type (e.g., widening a VARCHAR) requires locking every child partition, which can be expensive on hundreds of partitions.
• Partition‑key‑dependent pruning: The planner can prune only when the WHERE clause uses the partition key directly with simple equality or range comparisons. Applying a function like WHERE date_trunc('month', ts) = '2026-10' disables pruning and results in a full scan.
• Uniqueness constraints: A unique index that does not include the partition key cannot be enforced globally. You must either create a unique index on each partition (which only guarantees uniqueness within that partition) or include the partition key in a unique index on the parent.

These limitations mean that partitioning works best when the access pattern aligns with the key (e.g., time‑range queries) and when schema changes are infrequent.

Worked example: setting up a monthly partitioned table

1. Connect to the database as a user with CREATETABLE privileges.
2. Create the parent table partitioned by range on ts (see code block above).
3. Create the first two monthly partitions for September and October 2026.
4. Insert a few rows spanning the boundary to test routing:

INSERT INTO sensor_data (ts, device_id, value) VALUES
    ('2026-09-30 23:59:59+00', 101, 12.5),
    ('2026-10-01 00:00:00+00', 102, 13.0);

1. Run EXPLAIN (COSTS OFF) SELECT * FROM sensor_data WHERE ts >= '2026-09-30' AND ts < '2026-10-02';. Verify that the plan shows an Append with two child partitions (sensor_data_2026_09 and sensor_data_2026_10) and no scan of the parent.
2. Detach the September partition and confirm that a query for September returns no rows:

ALTER TABLE sensor_data DETACH PARTITION sensor_data_2026_09;
SELECT COUNT(*) FROM sensor_data WHERE ts < '2026-10-01'; -- should return 0

At this point the data for September lives in the detached table sensor_data_2026_09, which you can archive or drop.
Actionable next steps
If you manage a time‑series table that shows slow range scans or costly purges, try the following:

Identify a column with monotonic growth (usually a timestamp) that appears in most query predicates.
Prototype a partitioned parent with a range scheme matching your retention window (e.g., daily or weekly partitions).
Run a representative query with EXPLAIN to confirm partition pruning.
Test a detach operation on a stale partition to measure the lock duration.
Monitor for schema change needs; if frequent alterations are required, consider whether the partitioning overhead outweighs the benefits.

By aligning your table design with the natural time‑based access pattern, you can achieve predictable query performance and maintenance windows that fit within normal operational SLAs.