Postgres Table Partitioning: A Practical Guide

Partitioning splits one large logical table into several physical tables — partitions — while your queries keep using the parent table's name. Done for the right reasons, it makes old-data cleanup instant and keeps time-series queries fast. Done for the wrong ones, it adds complexity and slows queries down. Here's how to tell the difference, and how to do it properly.

How declarative partitioning works

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

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');

Inserts into events are routed to the right partition automatically. Queries against events read only the partitions they need.

Three strategies

Range — by time or numeric range. The common case: logs, events, metrics, orders by month.
List — by discrete values: region IN ('eu'), ('us').
Hash — spreads rows evenly by a hash of a key, e.g. account_id, into N partitions. Useful for splitting a huge table into manageable pieces without a natural range.

A default partition can catch rows that match no other partition — useful as a safety net, but rows landing there make later partition creation slower (Postgres must check the default doesn't contain rows for the new range).

Partition pruning: where the speed comes from

When a query's WHERE clause constrains the partition key, the planner skips partitions that can't contain matches:

EXPLAIN SELECT count(*) FROM events
WHERE occurred_at >= '2026-10-01' AND occurred_at < '2026-10-08';
-- scans only events_2026_10

Pruning also happens at execution time for parameters and some subqueries. But if a query doesn't filter on the partition key, it scans every partition — often slower than an unpartitioned table with a good index. (Reading Postgres EXPLAIN ANALYZE.)

The rules that surprise people

Primary keys and unique constraints must include the partition key. That's why the example uses PRIMARY KEY (id, occurred_at). There are no global unique indexes across partitions — uniqueness of id alone can't be enforced by Postgres.
Foreign keys referencing a partitioned table must reference its full primary key (including the partition key) — often awkward.
Indexes are per partition. Create an index on the parent and Postgres creates it on every partition (and future ones).
Moving a row between partitions (updating the partition key) works, but is effectively a delete plus insert.

The best reason to partition: retention

Deleting a month of old events from a 2-billion-row table with DELETE is slow, generates enormous WAL, and leaves bloat for VACUUM to deal with. With partitioning:

ALTER TABLE events DETACH PARTITION events_2025_09 CONCURRENTLY;
DROP TABLE events_2025_09;   -- or archive it first

Dropping a partition is near-instant and leaves no bloat. If your data has a lifecycle — "keep 13 months" — this alone justifies partitioning.

Other good reasons

Time-series queries that almost always target recent data benefit from pruning and smaller, hotter indexes.
Maintenance per partition: vacuum, reindex or archive one month without touching the rest.
Very large tables (hundreds of millions of rows or more) where single-table maintenance has become painful.

When partitioning hurts

Small or medium tables. Under tens of millions of rows with good indexes, partitioning usually adds overhead without benefit. Fix indexes and queries first. (Database indexes.)
Queries that don't include the partition key — they scan all partitions.
Too many partitions. Thousands of partitions increase planning time and memory. Daily partitions for ten years is 3,650 tables; monthly is usually plenty.
Wrong key. Partitioning by account_id when queries filter by time (or vice versa) gives the costs without the pruning.
Unique constraints you need on columns other than the partition key.

Creating partitions ahead of time

Inserts for a range with no partition fail (unless a default partition exists). Create partitions in advance — a scheduled job that creates next month's partition, or the pg_partman extension, which automates creation and retention. (Cron expressions explained.)

Converting an existing table

You can't ALTER TABLE ... PARTITION BY an existing table. Common approaches:

New partitioned table + backfill: create the partitioned table, copy data across in batches, dual-write during the switchover, then swap names. Most flexible; most work.
Attach the old table as a partition: create the partitioned parent, add a CHECK constraint on the existing table matching its range (validated beforehand so attaching is fast), and ATTACH PARTITION it as the "historical" partition. New data then flows into new partitions. Often the quickest path.

Either way, rehearse on a copy with production-sized data and plan for lock timeouts. (Postgres migrations on large tables.)

Partitioning vs sharding

Partitioning splits a table within one database server. Sharding splits data across servers. Partitioning doesn't add CPU, memory or write capacity; it organises data. If a single server can't keep up, look at scaling options — but most apps never need sharding.

The summary

Declarative partitioning splits a table by range, list or hash; queries use the parent name.
Speed comes from pruning — only when queries filter on the partition key.
Primary keys and unique constraints must include the partition key.
The killer feature is instant retention: detach and drop old partitions.
Don't partition small tables or by a key your queries don't use.

EasySpawn runs PostgreSQL on your server with daily backups, so Claude Code can rehearse a partitioning migration against a copy of real data and measure query plans before and after. See how it works or join the waitlist.

Postgres Table Partitioning: When It Helps, When It Hurts, and How to Do It