SQL vs NoSQL
The Real Question
Not "which is better" — it's "what does the access pattern actually need." Every HLD problem's data-model section should justify this choice against the specific read/write pattern, not a general preference.
SQL (Relational)
- Fixed schema, tables + rows, relationships enforced via foreign keys.
- ACID transactions: Atomicity, Consistency, Isolation, Durability — strong guarantees on multi-row/multi-table writes.
- Query flexibility via
JOIN— good when you don't know all your access patterns upfront. - Scales vertically easily; horizontal scaling (sharding) is possible but not native — you give up cross-shard joins/transactions (see [[Database Replication & Sharding]]).
- Use when: data is relational (orders↔users↔products), transactions must be atomic (money movement), schema is stable.
NoSQL — Four Sub-Types (not one thing)
| Type | Model | Example | Best for |
|---|---|---|---|
| Key-Value | key → blob | DynamoDB, Redis | Pure point lookups, session storage, caching |
| Document | JSON-like nested documents | MongoDB | Semi-structured data, evolving schema, one-document-per-entity reads |
| Column-family | Wide rows, column groups | Cassandra | Write-heavy, time-series, huge scale with simple access patterns |
| Graph | Nodes + edges | Neo4j | Relationship-heavy queries (social graphs, recommendation) |
- No fixed schema (or schema-on-read) — flexible, but no DB-enforced correctness on structure.
- Horizontal scaling is the native design point — partition by key, add nodes.
- Typically eventual consistency by default (tunable in some, e.g., Cassandra's quorum reads) — see [[CAP Theorem & Consistency Models]].
- Use when: access pattern is simple and known (point lookup by key), write volume is extreme, or schema will evolve fast.
The Interview Answer Pattern
State the access pattern first, then derive the choice:
- "Point lookup by a single key, no joins, need horizontal scale" → KV/Document store (URL Shortener, session store).
- "Need atomic multi-row transactions" → SQL (payments, inventory).
- "Append-heavy writes, query by partition key + time range" → Column-family (post store in a news feed, sharded by user_id, clustered by created_at).
- "SQL works fine at this scale too — worth saying explicitly" when the scale doesn't actually demand NoSQL yet; picking NoSQL by reflex is a pattern-matching pitfall (see [[SD Interview Framework & Pitfalls]]).
Interview Drill
Q: A news feed system stores posts. Why Cassandra over Postgres here?
A: The dominant access pattern is "recent posts by one author" — a single-partition scan (partition by user_id, cluster by created_at DESC). That's exactly Cassandra's strength: no joins needed, write-heavy (every post is an insert, never updated), and horizontal scale is native. Postgres would work at moderate scale too, but you'd eventually shard manually and lose the one thing SQL bought you (joins/transactions) without needing them for this access pattern.
Cross-links
[[Database Replication & Sharding]] · [[CAP Theorem & Consistency Models]] · [[Design a URL Shortener]]