Choosing between SQL and NoSQL
Pick a data store from access patterns and QPS arithmetic, not schema flexibility, and defend the choice against the cheaper cache-and-replica answer.
The choice is not which database is better. It is which two operations you are willing to give up. A relational engine hands you joins across tables and a transaction spanning many rows; a distributed NoSQL store buys horizontal scale by removing exactly those two, because both need coordination that turns expensive the moment data lives on more than one machine. Schema rigidity, query language and ecosystem maturity all follow from that trade or are incidental to it.
What actually differs
| Feature | SQL (relational) | NoSQL (non-relational) |
|---|---|---|
| Data model | Tables, rows, columns, declared upfront | Key-value, document, wide-column or graph |
| Schema | Enforced by the database on every write | Enforced by application code, or not at all |
| Transactions | Multi-row, multi-table ACID | Single-key or single-partition; multi-key is limited or absent |
| Relationships | JOIN executed by the engine | Embedding, application-side joins, or graph edges |
| Scaling reads | Followers and caching, then sharding | Partitioned across nodes by design |
| Scaling writes | Bounded by the leader until you shard | Adding nodes adds write capacity |
| Query language | SQL: declarative, planner picks the access path | Per-engine API; you pick the access path yourself |
| Access pattern | Chosen after the data model | Chosen before it, and baked into the partition key |
| Typical fit | Orders, payments, anything with invariants across rows | Event logs, metrics, sessions, feeds, catalogues, billions of single-key rows |
Two clarifications the table cannot carry. ACID's C and CAP's C are unrelated. ACID-C means a transaction leaves the database's own invariants intact — no negative balance, no orphan foreign key. CAP-C means every read observes the latest write. A store can be fully ACID and still serve a stale read from a follower.
And CAP is not "pick two of three". Partitions arrive whether or not you planned for them, so the only live question is what the system does during one: refuse writes to stay consistent, or accept them and reconcile afterwards. PACELC covers the rest of the time — with no partition you still trade latency against consistency, which is what "read from the nearest replica" buys and costs. Data consistency works through the models.
The number that decides it
A single commodity Postgres box handles roughly 5,000 simple queries per second. Locate yourself against that before arguing about engines.
A product with 1M daily active users making 20 requests each:
1,000,000 × 20 = 20,000,000 requests/day
20,000,000 ÷ 86,400 s ≈ 230 QPS average
peak at 2-5× ≈ 500-1,200 QPS
A fifth of one machine. No engine-choice argument survives contact with that, and back-of-the-envelope estimation is how you get the number in the room.
Push it to 50M DAU and the same arithmetic gives 1 billion requests/day: ≈ 12,000 QPS average, 24,000-60,000 at peak, five to twelve boxes. Even here "switch to NoSQL" is usually the wrong first move. Read-to-write ratios of 10:1 are ordinary, so ~10,800 of that 12,000 QPS are reads. A cache at an 80% hit rate leaves 2,160 read QPS; add 1,200 writes and the leader sees 3,360 QPS — back under one box, with followers taking the peak. Name that rejected alternative out loud: caching plus replication and sharding is weeks of work; a store migration is quarters.
Follower reads bill you in lag. A user edits their profile, the next request lands on a follower 200 ms behind, and their own change vanishes. Read-your-writes has to be preserved deliberately: pin a session to the leader for a few seconds after it writes.
NoSQL earns the switch when the shape changes, not when the traffic does — writes dominate reads, the row count reaches billions behind a single-key lookup, or the records have no common shape.
What sharding costs, drawn
Once two tables stop sharing a machine, a join between them becomes scatter-gather: the coordinator fans the query out, waits for every shard, then merges. Two things break. Latency is set by the slowest responder — if one shard exceeds 10 ms once in a hundred requests, an eight-shard fan-out exceeds it with probability 1 - 0.99^8 ≈ 7.7%, turning a p99 event into roughly a p92 one (tail latency). Second, a transaction touching two shards needs two-phase commit, holding locks across a network round trip — 0.5 ms inside a datacenter, ~200 ms India to US East — and stalling if the coordinator dies mid-protocol.
Distributed NoSQL stores do not solve this. They refuse it. DynamoDB and Cassandra make you declare a partition key, then offer fast operations inside one partition and almost nothing across partitions. That refusal is the source of the scale, and it is why "we will work out the queries later" is fatal there: the partition key is fixed before the first write, and changing it means rewriting the dataset.
Flexible schema is a deferred bill
Schema-less does not mean there is no schema. It means the schema moved out of the database and into every piece of code that reads the data, and nothing rejects a bad write any more, so versions accumulate.
Take 40 million user documents storing plan as the string "pro", and a release that starts writing the object {"tier": "pro", "seats": 5} instead. Every read path now carries a branch for both shapes, and it stays until the last old document is converted. Backfilling at a considerate 1,000 writes/s costs 40,000,000 ÷ 1,000 = 40,000 s ≈ 11 hours of extra write load with both shapes live throughout, and a failure halfway leaves a third state. The relational version is one ALTER TABLE with a default. Flexibility was not free; it was invoiced later, per read site.
Polyglot persistence
Modern systems commonly run both. An e-commerce site might use a relational database for accounts, orders and payments, a document store for a catalogue whose fields vary by category, a key-value store for sessions and caching, and a wide-column store for behavioural logs.
Each store is another thing to operate, back up, monitor and keep aligned with the others. The alignment is usually a queue, delivery is at-least-once, at-least-once produces duplicates, and duplicates mean every consumer must be idempotent. Three stores are a design. Seven are a staffing problem.
In an interview
What is being tested is whether you can derive a store from requirements instead of reciting the properties of one. The interviewer knows what a document database is; they want the access patterns first.
Demonstrate the trade-off, not the label. Do not say "NoSQL, because it scales" — say which queries the system must serve, at what QPS, under what consistency requirement, and which of those the candidate engine refuses to answer. Usable phrasing: "reads are 95% of a 5,000 QPS peak and every one is by user id, so one Postgres leader with a cache and two followers covers it; I would revisit at ~5x this traffic or if writes start dominating."
The mistake that loses points is choosing on schema flexibility. "Requirements might change, so a document store is safer" says you have not thought about queries, and it walks into the 11-hour backfill above. Close behind: claiming SQL cannot scale horizontally — it scales the same way, just with the join and the cross-shard transaction taken away from you. Third: picking a partition key without naming one query it makes impossible.
Check yourself
A social app has 5M DAU, 30 requests per user per day, 95% of them reads. Does it need NoSQL? Show the arithmetic.
5,000,000 × 30 = 150,000,000 ÷ 86,400 ≈ 1,700 QPS average, peak at 3x ≈ 5,200 QPS — about 4,950 reads and 260 writes. Reads are the whole problem, and reads are the cheapest thing to move: a cache at 80% hit takes database reads to ~990, plus 260 writes ≈ 1,250 QPS against a 5,000 QPS box. Answer no — relational leader, cache, two followers — and add that you would revisit when writes alone approach 5,000 QPS.
You shard orders by user_id across 16 shards. Product asks for "every order in the last hour above $500". What does that query cost, and what do you do instead?
It carries no partition-key predicate, so it is a scatter-gather over all 16 shards: every one scans, and the answer waits on the slowest. If each shard exceeds 20 ms 1% of the time, the chance at least one does is
1 - 0.99^16 ≈ 15%— a rare tail on one machine becomes a common tail on the query. Do not serve it from the transactional store: stream orders into a search index or warehouse keyed on time and amount, and accept a lag of seconds.
A product catalogue has ~200 attributes that vary by category. Document store or relational? Name what breaks in the option you rejected.
Either defends; the marks are in naming the failure. Document store: attributes vary per product and a page renders from one single-key lookup — what breaks is "all products where voltage = 240V and price < 5,000", a cross-partition scan unless you also run a search index. Relational with a JSONB column: that query is one indexed predicate — what breaks is that the database stops enforcing the shape inside the column, so the flexible part is back to application-side validation.