SQL vs NoSQL, honestly: where the assembly work goes

A seller renames itself. Orbit Supply becomes Orbit Tools. That is the whole argument in one event. On one side, the rename is one row. On the other, it is inside every product that seller has ever listed, because each product carries a copy of the name so a product page is one fetch instead of three. Both designs are correct. They put the work in different places — and the question that decides between them is not which is faster but when this answer changes, how many places change with it?

This is the written half of a ninety-second video. From here, “NoSQL” means the document model — MongoDB is the reference — because that’s the comparison people are actually having; key-value, wide-column and graph stores make different trades entirely. The relational side is checked against PostgreSQL 18.

The picture, in one rename

Stored as facts, a seller’s name lives in one table and a product points at it by id — a foreign key is the promise that a product can’t reference a seller that doesn’t exist. To build a product page you name the tables and the database assembles the answer:

select p.name, p.price, s.name as seller, r.rating, r.body
from products p
join sellers  s on s.id = p.seller_id
left join reviews r on r.product_id = p.id
where p.id = 42;

That join is planned, costed and paid on every read. Stored as the answer instead, the same catalogue is one document — seller and reviews nested right inside the product — so a fetch is one key, nothing left to join. MongoDB’s own principle: “data that’s accessed together should be stored together,” which “allows you to avoid complex joins across multiple collections.”

The assembly didn’t vanish; it happened at write time. Now Orbit Supply becomes Orbit Tools. On the relational side: one UPDATE, and every future join reads it. On the document side, the old name is inside every product that seller ever had, and each has to be found and rewritten. MongoDB’s own guidance says exactly when that’s fine: “The extra logic needed to handle infrequent updates is less costly than performing joins on read operations. However, frequently updating duplicate data can cause heavy workloads.” A seller name is usually rare to change. A price is not. The model doesn’t decide for you — the change frequency of each copied field does. Hold that picture; everything else here is a corner of it.

The document that becomes a bucket

The product above embeds its reviews. For ten reviews that’s fine; for ten thousand it’s a bad design, and MongoDB’s own docs say to stop embedding once “the child side has high cardinality” or “your embedded data grows without bounds.” There’s a hard ceiling behind the advice — the maximum BSON document size is 16 mebibytes — and the manual has a page titled Avoid Unbounded Arrays warning that an unindexed array “can strain application resources and decrease index performance.” The failure is silent for a long time: every read of the product fetches every review, reads get slower in proportion, and one day an insert fails because the document crossed sixteen mebibytes — on the one popular product where it matters.

The fixes are the two shapes of the relational answer, re-derived: keep a bounded subset (the last few reviews) with the full history in its own collection, or store just review ids — or nothing — and fetch reviews by product_id, which is a foreign key with the SQL taken out. Ask, for every array in a document: what bounds this? If the answer is “nothing,” it’s a reference. And the trap has a relational mirror: a reviews column of type jsonb on products is the identical bucket, and PostgreSQL’s own docs say why it hurts there too — “any update acquires a row-level lock on the whole row,” so every new review locks and rewrites the product.

Fanning a rename out from application code

Somebody understands that copies need rewriting and writes a loop: find every product with seller.id = 7, update each one’s embedded name. It works on a laptop. In production one of those updates doesn’t land — a timeout, a deploy mid-loop — and the catalogue now holds two answers to “what is seller 7 called,” with nothing in the database that notices they disagree. There’s no foreign key on the embedded copy, because the whole point was not to need one.

Two real fixes. Stop copying the field — store seller: { id: 7 } and look the name up, which is the reference again, paying a second fetch on read. Or make the fan-out atomic with a multi-document transaction, which every serious document store now has, and every one documents the cost of. MongoDB is blunt: “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” — transactions have a default one-minute runtime limit, and a busy catalogue can abort a large rename repeatedly. DynamoDB’s TransactWriteItems caps at 100 items and “performs two underlying reads or writes of every item… one to prepare, one to commit” — double cost, charged even on failure. Neither vendor is saying transactions are bad; they’re saying coordination across partitions is the exact thing the document model was chosen to avoid, and a transaction buys it back at a documented price. A relational store paid that price up front, in the join, and made the rename cheap instead.

