Skip to content

Lab 7 — Solutions: Range Partitioning

Tasks 1–2 — range placement

Partitions and bounds:

 vehicle_telematics_p2026_11_14 | FROM ('2026-11-14') TO ('2026-11-15')
 vehicle_telematics_p2026_11_15 | FROM ('2026-11-15') TO ('2026-11-16')
 vehicle_telematics_p2026_11_16 | FROM ('2026-11-16') TO (MAXVALUE)

Rows per partition:

 vehicle_telematics_p2026_11_14 |  1000
 vehicle_telematics_p2026_11_15 |  1000
 vehicle_telematics_p2026_11_16 | 20000
  • All 20,000 new pings landed in p2026_11_16 — the "current" partition. The two older partitions sit nearly idle for writes.
  • The one-day query plan scans exactly one partition (Seq Scan on vehicle_telematics_p2026_11_14) — partition pruning discards the rest without reading them.

Interpretation: range placement makes time-windowed reads extremely efficient (read only the shards whose ranges overlap the window) but concentrates all writes on the single shard holding "now" — the hot-shard problem. Hash placement is the mirror image: perfectly balanced writes, but a time-range read must touch every shard.

Task 3 — physical children and pruning

tableoid::regclass identifies the actual child storing each returned row. Expected counts from Task 1's seed are 1,000, 1,000, and 20,000; their sum is 22,000, equal to the parent query. No second copy of these rows is stored in the partitioned parent.

The midnight probes identify the Nov 14, Nov 15, and Nov 16 partitions respectively. Nov 15 midnight is excluded from the Nov 14 partition and included in the Nov 15 partition.

These are local PostgreSQL children on the coordinator, described by pg_inherits. They are not Citus shard placements. The fleet_id = 1 plan retains all three children because that predicate supplies no bound on recorded_at. The exact scan nodes depend on indexes and planner decisions; the important observation is which child relations remain eligible.

A time predicate can prune children even when each retained child uses a sequential scan. Conversely, a fleet index can speed access inside each child without allowing time-based pruning. Shard routing, partition pruning, and index access are three distinct decisions.

Task 4 — retention and future bounds

The candidate is vehicle_telematics_p2026_11_14: its upper bound is Nov 15 and it contains 1,000 rows. Detaching it would leave 21,000 rows visible through the parent, while preserving the detached table as a standalone archive. Dropping the child removes its data; neither operation needs a row-by-row delete when the whole interval expires. Real operations still need backup, lock, and retention planning. No detach or drop is performed in this lab — Lab 8 performs the distributed version.

The Nov 16 partition extends to MAXVALUE, so a new Nov 17 range would overlap it and be rejected. Production daily rotation uses bounded future partitions, or carefully reorganizes the catch-all and its rows before adding new ranges.

A native partitioned-parent primary key must include recorded_at so its uniqueness can be enforced within partitions. If the table is also distributed by fleet_id (Lab 8), Citus's distribution-key requirement stacks on top: the PK must include both fleet_id and recorded_at — e.g. (fleet_id, recorded_at, ping_id). That still does not establish global uniqueness of ping_id alone.

Task 5 — strategy for weekly reports

Answer: Range placement on recorded_at. The weekly report reads one contiguous time window, so range shards let the planner touch only the partitions covering that week instead of fanning out to every shard.

Trade-off: every new insert lands in the "current" partition, so writes concentrate on one hot shard while older shards idle — the classic range-sharding write hotspot. (Mitigations in real systems: rotate and retire old partitions, sub-shard the hot range, or accept it when read latency matters more than write balance. Lab 8 shows the combined answer: hash by fleet_id for write spread plus range partitions for time pruning.)