Is NoSQL better than SQL for a system design interview?

No, and reaching for NoSQL by reflex is a common way to lose points. Default to a relational store and switch only when you can name the access pattern that defeats it - a write rate no single primary can absorb, a read shape that needs a precomputed denormalised view, or a cost model that only works with a known key. Interviewers are grading the justification, not the product name. 'Postgres, because the order and the payment have to commit together' is a stronger answer than 'DynamoDB, because scale'.

Does NoSQL scale better than SQL?

NoSQL stores make horizontal distribution the default rather than a project, which is a real advantage. But the claim that relational databases cannot scale is out of date. Vitess shards MySQL, Citus shards Postgres, and CockroachDB and Spanner are distributed SQL with cross-shard transactions. Several well known products chose to shard Postgres rather than migrate off it. The honest difference is operational: scaling a relational store horizontally is work you take on, whereas a store like Cassandra or DynamoDB gives it to you up front and charges you in query flexibility instead.

Can Postgres handle millions of users?

Usually yes, and the number of users is the wrong unit. What matters is writes per second to the hottest table and the working set size. A single well provisioned Postgres primary on NVMe storage comfortably serves thousands of write transactions per second and tens of thousands of reads with replicas, which covers most products with millions of registered users. You run out of road when one logical entity takes concentrated writes - a global counter, a live feed, a metrics firehose. That is a hot partition problem, and it would also break a NoSQL store with the same key.

How do I justify a database choice in an interview?

State the access pattern first, then the store, then the thing you are giving up. For example: 'Reads are always by user id and the newest fifty items, writes are append only at about 40K per second, and I never need an ad-hoc query over this table - so Cassandra with user id as the partition key and timestamp as the clustering key. The cost is that any query I did not plan for means a new table or a Spark job.' Naming the cost is what separates a decision from a recital of properties.

Can you use both SQL and NoSQL in the same system?

Yes, and most real systems do. It is called polyglot persistence. A typical split keeps money and inventory in Postgres where multi-row transactions are available, serves the product catalog from a document store, serves text search from Elasticsearch, and keeps counters and sessions in Redis. The hard part is not picking the stores, it is keeping them in sync - use change data capture driven by an outbox table so the write to your source of truth and the event that updates the other stores cannot diverge.

⏱️ 15 min read

SQL vs NoSQL

Most comparisons of these two answer a question nobody has. You are not choosing between a table and a document in the abstract, you are choosing a store for a specific workload under interview time pressure. Here is how that decision actually goes.

The Short Answer

Default to a relational store. Move off it when you can name the specific access pattern that defeats it. If you cannot name the pattern, you are guessing, and the interviewer can tell.

That inverts the advice people absorbed around 2012. The reason is that the premise changed: “SQL does not scale” is no longer true in the way it was repeated. MySQL shards behind Vitess, Postgres shards behind Citus, CockroachDB and Spanner do distributed SQL with cross-shard transactions, and managed Aurora and AlloyDB push a single logical primary much further than a 2010 box could. Both Notion and Figma have published accounts of sharding Postgres instead of migrating off it.

Your situation	Pick
You cannot yet describe every query the product will need	Relational
Two rows must change together or not at all	Relational
One known read shape, enormous write volume, no ad-hoc queries	Wide-column or key-value
Reads are by one key and you want a fixed cost per operation	Key-value
Full-text relevance, facets, typo tolerance	Search index, alongside something else
Append-only measurements queried by time range	Time-series store

Nothing in that table is about data volume.

What Each One Actually Optimises For

Relational stores optimise for querying data whose shape you cannot fully predict, and for invariants that span rows. You normalise once, and every future question is a join away. A BEGIN ... COMMIT holds several tables consistent with each other, which is the only reasonable place to put “decrement inventory and create the order, or do neither”. The optimiser is doing work on your behalf that you would otherwise write by hand.

NoSQL stores optimise for one known access pattern at predictable cost, and treat horizontal distribution as a built-in rather than a project. You model the table as the answer to a query you already have. Cassandra and DynamoDB do not reward you for normalising - they reward you for writing the data once per read shape, so that serving it is a single partition lookup whose latency does not drift as the dataset grows.

The framing you should drop is structured versus unstructured. Postgres has stored JSON with GIN indexes for a decade, and a MongoDB collection has a schema in every meaningful sense - it is just enforced in your application code instead of by the engine, which is strictly worse for catching drift. Shape is not the axis. Query flexibility, transaction scope, and who owns the distribution problem are.

