When one database isn't enough
🎯You'll be able to
Explain read replicas, partitioning and sharding, what each solves, and what each costs, so you can choose (or decline) them.
🏠The everyday picture
A busy library first adds more reading-room copies of popular books (replicas). Then it shelves old newspapers by year in separate rooms (partitions). Only when one building is truly full does it open branches in other areas, each holding part of the collection (shards), and now finding "every book by this author" means asking several branches.
📘What it really is
- First, make one database efficient: queries, indexes and caching (BE4) usually go a very long way.
- Read replicas: copies that receive changes from the main database and answer reads.
- Good for reports and read-heavy pages.
- Replication lag: a replica can be slightly behind, so "read your own write" may show old data. Send reads that must be fresh (like "show the order I just placed") to the main database.
-
Also used for failover: a replica can be promoted if the main one fails.
-
Partitioning (inside one PostgreSQL): split a huge table into parts by a key, such as one partition per month of orders. Queries that name a month read only that part; old months can be archived easily.
-
Sharding: split data across separate database servers, for example by
kitchen_id. - Each shard handles part of the load.
- Costs: queries across shards, moving data when shards are rebalanced, and transactions that span shards become hard.
-
Choose the shard key so most requests touch one shard.
-
Each step adds operating work. Adopt it for a measured need, not because large companies do it.
📝A worked example
TiffinBox grows to 400 kitchens and 50 million orders. Choices, in order:
| Problem | Option | Trade-off |
|---|---|---|
| Kitchen reports slow down checkout | Read replica for reports | Reports may be a few seconds behind |
orders table hard to maintain at 50 million rows |
Monthly partitions on placed_at |
Queries without a date range read every partition |
| Write load beyond one server | Shard by kitchen_id |
"All orders for one customer" spans shards; needs a separate index or service |
Expected outcome: a written decision for each step, with the measurement that triggered it.
🌍Where it's used
- High-traffic apps: national e-commerce, payments, social feeds.
- Analytics and reporting off replicas.
- Large time-series tables: logs, events, sensor data (IoT path).
📝Case scenario
A startup shards its database across four servers before launch "to be ready for scale". Most of their queries are "everything for one customer", but they sharded by kitchen. Every customer page now queries four servers, development slows down, and their real traffic would have fitted on one database for years. A single well-indexed PostgreSQL with a replica would have served them better.
🖱️Try it
For TiffinBox, write which single change you'd make first if (a) the kitchen dashboard is slow every evening, (b) the orders table
reaches 200 million rows, (c) checkout writes exceed what one server can take. Success: each answer names the option, the measurement
you'd check first, and one cost.
⚠️Common mistakes
- Sharding too early. Sign: complex cross-shard queries for traffic one server could handle. Fix: optimise, cache and use replicas first.
- Reading fresh data from a lagging replica. Sign: "my order disappeared" right after placing it. Fix: read-your-own-writes from the main database.
- A shard key that doesn't match access patterns. Sign: most requests touch every shard. Fix: choose the key from the most common queries.
🛠️For your project
Your capstone handover notes how TiffinBox would scale its database next, and what you'd measure before doing it.
✅Quick check
What is replication lag, and when does it matter?
Show the answer
The delay before a replica has the latest changes; it matters when a user expects to see what they just wrote.
What does sharding make harder?
Show the answer
Queries and transactions across shards, and moving data between shards.