SQL vs NoSQL, Decided

Editorial

The question is bait

Forty minutes into a design interview, the interviewer leans back: “So — SQL or NoSQL for this?”

Candidate A takes the bait. “NoSQL, definitely — we need scale.” The next ten minutes are a religion debate: but what about the order totals? but Postgres shards too? but what does “scale” mean here? Every answer defends the flag instead of the design, and the whiteboard does not change once.

Candidate B answers a different question — the one actually being asked: “Our hottest read is by key, no invariant spans two facts, and the write rate is past one box — so a partitioned key-value store; DynamoDB or Cassandra, and I'd say up front that cross-key reads go eventual.” Twenty seconds, and the design continues.

The best-known interview guide there is says this about the question, verbatim:

“Most interviewers don't need an explicit comparison of SQL and NoSQL databases in your session and it's a pothole you should completely avoid.”

The question itself is a pothole — asked, often, precisely to see whether you fall in. What that guide does not teach is the sidestep. This lesson does.

Nobody ships “SQL” or “NoSQL.” They ship an engine, chosen by three properties of one workload. Answer the properties and the engine names itself; answer the flag and you have volunteered for a debate you cannot win.

Two candidates answering the same interview question, drawn as two paths from one prompt box that reads: so, SQL or NoSQL for this? The upper path is the pothole. The candidate answers the flag — a speech chip reading NoSQL, definitely — and the path descends through an escalating spiral of debate stops: but what about transactions, but what about scale, but Postgres can do that too, each drawn as a tightening loop, ending in a clock face with ten minutes shaded gone and a verdict chip reading a religion defended, no design shown. The lower path is the sidestep. The candidate answers with three properties instead — chips reading hottest read is by key, no invariant spans two facts, and write rate beyond one box — and the path runs straight: the three chips feed a funnel out of which an engine names itself, a partitioned key-value store, DynamoDB or Cassandra, with a scope sentence attached: per-key access, eventual across keys, and we size partitions up front. The path ends at a chip reading twenty seconds, and the design continues. Between the two paths sits the verbatim outside anchor in a quotation band: most interviewers don't need an explicit comparison of SQL and NoSQL databases in your session and it's a pothole you should completely avoid — the best-known interview guide calling the question itself a trap. A closing band states the law: nobody ships SQL or NoSQL; they ship an engine chosen by three properties of one workload — answer the properties and the engine names itself.

First, the reason the flags cannot decide anything

The debate assumes two camps with defining features: relational means transactions, NoSQL means flexible schema. If that were true, the flag would carry information. So this lesson's capture ran each camp's supposedly defining feature in the other camp's engine — live, with controls.

First, the “relational-only” feature, in MongoDB 7.0 — a two-collection money move with a crash injected mid-flight:

# captures/sqlnosql_the_flags_are_costumes.py — E1, real MongoDB 7.0
> session.withTransaction(() => {
    accounts.updateOne({_id:"alice"}, {$inc:{balance:-300}});
    throw new Error("process dies here");     // credit + ledger never run
  });
alice=1000 bob=1000 ledger=0                  <- NOTHING applied. Rolled back.

# CONTROL — the same crash, no transaction:
alice=700 bob=1000                            <- 300 gone, never arrived

# and the scope: BEFORE rs.initiate(), the same transaction was refused
TXN-REFUSED: NotWritablePrimary               <- the feature needs a replica set

With the transaction: nothing applied — alice and bob both back at 1000, the ledger empty, exactly the documented promise: “Transactions either apply all data changes or roll back the changes.” The control arm is the point, though: the same crash without the transaction left alice=700, bob=1000 — 300 gone from one account and never arrived at the other, no error anywhere. The torn write is real, and the transaction is what prevented it. In a document store.

Two honest footnotes, both measured. A standalone server refused the transaction outright — the feature requires a replica set; it exists with a scope. And MongoDB's own manual prices it: “a distributed transaction incurs a greater performance cost over single document writes, and the availability of distributed transactions should not be a replacement for effective schema design.” The feature is real, priced, and scoped — which is exactly the shape of every guarantee in this course.

