Partitioning

Some tables only ever grow: sensor readings, log lines, orders. Left as one giant heap, deleting last year's rows means a slow DELETE that leaves bloat behind, and every query scans the whole thing. Partitioning splits one logical table into physical children — each holding a slice of the rows — so old data can be dropped instantly and queries touch only the slices they need.

The seed already built one: a measurements table partitioned by month, with three monthly children holding 4,368 sensor readings between them.

SELECT count(*) FROM measurements;

The parent routes rows to children

measurements was created with a partitioning strategy, and each child claims a range of recorded_at:

CREATE TABLE measurements (
  id          bigint GENERATED ALWAYS AS IDENTITY,
  sensor_id   int         NOT NULL,
  recorded_at timestamptz NOT NULL,
  reading     numeric(6,2) NOT NULL,
  PRIMARY KEY (id, recorded_at)
) PARTITION BY RANGE (recorded_at);

CREATE TABLE measurements_2024_01 PARTITION OF measurements
  FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

You insert into the parent, and Postgres routes each row to the child whose range contains its recorded_at. The three children carry the rows; the parent holds none of its own. Ask each child directly:

SELECT 'jan' AS part, count(*) FROM measurements_2024_01
UNION ALL SELECT 'feb', count(*) FROM measurements_2024_02
UNION ALL SELECT 'mar', count(*) FROM measurements_2024_03;

1,488 + 1,392 + 1,488 = 4,368 — every parent row physically lives in exactly one child.

One catch of RANGE partitioning: a row whose recorded_at falls outside every child's range has nowhere to go and the INSERT errors. Try an April row (there is no April partition yet), and watch it fail:

INSERT INTO measurements (sensor_id, recorded_at, reading)
VALUES (1, '2024-04-15 09:00:00', 21.5);

no partition of relation "measurements" found for row — we'll fix that below.

Partition pruning: scan only what matters

The payoff is on reads. Filter on the partition key and Postgres skips children that can't match — . shows exactly which children the planner kept: