Choose Declarative Partitioning for PostgreSQL Time-Series: When to Range-Partition by Timestamp

The decision: keep one hot table or split time by range

Time-series tables grow monotonically. A single table makes recent queries scan old rows, VACUUM work grows with table size, and dropping old data requires DELETEs that generate bloat. The practical decision is whether to use PostgreSQL declarative partitioning by RANGE on a timestamp column and rely on constraint exclusion pruning, or stay with a single table and accept full scans.

Constraint exclusion is the optimizer feature that uses CHECK constraints on partitions to skip whole child tables when a WHERE clause cannot match their range. Partition pruning is the planner step that removes irrelevant partitions before execution. Both require the partition key to appear in the query.

Constraints that shape the choice

Partition key must be immutable and appear in every query that needs pruning. PostgreSQL 10+ supports declarative partitioning. LIMIT pushdown to individual partitions requires PostgreSQL 14+. UNIQUE constraints must include the partition key, otherwise duplicate detection cannot span partitions. VACUUM and autovacuum run per partition, so many small partitions increase catalog work.

Options compared

Approach	Pruning	Old data removal	Write overhead	Best fit
Declarative RANGE partitioning on timestamp	Yes via constraint exclusion	DROP PARTITION, minimal lock	Planner chooses child; insert routing by partition key	Regular retention windows, date-range filters
Manual table-per-period with UNION view	Application level	DROP TABLE	Extra application logic	When you need different schemas per period
Single table + BRIN index	No partition elimination	DELETE or pg_repack	Low	Append-only, mostly sequential scans
Partitioned table + FDW foreign partitions	Yes for local partitions	DETACH then archive	Network latency for remote reads	Tiered storage to object or remote DB

Trade-offs to weigh

Declarative partitioning reduces I/O for date-range filters because only matching partitions are scanned. CHECK constraints are created automatically and drive constraint exclusion. ATTACH PARTITION and DROP PARTITION allow rolling window maintenance with short locks compared to DELETE.

Costs are operational. Each partition needs its own indexes and autovacuum. Queries with OR conditions, implicit casts on the partition key, or missing partition key in WHERE will disable pruning. Cross-partition joins without indexes can degrade to nested loops. Earlier than PostgreSQL 14, LIMIT may be applied after all partitions are scanned.

Concrete implementation pattern

Run as a database owner with CREATE privilege on the schema. Replace placeholders with your names.

-- parent table, partition key must be NOT NULL
CREATE TABLE {{schema}}.events (
  id bigserial,
  ts timestamptz NOT NULL,
  payload jsonb
) PARTITION BY RANGE (ts);

-- monthly partitions for a rolling window
CREATE TABLE {{schema}}.events_y2026m01 PARTITION OF {{schema}}.events
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE {{schema}}.events_y2026m02 PARTITION OF {{schema}}.events
  FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

-- indexes per partition, not on parent
CREATE INDEX ON {{schema}}.events_y2026m01 (ts);
CREATE INDEX ON {{schema}}.events_y2026m01 (ts, id);

Rolling window maintenance uses ATTACH and DROP. Create an empty table for the next period and attach it ahead of time to avoid write stalls.

CREATE TABLE {{schema}}.events_y2026m03 (LIKE {{schema}}.events INCLUDING ALL);
ALTER TABLE {{schema}}.events ATTACH PARTITION {{schema}}.events_y2026m03
  FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

-- drop old period
ALTER TABLE {{schema}}.events DETACH PARTITION {{schema}}.events_y2025m01;
DROP TABLE {{schema}}.events_y2025m01;

Risk: ATTACH validates that the child has a compatible CHECK constraint and no overlapping data. A failure blocks the command and holds an AccessExclusiveLock on the parent briefly. Test ATTACH on a non-production copy first.

Validate pruning and constraint exclusion

Check that constraint exclusion is enabled and the planner sees the partitions.

SHOW constraint_exclusion;
SELECT relname FROM pg_inherits WHERE inhparent = 'events'::regclass;

Inspect the plan for a date-range filter. The plan should reference only the relevant child tables, not all partitions.

EXPLAIN SELECT * FROM {{schema}}.events
WHERE ts >= '2026-02-10' AND ts < '2026-02-20';

Look for Append with only the expected partition in the plan. If the partition key is missing or casted, the plan will show all partitions.

Measure scan reduction with statistics.

SELECT schemaname, relname, n_live_tup
FROM pg_stat_user_tables
WHERE relname LIKE 'events%';

Run the same query before and after adding the partition key filter to confirm row counts drop to the targeted partition.

Limitations and checks

Partition key must appear exactly as defined. Implicit casts like ts::date disable pruning. OR conditions often prevent exclusion. UNIQUE constraints must include the partition key.

VACUUM must run on each partition. Monitor autovacuum lag on older, less active partitions.

Rollback for the example above:

DROP TABLE {{schema}}.events CASCADE;

This removes parent and all attached partitions.