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 with a strategy and a key, then create children. There are three strategies, each for a different shape of key:

RANGE — each child owns a contiguous range (dates, ids). The default for time-series and append-mostly data.
LIST — each child owns an explicit set of values, e.g. FOR VALUES IN ('us', 'ca'). For categorical keys like region or status.
HASH — rows are spread across N children by a hash of the key, for even distribution when there's no natural range (e.g. sharding by user_id).

On INSERT, Postgres routes each row to the partition whose bounds contain its key — automatically. Build a range-partitioned table by year:

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. Now contrast that with a filter on a non-key column: the planner has no per-partition bounds to compare, so it can't prune and falls back to an Append scanning every partition:

sql · live postgresa non-key filter can't prune — Append over ALL partitions

EXPLAIN SELECT count(*) FROM events WHERE v = 42;   -- v isn't the partition key

EXPLAIN SELECT count(*) FROM events WHERE v = 42;   -- v isn't the partition key

⌘/ctrl + enter

That's the whole game: pruning only happens when the filter (or a join) touches the partition key.

// note · plan-time vs run-time pruning

When the filter is a constant (at >= '2024-01-01'), Postgres prunes at plan time — the pruned partitions never appear in the plan at all. When the value isn't known until execution — a query parameter, or a key coming from a join — it prunes at run time instead, which EXPLAIN (ANALYZE) reports as Subplans Removed or partitions marked (never executed). Either way the untouched partitions are never read.

// 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 (as you just saw) 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).

See the lifecycle win for real. Here DROP-ing a partition expires a whole year of data instantly — no DELETE, no dead tuples for VACUUM to chase:

sql · live postgresdropping a partition is instant, bloat-free data expiry

DROP TABLE IF EXISTS archive CASCADE;
CREATE TABLE archive (at date, v int) PARTITION BY RANGE (at);
CREATE TABLE archive_2023 PARTITION OF archive FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE archive_2024 PARTITION OF archive FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
INSERT INTO archive SELECT date '2023-06-01', g FROM generate_series(1, 1000) g;
INSERT INTO archive SELECT date '2024-06-01', g FROM generate_series(1, 1000) g;
SELECT count(*) AS before_drop FROM archive;   -- 2000

DROP TABLE archive_2023;                         -- expire all of 2023 in one step
SELECT count(*) AS after_drop FROM archive;     -- 1000 — the 2023 rows are simply gone

DROP TABLE IF EXISTS archive CASCADE;
CREATE TABLE archive (at date, v int) PARTITION BY RANGE (at);
CREATE TABLE archive_2023 PARTITION OF archive FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE archive_2024 PARTITION OF archive FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
INSERT INTO archive SELECT date '2023-06-01', g FROM generate_series(1, 1000) g;
INSERT INTO archive SELECT date '2024-06-01', g FROM generate_series(1, 1000) g;
SELECT count(*) AS before_drop FROM archive;   -- 2000

DROP TABLE archive_2023;                         -- expire all of 2023 in one step
SELECT count(*) AS after_drop FROM archive;     -- 1000 — the 2023 rows are simply gone

⌘/ctrl + enter

A billion-row DELETE WHERE at < '2024-01-01' would rewrite and bloat the table for hours; the DROP just unlinks a file.

// 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. Try it and Postgres refuses outright:

sql · live postgreseditable — run it

-- rejected: a PK on a partitioned table must include the partition key (at)
CREATE TABLE bad (id int PRIMARY KEY, at date) PARTITION BY RANGE (at);

-- rejected: a PK on a partitioned table must include the partition key (at)
CREATE TABLE bad (id int PRIMARY KEY, at date) PARTITION BY RANGE (at);

⌘/ctrl + enter

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 children. Three strategies: RANGE (contiguous ranges — time series), LIST (explicit value sets — categories), HASH (even spread — sharding). INSERT routes each row to the partition whose bounds contain its key.
02Partition pruning excludes partitions whose bounds can't match — but ONLY when the filter/join is on the partition key (a non-key filter Appends over every partition).
03Plan-time pruning removes partitions for constant filters; run-time pruning (Subplans Removed / never executed) handles parameters and join keys.
04DROP/DETACH of an old partition expires data instantly with no bloat (2000→1000 in the demo) — a huge DELETE would rewrite and bloat the table for hours.
05Each partition has its own smaller index, keeping maintenance and cache pressure per-partition; partition-wise joins/aggregates pair matching partitions (off by default).
06A unique/primary key on a partitioned table MUST include the partition key (Postgres rejects one that doesn't); over-partitioning adds planning overhead — partition genuinely 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