Table partitioning

conceptThe concept

CREATE TABLE events_p (...) PARTITION BY RANGE (at) creates a parent that stores no rows itself; partitions are tables created with PARTITION OF events_p FOR VALUES FROM (...) TO (...). LIST partitions by discrete values (region), HASH spreads rows evenly by modulus. A DEFAULT partition catches rows that fit nowhere else.

Partition pruning skips partitions that cannot match the WHERE clause, at plan time for constants and at execution time for parameters. A query without the partition key in WHERE scans every partition, which is slower than an unpartitioned table with a good index.

Indexes created on the parent are created on every partition. Primary keys and unique constraints must include the partition key. ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY removes a partition without blocking queries, and dropping a detached partition is instant, compared with a huge DELETE that bloats the table.

whyWhy it matters

Deleting 100 million old rows creates 100 million dead tuples, hours of VACUUM and a lot of WAL. Dropping last year's partition takes milliseconds. Retention is the number-one reason to partition.

Use caseReal-world scenario

The click-stream events table grows by 50 million rows a month and only 90 days are kept. Monthly range partitions make retention a DROP and keep each partition's indexes small enough to stay in memory.

Try itExamples

Run and edit these in the interactive lesson.

BEGIN;
CREATE TABLE events_p (LIKE events) PARTITION BY RANGE (at);
CREATE TABLE events_p_2025_10 PARTITION OF events_p FOR VALUES FROM ('2025-10-01') TO ('2025-11-01');
CREATE TABLE events_p_2025_11 PARTITION OF events_p FOR VALUES FROM ('2025-11-01') TO ('2025-12-01');
CREATE TABLE events_p_2025_12 PARTITION OF events_p FOR VALUES FROM ('2025-12-01') TO ('2026-01-01');
CREATE TABLE events_p_default PARTITION OF events_p DEFAULT;
INSERT INTO events_p SELECT * FROM events;
SELECT tableoid::regclass AS partition, count(*), min(at)::date, max(at)::date
FROM events_p GROUP BY 1 ORDER BY 1;
ROLLBACK;

BEGIN;
CREATE TABLE events_p (LIKE events) PARTITION BY RANGE (at);
CREATE TABLE events_p_2025_10 PARTITION OF events_p FOR VALUES FROM ('2025-10-01') TO ('2025-11-01');
CREATE TABLE events_p_2025_11 PARTITION OF events_p FOR VALUES FROM ('2025-11-01') TO ('2025-12-01');
CREATE TABLE events_p_2025_12 PARTITION OF events_p FOR VALUES FROM ('2025-12-01') TO ('2026-01-01');
CREATE TABLE events_p_default PARTITION OF events_p DEFAULT;
INSERT INTO events_p SELECT * FROM events;
ANALYZE events_p;
EXPLAIN (COSTS OFF) SELECT count(*) FROM events_p WHERE at >= '2025-11-10' AND at < '2025-11-20';
EXPLAIN (COSTS OFF) SELECT count(*) FROM events_p WHERE customer_id = 7;
ROLLBACK;

BEGIN;
CREATE TABLE events_p (LIKE events) PARTITION BY RANGE (at);
CREATE TABLE events_p_2025_10 PARTITION OF events_p FOR VALUES FROM ('2025-10-01') TO ('2025-11-01');
CREATE TABLE events_p_2025_11 PARTITION OF events_p FOR VALUES FROM ('2025-11-01') TO ('2025-12-01');
INSERT INTO events_p SELECT * FROM events WHERE at < '2025-12-01';
ALTER TABLE events_p DETACH PARTITION events_p_2025_10;
DROP TABLE events_p_2025_10;
SELECT count(*) AS remaining, min(at)::date AS oldest FROM events_p;
ROLLBACK;

BEGIN;
CREATE TABLE orders_p (id bigint, created_at timestamptz NOT NULL, total numeric) PARTITION BY RANGE (created_at);
ALTER TABLE orders_p ADD PRIMARY KEY (id);

internalsUnder the hood

Too many partitions hurt: planning time and memory grow with the number of partitions a query might touch, and every partition is a set of files. Hundreds are fine, tens of thousands are not. Tools like pg_partman automate creating future partitions and dropping old ones.

Avoid / preferDo this, not that

-- daily partitions kept for ten years: 3,650 partitions

-- monthly partitions, detached and archived after the retention period

DELETE FROM events WHERE at < '2025-01-01';

ALTER TABLE events DETACH PARTITION events_2024_12 CONCURRENTLY;
DROP TABLE events_2024_12;

PitfallsCommon mistakes

• Partitioning a table that queries rarely filter by the partition key. Every query then scans all partitions.
• Creating thousands of tiny partitions (daily for ten years, or hash into 1,024). Planning and memory costs explode.
• Forgetting to create future partitions, so inserts fail (or all land in the default partition, which then blocks adding the missing partition until its rows are moved).

LimitsWhen not to use it

Do not partition tables of a few gigabytes with no retention requirement. Pruning gains are small, and every query and unique key now has to consider the partition key.

SecuritySecurity notes

Privileges and row-level security on the partitioned parent apply to queries through the parent, but partitions are tables too and can be queried directly with their own grants. Do not grant access to individual partitions.

ChallengeTry it yourself

BEGIN;
CREATE TABLE events_p (LIKE events) PARTITION BY RANGE (at);
CREATE TABLE events_2025_10 PARTITION OF events_p FOR VALUES FROM ('2025-10-01') TO ('2025-11-01');
CREATE TABLE events_2025_11 PARTITION OF events_p FOR VALUES FROM ('2025-11-01') TO ('2025-12-01');
CREATE TABLE events_2025_12 PARTITION OF events_p FOR VALUES FROM ('2025-12-01') TO ('2026-01-01');
CREATE TABLE events_other PARTITION OF events_p DEFAULT;
INSERT INTO events_p SELECT * FROM events;
SELECT tableoid::regclass AS partition, count(*) AS rows FROM events_p GROUP BY 1 ORDER BY 1;
ROLLBACK;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• Table partitioning