SQL vs NoSQL

The real fork is not tables vs. no tables — it is where the data gets assembled and who enforces its shape. A relational engine stores each fact once in a normalized table and re-assembles the answer at read time with a JOIN, while the server guarantees the schema and referential rules on every write. A document/key-value store does the opposite: it stores the answer pre-assembled under one key so a read is a single fetch, and it pushes the job of keeping that shape correct onto your application code. Almost every practical trade-off below — joins, transactions, scaling, evolution — falls out of that one difference.

Take one concrete row of business: rendering the order page for order #4471 (one customer, three line items, a total). Here is the same data in both shapes.

Worked trace: the same read and the same write, both ways

Follow the two operations the order service actually runs. The numbers are representative of a warm cache on a mid-size instance — orders of magnitude, not benchmarks.

Read that table twice: NoSQL wins the read by pre-joining, and pays for it on the write by giving up the free cross-entity transaction. That single sentence is 80% of the decision.

Correcting a claim you will hear everywhere

"SQL schema changes require modifying the entire database structure" is false and it drives people to the wrong database. In modern PostgreSQL, ALTER TABLE ADD COLUMN note text — or even with a constant default since PG 11 — is a metadata-only change: it updates the catalog in milliseconds and does not rewrite existing rows. What actually hurts is a narrower set of operations: changing a column's type, adding a NOT NULL with a volatile default, or (pre-PG-11) any default, which forces a full table rewrite and holds an ACCESS EXCLUSIVE lock. So the honest statement is: additive schema evolution in SQL is cheap and online; rewriting evolution on a large hot table is the risk you plan a migration around. NoSQL's edge is not "no schema" — it is "no coordinated migration step," because old and new document shapes coexist and the app reconciles them on read (schema-on-read). You have simply moved the schema from the database into your code, where nothing enforces it for you.

The six-dimension decision matrix

Each row is a signal you can observe in your own requirements, then where it points and why.

Pitfalls

• Choosing NoSQL for "flexibility," then querying it like SQL. You picked a key-value store, and now the dashboard needs "orders by region last month." There is no index for it, so it becomes a full scan across every shard — slow and expensive. NoSQL isn't schema-free, it is query-pattern-locked: you must know the queries before you model the keys.
• Assuming your document store gives you cross-document transactions. The oversell bug in the trace above is the classic production incident: two writes to two partitions, one succeeds, one doesn't, inventory is now wrong. Multi-document transactions exist but are opt-in, size-limited, and slower — many teams discover this only after the first double-sold item.
• Fearing SQL schema changes and denormalizing prematurely. Teams pick NoSQL to "avoid migrations," then hand-roll consistency between duplicated copies of the same fact — reinventing foreign keys badly. Additive SQL migrations are online and millisecond-cheap; only large-table rewrites need a real playbook.
• Embedding unbounded arrays in a document. An order.items array is bounded; a user.activityLog that grows forever will blow past the document size cap (e.g., 16 MB in Mongo, 400 KB item limit in DynamoDB) and turn every read of that user into a large fetch.
• Treating "NoSQL = web scale" as a default. A single well-indexed Postgres instance comfortably serves tens of thousands of writes/s and terabytes. Most products never outgrow it; reaching for a distributed store early buys operational cost and a harder data model for scale you don't have.

When to use it / when NOT — and the trade-off

Choose SQL (PostgreSQL, MySQL) when the data is relational and queried from many angles, correctness across multiple rows is non-negotiable (payments, inventory, ledgers, bookings), your reporting needs are open-ended, and one primary can hold the write load. You gain enforced invariants and arbitrary queries for free; you pay with a write ceiling on a single node and the need to plan large migrations.

Prefer NoSQL when there is one dominant, known access pattern over self-contained records, write volume or dataset size exceeds a single node, and eventual consistency is acceptable — and pick the sub-type by access shape: key-value/document (DynamoDB, Mongo) for get-by-key, wide-column (Cassandra) for huge write-heavy time series, graph (Neo4j) for deep relationship traversals that would be JOIN-explosions in SQL. You gain flat latency at massive scale and painless additive shape changes; you pay with lost cross-entity transactions, query patterns frozen at design time, and consistency logic you now own in application code.

Choose SQL when in doubt. Its constraints (schema, single primary) surface problems early and loudly; NoSQL's freedom lets bad data and lost writes accumulate silently until they surface as a customer-facing incident. A very common senior move is the hybrid: Postgres as the system of record for anything transactional, plus a NoSQL store (or a cache/search index) for the one high-scale, denormalized read path — SQL owns truth, NoSQL owns scale.

Takeaways

• The core difference is assemble-at-read (SQL: JOIN, one copy of each fact) vs. assemble-at-write (NoSQL: pre-joined document, one fetch); every other trade-off follows.
• SQL gives you multi-row ACID transactions and arbitrary queries for free; NoSQL gives you near-linear write scaling and flat latency — at the cost of those transactions and open-ended queries.
• "NoSQL is schema-free" really means "schema lives in your app"; and additive SQL schema changes are cheap and online — evolution is rarely the deciding factor people think it is.
• Default to SQL, reach for NoSQL for a specific high-scale known-pattern read path, and don't be afraid to run both.

Re-authored and deepened for this guide, drawing on Martin Kleppmann, Designing Data-Intensive Applications (Ch. 2–3, storage & encoding); the PostgreSQL manual on ALTER TABLE (metadata-only column adds since 11) and locking; Amazon DynamoDB and MongoDB documentation on item/document size limits and multi-item transactions; and Alex Xu, System Design Interview (database selection). Latency and throughput figures are representative orders of magnitude, not benchmarks.