Model ServiceHub history with range, list, and hash partitioning; observe tuple routing, bounds, DEFAULT behavior, and partition-key updates; and justify partitioning against an unpartitioned baseline.
Range, List, Hash Partitioning, Partition Bounds, and Data Modeling Decisions
Model ServiceHub history with range, list, and hash partitioning; observe tuple routing, bounds, DEFAULT behavior, and partition-key updates; and justify partitioning against an unpartitioned baseline.
Learning outcomes
ServiceHub has accumulated work-order history that is queried by recent date ranges and retired by month. That workload has a natural lifecycle boundary: old months can be detached and archived as units. Declarative partitioning can make that lifecycle cheap, but only if its routing key, query predicates, uniqueness model, and maintenance cadence align. A large row count by itself is not enough justification.
Distinguish a partitioned parent from its storage-owning leaf partitions.
Create RANGE, LIST, HASH, and DEFAULT partitions and inspect their catalog bounds.
Observe INSERT routing and partition-key UPDATE row movement with tableoid.
Contrast a partitioned history table with an unpartitioned baseline on the same logical data.
Use lifecycle, pruning, index size, and maintenance evidence—not row count folklore—to justify partitioning.
A declaratively partitioned table is a routing and metadata object. Its rows physically live in child partitions. PostgreSQL routes a row using the partition key and enforces non-overlapping bounds. Partitioning is data placement; pruning is a separate query-optimization decision.
1. Build an unpartitioned baseline and a monthly RANGE hierarchy
The lab uses 12,000 deterministic work orders across June–August 2026. The unpartitioned and partitioned tables contain the same logical columns and rows so we can compare behavior without silently changing the workload.
DROP TABLE IF EXISTS app.ch16_work_order_event CASCADE;DROP TABLE IF EXISTS app.ch16_work_orders_unpartitioned CASCADE;DROP TABLE IF EXISTS app.ch16_work_orders CASCADE;DROP TABLE IF EXISTS app.ch16_region_queue CASCADE;DROP TABLE IF EXISTS app.ch16_customer_bucket CASCADE;CREATE TABLE app.ch16_work_orders_unpartitioned ( work_order_id bigint NOT NULL, customer_id bigint NOT NULL, region text NOT NULL, status text NOT NULL, opened_on date NOT NULL, labor_minutes integer NOT NULL DEFAULT 0 CHECK (labor_minutes >= 0), payload text NOT NULL DEFAULT '');INSERT INTO app.ch16_work_orders_unpartitionedSELECT 160000 + g, 5000 + (g % 400), (ARRAY['north','south','east','west'])[(g % 4) + 1], (ARRAY['queued','assigned','in_progress','completed'])[(g % 4) + 1], DATE '2026-06-01' + (g % 92), (g * 7) % 240, repeat(chr(65 + (g % 26)), 40)FROM generate_series(1, 12000) AS g;ANALYZE app.ch16_work_orders_unpartitioned;CREATE TABLE app.ch16_work_orders ( work_order_id bigint NOT NULL, customer_id bigint NOT NULL, region text NOT NULL, status text NOT NULL, opened_on date NOT NULL, labor_minutes integer NOT NULL DEFAULT 0 CHECK (labor_minutes >= 0), payload text NOT NULL DEFAULT '') PARTITION BY RANGE (opened_on);CREATE TABLE app.ch16_work_orders_2026_06PARTITION OF app.ch16_work_ordersFOR VALUES FROM ('2026-06-01') TO ('2026-07-01');CREATE TABLE app.ch16_work_orders_2026_07PARTITION OF app.ch16_work_ordersFOR VALUES FROM ('2026-07-01') TO ('2026-08-01');CREATE TABLE app.ch16_work_orders_2026_08PARTITION OF app.ch16_work_ordersFOR VALUES FROM ('2026-08-01') TO ('2026-09-01');CREATE TABLE app.ch16_work_orders_defaultPARTITION OF app.ch16_work_orders DEFAULT;INSERT INTO app.ch16_work_ordersSELECT * FROM app.ch16_work_orders_unpartitioned;ANALYZE app.ch16_work_orders;
RANGE bounds are lower-inclusive and upper-exclusive. A row
dated 2026-07-01 belongs to the July partition, not
June. The DEFAULT partition catches values not covered by
explicit bounds; without it, an out-of-range insert fails rather
than silently inventing a partition.
SELECT 'unpartitioned' AS source, count(*) AS rowsFROM app.ch16_work_orders_unpartitionedUNION ALLSELECT 'partitioned', count(*)FROM app.ch16_work_orders;SELECT tableoid::regclass AS physical_partition, count(*) AS rowsFROM app.ch16_work_ordersGROUP BY tableoidORDER BY physical_partition::text;
Expected total: 12,000 rows in each logical table. The partition
counts are determined by the generated dates; the important
evidence is that every parent query returns one logical set
while tableoid reveals several physical relations.
2. Inspect the tree and the actual partition bounds
SELECT pt.relid::regclass AS relation, pt.parentrelid::regclass AS parent, pt.level, pt.isleafFROM pg_partition_tree('app.ch16_work_orders'::regclass) AS ptORDER BY pt.level, relation::text;SELECT c.oid::regclass AS relation, pg_get_expr(c.relpartbound, c.oid) AS partition_boundFROM pg_class AS cWHERE c.oid IN ( SELECT relid FROM pg_partition_tree('app.ch16_work_orders'::regclass))ORDER BY relation::text;
pg_partition_tree() is the supported catalog-facing
way to enumerate the hierarchy.
pg_class.relpartbound is internal catalog
representation; use pg_get_expr to render it
instead of parsing raw node trees in application code.
3. Routing is automatic; missing coverage is an error unless DEFAULT exists
INSERT INTO app.ch16_work_orders(work_order_id, customer_id, region, status, opened_on, labor_minutes, payload)VALUES(169001, 5999, 'north', 'queued', DATE '2026-09-12', 0, 'future');SELECT work_order_id, opened_on, tableoid::regclass AS physical_partitionFROM app.ch16_work_ordersWHERE work_order_id = 169001;
The September row lands in
app.ch16_work_orders_default. That is operationally
convenient, but a busy DEFAULT partition can hide missing
partition creation. Monitor it explicitly and decide whether
unexpected rows are acceptable.
CREATE TABLE app.ch16_no_default ( event_id bigint, occurred_on date NOT NULL) PARTITION BY RANGE (occurred_on);CREATE TABLE app.ch16_no_default_augPARTITION OF app.ch16_no_defaultFOR VALUES FROM ('2026-08-01') TO ('2026-09-01');INSERT INTO app.ch16_no_default VALUES (1, DATE '2026-09-03');-- ERROR: no partition of relation "ch16_no_default" found for rowDROP TABLE app.ch16_no_default;The repair is to provision the intended September partition (or deliberately add a DEFAULT), not to bypass the parent and write directly into an arbitrary leaf.
4. Updating a partition key can move a row
Declarative tuple routing also applies to updates. If the new partition-key value no longer satisfies the original leaf's bound, PostgreSQL moves the row to the matching destination partition. Treat this as a DELETE+INSERT-like physical consequence rather than an in-place page update.
SELECT work_order_id, opened_on, tableoid::regclassFROM app.ch16_work_ordersWHERE opened_on = DATE '2026-08-15'ORDER BY work_order_idLIMIT 1;-- Substitute the returned work_order_id:UPDATE app.ch16_work_ordersSET opened_on = DATE '2026-07-15'WHERE work_order_id = <WORK_ORDER_ID> AND opened_on = DATE '2026-08-15';SELECT work_order_id, opened_on, tableoid::regclassFROM app.ch16_work_ordersWHERE work_order_id = <WORK_ORDER_ID>;
Do not use tableoid, physical leaf names, or
ctid as business identity. Partition layout is an
operational implementation detail and can change.
5. LIST partitioning fits small explicit categories
CREATE TABLE app.ch16_region_queue ( queue_id bigint NOT NULL, region text NOT NULL, payload text) PARTITION BY LIST (region);CREATE TABLE app.ch16_region_queue_northPARTITION OF app.ch16_region_queue FOR VALUES IN ('north');CREATE TABLE app.ch16_region_queue_southPARTITION OF app.ch16_region_queue FOR VALUES IN ('south');CREATE TABLE app.ch16_region_queue_otherPARTITION OF app.ch16_region_queue DEFAULT;INSERT INTO app.ch16_region_queueVALUES (1,'north','n'), (2,'south','s'), (3,'west','w');SELECT queue_id, region, tableoid::regclassFROM app.ch16_region_queueORDER BY queue_id;LIST works when the partition key has a controlled set of discrete values whose lifecycle or placement genuinely differs. It is usually a poor substitute for an ordinary index on a high-cardinality attribute.
6. HASH partitioning spreads a key across buckets
CREATE TABLE app.ch16_customer_bucket ( customer_id bigint NOT NULL, payload text) PARTITION BY HASH (customer_id);CREATE TABLE app.ch16_customer_bucket_0 PARTITION OF app.ch16_customer_bucketFOR VALUES WITH (MODULUS 4, REMAINDER 0);CREATE TABLE app.ch16_customer_bucket_1 PARTITION OF app.ch16_customer_bucketFOR VALUES WITH (MODULUS 4, REMAINDER 1);CREATE TABLE app.ch16_customer_bucket_2 PARTITION OF app.ch16_customer_bucketFOR VALUES WITH (MODULUS 4, REMAINDER 2);CREATE TABLE app.ch16_customer_bucket_3 PARTITION OF app.ch16_customer_bucketFOR VALUES WITH (MODULUS 4, REMAINDER 3);INSERT INTO app.ch16_customer_bucketSELECT g, repeat('x',20) FROM generate_series(1,1000) AS g;SELECT tableoid::regclass AS bucket, count(*)FROM app.ch16_customer_bucketGROUP BY tableoidORDER BY bucket::text;
Hash partitioning is useful when even distribution matters more
than chronological lifecycle. The remainder is based on
PostgreSQL's partition hash, not simply
customer_id % 4, so application code should not
predict the leaf itself.
7. Compare storage and plans—without manufacturing a benchmark claim
SELECT c.oid::regclass AS relation, pg_size_pretty(pg_total_relation_size(c.oid)) AS total_sizeFROM pg_class AS cWHERE c.oid IN ( 'app.ch16_work_orders_unpartitioned'::regclass, 'app.ch16_work_orders_2026_06'::regclass, 'app.ch16_work_orders_2026_07'::regclass, 'app.ch16_work_orders_2026_08'::regclass, 'app.ch16_work_orders_default'::regclass)ORDER BY relation::text;EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY ON)SELECT count(*)FROM app.ch16_work_orders_unpartitionedWHERE opened_on >= DATE '2026-08-01' AND opened_on < DATE '2026-09-01';EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY ON)SELECT count(*)FROM app.ch16_work_ordersWHERE opened_on >= DATE '2026-08-01' AND opened_on < DATE '2026-09-01';At only 12,000 rows, either layout may be fast and the planner may choose sequential scans. That is useful evidence: partitioning should not be sold as a universal speed switch. ServiceHub's stronger justification is the monthly retention/archival boundary explored in Lesson 4.
Choose a partition key that appears in real pruning predicates or lifecycle operations. Confirm that partition count, uniqueness requirements, hot-write concentration, and maintenance tooling remain manageable. If a normal table plus appropriate indexes and VACUUM already meets the service objectives, keep the simpler model.
8. Lab checkpoint
Check your understanding
- Where is the physical row storage for a declaratively partitioned parent?
- Which side of a RANGE upper bound owns the exact upper-bound value?
- What happens when an UPDATE changes the partition key across bounds?
- Why can a DEFAULT partition be both useful and dangerous?
- When is HASH partitioning a better fit than time RANGE partitioning?
Review the answers
The leaf partitions own storage; the parent is a virtual routing/metadata relation. RANGE upper bounds are exclusive. A qualifying partition-key update moves the row to another leaf. DEFAULT prevents routing failures but can conceal missed partition provisioning. HASH is useful for even key distribution when lifecycle/range pruning is not the dominant requirement.
Authoritative references
Partitioning behavior is planner-, lock-, constraint-, and version-sensitive. These PostgreSQL 18 primary sources define the mechanisms used in this lesson.