Now the “NoSQL-only” feature, in PostgreSQL 16:

# E2, real PostgreSQL 16 — 100,000 documents, three shapes, zero schema statements
INSERT ... jsonb_build_object('kind','order',  'items',[{sku,qty}], 'total',...)
INSERT ... jsonb_build_object('kind','review', 'stars',..., 'text',...)
INSERT ... jsonb_build_object('kind','ticket', 'tags',[...], 'nested',{...})

SELECT count(*) FROM docs
WHERE body @> '{"kind":"ticket","user":"u123","tags":["t3"]}';

no index : Parallel Seq Scan       ~19 ms
GIN index: Bitmap Index Scan     0.448 ms     <- 43.4x, plan changed

-- a field NO document ever had, queried with zero ALTERs:
WHERE body @> '{"kind":"refund"}'  -> 1 row

100,000 documents of three different shapes — orders with item arrays, reviews, tickets with tags and nested objects — inserted with zero schema statements. The selective containment query ran at ~19 ms as a sequential scan; one GIN index later it ran at 0.448 ms — 43.4× faster — with the plan visibly changed, which is the Postgres documentation keeping its own promise: “GIN indexes can be used to efficiently search for keys or key/value pairs occurring within a large number of jsonb documents.” And a refund field no document had ever carried was queried with zero ALTERs.

One harness note, kept because it teaches: the first draft of that query matched a tag alone — 6,191 rows — and the index paid only 3.6×, because the heap fetch dominated. Selectivity is part of the claim. An index is an argument about how few rows you want, not a speed potion.

So: the transaction runs in the document store, the schemaless documents run indexed in the relational store, and both engines have carried these features for years. The flags are costumes. The properties underneath — key-shaped access, invariants that span facts, write rates, staleness tolerance — are real, and they are what the interview is actually probing.

The costume swap, measured: each camp's supposedly defining feature running in the other camp's engine, drawn as two live panels. The left panel is MongoDB seven wearing the relational costume: two account cards, alice and bob at one thousand each, and a ledger. A transaction arrow carries debit three hundred, then a crash bolt strikes mid-flight, and the balances snap back — alice one thousand, bob one thousand, ledger zero — with the documentation's own sentence beneath: transactions either apply all data changes or roll back the changes. Next to it the control arm, same crash without the transaction: alice seven hundred, bob one thousand, and a red gap labelled three hundred gone, never arrived. A small scope stamp records that a standalone server refused the transaction outright — the feature requires a replica set. The right panel is PostgreSQL sixteen wearing the document costume: three differently shaped document cards — an order with items, a review with stars, a ticket with tags — stacked to one hundred thousand with zero schema statements, and a containment query drawn twice: without an index, a flat scan bar at about nineteen milliseconds; with a GIN index, a short bar at zero point four five milliseconds, forty-three times faster, the plan flipping from sequential scan to bitmap index scan. A final chip shows a never-seen refund field queried with zero ALTERs. The closing band: the camps traded defining features years ago — the flags are costumes, the properties are real.

Three properties in twenty seconds

SQL vs NoSQL: The Decision Rubric built the full six-question version of this decision, and it remains the deliberative tool — use it in design reviews, where you have an afternoon. In the room you have twenty seconds, and under pressure six questions compress to three properties, because three is what actually separates the engines:

