postgres://internals

Partitioning

// the problem

A table with a billion rows is one giant file to scan, one enormous index to maintain, and a nightmare to delete old data from. Partitioning splits it into many smaller tables — one logical parent, many physical children — each holding a slice of the data. The payoff: the planner can skip the partitions that can't possibly match a query, and you can drop a whole month of old data by dropping a partition instead of deleting a billion rows.

Below, six range partitions each own a slice of the key. Change the WHERE clause and watch the planner prune — grey out — the partitions it doesn't need to touch.

fig.01 · partition pruning6 range partitions of 100

a250

predicate

WHERE key = 250

partitions scanned

1 of 6

pruned

0–99pruned

100–199pruned

200–299scan

300–399pruned

400–499pruned

500–599pruned

the planner skips (prunes) any partition whose range can't contain a matching row — so a query touches only the partitions it must

Declarative partitioning

You define a parent table PARTITION BY RANGE (key) (or LIST, or HASH), then create child partitions that each own a range. On INSERT, Postgres routes each row to the partition whose range contains its key — automatically.

sql · live postgresrun me first — build a partitioned table

DROP TABLE IF EXISTS events CASCADE;
CREATE TABLE events (id int, at date, v int) PARTITION BY RANGE (at);
CREATE TABLE events_2023 PARTITION OF events FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE events_2024 PARTITION OF events FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
CREATE TABLE events_2025 PARTITION OF events FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
INSERT INTO events SELECT g, date '2023-01-01' + (g % 1000), g FROM generate_series(1, 3000) g;
ANALYZE events;
-- each row landed in the partition that owns its date:
SELECT tableoid::regclass::text AS partition, count(*) AS rows
FROM events GROUP BY 1 ORDER BY 1;

DROP TABLE IF EXISTS events CASCADE;
CREATE TABLE events (id int, at date, v int) PARTITION BY RANGE (at);
CREATE TABLE events_2023 PARTITION OF events FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE events_2024 PARTITION OF events FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
CREATE TABLE events_2025 PARTITION OF events FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
INSERT INTO events SELECT g, date '2023-01-01' + (g % 1000), g FROM generate_series(1, 3000) g;
ANALYZE events;
-- each row landed in the partition that owns its date:
SELECT tableoid::regclass::text AS partition, count(*) AS rows
FROM events GROUP BY 1 ORDER BY 1;

⌘/ctrl + enter

Partition pruning

This is the headline feature. When a query filters on the partition key, the planner compares the filter to each partition's bounds and excludes the ones that can't match — before executing anything. Ask for one month and it scans one partition; the rest are never opened:

sql · live postgresone partition scanned, two pruned

EXPLAIN SELECT count(*) FROM events
WHERE at >= date '2024-01-01' AND at < date '2025-01-01';

EXPLAIN SELECT count(*) FROM events
WHERE at >= date '2024-01-01' AND at < date '2025-01-01';

⌘/ctrl + enter

The plan shows a Seq Scan on only events_2024 — the 2023 and 2025 partitions are pruned. Remove the WHERE and you'd get an Append over all three.

// gotcha · prune only works on the partition key

Pruning depends entirely on the query filtering (or joining) on the partition key. WHERE at BETWEEN … prunes; WHERE v = 42 (not the key) can't prune and scans every partition. Choose the partition key to match how you actually query — usually a time column for append-mostly data.

Why partition at all?

Pruning — queries and index scans touch only relevant partitions.
Cheap data lifecycle — expiring old data is DROP TABLE events_2023 (or DETACH), which is instant and creates no bloat — versus a giant DELETE that leaves millions of dead tuples for VACUUM (from the VACUUM lesson).
Smaller indexes — each partition has its own index, so index maintenance and bloat are per-partition, and a hot recent partition's index stays cache- friendly.
Partition-wise joins/aggregates — the planner can join or aggregate matching partitions pair-by-pair (once enable_partitionwise_join / enable_partitionwise_aggregate are turned on — they're off by default).

// why it matters · the catch: the key is in every unique constraint

A primary key or unique constraint on a partitioned table must include the partition key — Postgres can only enforce uniqueness within a partition, not globally, without it. That constraint shapes your schema, so partition on a column that's naturally part of your identity (or accept uniqueness only per partition). Over-partitioning also hurts: thousands of tiny partitions add planning overhead. Partition when a table is genuinely large (100s of GB) and queried or aged by the key.

Your turn

// challengeuses the events table from the setup cell above

Show how the rows were routed: list each partition and how many rows it holds.

SELECT tableoid::regclass::text AS partition, count(*) AS rows
FROM events
GROUP BY 1 ORDER BY 1;

SELECT tableoid::regclass::text AS partition, count(*) AS rows
FROM events
GROUP BY 1 ORDER BY 1;

⌘/ctrl + enter

// challengeuses the events table from the setup cell above

Count only the 2024 events — a query the planner can prune to a single partition.

SELECT count(*) AS rows FROM events
WHERE at >= date '2024-01-01' AND at < date '2025-01-01';

SELECT count(*) AS rows FROM events
WHERE at >= date '2024-01-01' AND at < date '2025-01-01';

⌘/ctrl + enter

// what you now understand

01Partitioning splits one logical table into many physical child tables, each owning a range/list/hash slice of the partition key.
02On INSERT, Postgres routes each row to the partition whose bounds contain its key.
03Partition pruning: the planner excludes partitions whose bounds can't match the query's filter — but only when the filter is on the partition key.
04Dropping/detaching an old partition expires data instantly with no bloat, unlike a large DELETE.
05Each partition has its own smaller index, keeping maintenance and cache pressure per-partition.
06A unique/primary key on a partitioned table must include the partition key; over-partitioning adds planning overhead — partition large tables aged/queried by the key.

// self-test

Your events table is partitioned by month on `created_at`. Which query benefits from partition pruning?

// self-test

You need to delete all data older than 2023 from a huge partitioned table. What's the cheapest way?

// go deeper

Table Partitioning (official docs) — range/list/hash, attach/detach, maintenance
Partition pruning — plan-time and run-time pruning
CREATE TABLE … PARTITION OF — defining partitions and bounds

nextReplication