SQL vs NoSQL in System Design Interviews: How to Choose

Pick the data store that matches your access patterns and consistency needs, then defend it in two sentences. In a system design interview, "SQL vs NoSQL" is not a loyalty test; it is a reasoning test. The strong answer is usually this: start with a relational database unless you have a concrete reason (write volume beyond a single primary, a pure key-value or wide-column access pattern, or a schema that genuinely changes shape per record) that pushes you elsewhere. The weak answer is naming a database because it sounds modern and then backtracking the moment the interviewer pushes on consistency or joins.

This guide gives you a decision framework, the CAP and consistency facts stated correctly (which is where most candidates lose points), a concrete menu of stores, and a script for saying it out loud.

The question behind the question

When an interviewer asks what database you would use, they are not grading trivia. They want to see that you can map a workload to storage properties. The signal they listen for is whether you tie your choice to specifics: the read/write ratio, the query shapes, the consistency the product actually needs, and the scale at which a single node stops being enough.

So before you say any product name, say the requirements out loud. "We have roughly 100 reads per write, queries are mostly by user ID, we need read-your-writes for the user's own data but eventual consistency is fine for the global feed." That one sentence earns more credit than any database name, because it shows the name is a conclusion rather than a guess.

Relational and non-relational are not opposites on a quality scale; they are different defaults for different access patterns. Relational systems optimize for flexible queries and strong transactional guarantees over normalized data. Many non-relational systems optimize for a specific access pattern (key lookups, wide-column ranges, document retrieval) and for horizontal scale, trading away ad hoc joins and, by default, some consistency.

A decision framework you can run live

Walk these four questions in order. Each one narrows the field.

1. What are the dominant access patterns? If most queries are "get this entity and its related rows by key," a key-value or document store is a clean fit. If you need ad hoc filtering, aggregation, and joins across entities, relational is the path of least resistance. Analytics over huge scans points to a columnar warehouse, not either default.

2. How strong must consistency be, and for which data? Money, inventory, and anything with an invariant ("balance cannot go negative") wants transactional, strongly consistent writes. A like counter or a view count can tolerate staleness. Split the system: you rarely need the same guarantees everywhere.

3. When does a single node stop being enough? A single well-provisioned relational primary with read replicas handles a very large amount of traffic before it breaks. Reach for a horizontally partitioned store when write throughput or dataset size genuinely exceeds one primary, or when you need multi-region writes. If sharding is on the table, it helps to know the mechanics; our database sharding field guide covers the trade-offs.

4. How stable is the schema? Stable, relational entities with clear relationships favor SQL. Documents whose fields vary per record, or where you want to store a whole aggregate together, favor a document store. Note that modern relational engines also store JSON, so "my data is JSON" is not by itself a reason to leave SQL.

CAP and consistency, stated correctly

This is the section that separates seniors from juniors, so get it right.

