SQL vs NoSQL
One shop stored four ways (Postgres, MongoDB, Redis Cluster, Cassandra): which questions each answers cheaply, which it refuses, and what each does with a schema change, a half-finished order, a write flood and a dead machine.
An interactive System Design lesson: 21 steps, about 35 minutes, on a live simulation in your browser.
The shop has 1,000,000 users, 10,000,000 orders and 10,000 products, and the same data lives in four stores side by side. Start with the one most teams start with: Postgres, a relational database.
Relational means normalized: each fact is stored once. A user is a row in users; each of their orders is a row in orders that points back with user_id. The "My account" page joins them: SELECT * FROM users u JOIN orders o ON o.user_id = u.id WHERE u.id = 42.
What you will learn
One shop, four stores
- Tables and a join: A relational database stores each fact once and assembles answers at query time with joins and indexes, so it can answer questions nobody planned for.
- One document: A document store keeps together what is read together. One read returns the whole aggregate, as long as you ask by the key it is stored under.
- A key, and a partition: Key-value and wide-column stores answer by key: the key names the machine and the place on it. The table is designed around one question, and its name often says which.
The question nobody planned for
- All pending orders: A query that does not use the key a store is organised by becomes a scan of everything: a Seq Scan, a COLLSCAN on every shard, a full scan of every node.
- Ask Redis
- Break it: Cassandra says no: Cassandra only serves queries that name a partition. Anything else is refused until you write ALLOW FILTERING, which means "scan the whole cluster".
- Model for your queries: Relational: model the data, then query it any way you like. NoSQL: write down the queries, then model the data for each one.
- Drill: a partition key for a question
Changing the shape
- A schema change on 10 million rows
- No ALTER at all: Schemaless means the database does not enforce the schema, not that there is none. Every reader carries every version of the shape.
Half an order
- The app dies between two writes
- Break it: the other three: A relational transaction makes several writes all-or-nothing across any rows. Most NoSQL stores make one write, one document or one partition atomic, and leave the rest to you.
- Drill: two keys, one slot
More writes than one machine
- 12,000 writes a second: One primary has one write ceiling. Past it, latency jumps to the length of the queue and then requests are refused; adding readers does not move it.
- Writes spread across machines: Partitioning by key is a trade: writes and key lookups scale with machines, and every query that does not name the key has to visit all of them.
- Adding machines
When a machine dies
- Break it: one machine dies: In a partitioned store, a dead partition takes its keys with it. Whether everything else keeps working is a setting and a design choice, not a law.
- A Cassandra node dies: Replication factor sets how many copies exist; consistency level sets how many must answer. RF 3 at ONE survives two dead replicas, and may read a stale one.
Choosing
- A decision guide
Recap & playground
- Cheat sheet
- Playground