SQL vs NoSQL — when to choose which
A decision framework for the most common database question — driven by access patterns, consistency needs, and scale rather than fashion.
Make it a decision, not a default
Chapter 2 covered what each family is. This is how to choose under pressure. Don’t answer “NoSQL because scale” — walk a short checklist out loud and let it decide.
The questions that decide it
- What are the access patterns? Known, key-addressed lookups → NoSQL shines. Ad-hoc queries, joins, and reporting → SQL.
- Do you need multi-record transactions / strong invariants? Money, inventory, bookings → SQL/ACID. If a single key’s atomicity is enough → NoSQL is fine.
- What’s the scale and write volume? Beyond a single primary’s write capacity, or multi-region writes → NoSQL (or distributed SQL).
- How structured / stable is the schema? Rigid, relational, evolving via migrations → SQL. Flexible, varied, fast-changing documents → NoSQL.
- What consistency does the data tolerate? Must-be-fresh → SQL/strong. Eventual-is-fine → NoSQL/BASE.
A quick mapping
| Signal | Lean SQL | Lean NoSQL |
|---|---|---|
| Transactions / invariants | ✔ | |
| Rich ad-hoc queries & joins | ✔ | |
| Massive scale / write volume | ✔ | |
| Flexible / evolving schema | ✔ | |
| Known key-based access patterns | ✔ | |
| Tolerates eventual consistency | ✔ |
If most ticks land in one column, you have your answer — and your justification.
Don’t forget the modern middle
- Managed/scaled SQL (Postgres with read replicas, Vitess, distributed SQL like Spanner/CockroachDB) pushes relational far past where people assume it caps — often you don’t need to leave SQL.
- NoSQL with transactions (DynamoDB transactions, MongoDB multi-doc) softens the old “no transactions” rule.
So “we’ll outgrow SQL” is often premature. Reach for NoSQL when a specific requirement (write volume, multi-region, flexible shape) demands it.
The mature answer: polyglot persistence
The strongest response splits the data: “Orders and payments in Postgres for ACID transactions; the activity feed and session store in a wide-column / KV NoSQL store for scale; hot reads cached in Redis.” One system, multiple stores, each chosen for its data.
The interview cue
Verbalize the checklist: “This needs transactional integrity for payments and ad-hoc reporting, so the core is SQL; the high-volume event stream goes to NoSQL. I’d revisit only if write volume on the core exceeds a single primary.” That’s a decision with a reason and a trigger to change it — exactly what’s being tested.