Part 6 of 6 · 4 min read
Exercises
Easy
Easy 1: Separate the two halves of the schema (10 minutes)
List which of this platform's tables belong to the rarely-written content hierarchy, and which belong to the write-heavy learner-activity side.
Done when: you correctly sort subjects, levels, modules into content, and learners, progress into activity.
Hint
content tables rarely change; learner-activity tables change on almost every learner action.
Easy 2: Check leftmost-column fit (10 minutes)
Does the query SELECT * FROM progress WHERE learner_id = 501 AND module_id = 42 benefit from the existing composite primary key, and why?
Done when: you confirm yes, since the query filters on both columns in the same order the composite key is defined.
Hint
the composite primary key's column order matches the query's filter order exactly, so it serves the query directly.
Easy 3: Classify the consistency need (10 minutes)
Is "a learner's progress update appearing a few seconds late on a dashboard" a strong or weak consistency requirement, and why?
Done when: you classify it as weak, since a brief delay causes no real harm to the learner or the platform.
Hint
nothing breaks and no harm results from a few seconds of staleness on a progress dashboard.
Medium
Medium 1: Add a new content table (20 minutes)
Design a bookmarks table letting a learner bookmark a specific module for later, and decide its primary key.
Done when: your table has foreign keys to learners and modules, with a composite primary key on both, mirroring the progress table's own design.
Hint
a composite primary key on (learner_id, module_id) for bookmarks prevents the same bookmark being inserted twice, exactly like progress.
Medium 2: Justify the module_id index (20 minutes)
Explain, using leftmost-column fit, exactly why CREATE INDEX idx_progress_module ON progress (module_id) is necessary even though progress already has a primary key covering module_id.
Done when: you explain that module_id is not the primary key's leftmost column, so a query filtering on module_id alone cannot use the primary key efficiently.
Hint
a composite key only serves queries that filter starting from its leftmost column; module_id alone is the second column, not the first.
Medium 3: Apply the framework to a new feature (20 minutes)
A new feature lets learners leave comments on a module. Apply the four-question framework, consistency, scale, pattern, team cost, to decide where this feature's data should live.
Done when: you address all four questions and reach a reasoned conclusion, such as staying on the same relational database given the feature's likely modest scale.
Hint
weigh whether comments need strong consistency (probably yes, for display correctness), whether scale is large (probably not, yet), and whether a new system is justified (probably not).
Hard
Hard 1: Design the sharded progress table (25 minutes)
Sketch how the progress table's rows would be distributed if sharded by learner_id, and explain why nearly every query against it still works well after sharding.
Done when: your sketch shows rows for the same learner on the same shard, and your explanation notes that almost every real query filters by one learner at a time.
Hint
grouping all of one learner's rows onto the same shard is exactly what lets nearly every learner-scoped query stay confined to a single shard.
Hard 2: Fix the broken query after sharding (25 minutes)
After sharding progress by learner_id, the module-completion-count query from part 2 no longer works as a single-shard lookup. Propose how to answer it correctly across shards.
Done when: you propose a scatter-gather approach, querying every shard for its own partial count and summing the results, and note the added latency this accepts compared to the single-instance version.
Hint
a scatter-gather query asks every shard for its own count for that module and adds the partial results together.
Hard 3: Decide the triggering threshold (25 minutes)
State a concrete, measurable threshold, in terms of write throughput or data volume, that should trigger actually implementing the progress sharding plan, rather than doing it preemptively.
Done when: you name a specific measurable signal, such as a sustained write rate or table size approaching what a single instance comfortably handles, rather than a vague "if it gets big."
Hint
pick a number you could actually measure and alert on, such as a specific writes-per-second figure or a table-size figure approaching a known single-instance limit.