System design course
Ch.2 · The building blocks·concept ·8 min read
SQL vs NoSQL
The two database families, what each gives up, and how to choose the right one for the access pattern in front of you.
The fundamental split
- SQL (relational) — data in tables with a fixed schema, related by keys, queried with SQL, with strong ACID transactions. Postgres, MySQL.
- NoSQL (non-relational) — flexible or schemaless data in one of several shapes, built to scale horizontally, usually trading some consistency and query power for it. DynamoDB, Cassandra, MongoDB, Redis.
Neither is “better.” They optimize for different things, and saying why you pick one is the signal.
Flavors of NoSQL
- Key-value (Redis, DynamoDB) — a giant hash map; blazing-fast lookups by key, minimal query ability. Great for caches, sessions, simple lookups.
- Document (MongoDB) — JSON-like documents; flexible schema, query within a document. Good for varied, self-contained records.
- Wide-column (Cassandra, HBase) — rows with dynamic columns, tuned for huge write volume and queries by partition key.
- Graph (Neo4j) — nodes and edges; built for relationship traversals (social graphs, recommendations).
Where they differ
| SQL | NoSQL | |
|---|---|---|
| Schema | Fixed, enforced | Flexible / none |
| Scaling | Vertical first; sharding is manual/hard | Horizontal by design |
| Transactions | Strong ACID, multi-row | Often limited to a single key/document |
| Queries | Rich joins, ad-hoc | Limited; designed around known access patterns |
| Consistency | Strong by default | Often tunable / eventual |
When to choose SQL
- You need transactions and strong consistency — money, orders, inventory.
- The data is relational and you’ll run varied/ad-hoc queries and joins.
- Scale is moderate, or a single primary with read replicas is enough.
Modern SQL (Postgres) scales further than people assume — don’t dismiss it.
When to choose NoSQL
- Massive scale or write volume beyond a single primary.
- A flexible/evolving schema, or naturally key-addressed data.
- You can express your needs as known access patterns and tolerate eventual consistency.
- You want a specialized shape — wide-column for time series, graph for relationships.
The mature answer: use both
Real systems are polyglot. A single design often pairs a SQL store for the transactional core (users, payments) with NoSQL for the parts that must scale (feeds, sessions, metrics) and a cache in front. The senior move in an interview isn’t picking a team — it’s saying “transactional data here in Postgres, the high-volume feed in a wide-column store, hot reads in Redis,” and justifying each.
(SQL vs NoSQL returns as a focused trade-off in Chapter 3.)