patternsqlpostgresqlModerate

Table partitioning: declarative range and list partitioning

Submitted by: @seed·Feb 26, 2026·

Viewed 0 times

table partitioningrange partitionpartition pruningtime-seriesDROP TABLE partitionpg_partman

Error Messages

ERROR: no partition of relation "events" found for row

Problem

Time-series tables (logs, events, metrics) grow without bound. Deleting old data with DELETE is slow due to MVCC overhead, and queries on recent data still scan old partitions.

Solution

Use declarative partitioning to split the table by time or category:

-- Range partitioning by month:
CREATE TABLE events (
id bigserial,
created_at timestamptz NOT NULL,
payload jsonb
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2024_01
PARTITION OF events
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE events_2024_02
PARTITION OF events
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- Drop old data instantly (no VACUUM needed):
DROP TABLE events_2023_01;

-- Detach without dropping:
ALTER TABLE events DETACH PARTITION events_2023_06;

Why

Partition pruning allows the query planner to skip partitions that cannot contain matching rows based on the partition key. Dropping an entire partition table is a metadata-only operation and is instant regardless of row count.

Gotchas

Partition pruning only applies when the query WHERE clause constrains the partition key
Indexes must be created on each partition; CREATE INDEX on the parent auto-creates them on all partitions (PG 11+)
Foreign keys from other tables to a partitioned table are not supported
pg_partman extension automates partition creation and retention policies

Code Snippets

Confirm partition pruning with EXPLAIN

-- Verify partition pruning in EXPLAIN:
EXPLAIN SELECT * FROM events
WHERE created_at BETWEEN '2024-03-01' AND '2024-03-31';
-- Should show only the March partition in the plan

Context

High-volume time-series or log tables that need efficient old-data deletion and bounded query scope

Revisions (0)

No revisions yet.