SQL vs NoSQL: How to Choose in a System Design Interview

"SQL or NoSQL?" comes up in almost every system design interview, and the weak answer is a memorised list of pros and cons. The strong answer derives the database from the access pattern, knows that "NoSQL" is four quite different things wearing one label, and is honest that the dichotomy has narrowed a lot since the term was coined.

This post covers what actually differs (the data model, not the acronym), where the scaling ceiling really sits, the four shapes of NoSQL and when each earns its place, what you give up, and how to answer the question out loud.

The real difference is the shape of the data

Start here, because ACID and BASE are downstream of it. A relational database stores each fact once and reconstructs the answer at read time with a join. A document store precomputes the answer and stores it as one object.

The same data, two shapes. Everything else follows from this. Normalised storage makes writes cheap and reads do work; denormalised storage makes reads cheap and pushes the work onto writes. Pick based on which side of that ratio your system actually lives on.

That trade is the whole argument in miniature. A read-heavy profile page that always wants "user plus their recent orders" is well served by the document. A system where a user's email appears in forty places and changes occasionally is well served by the join.

It also explains the failure mode nobody mentions: denormalised data can disagree with itself. If the same email is embedded in 200 order documents and the fan-out write fails halfway, you now have two truths and no way to tell which is correct. Relational schemas make that structurally impossible, which is worth more than it sounds.

ACID and BASE, without the hand-waving

ACID — atomicity, consistency, isolation, durability — is a guarantee about transactions. Either every write in the transaction lands or none does; concurrent transactions don't see each other's half-finished work; once committed, it survives a crash.

BASE — basically available, soft state, eventual consistency — is the opposite posture: stay up and fast, let replicas disagree briefly, converge later.

One clarification worth making in an interview, because it catches people out: the C in ACID is not the C in CAP. ACID's C means "the database's own invariants and constraints hold" — foreign keys, uniqueness, check constraints. CAP's C means linearizability, that every read sees the latest write across nodes. They are different properties with the same letter. If you want the full version of that argument, it's in the CAP theorem explained.

The practical question is never "ACID or BASE" in the abstract. It is: for this piece of data, what does a stale or partially-applied read actually cost? A follower count that's five seconds behind costs nothing. An account balance that's five seconds behind costs money.

Where the scaling ceiling actually is

The usual claim is "SQL scales vertically, NoSQL scales horizontally." That is roughly true and almost useless, because it skips the part that matters: which operation hits the ceiling first.

Read replicas don't raise the write ceiling. This is the distinction that separates a real answer from a rehearsed one. Adding replicas is easy and scales reads; the moment writes are the constraint, you are sharding, and sharding is where relational databases get genuinely harder — not because of the storage, but because joins and transactions stop being free.

Two things follow that are worth saying out loud in an interview.

First, the ceiling is much higher than people assume. A well-provisioned Postgres instance on modern hardware handles thousands of writes per second and tens of thousands of reads per second before you need to think hard. Work that out against your estimated load before declaring you need to shard — the back-of-the-envelope estimation usually shows you don't. A system doing 100 writes per second is three orders of magnitude away from the problem it is being architected around.

Second, NoSQL stores didn't remove that difficulty, they relocated it. Cassandra partitions your data automatically because it refuses to offer cross-partition joins and multi-key transactions in the first place. You are not getting sharding for free; you are getting it in exchange for capabilities you must now rebuild in application code, without the database's help. Partitioning itself typically leans on consistent hashing, which is worth being able to explain when asked how the router knows where a key lives.

The four shapes of NoSQL

Saying "I'd use NoSQL" tells an interviewer almost nothing. Saying "a wide-column store, because the access pattern is write-heavy append-and-scan per user" tells them you have actually thought about it. The four families have genuinely different data layouts:

Four different databases wearing one label. The layout determines the access pattern each is good at, and they are not interchangeable. Naming the family — and why its layout matches your reads — is the part interviewers are actually scoring.

Where each earns its place:

Family	Good at	Bad at
Key-value	Point lookups by a known key at very low latency — sessions, caches, feature flags	Anything requiring a query over the value's contents
Document	Entities read as a whole, with fields that vary — catalogues, profiles, CMS content	Cross-document joins; heavy duplication when entities are shared
Wide-column	Enormous write volume, append-and-scan by partition — time-series, event logs, feeds	Ad-hoc queries; anything the partition key wasn't designed for
Graph	Traversals of arbitrary depth — social graphs, recommendations, fraud rings	Bulk analytical scans; very high write throughput

The pattern to notice: each one is fast at exactly the access pattern its layout was designed for, and poor-to-impossible at everything else. That is the opposite of a relational database, which is moderately good at almost any query you think of later. You are trading generality for speed on one path.

A worked example: one product, three stores