Side by Side

	Relational (Postgres, MySQL)	NoSQL (Cassandra, DynamoDB, MongoDB)
Data model	Normalised tables, relationships as foreign keys	Denormalised per access pattern, relationships duplicated
Query flexibility	Any query the data supports, decided later	The queries your keys support, decided up front
Transactions	ACID across many rows and tables	Single partition or single document by default. DynamoDB and Mongo offer multi-item transactions with tight limits; Cassandra offers lightweight transactions at a steep latency cost
How scaling works	Vertical first, then read replicas, then sharding you plan and run	Add nodes, the ring or partition map rebalances
Consistency default	Strong on the primary, replicas lag	Tunable. Often eventual unless you pay for a quorum or strong read
Schema evolution	Explicit migrations, enforced, occasionally painful on huge tables	No migration step, so old and new shapes coexist and your code handles both forever
Joins	Native, optimised, planner picks the strategy	None or heavily limited. You denormalise, or you join in the application
Operational pain	Sharding, connection limits, long-running migrations, failover of a stateful primary	Modelling mistakes are near permanent, compaction and repair tuning, hot partitions, surprise bills

The transactions row is where most interview answers are vague. “NoSQL has transactions now” is technically true and practically misleading - DynamoDB caps a transaction at 100 items, and Cassandra’s lightweight transactions use Paxos per operation, so people who have run them do not use them on a hot path.

The Four Questions That Actually Decide It

1. Do you need multi-entity atomic writes?

Not “do you want them”. Is there a pair of writes where a partial failure leaves the system wrong in a way a user would notice? Order plus payment. Seat hold plus booking. Balance debit plus balance credit.

If yes, a relational store is the cheap answer, because the database already solved it. The alternative is a saga with compensating transactions, which is a correct pattern and roughly ten times the work, and which leaves you with intermediate states that are visible to readers. Pick that deliberately, not by accident.

2. Do you know your access patterns up front?

A DynamoDB or Cassandra schema is a commitment to a set of queries. Get the partition key right and the store is fast and cheap forever. Get it wrong and the fix is a new table plus a backfill of everything, because there is no index you can add to rescue a bad key.

So the real question is how confident you are. A metrics ingest path or a chat message store has patterns that will not change. A product’s core domain in month three does not.

3. Do you need ad-hoc queries you have not thought of yet?

Someone will ask for revenue by region by week, filtered to customers who did something else. In Postgres that is a query. In Cassandra it is a Spark job or a new table, and the business waits.

This is the single most underrated advantage of a relational store, and the one candidates never mention.

4. What is your real write throughput per partition?

Not total throughput - per partition. This is the question that tells you whether you actually have a scale problem.

Work out the number. 100 million writes a day is about 1,200 per second averaged, maybe 5,000 at peak, and that is a single Postgres primary with room to spare. Two million writes a second to a location table is not, and no amount of tuning changes that.

Then check the concentration. DynamoDB documents a per-partition ceiling of 1,000 write units and 3,000 read units, and Cassandra’s own guidance is to keep a partition under a few hundred megabytes. If your key is country_id you have built a hot partition and the engine’s total capacity is irrelevant - you will be throttled on one shard while the other 99 idle.
💡 A hot partition is a shard that receives a disproportionate share of traffic because the partition key has low cardinality or a skewed distribution. No database makes this go away; it is a modelling bug.

Where Each Family Fits

Postgres - the default. Transactions, JSONB when you need a flexible column, strong indexing, PostGIS for geospatial, and extensions that cover full-text search and time-series well enough to delay a second store. Reach for it unless a question above says otherwise.
MySQL - the same role, with Vitess as the mature horizontal sharding story if you expect to outgrow one primary. Picked for that reason by teams running very large fleets.
Cassandra - append-heavy, read-by-known-key, write throughput that no single primary absorbs. Home timelines, message history, event logs. The cost is that your tables are query-shaped and compaction tuning becomes someone’s job. Discord’s published move from Cassandra to ScyllaDB is worth reading on the operational side of that bargain.
DynamoDB - the same access shape as Cassandra with no servers to operate and a hard limit on how clever you can be. Single-digit millisecond gets by key, pay per request, and a bill that becomes a design constraint at high write volume. Excellent for session stores, idempotency keys, and shopping carts.
MongoDB - documents that are naturally read and written whole, where the aggregate boundary matches the document boundary. Product catalogs, CMS content, user profiles with varying fields. Its weak spot is the same as its selling point: nothing stops the shapes diverging.
Redis - not a system of record. Caches, counters, rate limiter buckets, sorted sets for leaderboards, geospatial radius queries, pub/sub. Treat durability as best effort and keep the truth elsewhere.
Elasticsearch or OpenSearch - text relevance, faceted filtering, typo tolerance, autocomplete. Always a secondary store fed from a primary, never the source of truth, because reindexing is normal and losing it should be survivable.
Time-series stores - ClickHouse, TimescaleDB, Prometheus, InfluxDB. Append-only measurements, queried by time range and aggregated. Columnar layout and time-based partitioning make a year of metrics answerable in a way a row store cannot match.

