Practice: Debugging & Design
Realistic production symptoms and schema design exercises. For debugging challenges, choose what to investigate; each area reveals a finding, and some are red herrings. Find the root cause, then compare your fix.
Debugging challenges
Query PlansBeginner
The orders page went from 20 ms to 4 seconds
A customer’s order list loaded instantly at launch and now takes ~4 s.
IndexesIntermediate
The index exists but the planner ignores it
There is a composite index that "covers" the query, but EXPLAIN shows a Seq Scan.
Query PlansAdvanced
A good plan turned bad overnight
A report that ran in 200 ms now takes 30 s, starting after a bulk data load.
Query PlansBeginner
The list endpoint is slow but every query is fast
GET /users takes 900 ms to return 100 users.
TransactionsIntermediate
Money left one account and never arrived at the other
A support ticket: a transfer debited the sender but never credited the recipient.
ConcurrencyAdvanced
Two users booked the same seat
Occasionally two customers are assigned the same seat, despite a "check if taken, then book" flow.
ConcurrencyAdvanced
Checkout throws "deadlock detected" under load
During peak traffic, some checkouts fail with SQLSTATE 40P01 deadlock detected.
ConcurrencyAdvanced
A table keeps growing though its row count is flat
A frequently-updated table is 40 GB on disk but holds only ~2 million live rows.
PostgreSQLIntermediate
"remaining connection slots are reserved" during a traffic spike
Under a traffic spike, requests fail with "FATAL: sorry, too many clients already".
DistributedBeginner
Users see their profile revert right after saving
After saving a profile change, an immediate page refresh shows the old value.
CachingAdvanced
Every deploy briefly takes the database down
A few seconds after each deploy, database CPU spikes to 100% and queries queue.
CachingAdvanced
One Redis node is at 100% while the others idle
A sharded Redis cluster has one node pinned at 100% CPU; the rest are near idle.
NormalizationIntermediate
The follower count is visibly wrong
A profile shows 1,240 followers but listing the followers returns 1,207.
Design challenges
Requirements and access patterns in, tables and indexes out.
DesignBeginner
Design an E-Commerce Database
A shop with a catalogue, carts, orders and payments. Design the schema from the access patterns, decide what to normalise and what to duplicate on purpose, and name the indexes each pattern needs.
DesignAdvanced
Design Instagram-Like Storage
Users, posts, follows, likes, comments and a home feed at social-network scale. The interesting decisions are all about read amplification: how to serve a feed and a like count without recomputing them on every view.