How to partition a large PostgreSQL table by range

What and why

Partitioning splits one logical table into physical child tables, each holding a slice of the data. PostgreSQL's declarative range partitioning is ideal for time-series and event data: queries that filter on the key scan only relevant partitions, and dropping an old partition is an instant way to expire data. This tutorial partitions an events table by month.

Prerequisites

• PostgreSQL 13+ (later versions improve pruning and performance).
• A large table keyed by a timestamp.
• Privileges to create tables and move data.

Steps

1. Choose a partition key

Pick a column present in most WHERE clauses, typically created_at. The key must be part of any primary key or unique constraint.

2. Create the partitioned table

CREATE TABLE events (
  id BIGINT GENERATED ALWAYS AS IDENTITY,
  created_at TIMESTAMPTZ NOT NULL,
  payload JSONB,
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

3. Create child partitions

Define a partition per month:

CREATE TABLE events_2026_06 PARTITION OF events
  FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');
CREATE TABLE events_2026_07 PARTITION OF events
  FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

Add a default partition to catch out-of-range rows if needed.

4. Migrate existing data

If converting an existing table, create the partitioned table under a new name, copy data with INSERT INTO events SELECT * FROM events_old, then swap names in a transaction. Test on a copy first.

5. Automate new partitions

Create next month's partition ahead of time with a scheduled job, or use the pg_partman extension to manage creation and retention automatically.

6. Verify partition pruning

EXPLAIN SELECT * FROM events WHERE created_at >= '2026-07-01';

The plan should reference only events_2026_07, confirming the planner pruned other partitions.

Verification

Query a single month and confirm the plan touches one partition. Drop an old partition with DROP TABLE events_2026_06 and confirm it removes data instantly without a slow DELETE.

Next Steps

Add retention automation, create indexes on each partition (or rely on inherited definitions in newer versions), and consider sub-partitioning or hash partitioning for non-time keys.