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

"I'd use NoSQL because it scales better" is the single most common weak answer in system design interviews. It is also, in 2026, mostly false — Postgres shards, and DynamoDB has transactions. The interesting question is not which is faster. It is which one matches how you are going to read the data.

Start from the access pattern, not the engine

The right order is:

  1. What are the queries? Write down the three or four that will dominate.
  2. What is the shape of the data? Does one record make sense alone, or only joined to five others?
  3. What consistency does each operation need?
  4. Then pick a store.

Candidates who name a database in their first sentence are guessing. Candidates who say "the dominant query is 'all messages in this conversation, newest first' — so I want those physically adjacent, keyed by conversation" have already justified their answer before giving it.

What relational databases are actually good at

Not "small data". They are good at:

  • Queries you have not thought of yet. An ad-hoc join across three tables is one statement. In a key-value store it is a redesign.
  • Invariants spanning rows. Transfer money between accounts, decrement inventory and create an order atomically. One transaction, no compensating logic.
  • Normalisation. One copy of each fact, so an update cannot leave two records disagreeing.

Reach for relational by default when the domain has real relationships and correctness matters: payments, orders, bookings, anything a finance team will reconcile.

The honest limit: a single primary caps your write throughput, and cross-shard joins and transactions get expensive fast once you outgrow it.

The four NoSQL families, and what each is for

They are not one category, and conflating them is a tell.

Key-value (Redis, DynamoDB). Get and put by key, extremely fast, scales horizontally with almost no ceremony. Right for sessions, caches, feeds, user profiles — anything you fetch by a known id. Wrong whenever you need to ask a question that is not "give me this key".

Document (MongoDB). A key-value store that can index and query inside the value. Right when records are self-contained and the schema varies per record — product catalogues, CMS content, event payloads. Wrong when the same fact is duplicated across many documents and has to stay in step.

Wide-column (Cassandra, HBase). Rows keyed by a partition key and sorted by a clustering key. Built for very high write throughput and for queries over a sorted range inside a partition — time series, message history, activity logs. The catch: you design the table for the query, and a new query usually means a new table. Say that out loud; it is what the interviewer wants to hear.

Graph (Neo4j). Stores relationships as first-class things, so multi-hop traversals stay cheap. Right for "friends of friends who work at X" — the query that becomes an unbearable self-join in SQL. Rarely the primary store.

The answer that is usually correct

Most real systems use more than one, and saying so is a strength rather than a hedge — as long as you can draw the boundary.

For a chat product:

  • Postgres for users, accounts, billing. Relational, needs transactions, modest volume.
  • Cassandra for messages. Partition on conversation_id, cluster on timestamp descending. The dominant query — recent messages in a conversation — is a single sequential read inside one partition, and the write volume is exactly what wide-column is built for.
  • Redis for presence and unread counts. Ephemeral, hot, fine to lose.
  • Elasticsearch for message search, fed asynchronously.

Each choice justified by a query, not a preference. Then name the cost you just accepted: four stores means four operational burdens, and data that lives in two places can disagree. The feed is eventually consistent with the messages table, and you should be the one to point that out.

Things to stop saying

"NoSQL is schemaless." The schema moved into your application code, where nothing enforces it. A four-year-old collection has six generations of document shape in it and every reader must handle all six.

"SQL doesn't scale." It scales vertically a long way, then horizontally with more effort than DynamoDB. "More effort" is a real cost — just not the same claim.

"NoSQL has no transactions." DynamoDB and MongoDB both have them now, with limits worth knowing.

"I'd use MongoDB." Without a reason, this reads as the only NoSQL database you can name. Any specific store plus a sentence of justification beats a brand name on its own.

In one line

Pick the store whose physical layout matches your dominant query, keep anything with real invariants in a relational database, use more than one if the access patterns genuinely differ — and name what each choice costs you.

Practise it