Choosing a database, indexes and replicas

Part of the free System Design course on LogicWiz, in the chapter: Databases at scale. The interactive whiteboard exercises and simulations run in the app.

Week 5: The query that ate the database

"The orders page took 40 milliseconds on my laptop and 11 seconds in production. Same query. Same code."

Meera, staring at a slow-query alert

Start with the question you will ask, not the database you like

Here is a real SwiftKart moment. A customer opens "My orders" and the app runs one query: give me every order for customer 48213, newest first.

In production the orders table has grown to about 20 million rows. Meera had tested on 2,000.

Carry one rule of thumb through this module. One database serves roughly 1,000 to 10,000 simple lookups a second. Heavy multi-table queries manage only a few hundred or fewer.

Before you pick any database, write down the access pattern: the exact questions the app will ask, and how often. Two examples from SwiftKart:

Orders and payments. "Debit the wallet and create the order, or do neither." Rows relate to each other (order, items, payment). Many different questions get asked: by customer, by date, by store. This wants a SQL database, because it gives you transactions (all-or-nothing changes) and flexible queries.
Rider location pings. 30,000 riders each send a point every 3 seconds, which is roughly 10,000 writes per second. The only question is "latest point for rider X". No joins, no multi-row transactions, but a very high write volume. This suits a NoSQL store (a key-value or wide-column one), which is built to spread simple reads and writes over many machines.

Meera's first instinct was "NoSQL is faster and scales better, so use it for everything." Not quite. NoSQL stores are fast because they give up things: joins, flexible queries, often multi-row transactions. If you need those, you will rebuild them badly in application code. Choose by access pattern. Relationships and varied questions lean SQL. A huge volume of simple lookups by key leans NoSQL. Plenty of systems use both.

What an index does, with real numbers

Back to the slow query. Without help, the database answers "orders for customer 48213" by reading every row and checking the customer id. That is a full scan. If a database can scan roughly 5 million rows a second from memory (and far fewer from disk), 20 million rows is about 4 seconds. Fine for a report, hopeless for a page a customer is waiting on.

You met the fix in Week 4: an index, a sorted lookup structure kept beside the table, like the index at the back of a textbook. It is usually a B-tree: a tree of sorted pages where each step narrows the search by a factor of a few hundred. Each page points to about 300 children (its fan-out), so three levels cover 27 million entries. Finding one customer among 20 million rows needs three index lookups plus one read of the row. That is about four reads in total, instead of 20 million row checks. Four seconds became a few milliseconds.

⚠️ Warning: An index is not free. Every insert must also update every index on the table, so a table with ten indexes has up to eleven structures to update on every insert. Index the columns your real queries filter and sort by, not every column.

Read replicas and the lag you must respect

One database machine handles both reads and writes. Customers browsing orders create far more reads than writes, so you add read replicas: extra machines that copy every change from the primary and serve only reads.

flowchart LR
  A["Service"] -->|"writes"| P["Primary"]
  P -->|"copies changes"| R1["Replica 1"]
  P -->|"copies changes"| R2["Replica 2"]
  A -->|"reads"| R1
  A -->|"reads"| R2

Say the primary is comfortable at about 2,000 simple queries a second, and 90% of them are reads. Two replicas add roughly 4,000 reads a second (a little less, because each must also replay the writes). With the primary still serving some reads, the group handles about 5,500 to 6,000 in all.

A note on the board: a component with replicas set to 2 means two instances in total, a primary plus one copy.

The catch is replication lag. Copying takes time, typically milliseconds, sometimes seconds under load. So this happens: a customer places an order (write to primary), the app redirects to "My orders" (read from a replica), and the new order is missing. They tap pay again. The fix is a rule, not hardware. Read your own writes from the primary for a short window, and for pages that must be exact. Use replicas for everything that can be a few seconds stale.

Kabir's rule of thumb for Meera: if a page shows data the user just created, send it to the primary. If it shows a catalogue, a history or a leaderboard, a replica that is two seconds behind will rarely be noticed (lag can spike, so keep read-your-own-writes on the primary). Most pages are the second kind, which is why replicas pay off so well.

Failover: when the primary dies

The primary can crash. A replica can be promoted (made the new primary) to take over, usually automatically within tens of seconds. Two dangers:

Lost writes. If copying is asynchronous, the primary may have confirmed a write the replica never received. After promotion, that write is gone. Synchronous replication closes this gap but makes every write wait for a replica, so writes get slower.
Two primaries. If the old primary was only slow, not dead, and comes back thinking it is still in charge, you have a "split brain" with two machines accepting writes. Good failover systems fence the old primary before promoting a replica, which means blocking it from making changes. They cut its access to storage or the network, or reject its writes using a higher epoch (a leadership term number that goes up at each failover). A slow or partitioned machine cannot be trusted to step down on its own.

Replicas therefore buy you two things at once: more read capacity and a standby for failure. That is why an interviewer will ask, almost every time: what happens if the single database dies?

📌 Summary: Choose a database by access pattern. Indexes turn a scan of millions of rows into a few reads but slow writes. Replicas add read capacity and a standby, but lag.

What Meera learns next

Replicas copy all the data, so they do not help when the data itself no longer fits on one machine, or the writes outgrow one primary. Next: sharding, which splits the data itself.

Check your understanding

SwiftKart stores an order, its items and its payment, and must "debit the wallet and create the order, or do neither". Support also looks orders up by customer, date and store. Which store fits best?
"My orders" scans all 20 million rows for one customer id and takes about 4 seconds. Meera adds a B-tree index on customer_id. What changes?
Reads go to replicas. A customer places an order, is sent to "My orders", and the new order is missing, so they tap pay again. What is the right fix?
The primary stops answering and failover is about to promote a replica. The old primary might only be slow, not dead. What must happen before the replica starts taking writes?