"Should we use SQL or NoSQL?" is asked in almost every system design interview, and usually answered badly — with "NoSQL scales better", which is both imprecise and mostly wrong.
The honest framing is that these are different trade-offs, not different quality levels. Postgres runs enormous systems. Plenty of teams have suffered by choosing MongoDB because it sounded modern.
The real question is: what shape is your data, and what do you need guaranteed?
Diagram, as text
- What does the workload look like? leads to Queries cut across entities?
- Queries cut across entities? leads to Relational (yes, many join paths)
- Queries cut across entities? leads to Document (no, one document at a time)
- What does the workload look like? leads to Key lookup only, huge volume?
- Key lookup only, huge volume? leads to Key-value (yes)
- What does the workload look like? leads to Aggregates over few columns?
- Aggregates over few columns? leads to Columnar (yes)
Relational databases
Data lives in tables with a fixed schema. Rows relate through foreign keys, and SQL lets you combine them at query time.
Two properties define the category.
The schema is enforced
Every row must match the defined structure. A column declared NOT NULL INTEGER cannot hold text or nothing.
Beginners see this as rigidity. It is a guarantee: no code path anywhere, however careless, can insert a malformed row. As covered in the schema lesson, the database is the last line of defence for data integrity, and it never gets tired.
ACID transactions
The property that matters most in interviews, and worth being precise about:
Atomicity — a transaction happens completely or not at all. Debit one account and credit another; a crash between them leaves neither applied.
Consistency — the database moves from one valid state to another; constraints are never violated.
Isolation — concurrent transactions do not observe each other's partial work. Without it, two simultaneous withdrawals can both read the same balance and both succeed.
Durability — once committed, it survives a crash. It is on disk, not sitting in memory.
BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
COMMIT; -- both, or neitherIf money, inventory, bookings or anything with real-world consequences is involved, you want these guarantees, and reimplementing them in application code is far harder than it looks.
NoSQL: four different things
"NoSQL" is not one technology. It is four families with genuinely different uses, and conflating them is a common interview mistake.
Key-value (Redis, DynamoDB) — a giant dictionary. Extremely fast lookups by key, no querying by value. Ideal for sessions, caches, feature flags.
Document (MongoDB, Couchbase) — JSON-like documents, each potentially a different shape. Good when records vary naturally, like product catalogues where a book and a fridge share few attributes.
Wide-column (Cassandra, HBase) — rows with dynamic columns, designed for enormous write volume across many machines. Time-series data, event logs, sensor readings.
Graph (Neo4j) — nodes and edges, where relationships are the primary thing you query. Social graphs, fraud rings, recommendations. "Friends of friends who bought this" is a nightmare in SQL and natural here.
Choosing well
Ask these questions in order.
Is my data relational? If entities genuinely reference each other and you will query across those relationships, SQL is a natural fit. Forcing this into documents means either duplicating data everywhere or performing joins in application code — which is slower and buggier than letting the database do it.
Do I need transactions across records? If yes, relational databases give it directly. Some NoSQL stores now offer limited transactions, but it is not their strength.
Do I know my query patterns in advance? This is the sharpest distinction. SQL lets you ask questions you did not anticipate — a new report is a new query. Many NoSQL databases require you to model for the queries you will run, and a new access pattern can mean restructuring your data.
Is the schema genuinely variable? If records legitimately differ, documents help. If you are reaching for schemaless because you have not decided on your model, you are deferring the decision, not avoiding it — and inconsistent data will accumulate.
| Need | Reach for |
|---|---|
| Transactions, correctness, money | Relational |
| Complex or ad-hoc queries | Relational |
| Simple lookups at massive scale | Key-value |
| Variable record shapes | Document |
| Huge write volume, time-series | Wide-column |
| Relationships as the main query | Graph |
The practical default
For most systems, and for most interviews, start with a relational database.
- Your data is probably more relational than you think.
- You will want queries you have not thought of yet.
- The correctness guarantees are genuinely hard to rebuild.
- It will scale far further than the traffic you actually have.
Add specialised stores when a specific workload demands it: a cache for hot reads, a search engine for full-text, object storage for files, a time-series store for metrics.
The mistake is not choosing NoSQL. It is choosing it before you have a reason, then reimplementing joins and transactions in application code — badly.
Check your understanding
Common mistakes
Choosing on popularity. "Everyone uses X" is not a requirement.
Assuming NoSQL means no modelling. It means modelling around access patterns instead of relationships — often more upfront design, not less, because a wrong choice is harder to query around later.
Ignoring operational maturity. Relational databases have decades of tooling for backups, replication, monitoring and recovery. That matters at 3am.
Using a database as a queue. Polling a table works until it does not. Use a message queue.
One database for everything. Equally, do not use six because you can. Each is another thing to operate, back up and secure.
Practice
You are choosing storage for an events table: 500 million rows a month, written once and never updated, queried almost exclusively as 'all events for customer X between two dates'. What would you choose and how would you organise it?
Write your own answer first, on paper or in a note. Then compare.
Show a hint
Write-once, never-updated, always-range-scanned is a shape, and it suggests its own layout.
Show a strong answer
The access pattern decides everything here, and it is unusually clear: append-only, no updates, and every read is a range scan within one customer.
- Partition by time. Monthly or weekly partitions mean a query for a date range touches only the relevant partitions and skips the rest entirely. It also makes deletion free: dropping an old partition is a metadata operation, whereas deleting 500 million rows individually is an expensive, lock-heavy job that leaves the table bloated.
- Order within the partition by customer, then timestamp. Then "customer X between two dates" is a single contiguous read rather than scattered lookups — the sequential-versus-random distinction, and at this volume it dominates.
- Index sparingly. With the layout above, the primary access path needs no secondary index. Every index you add is 500 million writes a month of maintenance for a query that may not exist. Add them only when a real query demands one.
- Consider a column-oriented store if the reads are analytical. If queries aggregate over one or two fields across many rows, columnar storage reads only those fields and compresses far better. If queries fetch whole events, row storage is right.
What I would not do: put this in the same database as the transactional tables. A table growing by 6 billion rows a year has different backup, retention and scaling needs from a users table, and coupling them means the smallest table inherits the largest one's problems.
A query is slow. Walk through the order in which you would investigate, and say what each step rules in or out.
Write your own answer first, on paper or in a note. Then compare.
Show a hint
Start with what the database says it is doing, not with what you assume it is doing.
Show a strong answer
Work in this order, because each step makes the next one meaningful:
- Read the query plan. This is the only step that tells you what the database is actually doing rather than what you think it should. A sequential scan where you expected an index lookup, or a nested loop over a large set, is usually the whole answer. Rules in or out: whether the problem is the plan at all.
- Check the row counts in the plan against reality. If the planner expected 100 rows and got 2 million, the plan was chosen on bad information and the fix is statistics, not an index. Rules out: "the planner is stupid" — usually it was misinformed.
- Ask whether the query is selective. If it returns most of the table, no index will help and a scan is correct. The fix is to return less, not to index more. Rules out: the index-will-fix-it assumption.
- Check whether the data is in memory. A plan that is fine and a query that is slow only on the first run points at cold pages, not at the query. Rules in: a working-set problem, which is a capacity question rather than a query one.
- Look for lock contention. If the query is fast in isolation and slow under load, it is waiting rather than working. Rules in: concurrency, which no amount of query rewriting fixes.
The discipline is refusing to add an index before step 1. An index added on a guess is permanent write cost bought for an unproven read benefit.
Key takeaways
- SQL and NoSQL are different trade-offs, not different quality levels.
- Relational databases give you an enforced schema and ACID transactions — genuinely hard to rebuild in application code.
- NoSQL is four families — key-value, document, wide-column, graph — with different purposes; treating them as one thing is a common error.
- "NoSQL scales better" is a consequence of dropped guarantees, chiefly cross-machine joins and transactions. You traded, not gained.
- Choose by asking whether data is relational, whether you need transactions, whether query patterns are known in advance, and whether the schema is genuinely variable.
- Default to relational, then add specialised stores per workload — polyglot persistence.
Next: one database is a single point of failure and a read bottleneck. The first fix is to make copies.
Further reading
- Martin Kleppmann, Designing Data-Intensive Applications (O'Reilly, 2017), chapters 2–3 — data models and storage engines.
- Edgar F. Codd, "A Relational Model of Data for Large Shared Data Banks" (CACM, 1970) — the paper the relational model comes from.
- Jim Gray and Andreas Reuter, Transaction Processing: Concepts and Techniques (Morgan Kaufmann, 1992) — the definitive treatment of ACID.
- Michael Stonebraker and Uğur Çetintemel, "One Size Fits All: An Idea Whose Time Has Come and Gone" (ICDE 2005) — why storage choice should follow workload.
- Goetz Graefe, "Modern B-Tree Techniques" (Foundations and Trends in Databases, 2011) — how the index you keep adding actually works.