“NoSQL scales and SQL doesn’t”

Both families split rows across machines — PostgreSQL has built-in partitioning, DynamoDB scales by adding partitions, Cassandra’s whole addressing scheme is a partition key. Nobody here has a store that doesn’t partition. What differs is what a query is allowed to do once rows are split. A query that names the partition key is one hop. A query that doesn’t is one hop per node plus a merge, and the document stores say so themselves: MongoDB calls it “scatter/gather,” and warns such queries “can be long running operations.” Cassandra: “joins are not supported… so all required fields must be grouped together in a single table.”

So the honest claim isn’t “doesn’t scale” — it’s “scales the cheap operations and forces you to design every table around the query that will hit it.” A relational store lets you ask a question nobody designed for, and at scale the honest answer is that the question might reach every machine. Whether that’s a feature depends entirely on whether you know the queries in advance. The corollary shows up in secondary indexes: DynamoDB’s global secondary index is a second copy of the data, updated asynchronously — “applications need to anticipate… results that are not up to date” — which is a denormalised read model with the first section’s write-amplification built back in. There is no free secondary index across partitions; it’s either a copy (eventually consistent) or a lookup that needs the partition key.

Eventual consistency, and the C that isn’t ACID’s C

Write a product, read it back, get the old version. That’s often the default in a replicated store, not a bug. DynamoDB’s default read is eventually consistent — “responses might not reflect the results of a recently completed write” — and a strongly consistent read costs more for exactly that reason. A single PostgreSQL primary gives you read-your-writes for free, because there’s one copy; add a read replica and you inherit the same problem with a different name, replication lag. Cassandra states the knob most plainly: QUORUM means “a majority of replicas must respond,” and if both reads and writes use it, “at least one replica is guaranteed to participate in both,” so the sets can’t miss each other. Below quorum, convergence is best-effort.

This is the second conflation worth naming, and Brewer corrected it himself: “In ACID, the C means that a transaction preserves all the database rules… In contrast, the C in CAP refers only to single-copy consistency, a strict subset of ACID consistency.” A CHECK (rating between 1 and 5) is ACID’s C — a write that breaks it is refused. CAP’s C is about copies: does every read see the latest write, wherever it lands? A store can be full of CHECK constraints and still serve a stale replica.

Schema and the join you get back

“Schemaless” is the second-most misleading word here — there’s always a schema, the choice is where it’s enforced. PostgreSQL checks it at write time, before the row exists; a violating insert is refused and every reader can rely on the shape without checking. MongoDB, by default, lets a collection hold documents with different fields entirely — which isn’t the absence of a schema, it’s a schema that lives in every piece of code that reads the collection. A validator ($jsonSchema) buys the same guarantee back, with a validationLevel that decides whether old documents are grandfathered in.

And the join comes back too. MongoDB’s $lookup “performs a left outer join to a collection in the same database” — but it’s a correlated subquery, not a planned join: “a $lookup operation… will likely have poor performance” without a supporting index, and the result is itself a document, so the 16 MiB ceiling applies to what it just assembled. It’s the right tool for an occasional cross-collection read in a model that mostly embeds — not for a model that mostly references, where a pipeline full of $lookup is a relational schema paying a document store’s join to get a relational read.

How to actually pick

There’s no winner here, and every vendor quoted above admits the cost on its own side. Ask these of the data you’re about to store, not of the technology. When the assembled answer changes, how many places change with it? Forty thousand products with a monthly seller rename is forty thousand writes a month; four products that never rename, embed and stop worrying. Is every “many” bounded? If you can’t name the bound, it’s a reference, on either side. Do you know the queries in advance? A store shaped to the query is fastest when the queries are known and slowest when a new one arrives; a store that reassembles at read time is the reverse. Can a read be stale, and for how long? An inventory count can; a balance can’t. Who checks the shape — the database once, or every reader, forever?

The honest answer for most catalogues is “some of each,” which is also why PostgreSQL’s jsonb lets you keep the seller in a column and the rarely-changing extras in JSON — the choice made at the finest grain either model allows.