SQL vs NoSQL | System Design Interview Handbook

🗄

What Relational Actually Buys

and why "SQL doesn't scale" is mostly a myth

A relational database gives you four things that are genuinely hard to rebuild elsewhere: a schema the database enforces so bad data can't land, joins so you can combine data without duplicating it, transactions so multi-row changes are all-or-nothing, and a mature query planner that turns declarative SQL into an efficient plan.

The reflexive "SQL doesn't scale" is mostly wrong. A single well-tuned Postgres or MySQL node handles tens of thousands of writes per second and terabytes of data. Read replicas scale reads further. You reach the wall only at genuinely large write volume — and most systems in an interview never get there (Estimation).

⚠️

The honest limit is write scaling. Relational databases are hard to shard because joins and transactions across shards are expensive or impossible. So the real question is rarely "SQL or NoSQL for size" — it's "does my access pattern need joins and multi-row transactions, or can I trade them away for easy horizontal writes?"

🧬

The NoSQL Families

four models, each optimizing one access pattern

"NoSQL" is not one thing. It's four distinct data models, each purpose-built for an access pattern and each giving something up to get there.

Key-value Redis, DynamoDB

A giant hash map: get and put by key, nothing else. Fastest possible lookups, trivial to shard by key. Gives up: querying by anything but the key. Great for caches, sessions, and simple lookups.

Document MongoDB

Stores JSON-like documents, queryable by fields inside them. Flexible schema, whole entities in one record — no joins to assemble an object. Gives up: cross-document joins and multi-document transactions (mostly). Good for content and catalogs.

Wide-column Cassandra

Rows keyed by a partition key, with flexible columns per row. Built for massive write throughput and linear horizontal scaling. Gives up: ad-hoc queries — you model tables around exact query shapes. Good for time series, event logs, huge write loads.

Graph Neo4j, Neptune

Nodes and edges as first-class citizens, tuned for traversing relationships. Friends-of-friends in one hop instead of a pile of joins. Gives up: general-purpose scale and simplicity. Overkill unless traversal is the workload (Data Stores).

🎯

The Actual Decision Criteria

four questions that settle it

Ignore the brand names and ask about the workload. Four questions do most of the work.

Question	Leans SQL	Leans NoSQL
Are access patterns known up front?	No — ad-hoc queries, reporting	Yes — a few fixed, high-volume patterns
Need multi-row / multi-entity transactions?	Yes — money, orders, inventory	No — independent records
Write volume beyond one node?	Below the wall	Massive, must shard writes
Is the schema stable or fluid?	Stable, benefits from enforcement	Fluid, varies per record

💡

Schema flexibility cuts both ways. "No schema" feels freeing early, then becomes the application quietly enforcing structure the database won't — and every bug that ships bad data lands in the store forever. Treat flexibility as a real benefit only when records genuinely differ, not as an excuse to skip modeling (Tradeoffs).

✏️

Modeling Differences

normalize-then-join vs query-first

The two worlds model data in opposite directions. Relational design normalizes: store each fact once, join at read time. NoSQL design is query-first: you decide the queries first, then shape the data — often duplicated — so each query is a single cheap lookup.

  RELATIONAL — normalize, join on read       NoSQL — denormalize around the query
  ┌────────┐   ┌──────────┐                  ┌──────────────────────────────┐
  │ users  │   │  orders  │                  │  "orders by user" table       │
  │ id name│◀──│ user_id  │  JOIN at         │  user_id → [ order, order, … ]│
  └────────┘   │ total    │  read time       │  everything the query needs,  │
               └──────────┘                  │  pre-joined, duplicated       │
   one copy of each fact,                    └──────────────────────────────┘
   assembled per query                        read = one lookup; writes must
                                              update every copy

Neither is "correct" — it's the read-vs-write trade again. Normalization keeps writes clean and truth in one place, paying with joins on every read. Denormalization makes reads a single lookup, paying with duplicated data and update fan-out. In NoSQL you model around access patterns because the store can't join for you, so the duplication is the design, not a smell (Tradeoffs).

🤝

The Middle Ground

the binary is false

The SQL/NoSQL split is not a wall. Two developments erased most of it, and interviewers know both.

Postgres JSONB document features in SQL

Relational databases store and index schemaless JSON columns natively. You get flexible documents where you want them and joins, transactions, and constraints everywhere else — in one database. Often this alone removes the reason to add a document store.

NewSQL Spanner, CockroachDB

Distributed SQL: horizontal write scaling with real transactions and the relational model. It pays with cross-node coordination latency, but it dismantles the "SQL can't scale writes" premise. "Just use Spanner" is a real answer now.

ℹ️

The strong interview move is to resist the false binary. "I'd start on Postgres — it handles this scale and gives me transactions. If write volume outgrows a single node, I'd shard or move the hottest table to a wide-column store, or reach for a NewSQL engine if I need to keep transactions." That shows you know the whole spectrum, not two camps.

📋

Quick Reference

decision and families on one card

Requirement	Leans
Multi-row transactions (money, orders)	SQL
Ad-hoc queries, reporting, joins	SQL
Massive write throughput past one node	NoSQL (wide-column)
Simple key lookups, cache, sessions	NoSQL (key-value)
Flexible per-record shape, whole entities	NoSQL (document) or Postgres JSONB
Relationship traversal is the workload	Graph
Scale + transactions both required	NewSQL

Family	Optimizes	Gives up	Tool
Key-value	Fastest lookups by key	Querying by anything else	Redis, DynamoDB
Document	Whole entities, flexible schema	Joins, multi-doc transactions	MongoDB
Wide-column	Write throughput, scale	Ad-hoc queries	Cassandra
Graph	Relationship traversal	General scale, simplicity	Neo4j, Neptune

ℹ️

The one-liners to keep ready. Relational buys schema, joins, and transactions; its real limit is write scaling, not size. NoSQL trades joins and cross-record transactions for horizontal writes and query-first modeling. Default to Postgres until a specific requirement forces something else — JSONB and NewSQL mean the binary rarely holds.