PostgreSQL / Advanced: evidence and reliable operations / Lesson 15

Partitioning and data lifecycle

Use time partitions for a justified retention and query pattern. Explain routing, pruning, uniqueness and maintenance instead of treating partitioning as universal acceleration.

About 4 min readContent reviewed 2026-10-25

On this page

Lesson goals

Design nonoverlapping range partitions with complete boundaries.
Evaluate pruning, keys and lifecycle locking.

Partition when lifecycle and access align

A partitioned table presents one logical relation while storing rows in child partitions. Range, list and hash strategies address different data patterns. Time ranges can help retention and queries restricted to recent periods, but many tiny partitions add planning and operational overhead. Partitioning does not remove the need for indexes inside relevant partitions. Start from data volume, common predicates, retention rules and maintenance windows; a table being slow is not sufficient evidence that splitting it will solve the actual bottleneck.

Declare half-open monthly ranges

This isolated definition partitions synthetic scans by scan_day. The October range includes October first and excludes November first. The composite primary key includes the partition key, reflecting PostgreSQL’s restriction on a partitioned-table primary or unique constraint in this straightforward design. It does not prove scan_id alone is globally unique. The parent has no ordinary row storage of its own; rows inserted through it are routed to a matching partition. The DDL is illustrative and must never target a configured application database.

sql Illustrative SQL; expected results are reasoned, not measured.

CREATE TABLE quay_lab.scans (
 scan_id bigint NOT NULL, scan_day date NOT NULL, payload text,
 PRIMARY KEY (scan_day, scan_id)
) PARTITION BY RANGE (scan_day);
CREATE TABLE quay_lab.scans_2026_10 PARTITION OF quay_lab.scans
FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');

Range lower bounds are inclusive and upper bounds exclusive. No default partition is created here.

Missing partitions are an ingestion failure

If no partition accepts a row and there is no matching default partition, insertion fails. A default partition can catch unexpected values but creates its own operational obligation: inspect and relocate them before attaching a more specific overlapping range. Pre-create future partitions through a reviewed schedule and verify boundary dates. Updating a partition key can move a row between partitions, which interacts with concurrency and constraints. Keep a deliberate policy for null dates rather than assuming a range partition accepts them.

Make the partition predicate visible

After preparing a synthetic partition fixture, this query asks only for October dates. EXPLAIN can show whether irrelevant partitions are excluded during planning or execution. Pruning uses partition bounds and compatible predicates, not the mere existence of an index on each child. A query filtering only parcel label may still need many partitions. Avoid claiming a numeric speedup without representative measurements, and inspect whether casts or expressions obscure the intended partition-key restriction in the deployed PostgreSQL release.

sql Illustrative SQL; expected results are reasoned, not measured.

EXPLAIN SELECT scan_id FROM quay_lab.scans
WHERE scan_day >= date '2026-10-01'
  AND scan_day < date '2026-11-01';
SELECT tableoid::regclass AS physical_partition, scan_id, scan_day
FROM quay_lab.scans
WHERE scan_day = date '2026-10-01' ORDER BY scan_id;

tableoid identifies the physical relation holding a returned row. Actual rows require a populated fixture.

Retention is still an operational change

Dropping or detaching an old partition can avoid row-by-row deletion and its cleanup work, but locks, dependencies and recoverability still require review. ATTACH may validate existing data and need scans or locks; supported concurrent forms have version-specific restrictions. Autovacuum handles ordinary leaf partitions, while partitioned-parent statistics need a deliberate ANALYZE policy when relevant distributions change. Verify index coverage and privileges for new partitions. A partition schedule is part of the service lifecycle, including backup restoration and historical report availability.

Common errors and fixes

no partition of relation found for row (23514)

Why it happens: The incoming partition key falls outside defined ranges.
How to fix it: Correct invalid data or provision the legitimate future partition through review.
Prevent it next time: Test boundary dates and monitor future partition readiness.

A supposed global identifier repeats across dates

Why it happens: The declared unique key includes scan_day rather than scan_id alone.
How to fix it: Align the identity contract with supported partitioned uniqueness design.
Prevent it next time: Review cross-partition identity before migrating a primary key.

Queries still scan many partitions

Why it happens: Their predicates do not restrict the partition key usefully.
How to fix it: Inspect pruning and workload alignment before adding more partitions.
Prevent it next time: Choose partitioning from real query and retention patterns.

Check your understanding

1. Where does date 2026-11-01 belong when October is FROM 2026-10-01 TO 2026-11-01?

Show worked solution

It does not belong to October because the upper bound is exclusive. It needs a November or suitable default partition; otherwise insertion fails. Test both the final included day and the first excluded day. This half-open convention avoids overlap between adjacent monthly ranges and makes boundary ownership unambiguous.

2. A report searches all historical scans by tracking code, with no date. Does monthly partitioning guarantee a faster lookup?