Franck PachotDatabase Developer Advocate
All minibooks
Database field guide · 16
Pruning, placement, and global constraints
Design range, list, and hash boundaries that improve lifecycle and locality without sacrificing SQL semantics.
Build the mental model before choosing the mechanism.
This minibook was AI-generated from Franck Pachot's archived blog posts. Links to the original articles are included for source context and verification.01
Partitioning is valuable when a boundary supports pruning, lifecycle operations, locality, or independent maintenance. Merely splitting a table does not reduce the work of an unprunable query.
02
Static pruning uses known values at planning time; runtime pruning uses parameters or join values during execution. Expressions and implicit casts can hide the boundary.
03
Range partitions preserve locality and permit detach-by-time operations. Hash distributes keys broadly but gives up adjacent-key locality.
04
Uniqueness and foreign keys become harder when the constrained key omits the partition key. Engines use global indexes, distributed transactions, or restrictions to preserve semantics.
05
A distributed shard is also a replication and balancing unit. Splitting and movement change physical placement while SQL identity should remain stable.
06
Top-N, aggregation, and joins across partitions require a global merge unless predicates prove only one partition matters.
07
Concrete mechanics, diagnostic evidence, and executable patterns to carry into a real system.
A default partition catches unexpected values but can hide routing defects. Use half-open boundaries and automate creation before the boundary arrives.
CREATE TABLE event (...) PARTITION BY RANGE (event_time);
CREATE TABLE event_2026_08 PARTITION OF event
FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');EXPLAIN must show which children are scanned and which are removed. Parameterized statements may prune at initialization or execution rather than planning.
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM event
WHERE event_time >= $1 AND event_time < $2;
-- Inspect Subplans Removed and loops for every partition.PostgreSQL requires a partitioned unique constraint to include all partition key columns because no global index arbitrates duplicate keys across children.
ALTER TABLE reservation ADD CONSTRAINT reservation_uk
UNIQUE (tenant_id, reservation_id);
-- If reservation_id alone is globally unique, enforce it elsewhere or redesign.YugabyteDB hash keys distribute writes while following range columns retain order within a tablet keyspace. Explain distribution as part of schema review.
CREATE TABLE reading (
device_id bigint, observed_at timestamptz, value numeric,
PRIMARY KEY ((device_id) HASH, observed_at DESC)
) SPLIT INTO 24 TABLETS;08
Optional deep dives with the complete experiments and product-version context behind this guide.