Partition a Large Table by Time Range

Convert a growing events table into monthly partitions, keep indexes attached, and verify that queries scan only the partitions they need.

~35 minpostgresqlpartitioningperformance

Task

Split a large time-series table into monthly partitions so old data is dropped instantly and queries scan only recent ranges.

When to use this

An events table grows by fifty million rows per month. Deleting old data takes hours and bloats the table, while every query pays for scanning years of history.

Prerequisites

PostgreSQL 13 or newer
A table with a timestamp column used in range filters
A maintenance window for the initial migration

Steps

1. Create a partitioned parent whose primary key includes the partition key.

CREATE TABLE events (
  id bigint GENERATED ALWAYS AS IDENTITY,
  tenant_id uuid NOT NULL,
  occurred_at timestamptz NOT NULL,
  payload jsonb NOT NULL,
  PRIMARY KEY (id, occurred_at)
) PARTITION BY RANGE (occurred_at);

Expected output: The parent table exists but accepts no rows until a partition or default partition is attached.

2. Create monthly partitions plus a default partition for unexpected dates.

CREATE TABLE events_2026_09 PARTITION OF events
  FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');
CREATE TABLE events_2026_10 PARTITION OF events
  FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
CREATE TABLE events_default PARTITION OF events DEFAULT;

Expected output: September and October rows land in their own partitions; any other date lands in events_default.

3. Create the index once on the parent so every partition, including future ones, inherits it.

CREATE INDEX events_tenant_time_idx ON events (tenant_id, occurred_at DESC);

Expected output: Each partition has its own copy of events_tenant_time_idx, and partitions created later get it automatically.

4. Confirm partition pruning with a bounded time range and the tenant filter.

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM events
WHERE tenant_id = '00000000-0000-0000-0000-000000000001'
  AND occurred_at >= '2026-10-01' AND occurred_at < '2026-10-08';

Expected output: Only events_2026_10 appears in the plan; older partitions and events_default are pruned.

5. Retire old data by dropping a partition instead of deleting rows.

DROP TABLE events_2025_01;

Expected output: The month disappears in milliseconds, with no dead tuples to vacuum and no table bloat.

Troubleshooting

Problem

no partition of relation events found for row

Solution

The date falls outside every range and no default partition exists. Automate partition creation ahead of the month, or keep a default partition as a safety net.

Problem

The plan still scans every partition

Solution

The filter is not on the raw partition key, for example date_trunc('day', occurred_at) or a cast to date. Use an explicit range on occurred_at.

Problem

A unique constraint on tenant_id alone cannot be created

Solution

Unique indexes on a partitioned table must include the partition key. Enforce global uniqueness with a separate lookup table or in the application.

Limitations

The primary key must contain the partition key, which changes the schema contract for existing tables.
Many partitions increase planning time; keep monthly granularity and archive old partitions instead of keeping hundreds.
Queries without a partition-key predicate still scan every partition.

Why range partitioning pays off

Time-series tables have two problems that grow with age: queries must skip old rows, and deletion must not rewrite the table. Range partitioning by month solves both. The planner prunes partitions that cannot contain matching rows, so a query for one week touches one partition instead of years of data. Dropping a partition removes files immediately, while DELETE leaves dead tuples, bloats indexes, and competes with foreground traffic.

The primary key changes

A partitioned table’s unique constraints must include the partition key. That means PRIMARY KEY (id) becomes PRIMARY KEY (id, occurred_at), and any foreign key pointing at events(id) must change or be dropped. Plan this before the migration: child tables, replication, and ORM models all assume the old key. If global uniqueness on id is essential, generate IDs from a sequence or UUID and keep a small lookup table.

Indexes on the parent

Since PostgreSQL 11, creating an index on the partitioned parent creates matching indexes on every existing partition and on partitions added later. You do not need to loop over children, and you should not: a manually created index on one partition will drift. Check with \d+ events_2026_10 that the index exists, and monitor Index Size per partition because a per-tenant index on a huge month may dominate storage.

CREATE INDEX events_tenant_time_idx ON events (tenant_id, occurred_at DESC);

Make pruning visible

Write one test query with a bounded range and assert the plan. If a filter wraps the partition key in a function, PostgreSQL cannot prune and the query degrades quietly as partitions accumulate. The most common offenders are date_trunc, casting timestamptz to date, and comparing against a parameter that is not a constant.

Operate it

Create next month’s partition before the month starts, either with a scheduled job or pg_partman. Keep a default partition only as a safety net, because it must be scanned when you attach a new partition and can hide rows that belong elsewhere. Alert on default-partition row count; a growing default means the automation failed.