SQL vs NoSQL

“Which database would you use?” arrives within the first fifteen minutes of every system design round. The weak answer is a brand name with a slogan attached: “MongoDB, because it scales.” The interviewer has heard it forty times this month.

The strong answer does not start with a database at all. It starts with two questions. What will I ask this data? And what must never go wrong with it? The database falls out of those answers.

What a SQL database gives you

A SQL database (Postgres, MySQL, SQL Server) stores data in tables with fixed columns, and lets rows in one table point at rows in another. People also call it a relational database. It gives you three things that are hard to get anywhere else.

Joins. You can combine tables in one query, including in ways nobody planned. “Which restaurants in Indiranagar had more than 50 cancelled orders last week?” Nobody designed the tables for that question. You can still ask it this afternoon.

Transactions. A group of changes that either all happen or none do. Placing an order means saving the order, saving its items and recording the payment. If the server crashes after step two, a transaction makes sure you are not left with an order that was never paid for.

A schema. The database knows what a valid row looks like and refuses anything else. An order without a customer simply cannot be saved.

Transactions come with four promises, known as ACID. In plain words:

• Atomic. All of the changes happen, or none of them.
• Consistent. Your rules always hold. Stock never goes below zero.
• Isolated. Two people buying the last item at the same moment do not both succeed.
• Durable. Once it says “saved”, a crash cannot lose it.

The price is on the write side. A SQL database normally has one primary machine that takes every write. That machine can be very large, and it carries most companies for years. When you do outgrow it, splitting the data across machines is work you do yourself.

What “NoSQL” actually means

NoSQL is not one thing. It is a label for four quite different kinds of database that each dropped something from the list above to gain something else, usually the ability to spread across many machines without effort.

The same order, two shapes

Here is one food order in a SQL database. It lives in two tables, linked by order_id.

And here is the same order as a single document.

{
  "_id": "ord_7841",
  "customer": { "id": 4471, "name": "Riya" },
  "restaurant": { "id": 92, "name": "Biryani House" },
  "status": "delivered",
  "items": [
    { "name": "Chicken biryani", "qty": 1, "price_paise": 32000 },
    { "name": "Raita", "qty": 2, "price_paise": 4000 }
  ],
  "total_paise": 40000,
  "created_at": "2026-10-12T20:14:00+05:30"
}

The document is lovely for the order screen. One read, by one key, and you have everything to draw the page. No join.

It is awkward for almost everything else. “How many biryanis did we sell this week?” means opening every order document and looking inside. And the restaurant’s name is copied into every order, so when Biryani House renames itself, you either update a million documents or live with old names.

Neither shape is better. The document is shaped for one question. The tables are shaped for questions you have not thought of yet.

Design from the query backwards

This is the practical difference, and it is worth saying out loud in a round.

With SQL, you model the data first. Then you write whatever queries you need, and add indexes when one is slow.

With NoSQL, you list the queries first. Then you shape the data so that each query is a single lookup. In Cassandra, a chat table might be keyed by chat_id and sorted by time, so “the latest 50 messages in this chat” is one cheap read from one machine. Ask that same table for “every message Riya ever sent” and there is no good way to answer. You would need a second table, keyed by sender, written to at the same time.

So before choosing NoSQL, you must be able to list your queries. If you cannot, you are not ready to choose it.

Pick the store

For each workload, decide what you would use before you reveal it.

“NoSQL scales and SQL does not”

You will hear this, and it is half true in a way that misleads.

SQL databases scale reads with replicas and writes with sharding. It is manual work, and plenty of large products have done it. Instagram wrote in 2012 about sharding Postgres. Notion wrote about doing the same in 2021.

NoSQL stores spread across machines easily because of what they gave up. With no joins and no transactions across rows, any row can live on any machine without the others caring. The scaling is not magic. It is the receipt for the features you handed back.

The line has also blurred. MongoDB and DynamoDB both added multi item transactions in 2018. And a newer group, sometimes called NewSQL (Google Spanner, CockroachDB), offers SQL with joins and transactions spread across many machines, paying for it in latency. The global database page covers how.

Most real systems use more than one

You do not have to pick a side. Each kind of data goes where it fits. Figure 1 is a food delivery backend, and it is typical.

The rule that keeps this sane: one store is the source of truth for each piece of data, and the others are copies built from it. Orders live in Postgres. The search index is rebuilt from Postgres and is allowed to be a few seconds behind.

Break it

One Postgres database, growing. Watch what fails at each size and notice how late in the story a different database becomes the answer.

Trade-offs

Interview replay

Checkpoint