Chapter 05 — SQL vs NoSQL (For Designers)
Chapter 05 — SQL vs NoSQL (For Designers)
Hey everyone! Welcome to Season 2 — Data & Storage! 🙏
Every big system is really a data problem wearing a trench coat. And the first fork in the road is always: SQL or NoSQL? You may know the basics, but interviewers want the designer's answer — which one, for THIS system, and why? That's what we build today.
What we will cover:
- SQL (relational) in one picture
- NoSQL and its 4 flavors — one real product each
- Schema, joins, and ACID vs BASE
- The real decision framework
- Why "NoSQL scales better" is only half true
- What do big companies actually use?
- Polyglot persistence — using both
- Interview Questions
1. SQL — The Organized Filing Cabinet
SQL databases (MySQL, PostgreSQL) store data in tables — rows and columns, like a spreadsheet with strict rules. Every row in a table has the same columns (a fixed schema).
USERS table ORDERS table
┌────┬────────┬─────────┐ ┌────┬─────────┬────────┐
│ id │ name │ email │ │ id │ user_id │ amount │
├────┼────────┼─────────┤ ├────┼─────────┼────────┤
│ 1 │ Aisha │ a@x.com │◀──┐ │ 10 │ 1 │ ₹500 │
│ 2 │ Rohit │ r@x.com │ └────│ 11 │ 1 │ ₹200 │
└────┴────────┴─────────┘ └────┴─────────┴────────┘
a JOIN links them via user_id
Superpowers: relationships via JOINs, a strict schema that guarantees clean data, and ACID transactions (money never vanishes mid-transfer).
2. NoSQL — The Flexible Toy Box
NoSQL means "not only SQL." It drops the rigid table structure for flexibility and easier horizontal scaling. There are 4 main flavors — let's go through them one by one, with a real product and a real example for each.
2.1 Document Store — MongoDB
Each record is a JSON-like document. Different documents in the same collection can even have different fields.
// one document in a "users" collection
{ "_id": "u1", "name": "Aisha",
"orders": [500, 200],
"address": { "city": "Pune" } }
// another document, SAME collection — different shape!
{ "_id": "u2", "name": "Rohit", "bio": "loves cricket" }
👉 Great for: product catalogs, user profiles, content management — anything with nested or varying fields.
2.2 Key-Value Store — Redis / DynamoDB
The simplest model of all: a giant hashmap. Give it a key, it gives you back a value. No queries, no joins — just blazing-fast lookups.
SET "session:abc123" → "{ userId: 42, loggedIn: true }"
GET "session:abc123" → "{ userId: 42, loggedIn: true }"
Lookup time: ~1ms. That's the whole point.
👉 Great for: sessions, caches, rate limiters, leaderboards — anything looked up by a single key, constantly.
2.3 Column-Family (Wide-Column) — Cassandra
Data is stored by column instead of by row, spread across many machines. Built to absorb enormous write volume.
sensor_id | timestamp | temperature ----------|------------------|------------ sensor_1 | 2026-07-14 10:00 | 24.5°C sensor_1 | 2026-07-14 10:01 | 24.6°C sensor_2 | 2026-07-14 10:00 | 19.1°C Millions of rows like this land EVERY SECOND from IoT sensors, app logs, or activity feeds.
👉 Great for: time-series data, IoT sensor data, logs, activity feeds — huge write-heavy workloads.
2.4 Graph — Neo4j
Data is stored as nodes (things) and edges (relationships) — because for some data, the relationships ARE the point.
(Aisha) ──FRIENDS_WITH──▶ (Rohit) (Aisha) ──FOLLOWS───────▶ (Priya) (Rohit) ──WORKS_AT──────▶ (Google) Query: "friends of friends who work at Google" → a JOIN nightmare in SQL, a 3-hop walk in a graph DB.
👉 Great for: social networks, recommendation engines, fraud detection, "people you may know."
Visual Comparison
| Type | Real product | Structure | Best for |
|---|---|---|---|
| Document | MongoDB | Nested JSON docs | Catalogs, profiles, CMS |
| Key-Value | Redis, DynamoDB | key → value | Sessions, caches, leaderboards |
| Column-Family | Cassandra | Wide columns, distributed | Time-series, IoT, logs |
| Graph | Neo4j | Nodes + edges | Social graphs, recommendations |
Superpowers, across all four: flexible schema (add fields anytime), scales horizontally with ease, and fast for simple lookups by key.
3. ACID vs BASE — The Core Philosophy Clash
| ACID (usually SQL) | BASE (usually NoSQL) | |
|---|---|---|
| Motto | "Be correct, always." | "Be available, mostly." |
| Consistency | Strong — data is always valid | Eventual — catches up in a moment |
| Example need | Bank transfer 💸 | Instagram like count ❤️ |
| Trade | Correctness over raw speed/scale | Scale/availability over instant consistency |
ACID = Atomicity, Consistency, Isolation, Durability
→ "the transaction fully happens, or not at all"
BASE = Basically Available, Soft state, Eventual consistency
→ "you'll see the update... in a second or two"
We'll go deep on eventual consistency in Chapter 09 (CAP) and Chapter 14. For now: ACID = trust; BASE = scale.
4. The Decision Framework
Don't memorize "SQL good / NoSQL good." Ask these questions:
CHOOSE SQL WHEN... CHOOSE NoSQL WHEN... ───────────────── ─────────────────── ✔ Data is highly relational ✔ Data is unstructured / varies (users↔orders↔products) (flexible fields per record) ✔ You need ACID transactions ✔ You need massive write scale (finance, inventory) (feeds, logs, IoT, chat) ✔ Complex queries & JOINs ✔ Simple lookups by key ✔ Schema is stable ✔ Schema evolves fast ✔ Consistency is critical ✔ Availability > instant consistency
The "NoSQL scales better" myth
People say NoSQL "scales better." The honest truth: NoSQL was designed to scale horizontally out of the box, while scaling SQL horizontally (sharding) is harder. But modern SQL (with read replicas + sharding) scales enormously too. The real difference is ease, not a hard ceiling. Say that in an interview and you'll sound senior.
5. What Do Big Companies Actually Use?
Here's the honest answer: big companies almost never pick just one. They mix SQL and multiple NoSQL flavors depending on the job.
Instagram PostgreSQL (sharded) for core data,
Cassandra for some high-volume data,
Redis heavily for caching and feeds.
Uber MySQL/PostgreSQL for transactional data,
plus "Schemaless" (a NoSQL layer they built
on top of MySQL) and Cassandra for scale.
Netflix A famously heavy Cassandra user (they've
open-sourced tools built around it), plus
MySQL and EVCache for caching.
Amazon DynamoDB — their own key-value/document
NoSQL store — powers huge parts of
amazon.com and is also sold as an AWS service.
Discord Started message storage on Cassandra,
later migrated to ScyllaDB for more scale.
Default answer for an interview: "I'd start with a relational database for the core, transactional data — users, orders, payments — because I want ACID guarantees. Then I'd reach for NoSQL for the parts that need to scale differently: a key-value store like Redis for sessions and caching, and a column-family store like Cassandra for high-volume writes such as activity feeds or logs."
6. Polyglot Persistence — Use Both!
Real systems rarely pick just one. They use the right tool per job:
[ User accounts, payments ] → PostgreSQL (SQL, ACID) [ Product catalog ] → MongoDB (flexible docs) [ Session / cache ] → Redis (key-value, fast) [ Activity feed / logs ] → Cassandra (write-heavy) [ "People you may know" ] → Neo4j (graph) ONE company, MANY databases. This is polyglot persistence.
Interview Questions — Quick Fire!
Q: When would you choose SQL over NoSQL?
"I'd choose SQL when the data is highly relational and I need complex queries with joins, when a fixed schema and data integrity matter, and especially when I need ACID transactions — for example a banking or inventory system where correctness is non-negotiable. SQL guarantees the data is always consistent and valid."
Q: What are the types of NoSQL databases?
"Four main types: document stores like MongoDB that hold JSON-like documents; key-value stores like Redis or DynamoDB that act as a giant fast hashmap; wide-column stores like Cassandra, great for write-heavy time-series data; and graph databases like Neo4j for relationship-heavy data such as social connections."
Q: What's the difference between ACID and BASE?
"ACID — atomicity, consistency, isolation, durability — prioritizes correctness; a transaction either fully happens or not at all, and data is always valid. It's typical of SQL. BASE — basically available, soft state, eventual consistency — prioritizes availability and scale, accepting that data may be briefly stale before it converges. It's typical of NoSQL. ACID is about trust; BASE is about scale."
Q: Does NoSQL really scale better than SQL?
"It's more accurate to say NoSQL was designed for horizontal scaling from the start, so scaling out is easier. SQL can also scale massively with read replicas and sharding, but it takes more effort because of joins and strict consistency. So the difference is ease of scaling, not an absolute ceiling."
Q: Do real companies actually pick just SQL or just NoSQL?
"Almost never. Most large companies run several databases side by side — a relational database like PostgreSQL or MySQL for transactional data such as orders and payments, plus one or more NoSQL stores for specific needs, like Redis for caching and sessions, or Cassandra for high-volume writes like logs and activity feeds. Netflix and Uber are well-known examples of this mix-and-match approach, usually called polyglot persistence."
Key Points to Remember
| Concept | Key Takeaway |
|---|---|
| SQL | Tables + schema + JOINs + ACID. Best for relational, transactional data. |
| NoSQL | Flexible schema, easy horizontal scale. 4 flavors: document (MongoDB), key-value (Redis), column (Cassandra), graph (Neo4j). |
| ACID vs BASE | ACID = correctness (trust) · BASE = availability + eventual consistency (scale). |
| Decision | Relational + transactions → SQL. Flexible + huge write scale → NoSQL. |
| Real world | Instagram, Uber, Netflix, Amazon all mix SQL + multiple NoSQL stores — nobody picks just one. |
| Polyglot | Real systems use multiple databases, one per job. |
What's Next?
Chapter 06 covers the single biggest performance trick in all of system design: caching. We'll see how a scoop of RAM in the right place can turn a 50ms database read into a 1ms one — and the famous problems (stale data, cache invalidation) that come with it.
Keep designing, keep scaling! See you in the next one!
Post a Comment