Module 7 · Performance and indexing
Sign in to spin up your own Postgres sandbox and run the queries for this lesson.
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;
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.
The payoff is on reads. Filter on the partition key and Postgres skips children that can't match — partition pruning. EXPLAIN shows exactly which children the planner kept:
EXPLAIN
SELECT count(*) FROM measurements
WHERE recorded_at >= '2024-02-01' AND recorded_at < '2024-03-01';
Only measurements_2024_02 appears in the plan — January and March were pruned away before a single row was read. Now drop the filter and the planner has to keep them all:
EXPLAIN
SELECT count(*) FROM measurements;
All three children show up. That difference is the whole point: on a table with three years of monthly partitions, a one-month query reads 1 partition instead of 36. Pruning works whenever the filter references the partition key.
Create an index on the parent and Postgres creates a matching one on every current child — and on any child you add later. One statement, all partitions covered:
CREATE INDEX ON measurements (sensor_id);
SELECT indexrelid::regclass AS index_name, indrelid::regclass AS on_table
FROM pg_index
WHERE indrelid IN ('measurements_2024_01'::regclass, 'measurements_2024_02'::regclass, 'measurements_2024_03'::regclass)
ORDER BY on_table;
Each child got its own sensor_id index automatically. Combined with pruning, a query like "sensor 3 in February" narrows to one partition and then uses that partition's index.
Here is the operational win. To retire January, you don't run a DELETE over millions of rows — you drop or detach the whole child in one metadata operation:
DROP TABLE measurements_2024_01; -- gone, no bloat, no VACUUM
ALTER TABLE measurements DETACH PARTITION measurements_2024_01; -- keep it, unlink it
DROP TABLE reclaims the space immediately; DETACH turns the child into an ordinary standalone table you can archive or move elsewhere. Both beat a bulk DELETE, which would leave dead tuples for VACUUM to clean up.
RANGE isn't the only option. When rows fall into discrete categories rather than ordered ranges, use LIST:
CREATE TABLE events (region text, payload jsonb) PARTITION BY LIST (region);
CREATE TABLE events_eu PARTITION OF events FOR VALUES IN ('de', 'fr', 'es');
CREATE TABLE events_us PARTITION OF events FOR VALUES IN ('us', 'ca');
And to spread rows evenly with no natural key — for parallelism rather than pruning by value — HASH partitioning assigns each row to one of N buckets by a hash of the key: PARTITION BY HASH (id), then children declared FOR VALUES WITH (MODULUS 4, REMAINDER 0), and so on.
For RANGE you can also add a catch-all so out-of-range rows land somewhere instead of erroring:
CREATE TABLE measurements_default PARTITION OF measurements DEFAULT;
Handy as a safety net, though rows in the DEFAULT partition can't be pruned by value — treat it as a place to notice stragglers, not a substitute for real partitions.
April data is arriving. Add a fourth monthly partition for April 2024, then insert the reading that failed earlier — this time it has a home to route into.
CREATE TABLE measurements_2024_04 PARTITION OF measurements
FOR VALUES FROM ('2024-04-01') TO ('2024-05-01');
INSERT INTO measurements (sensor_id, recorded_at, reading)
VALUES (1, '2024-04-15 09:00:00', 21.5);
Confirm the row landed in the new child, not anywhere else:
SELECT count(*) FROM measurements_2024_04;
One row — routed straight into April by its recorded_at. The parent's total is now 4,369, and the new child inherited the sensor_id index from the parent without you asking.
PARTITION BY RANGE (col) splits one logical table into physical children, each owning a slice of the key; inserts into the parent route to the matching child automatically.DEFAULT partition as a catch-all (which can't be pruned by value).EXPLAIN shows only the partitions actually scanned.DROP TABLE partition reclaims space instantly, DETACH PARTITION unlinks a child to keep — both avoid a bulk DELETE and its VACUUM cleanup.Up next: Module 8 — Programmability, starting with views.