SQL vs NoSQL
Learn to choose a data store based on access patterns and consistency needs, not brand familiarity — and to say the trade-off out loud instead of just naming a database.
Problem it solves
Every system-design answer needs a place to put data, and the interviewer wants to hear why you chose that shape of storage — not just a product name. The real decision is about the shape of your queries (joins vs single-key lookups), how strict your consistency requirements are, and whether your schema is stable or constantly evolving.
When not to use it
- Don't treat this as a binary religious choice — most real systems at scale use both (e.g. relational for orders, a key-value store for session cache).
- Don't pick NoSQL purely for "web scale" reasons if your access pattern is relational (multi-entity joins, ACID transactions) — you'll rebuild joins in application code.
- Don't pick SQL purely for familiarity if your data is a huge, sparse, rapidly-changing document with no natural relational shape.
Failure modes to call out
- Choosing NoSQL, then designing an access pattern that needs a join across two collections anyway (you've just moved the join into application code, and it isn't atomic).
- Choosing SQL for a system with unbounded, rapidly changing attributes per record, forcing constant schema migrations.
- Ignoring consistency requirements: financial balances usually need strong consistency; a social-media like count usually doesn't.
- Assuming NoSQL means "no schema at all" instead of "schema enforced by the application, not the database."
Alternatives & trade-offs
| Alternative | Trade-off |
|---|---|
| Relational (SQL): Postgres, MySQL | Strong consistency, joins, and transactions; schema changes need migrations, and horizontal write scaling is harder. |
| Document (NoSQL): MongoDB, DynamoDB | Flexible, per-document schema and scales horizontally by key; multi-document transactions and secondary-index queries exist but are more limited or costlier than in a relational engine, and defaults/consistency guarantees vary by database and by which read/write API you choose. |
| Wide-column (NoSQL): Cassandra, Bigtable | Excellent for very high write throughput and time-series data; querying by anything other than the partition key is expensive. |
| Key-value (NoSQL): Redis, DynamoDB | Fastest for single-key reads/writes; broader query patterns (secondary indexes, scans) are limited or slower and vary by product. |
Diagram
Same requirement — "look up a user's recent orders" — modeled two ways.
Scroll the diagrams sideways to see all of them.
Estimation example
A social feed reading 5,000 posts/sec but writing only 50 posts/sec is read-dominated and tolerant of eventual consistency — a document store denormalized for the read path (embed author name/avatar in the post) avoids a join on every feed render, at the cost of a background job to fix stale embedded data when a user changes their name.
Say this out loud in the interview
- "What's the access pattern — single-key lookup, or do I need to join across entities?"
- "Does this data need strong consistency (a balance) or is eventual consistency acceptable (a like count)?"
- "If I denormalize for read speed, what's my strategy for keeping the duplicated data in sync?"
Practice it in a case study
Design a URL Shortener
A single key-value lookup (short code → long URL) is the textbook case for a key-value or document store over a relational join.
Done with this lesson?
Mark it complete once you've worked through the content and practice above.
Next lesson
Caching
