SQL vs NoSQL — and why, not just names
Choose by how you read the data, and by what must never be wrong. Not by which word sounds bigger.
What problem does a relational database solve?
Relational databases give you a fixed schema, joins, and real transactions across many rows. Non-relational ones give some of that up. In return they spread across machines more easily, and they store data in the shape you read it.
This choice matters because it is very hard to undo. A cache or a queue can be swapped in a sprint. A data model that holds years of production data cannot.
What a relational database costs you
Every concept in here states a cost. A technique with no price is a technique you have not understood yet — and naming the cost out loud is most of what separates a senior answer from a junior one.
When to reach for a relational database, and when not to
Use it when
- Relational: money, stock, anything that needs a transaction across several rows. Also queries you cannot predict, and normal scale, which covers most systems.
- Non-relational: one main, well-known way of reading the data. Very heavy writes. Data with no natural joins. Rows whose shape varies.
Do not, when
- Do not pick non-relational for scale you have not estimated. Do the maths first. One well-indexed relational database handles more than people think.
- Do not pick relational, then use it as a key-value store with one big JSON column, and pretend you chose carefully.
How a relational database actually works
- —Transactions across many rows, so "take payment and reserve the seat" is one all-or-nothing step.
- —Ask questions you never planned for. Joins and one-off queries just work.
- —Rules live in the database, so bad data cannot get in even through a buggy service.
- —Scaling reads is easy (replicas). Scaling writes past one machine is real work you do by hand.
- —Changing the schema of a huge table needs care and a plan.
- —Spreads across machines by design, so write capacity grows by adding boxes.
- —You store data in the shape you will read it, so a read is one lookup with no joins.
- —Rows can differ from each other, so adding fields is cheap.
- —Transactions are limited, usually to one item or one partition. Past that it gets awkward.
- —A new way of reading means a new table and a backfill, not just a new query.
Two questions decide it. What is the one query this system runs ten thousand times a second? And does anything here have to be correct when many people compete for it?
If the hot query is "give me everything for this one key", non-relational fits well and its limits will not hurt you. If the system involves seats, stock, balances or unique names, you want real transactions. Building those yourself on top of a store that lacks them is much harder than it looks.
Two things people get wrong. First, non-relational is not automatically faster. It is faster for the pattern you designed it around, and often much slower for anything else. Second, relational databases have improved a lot: replicas, partitioning, JSON columns, and hosted versions that split the data for you. The gap you may be imagining is ten years out of date.
For large scale, be specific about the shard key instead of the brand name. "Cassandra" tells the interviewer nothing. "Partition by user id, sort by timestamp descending, so one user's recent items are one continuous read" tells them you understand the machine.
- Naming a database instead of naming how you will read the data.
- Claiming non-relational stores "cannot do transactions". Many can, within one partition. Know the limit you are describing.
- Choosing for write volume you never estimated.
The a relational database follow-up an interviewer will ask
"Why this database and not Postgres?"
The answer has to be a number or a pattern, not a preference. Something like: "The write rate is around 80,000 per second, all day, on one logical table. It is append-only with no transactions across rows. One Postgres primary will not take that, so I would be splitting it by hand, which means building what a distributed store already gives me, including rebalancing and failover. The read pattern is a single key lookup, so I am giving up joins I do not need." Then name the cost: no transactions across partitions, so any rule that spans partitions has to live in application code, and analytics queries need a separate copy of the data. If you cannot produce a sentence like that, then Postgres is fine, and saying so is a strong answer.
Your turn
You are designing a system that records payments. Which do you pick, and what is the one word that decided it?
Choosing storage for a link shortener
Around 40 new links a second and 4,000 redirects a second. Roughly 1.2 TB after five years. Every read is "give me the destination for this code". Users can pick custom codes, so two people must never get the same one. Which database, and why?
- 1Look for an invariant first
There is one: no two links may share a code. That is a uniqueness constraint under concurrency, which a database enforces well and application code enforces badly, because check-then-insert has a race between the check and the insert.
- 2Check whether the numbers force a distributed store
40 writes a second is nothing. A single primary handles that with about two orders of magnitude of headroom. 1.2 TB fits on one machine. So nothing here forces a distributed database, and saying that explicitly is worth more than reaching for one.
- 3Look at the access pattern
Every read is a lookup by a single key. That is key-value shaped, which is why people reach for a key-value store here, and the read path does use one, as a cache. The question is what holds the durable truth.
- 4Decide
Postgres, keyed by the short code, with a unique constraint on it. The constraint gives collision handling and "that name is taken" for free, in one mechanism, with no race. Redis in front absorbs the 4,000 reads a second so the database rarely sees a redirect at all.
- 5Name what it costs
One primary is the write ceiling. At 40 a second against a ceiling in the thousands, that is fine, and I would revisit at roughly 100x. The cache means a disabled link stays live for the length of its TTL.
- 6Say what would change your mind
If writes reached tens of thousands a second, I would move to a key-value store partitioned by code. Then I would have to solve uniqueness myself, most likely by generating codes that cannot collide rather than by detecting collisions.
Learn it from the source
This page gets you interview-ready. These take you to where the idea is actually specified or measured — read them when you want the real depth.
- PostgreSQL — Indexes ↗What a relational database actually gives you, written by the people who implement it.
- Jepsen — Consistency models ↗The guarantees different databases provide, tested rather than claimed.