1. Courses
  2. Databases at Scale
  3. Capstone

Capstone project for Databases at Scale

Scale and protect the Partner app's order database

Mangrove Market's order database serves the Partner app, where about 1,800 partner shops order stock wholesale, and the Mangrove app. Together they add about 1.4 million order_line rows a day, three years of history sits in one table, the morning reports slow down the 7 to 11 am ordering rush, there is one streaming replica, and nobody has ever tested a restore. Ken, the engineering manager, asks you for a short design and working SQL that keep this database fast and recoverable for the next two years. Imran, the product manager, needs to understand the trade-offs, and the team will only consider sharding if you can show with numbers that it is needed.

What you hand in

  1. A design document of 2 to 3 pages: partitioning, read routing, the sharding decision with a write-rate estimate, backups and recovery, and monitoring
  2. A SQL file that creates order and order_line as monthly range-partitioned tables with keys, indexes, a default partition and a function that creates future months, plus EXPLAIN output showing a report that prunes; run it on PostgreSQL 16 or newer (PGlite in the browser is fine)
  3. A table of the ten most important reads, each marked primary or replica with a one-line reason
  4. An RPO and RTO table for three disasters (a dead server, a bad DELETE, a lost cloud region) and a one-page restore drill checklist
  5. Daily health-check SQL with an alert threshold and an owner for each query

Check your work

Tick each item when your project meets it. Be strict: this is the rubric the reviewer uses.

  • The partition key is the column most queries filter by, and it is part of every primary key and unique constraint
  • Your EXPLAIN output shows a date-filtered report reading only the partitions it needs
  • Old months leave by DETACH and DROP, and future months are created ahead of time with a lock timeout
  • Reads right after a user's own write, reads that decide a write, and auth reads stay on the primary
  • The sharding decision rests on a write-rate estimate and lists the cheaper steps you take first
  • Each disaster has its own RPO and RTO, and the replica is never counted as a backup
  • Every health-check query has a threshold, says who gets alerted, and covers long transactions, dead rows and transaction ID age

0 of 7 checked

Model answer

The model answer opens once your capstone passes the review, so you can compare your work with an outline of a strong submission.

Hand in your capstone

Your links are checked automatically, then the work is scored against the rubric above, item by item, with comments. A pass counts toward the course certificate and shows on your profile.

Loading…