Part 9 · 1 chapters · ~8 min

Partitioning and Large Data

Declarative range, list and hash partitioning, plan-time and run-time pruning, partition-wise joins and aggregates, attaching and detaching without downtime, inheritance-based partitioning as legacy, and TimescaleDB hypertables as an extension case study.

Declarative partitioning and pruning

code

CREATE TABLE transfers (id bigint, account_id bigint, amount_kobo bigint, created_at timestamptz NOT NULL)
  PARTITION BY RANGE (created_at);
CREATE TABLE transfers_2026_10 PARTITION OF transfers FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');

-- list and hash
CREATE TABLE accounts_by_country (...) PARTITION BY LIST (country);
CREATE TABLE sessions (...) PARTITION BY HASH (user_id);
CREATE TABLE sessions_0 PARTITION OF sessions FOR VALUES WITH (MODULUS 8, REMAINDER 0);

-- zero-downtime attach: create, load, add a matching CHECK constraint first so attach skips validation
CREATE TABLE transfers_2026_11 (LIKE transfers INCLUDING ALL);
ALTER TABLE transfers_2026_11 ADD CONSTRAINT b CHECK (created_at >= '2026-11-01' AND created_at < '2026-12-01');
ALTER TABLE transfers ATTACH PARTITION transfers_2026_11 FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');
ALTER TABLE transfers DETACH PARTITION transfers_2026_08 CONCURRENTLY;

partitioning buys	it does not buy
cheap retention (drop partitions), smaller indexes per partition, vacuum per partition	faster point lookups by primary key (each partition has its own index)
pruning for queries filtered by the key	help for queries that do not filter by the key (they scan every partition)
partition-wise joins and aggregates (`enable_partitionwise_join`)	global unique constraints unless they include the partition key

Legacy inheritance partitioning (child tables plus CHECK constraints and triggers) predates 10; you meet it in old systems. TimescaleDB hypertables automate time partitioning into chunks, add compression to columnar form and continuous aggregates, all as an extension.

DECLARATIVE PARTITIONING AND PRUNING

a parent table that routes rows to partitions, and a planner that skips the ones it does not need

swipe the figure sideways, or tap expand for full screen

1/5

routing

Inserts into the parent are routed to the partition whose bounds contain the key. The parent holds no data itself.

rows route to the matching partitionthe parent stores nothing