System Design · Lesson 09

SQL vs NoSQL

I have picked the wrong database twice in my career. Once I put a reporting query on the database that took the money, and once I let a "flexible" document store drift until nobody could answer a simple question. This lesson walks one write and one read through each kind of store, so you can see what each one is actually good at before you commit to it.

Difficulty: Intermediate · Know this first: Lesson 01, Lesson 02

Run it as:

When it breaks, I check

One thing to remember

Where the time goes

Every hop charges rent in milliseconds. The bar below is a worked example for this lesson's scenario — not a measurement from a live system — but the shape is honest: in the normal case the time spreads evenly and no single hop dominates, while in the bad scenarios one hop eats almost the whole budget. Step through the lesson or switch the scenario and watch the bar change. The lesson is not that one database is faster. It is that the wrong database for the question makes one hop pay for the whole mistake.

Move the knobs, watch the budget

The budget bar above is one snapshot. These three knobs are the levers I actually reach for when a database starts hurting. Move one and watch the total move. The timings are still a teaching example, not live measurements.

Reads: 70% of traffic

Reads: 70% of traffic (the rest is writes). More reads means lighter commits and heavier reads. More writes means the opposite — the mix shifts the pain between hops, it does not create it.

Report queries per hour: 1

Every extra report query is another long scan fighting the checkouts for the same tables. At 24 per hour the report hop reaches 850 ms, the same number as the report scenario.

Documents with a mismatched field: 0%

% of documents with a mismatched field. The app has to reconcile the mismatched fields at read time, and the report has to normalize them before it can count anything.

Check yourself

Three questions. No score is saved anywhere — this is just you against the material.

Go deeper

Trade-offs I actually weigh. One store is simpler to run and simpler to reason about. Three stores means three backups, three upgrades, three things to page me. I pay that cost only when each store earns it: the relational database earns it on the money, the document store earns it on the flexible read, the key-value store earns it on the instant lookup. The moment one of them stops earning it, I consolidate. Polyglot is a tool, not a trophy. And strong consistency is not free either. A transaction that locks three tables is a queue forming in slow motion. I keep transactions small and short. If a transaction touches more than a handful of rows, I ask whether the work belongs in a queue instead.

Failure modes that do not show up in a demo. Replica lag is the first one. You write an order, you read it back from the replica a second later, and it is not there yet. The user sees an empty order history and files a ticket. The fix is not a faster replica. It is routing: read your own writes from the primary for a few seconds, then fall back to the replica. Second, deadlocks. Two transactions grab the same rows in opposite order and both wait forever. The database picks a victim and kills it. If your code does not retry a deadlocked transaction, the user gets an error for a problem that fixed itself. Third, the hot key. One key in the key-value store gets all the traffic, one node gets all the load, and the cache that was supposed to save the database becomes the bottleneck. Fourth, drift, which this lesson already showed you: the failure mode is not an error. It is a report that is wrong and nobody notices for a quarter.

What changes at 10x. Ten times the orders and the relational database still holds. What breaks first is the connection count, so I add a connection pooler and stop letting every app server open its own hundred connections. The report moves to the replica if it has not already. The document store gets a validation rule at write time, because ten times the writes means ten times the drift. Nothing about the store choice changes. The plumbing around it does.

What changes at 100x. A hundred times the orders and one relational database does not hold the writes anymore. That is when sharding comes in, and it is a real project, not a config change: pick a shard key you will never regret, because re-sharding is a migration with the site running. The document store splits too, usually by the same customer id. The report leaves the operational databases entirely and moves to a warehouse that is built to scan. The key-value store stays the same shape, just bigger. And the rule from this lesson does not change at 100x either. The money still moves in a transaction. The report still does not run on the primary.

Code — the transaction, and where each query goes. First, the checkout as one transaction. If any line fails, the whole thing rolls back, and the money never moves halfway:

BEGIN;
UPDATE inventory SET qty = qty - 1 WHERE sku = 'A101' AND qty > 0;
INSERT INTO orders (customer_id, total, status) VALUES (84219, 59.98, 'placed');
INSERT INTO payments (order_id, amount) VALUES (LAST_INSERT_ID(), 59.98);
COMMIT;
-- If any line above fails, ROLLBACK. The customer is never charged
-- for an order that does not exist.

