PostgreSQL Table Partitioning Tutorial
On this page
The 90-day retention job that never finishes
An events table grows by 20 million rows a day, and you only keep 90 days. Every night a job runs:
DELETE FROM events WHERE occurred_at < now() - INTERVAL '90 days';It deletes 20 million rows one at a time. It writes WAL for each row, leaves 20 million dead tuples for vacuum (Day 24), and takes longer every week. Meanwhile, queries for "today's events" walk an index over billions of rows.
Partitioning fixes both problems. One logical table is split into physical pieces, called partitions, for example one per month. Applications still query events as one table. But removing a month becomes dropping a table, and a query for today only touches the current partition.
Range partitioning by month
CREATE TABLE events (
event_id bigint GENERATED ALWAYS AS IDENTITY,
occurred_at timestamptz NOT NULL,
kind text NOT NULL,
payload jsonb,
PRIMARY KEY (event_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');
CREATE TABLE events_default PARTITION OF events DEFAULT;Three details matter:
- Ranges include the start and exclude the end, so
'2026-10-01'belongs to October, not September. - The primary key includes
occurred_at. PostgreSQL checks uniqueness inside each partition, so a unique key must contain the partition key. A plainPRIMARY KEY (event_id)is rejected. - The DEFAULT partition catches rows that fit no other partition. Without it, inserting an event dated November fails until someone creates the November partition.
Inserts go to the right partition automatically:
INSERT INTO events (occurred_at, kind) VALUES ('2026-10-08 09:00', 'signup');
SELECT tableoid::regclass AS partition, kind FROM events;
-- events_2026_10 | signupDropping old partitions for instant retention
Removing September is now a catalog change, not 600 million row deletions:
ALTER TABLE events DETACH PARTITION events_2026_09;
DROP TABLE events_2026_09;There's no row-by-row WAL and nothing left for vacuum. If you need to keep the data, archive the detached table with pg_dump before dropping it.
A plain DETACH briefly takes a strong lock on events, so it waits for running queries and blocks new ones for a moment. PostgreSQL 14 added DETACH PARTITION ... CONCURRENTLY, which avoids that, but it can't be used while the table has a DEFAULT partition. That's one reason some teams skip the DEFAULT partition and alert on missing partitions instead.
New partitions need to exist before their month starts. Do this with a scheduled job, or with the pg_partman extension, which creates future partitions and drops old ones according to a retention policy.
Partition pruning
When a query filters on the partition key, the planner leaves out partitions that can't contain matching rows. This is called partition pruning:
EXPLAIN (COSTS OFF)
SELECT count(*) FROM events WHERE occurred_at >= '2026-10-05' AND occurred_at < '2026-10-06';
-- Aggregate
-- -> Seq Scan on events_2026_10 eventsOnly October is read. Remove the filter on occurred_at, and the plan lists every partition under an Append node. That's why partitioning only helps when most queries filter on the partition key.
Sometimes the value is only known when the query runs, such as a parameter in a prepared statement. Pruning then happens during execution, and EXPLAIN ANALYZE shows it as Subplans Removed: N rather than leaving the partitions out of the plan. That still counts as pruning.
Partition indexes, LIST and HASH partitioning
Create indexes on the parent table. PostgreSQL creates a matching index on every partition, including partitions added later:
CREATE INDEX events_kind_idx ON events (kind);Each partition is also vacuumed separately, so autovacuum works through manageable pieces instead of one giant table.
Range by time is the most common strategy, but there are two others:
-- LIST: known categories, such as region
CREATE TABLE customers (
customer_id bigint GENERATED ALWAYS AS IDENTITY,
region text NOT NULL,
name text NOT NULL,
PRIMARY KEY (customer_id, region)
) PARTITION BY LIST (region);
CREATE TABLE customers_na PARTITION OF customers FOR VALUES IN ('US', 'CA');
CREATE TABLE customers_eu PARTITION OF customers FOR VALUES IN ('DE', 'FR', 'ES');
CREATE TABLE customers_other PARTITION OF customers DEFAULT;
-- HASH: spread rows evenly when there's no natural grouping
CREATE TABLE sessions (
session_id uuid PRIMARY KEY,
user_id bigint NOT NULL
) PARTITION BY HASH (session_id);
CREATE TABLE sessions_p0 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE sessions_p1 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE sessions_p2 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE sessions_p3 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 3);Hash partitioning doesn't help retention, and it only prunes for equality on the key. Its benefit is splitting a huge table into evenly sized pieces for vacuum and maintenance. A partition can itself be partitioned, for example by month and then by tenant, but each level multiplies the number of tables to manage. Add a second level only when one isn't enough.
When partitioning isn't worth it
Partitioning adds real complexity: unique keys must include the partition key, there are more objects to manage, and every query that doesn't filter on the key now plans and scans many tables. It pays off when:
- the table is very large, typically hundreds of millions of rows or more,
- most queries filter on one column, usually time or tenant, and
- old data is removed in bulk.
It's the wrong tool when the table has a few million rows (indexes do the job), when queries rarely filter on the would-be key, or when you need uniqueness on columns that aren't part of it. And it doesn't replace indexes. Each partition still needs the right ones for queries within it.
Check yourself
Uniqueness is enforced per partition, so a unique constraint must include the partition key. Use PRIMARY KEY (order_id, created_at). If order_id must be globally unique, an identity column already gives you that in practice.
Whether the query filters on the partition key in a way the planner can use, such as occurred_at >= .... A filter on another column, or on an expression wrapping the key like date(occurred_at) = ..., prevents pruning.
Nobody created the November partition, and there's no DEFAULT partition. Create partitions ahead of time with a scheduled job or pg_partman, and alert when the next month's partition is missing.
Dropping or detaching a partition removes its files in one quick operation, with no dead rows left for vacuum. A large DELETE scans and marks millions of rows, generates lots of WAL and leaves bloat behind. That retention speed-up is often the main reason to partition.
When the table is small enough to manage, when queries rarely filter on the partition key, or when there's no retention or archiving need. Partitioning adds planning overhead and constraints, such as primary keys including the partition key, so it should solve a concrete problem.