Module 08 — Capstone: Design the Dispatch Workload¶
Apply everything. No guided steps — you make the calls and defend them with evidence from the cluster.
Scenario¶
The platform adds driver management. Two new tables:
CREATE TABLE drivers (
fleet_id bigint NOT NULL,
driver_id bigint NOT NULL,
full_name text NOT NULL,
license_no text NOT NULL,
hired_on date NOT NULL DEFAULT '2020-01-01',
PRIMARY KEY (fleet_id, driver_id)
);
CREATE TABLE shifts (
fleet_id bigint NOT NULL,
shift_id bigint NOT NULL,
driver_id bigint NOT NULL,
vehicle_id bigint NOT NULL,
started_at timestamptz NOT NULL,
ended_at timestamptz,
PRIMARY KEY (fleet_id, shift_id)
);
INSERT INTO drivers (fleet_id, driver_id, full_name, license_no)
SELECT s.fleet_id, d, 'Driver ' || s.fleet_id || '-' || d,
'DL-' || s.fleet_id || '-' || d
FROM fleets s, generate_series(1,4) AS d; -- 40,000 drivers
INSERT INTO shifts (fleet_id, shift_id, driver_id, vehicle_id, started_at, ended_at)
SELECT s.fleet_id, h, (h % 4) + 1, (h % 4) + 1,
timestamp '2026-11-01' + ((s.fleet_id * 3 + h * 7) % 360) * interval '1 hour',
timestamp '2026-11-01' + ((s.fleet_id * 3 + h * 7) % 360) * interval '1 hour' + interval '8 hours'
FROM fleets s, generate_series(1,4) AS h; -- 40,000 shifts
Run the DDL + seed on the coordinator first (they're local tables).
Dominant queries (given by product)¶
SELECT * FROM drivers WHERE fleet_id = ?— fleet roster page (highest traffic).SELECT * FROM drivers d JOIN shifts s USING (fleet_id, driver_id)WHERE d.fleet_id = ?— a fleet's driver shift history.- Weekly utilization report:
SELECT date_trunc('week', started_at) w, count(*) FROM shifts GROUP BY 1(run by dispatch analytics, off-hours).
Your tasks¶
- Choose a distribution column for
driversand justify it against the five criteria (cardinality, uniformity, query alignment, co-location, immutability). Note the PK constraint implications. - Distribute
driversandshiftswith co-location that serves query 2 optimally. - Prove it:
EXPLAIN (COSTS OFF)queries 1 and 2 forfleet_id = 1andfleet_id = 7. RecordTask Countfor each. - Analyze the trade-off: what does query 3 (no
fleet_idfilter) cost under your design? Could a different design serve it better — and what would it cost queries 1–2? - Break something: try
UPDATE drivers SET fleet_id = fleet_id + 100000WHERE fleet_id = 1. Explain the error and fix the design constraint it reveals. - Simulate growth:
SELECT citus_add_node(...)can't add a 4th worker (only three exist) — instead, reason it through: after addingworker4, how would your 8 shards redistribute, and which Citus command does it?
Self-check rubric¶
-
driversandshiftsshare acolocation_idwithfleets(or you can defend a different grouping). - Query 1 and query 2 both plan as
Task Count: 1. - You can state why query 3 fans out — and that it's acceptable here (off-hours batch) rather than a design error.
- You predicted the Task-5 error before running it.
- You named
rebalance_table_shards()for task 6 and predicted a 2/2/2/2 (or 3/3/2 → 2/2/2/2) placement split.
Stretch goals¶
- Extend Lab 7's partitioning investigation
into a design for time-partitioned
shifts. Plan it separately before changing the working capstone tables: retain fleet distribution, choose bounded monthly ranges, and compare fleet-only, time-only, and fleet-plus-time queries. Explain which layer prunes partitions and which routes worker tasks. - Identify how partitioning
shiftsonstarted_atchanges the existing(fleet_id, shift_id)primary key: a native partitioned-parent key must also include the time key. Explain the consequences for thedrivers ⋈ shiftsjoin, whose current join columns have no timestamp, and propose a compatible key/join design rather than assuming the original constraints work unchanged. - Write a retention plan for expired telematics, like Lab 8's partition drop.
How does dropping a partition across a Citus cluster differ from a row-by-row
DELETE— in I/O, locking, andVACUUMdebt? How does detaching differ from dropping? - Make
service_zonesa reference table (create_reference_table) — Lab 8 does this — and explain when that beats distributing it. What breaks if a geofence grows to millions of rows? - Design a
region-sharded alternative fordrivers. What queries get faster, which get catastrophic, and why did the fleet-side key still win?
This capstone has no single right answer — the rubric rewards designs whose evidence (EXPLAIN output, metadata queries) supports the stated trade-offs.