Abstract rules are hard to apply under interview pressure, so here is the reasoning applied to a single system — a ride-hailing app, the kind of thing you might be asked to design end to end. Four subsystems, four different access patterns.

Riders, drivers, trips and payments. Reads are varied and hard to predict — support tools, admin dashboards, analytics, the app itself. A trip must be created, a driver marked busy, and a payment authorised as one atomic unit, or you get charged for a ride that was never dispatched. Writes are modest: even at a million trips a day, that is roughly 12 writes per second, and a well-provisioned instance handles that without noticing.

Relational. The transaction requirement alone settles it, and the unpredictable query shapes reinforce it.

Driver location updates. Every active driver reports its position every few seconds. A hundred thousand concurrent drivers at one update every four seconds is 25,000 writes per second — three orders of magnitude more than the trips table, and the data is worthless within a minute. Nobody ever asks an ad-hoc question of it.

Key-value, in memory. Redis with a geospatial index. The workload is the opposite of the trips workload in every dimension — huge write rate, trivial query shape, no durability requirement — which is exactly why it does not belong in the same database.

Trip history per rider. Append-only, always read as "the most recent N for this rider", never joined. It grows forever and is read rarely.

Wide-column. Partition by rider, cluster by timestamp, and the read is a single sequential scan of one partition. This is the access pattern Cassandra was built for. A relational table would also work fine for a long time — this is the one where you should say "and I'd only move it here once the volume justifies it."

Searching for a pickup address. Fuzzy, typo-tolerant, ranked text matching. No relational index does this well.

Search engine. Elasticsearch or a Postgres full-text index if the volume is small enough to avoid a second system.

The point is not that every system needs four databases — most don't, and collecting databases to look sophisticated is a recognised anti-pattern. The point is that the reasoning went requirement, then access pattern, then store, four separate times. That is what is being scored. And notice that the default answer was relational three times out of four if you're honest about scale, with specialised stores earning their place only where the access pattern genuinely diverges.

The dichotomy is weaker than it was

This is the part most interview prep material is a decade out of date on, and saying it well is a strong signal.

The original argument was roughly: relational databases can't do JSON, can't scale horizontally, and force a rigid schema. Every part of that has eroded.

Postgres has jsonb with GIN indexing, so semi-structured documents live perfectly well inside a relational database — with transactions and joins still available around them.
Horizontal scaling exists for relational stores: Citus for Postgres, Vitess for MySQL (what YouTube and Slack run on), and the distributed-SQL generation — CockroachDB, Yugabyte, TiDB, Spanner — which offer sharded storage with real cross-shard transactions.
Several NoSQL stores added transactions back: MongoDB has multi-document ACID transactions, DynamoDB has transactional writes. The market moved toward the middle from both directions.

So the modern honest answer is closer to: start relational, because it keeps the most options open, and move a specific workload to a specialised store when that workload's access pattern clearly demands it. "Postgres until it hurts, then Postgres plus something" is a defensible, current stance — and it is much more credible than reciting a 2012 comparison table.

What NoSQL does not solve

Naming the limits is what separates understanding from memorisation.

The joins do not disappear, they move into your code. Without a join, fetching a user and their orders becomes two round trips plus assembly in the application — or a denormalised copy you now have to keep in sync. You have not removed the work, you have removed the database's help with it.

Queries you didn't anticipate become expensive or impossible. A wide-column table is designed around its partition key. When the product team asks a question that key wasn't designed for, the answer is a full scan or a second copy of the data written for the new pattern.

Eventual consistency is a user-visible behaviour, not an implementation detail. "Write then immediately read your own write" is the case that breaks, and users notice it instantly — they post a comment and it isn't there. Mitigating it means read-your-writes routing or sticky sessions, which is complexity that lands in your application.

Operational maturity is not evenly distributed. Postgres and MySQL have thirty years of tooling, documentation, and engineers who have debugged them at 3am. A newer distributed store may be technically superior and still be the riskier choice for a small team.

Answering it in an interview

Derive, don't recite. The order that lands well:

State the access pattern first. "Redirects are point lookups by short code, read-heavy, no joins needed."
Name the requirement that constrains you. Transactions? Strong consistency? Write volume? Unknown future queries?
Then name a database, and the family it belongs to. "A key-value store — DynamoDB or Redis — because the whole workload is get-by-key."
Say what you'd give up, unprompted. That is the sentence that sounds senior.
Be willing to mix. Postgres for orders and users, Redis for sessions, Elasticsearch for search is a normal, mature architecture — as long as you can justify each one rather than collecting databases.

The failure modes interviewers are watching for: reaching for NoSQL "because scale" with no number attached; treating NoSQL as a single thing; forgetting that money and inventory need transactions; and picking the database before describing how the data is read.

If you want to be pushed on exactly that — "why that one, and what did it cost you?" — you can run a full system design mock by voice on Whitepad and find out how the reasoning holds up out loud.