Second, the routing rule that keeps the report off the primary. This is a config file, not a hope. Writes and fresh reads go to the primary. Order history goes to the replica. Reports go to the analytics store, and the analytics credentials cannot even connect to the primary:

database:
  write: primary.db.internal      # checkouts and payments only
  read: replica.db.internal       # order history, product pages
  report: analytics.db.internal   # finance reports, never the primary

One time it broke

What broke. A monthly finance report ran straight against the primary database, the same one that takes orders. It scanned the orders table for twenty minutes. While it ran, checkouts queued behind its locks and the site felt down, even though nothing had actually failed.

Timeline.

  • 09:02Finance kicked off the quarterly revenue report. It connected to the primary, because that was the connection string in the reporting tool.
  • 09:05Checkout latency climbed from about 40 ms to over 500 ms. App servers looked fine. CPU flat, memory flat.
  • 09:09The on-call page fired on checkout p95. I checked the app first. Wrong place. The app was healthy and waiting.
  • 09:14Database CPU pegged, lock waits climbing. One long query was holding locks on the orders table while a hundred checkouts waited behind it.
  • 09:18Found it. The finance report, scanning millions of rows on the primary, on a Tuesday morning, during peak checkout traffic.
  • 09:22Killed the report query and reran it against the read replica. Lock waits drained within a minute.
  • 09:35Checkout latency back under 60 ms. The report finished on the replica ten minutes later, with the same numbers.

What changed after. Four things, and all four are still in place. One, reports get their own credentials that cannot connect to the primary at all, so the mistake cannot be repeated by pasting the wrong connection string. Two, a five-minute statement timeout on the primary, so a runaway query dies on its own instead of waiting for a human. Three, an alert on lock waits, not just CPU, because CPU was the last thing to show the problem. Four, a rule that any scheduled query scanning more than a million rows gets reviewed before it runs. The report was correct the whole time. It was also the incident. That is the part I remember.

If you remember nothing else

Try it yourself — design the stores

The prompt. Design the data stores for a small online shop. It takes 1,000 orders a day today and expects 100,000 a day in two years. It needs orders, payments, a product catalog, customer carts, and a daily revenue report. Pick the stores. Say where each piece of data lives. Say what breaks first as it grows.

Clarifying questions to ask before you answer. How much money moves per order, and what happens if a payment lands without an order? How fresh does the daily report need to be — end of day, or within the hour? How often does the catalog change shape — new fields every month, or a fixed shape for years? What happens if a cart is lost — annoyed customer, or lost sale? Is the shop one region, or does it need to serve readers far from the database?

Rough sizing. 1,000 orders a day is about 0.01 writes a second on average, with peaks of maybe 1 a second. 100,000 a day is about 1.2 a second on average, with peaks of 10 to 20 a second. No store on the market is stressed by those numbers. That is the first thing to notice: at this size, the decision is about correctness and access patterns, not raw scale. Anyone who starts this answer with sharding is solving a problem the shop does not have yet.

Worked approach. Put orders, payments, and inventory in a relational database, wrapped in one transaction per checkout, because a payment without an order is the failure you cannot afford. Put the product catalog in a document store, one document per product, because the catalog shape changes and the product page always reads the whole product at once — but add a schema version and validation at write time, so the catalog cannot drift the way this lesson showed. Put carts and sessions in a key-value store, keyed by customer id, because the access pattern is always "give me this cart" and nothing else. Put the daily revenue report on a read replica now and a warehouse later, never on the primary. The answer is four stores, and each one earns its place by the question it answers.

Trade-offs to name. Four stores means four things to back up, upgrade, and monitor, and copies of the data have to be kept in sync — an order lives in the relational database and a copy lives in the document store, and those copies can disagree. Replica lag means a customer can place an order and not see it in their history for a second, so fresh reads go to the primary. And the document store's flexibility is a contract you have to guard yourself. If you cannot name who owns the catalog schema, pick the relational table and accept the migration.

Next in this series
Lesson 10 — Storage Engines: How Databases Write
Coming next