CAP (formalized by Gilbert and Lynch from Brewer's conjecture) says that when a network partition occurs, a distributed system must choose between consistency and availability. It is a statement about behavior during a partition, not a permanent two-letter label stamped on every database. A single-node PostgreSQL instance is not meaningfully "CP" in the CAP sense, because CAP describes distributed systems under partition; a lone node simply goes down if it fails. The honest framing is: for a given replicated deployment, during a partition, does it prefer returning an answer that might be stale (availability) or refusing to serve rather than risk inconsistency (consistency)?

Two more corrections candidates get wrong:

Partition behavior is configurable, not fixed per product. Several widely used stores let you tune the trade-off. DynamoDB supports both eventually consistent reads (the default) and strongly consistent reads. Cassandra exposes per-query consistency levels (ONE, QUORUM, ALL, and so on), so the same cluster can lean toward availability or consistency depending on the call. So "DynamoDB is AP" or "Cassandra is AP" is an oversimplification; the behavior depends on configuration, replication topology, and the specific operation.
"NoSQL means eventual consistency / BASE" is false as a blanket claim. Many non-relational systems do default to eventual consistency, but plenty offer strong or tunable consistency. MongoDB has supported multi-document ACID transactions since version 4.0 and exposes read and write concerns you can dial up. Describe the consistency you need and then check whether a given store can provide it, rather than assuming the category decides for you.

If you want to sound precise, mention PACELC: even when there is no partition (the "else" case), distributed systems still trade latency against consistency. That nuance signals you have thought past the bumper-sticker version of CAP.

Picking a concrete store

Here is a menu you can reason from. Treat it as defaults to argue with, not laws.

Workload	Reasonable default	Why
CRUD app, relational data, transactions	PostgreSQL / MySQL	Joins, constraints, ACID, mature tooling
High-volume key-value, predictable access	DynamoDB / Redis (as data store)	Single-digit-ms lookups, horizontal scale
Write-heavy, wide-column, multi-region	Cassandra / ScyllaDB	Tunable consistency, linear write scale
Aggregate documents, flexible fields	MongoDB	Store whole entities, per-query consistency controls
Full-text and relevance search	Elasticsearch / OpenSearch	Inverted index, ranking (as a secondary store)
Analytics over large scans	Snowflake / BigQuery / ClickHouse	Columnar, built for aggregation

A few editorial opinions from running mock interviews all day. First, we think a mature relational database is a sensible default for most new transactional systems: you can model relationships cleanly and relax guarantees later, which is easier than adding them after the fact. Second, PostgreSQL extends surprisingly far: with extensions it can serve vector search (pgvector) and time-series workloads (TimescaleDB), which often removes the need for a separate specialized store early on. That is a reason to reach for it, not a claim that it beats a purpose-built engine at extreme scale. Third, a cache like Redis is usually a complement to a primary database, not the system of record.

On MongoDB specifics, one detail worth getting right: its multi-document transactions have a default lifetime of 60 seconds, controlled by transactionLifetimeLimitSeconds. That is a configurable default, not an absolute hard ceiling, and the real guidance is to keep transactions short rather than to raise the timer.

How to say it in the interview

Use this four-beat structure so you lead with reasoning, not a product name:

State the access pattern and scale. "Reads dominate, queries are by user and by time range, and writes fit comfortably on one primary for the foreseeable future."
State the consistency need per data type. "Orders need strong consistency; the activity feed can be eventually consistent."
Name the default and the reason. "So I would start with PostgreSQL: relational model, transactions for orders, read replicas for the read load."
Name the escape hatch. "If write throughput outgrows a single primary, I would move the feed to a partitioned store like Cassandra with QUORUM reads and writes for the parts that need it."

That last beat matters. Interviewers almost always push with "now 100x the traffic," and having a migration path ready is the difference between a confident senior answer and a defensive one. You can rehearse exactly this back-and-forth in our AI mock interviews, which push on your trade-offs the way a real interviewer does.

FAQ

Is it ever wrong to just say "PostgreSQL"?

It is rarely wrong as a starting point, but saying it without justification is the mistake. Tie it to the access pattern and consistency needs, and name the condition that would make you switch. A default you can defend and know when to abandon beats a default stated on faith.

How do I answer "how would you scale this to millions of users"?

Separate reads from writes. Reads scale with caching and read replicas; writes eventually need partitioning or a store built for horizontal write scale. Say which data crosses the single-primary threshold first and how you would shard or move just that part.

Should I memorize which databases are CP or AP?

No, because that framing is itself imprecise. Explain that CAP is about behavior during a partition and that many stores are tunable per query. Describing the consistency a feature needs, then checking whether a candidate store can deliver it, scores better than reciting labels.

Do I need to know NoSQL internals to pass?

You need working mental models, not source code. Know what each category optimizes for, what it gives up, and the one or two tunable knobs (consistency level, read/write concern, partition key design) that matter in conversation.