Using Both Is Normal

Polyglot persistence is the usual end state, not a compromise. A food delivery system is a clean example, and it is how the Zomato design on this site is put together:

Orders, payments, restaurant inventory live in Postgres. These have money in them and need multi-row transactions
The menu catalog lives in a document store, because a menu is read whole and every restaurant’s shape differs
Search lives in Elasticsearch, because “paneer near me open now under 300” is relevance plus facets plus geo, and no relational query plan wins that
Delivery partner locations and cart state live in Redis, because they are written constantly and tolerate loss

The interesting engineering is the sync. Writing to Postgres and then publishing to Kafka in application code gives you two operations that can fail independently, and eventually the catalog or the search index disagrees with the truth. Use the outbox pattern: write the row and the event to the same database in one transaction, then a change data capture process tails the log and publishes. Atomic because it is one commit, and the index converges because the log is replayable.

Say the lag out loud when you present this. A search index a few seconds behind is fine. A balance a few seconds behind is a bug.

What Interviewers Actually Want to Hear

They are listening for whether you reasoned from the workload or recited a property list. Two answers, same candidate knowledge, very different signal:

“I will use NoSQL for the feed because it scales better and the data is unstructured.”

“The feed is read by user id, newest first, about 50 items, and written at roughly 40K per second with no ad-hoc queries. So Cassandra, partition key user id, clustering key timestamp descending. What I give up is any query I did not plan for, and if product asks for ‘most liked posts this week’ I need a separate table or a batch job.”

The second one names the access pattern, the key, and the thing being traded away. That is the whole game. A few shapes that land:

“I would start with Postgres and shard later, because the access patterns are still moving and I would rather defer that commitment.”
“This table is the exception - it takes 50x the writes of everything else, so I would move it to Cassandra and leave the rest relational.”
“Eventual consistency is fine for the follower count. It is not fine for the wallet balance, so those live in different stores.”
“The bottleneck here is writes per partition, not total volume, so changing engines does not help. The partition key does.”

Saying “I would benchmark this before committing” is a good answer, not a dodge, as long as you also commit to a starting point.

Common Mistakes

Mistake	Why it costs you
“NoSQL, because the data is large”	Volume is handled by sharding or partitioning in either family. Size alone decides nothing, and leading with it signals you have not thought about queries
“NoSQL is schemaless”	The schema moved into your application, where nothing enforces it. Three years of accumulated shapes and every reader handles all of them
Ignoring the hot partition	One key taking a disproportionate share of writes caps you well below the cluster’s rated throughput. Every engine, no exceptions
Eventual consistency for money	A balance read that is 200ms stale lets the same funds be spent twice. Ledgers want a strong read, or an idempotency key plus a conditional write
Forgetting where the joins went	Removing joins from the database does not remove the work. It becomes N+1 round trips in your service, or duplicated data you now have to keep in sync
Picking the store before the access pattern	The pattern determines the key, the key determines the store. Running that backwards is how teams end up with a table they cannot query and cannot migrate

Where to Go Next

The mechanics behind this decision, in roughly the order they come up:

Database Sharding → — how horizontal scaling actually works in both families, and why the shard key is the decision
Consistency Models → — strong, eventual, read-your-writes, and which one each store gives you by default
CAP Theorem → — what it does and does not say, since it is routinely misquoted in this argument
Transactions and Isolation → — what ACID buys you, and what read committed still permits
Database Indexing → — why a relational store can answer a query you did not plan for

Designs where the choice is made concretely, with reasons:

Zomato → — Postgres for orders, Elasticsearch for search, Redis for live state
Twitter Feed → — the clearest case for wide-column, and the celebrity hot partition it creates
Payment System → — why the ledger is relational and stays relational
Metrics Monitoring → — where a time-series store beats both families

← Back to Concepts

Next: Database Sharding →