What shape is the hottest question? By key (fetch cart 4711, session abc) — or arbitrary (filter, join, aggregate, and tomorrow's queries unknown)? Key-shaped access is the one thing every partitioned store does perfectly and the thing that never needs a relational engine.
Does any invariant span two facts? Money moving between accounts, stock decrementing as an order commits, a booking that must not double-sell. If two things must change together, you need the engine to referee — and the capture above showed what the referee is worth: 300 units survived with it, vanished without it.
What are the honest numbers? Write rate and working set, said as numbers, against the fact that one well-tuned relational box goes much further than the debate admits — and that past a real ceiling, partitioning is not optional for anybody.

Then the speech pattern, in this order:

Properties → engine → scope. “Key-shaped, no cross-fact invariants, write-heavy past one box → a partitioned key-value store → and I'll say the scope out loud: per-key ordering, eventual across keys, partitions sized up front.”

The third clause is the one that ends follow-ups before they start — it is the same finish the sentence discipline the infrastructure checkpoint drilled, pointed at databases. Say the scope before the interviewer asks and there is nothing left to catch you on.

Rapid fire: eight prompts, one line each

The compression, exercised. Eight prompts the way interviewers say them, each answered in one line of the speech pattern — properties first, engine second, scope third.

the prompt	the one-line answer
“shopping cart”	key-shaped by user, no cross-fact invariant until checkout → KV store, and checkout crosses into the order system's transactional write
“payments ledger”	every write is an invariant across two facts → relational, single-writer, and I scale reads before writes
“product catalog”	read-heavy, arbitrary tomorrow-queries, fits one box for years → relational; documents-in-relational (`jsonb`) for the variant attributes
“clickstream events”	append-only, no invariants, firehose write rate → not a database question: a log first, then a warehouse
“user sessions”	key-shaped, TTL, loss is annoying not fatal → in-memory KV, and I say out loud that a restart logs people out
“chat messages”	key-shaped by conversation, ordered within it, write-heavy → wide-column family, ordering scoped per partition
“social graph”	traversals two hops out → graph-shaped access, and below big scale a relational join table honestly serves it
“leaderboard”	one sorted read, updated constantly → a sorted structure in memory, backed by something durable that is allowed to lag

Notice what never appears in the right-hand column: the words SQL or NoSQL. Every answer names properties, then an engine family, then a scope — and three of the eight aren't even a database-versus-database decision, which is itself a thing the flag question hides.

The follow-up is aimed at your reason

Here is the part nobody rehearses. The interviewer's follow-up is not random — it is aimed at the reason you gave. State a reason, and you have chosen which attack comes next. There are three, and this course has already measured the ammunition for each:

You said “ACID, so relational.” The attack: “and when one box isn't enough?” The parry is the ladder, said with numbers: read replicas, then partitioning by tenant or key — and the honest write tax named before they name it: PostgreSQL, Inside-Out measured 77 bytes of log per index per update, six untouched indexes turning one update into 2.4× the write volume. You are not claiming relational scales forever; you are showing you know exactly what it costs and where the ceiling is.

You said “it has to scale, so NoSQL.” The attack: “and the cart total that must not tear?” The parry carries this section's sharpest number: Cassandra, Inside-Out measured 300 units vanishing at QUORUM with zero errors — leaderless stores referee concurrent writes with a clock. So anything that must not tear gets a conditional write, a single-writer partition, or a different home — and you say which, unprompted.

You said “flexible schema, so document store.” The attack: “so who validates now — and what do your indexes cost?” The parry: MongoDB, Inside-Out measured a fifty-tag array at 40 times the index, and the useful distinction is that variation in values is cheap, variation in keys is a query problem, and indexed arrays are a multiplier. Flexibility did not delete the schema; it moved it into the code.

One shape under all three: every reason is a surface you must defend one level deeper. That is the data checkpoint's second-surface law wearing interview clothes — so give reasons whose numbers you know, and the follow-up becomes the easiest part of your hour.

The follow-up is aimed at your reason, drawn as three parries. Each row begins with a speech chip holding the reason a candidate gave, a red attack arrow carrying the interviewer's counter, and a green parry card holding the honest answer with the measured number this course produced for it. Row one: the stated reason is we need ACID, so relational; the attack is and when one box is not enough?; the parry is the ladder, said with numbers — a partitioned relational tier goes far, and the write tax is knowable: seventy-seven bytes of log per index per update, six untouched indexes turning one write into two point four times the volume — so I would name the ceiling before switching religions. Row two: the stated reason is it has to scale, so NoSQL; the attack is and the cart total that must not tear?; the parry carries the measured torn write — three hundred units vanished at QUORUM with zero errors — and the honest sentence: leaderless stores referee concurrent writes with a clock, so anything that must not tear needs a conditional write or a different home. Row three: the stated reason is flexible schema, so document store; the attack is so who validates now, and what do your indexes cost?; the parry carries the fifty-tag array measured at forty times the index, and the sentence: variation in values is cheap, variation in keys is a query problem, and indexed arrays are a multiplier — flexibility moved the schema into the code. The closing band: every reason you give is a surface you must defend one level deeper — give reasons whose numbers you know.

The clock is running

Now do it under the actual constraint: time. The clock deals you a prompt the way an interviewer says it and gives you twenty seconds to click the properties that matter — and here is the pedagogy of the thing: you never pick the engine. The engine derives itself, live, from the properties you have selected, because that is the entire lesson. Then the follow-up arrives, aimed at exactly what you claimed, and you pick the parry.

Miss the window and the moment passes — the way it does in the room.

The decision clock. Ten prompts, twenty seconds each: click the two or three workload properties that matter and watch the engine name itself from your selection — you never choose it directly. Then survive one follow-up aimed at the exact property you claimed. Streaks tracked; the clock is the point.

When one flag honestly wins

The sidestep is not fence-sitting, so here are the honest boundaries, with the course's own numbers attached.

The partitioned world genuinely wins when key-shaped access meets a write rate no single box survives and the invariants are per-key: the ancestor case is the shopping cart that must accept writes through a datacenter fire, and the modern proof point is DynamoDB holding 89.2 million requests per second across Prime Day 2021 — a number no relational deployment approaches. If the interviewer's prompt smells like that, say so in one sentence and move.

The relational world genuinely wins when invariants span facts, when tomorrow's queries are unknown (the catalog you will filter forty new ways next quarter), and when the honest numbers fit a box — which they do far more often than the debate admits. SQL vs NoSQL: The Decision Rubric closed on the stance this lesson inherits: default first, specialize on proof — the relational default until a measured property forces the move, because the migration back is the expensive direction.

And when neither cleanly wins, say that too: “both serve this; I'll take Postgres for operational familiarity and revisit at the write ceiling” is a senior sentence. Indecision dressed as balance is not — the difference is whether you named the ceiling.

What this lesson does not claim

JSONB is not MongoDB. The capture shows Postgres serving schemaless documents fast; it does not show MongoDB's update operators, its aggregation pipeline ergonomics, or its sharding story. The claim is that flexible schema stopped being a flag, not that the engines are interchangeable.
The Mongo transaction is real, priced, and scoped. Its own manual calls the cost out, the capture caught the replica-set requirement, and DynamoDB, Inside-Out measured the same shape elsewhere: transactional writes at 2× the capacity. Cross-fact invariants as a primary workload still point relational.
43.4× is one query on one box. It is the measured gap between a sequential scan and a GIN index on 100,000 documents — not a benchmark of Postgres against anything else, and nothing here measured distribution. The deep-dives own those numbers.
The eight rapid-fire answers are defaults, not verdicts. Each one is the first sentence of a conversation; the follow-up section is what makes them survivable.
The measured facts here are two engines on one machine. Everything cross-node in this lesson — the torn QUORUM write, the index write taxes — is cited to the lesson that measured it, not re-measured.

Takeaways

The question is bait, and the best-known interview guide says so verbatim: the comparison is “a pothole you should completely avoid.” The sidestep is to answer the workload, not the flag.
The flags stopped carrying information years ago — measured: a two-collection ACID transaction rolled back cleanly in MongoDB (control arm: 300 gone, torn), and 100,000 schemaless documents queried 43.4× faster under a GIN index in Postgres, zero ALTERs.
Compress to three properties: the hottest question's shape, whether any invariant spans two facts, and the honest numbers. Then speak in the pattern: properties → engine → scope.
The follow-up is aimed at your stated reason — ACID gets the scale attack, scale gets the torn-write attack, flexibility gets the who-validates attack — and every parry in this lesson carries a number this course measured.
Honest boundaries: 89.2 million requests per second is the partitioned world's real trophy; unknown tomorrow-queries and cross-fact invariants are the relational world's; and default first, specialize on proof remains the stance when neither dominates.
Next in this decision layer: the same discipline one level deeper — one workload, defended three ways, against Postgres, DynamoDB, and Cassandra specifically.