SQL vs NoSQL
Relational stores enforce relationships with ACID and a schema; NoSQL families (document, key-value, wide-column, graph) trade joins and strong consistency for an access-pattern-first layout that scales out. The senior call is which costs you can pay, not which is modern.
A team launched a social product on a document database with one stated reason: “we don’t want to deal with migrations.” For a year it was bliss — ship a feature, add a field, no ALTER TABLE. Then product asked a question the launch never anticipated: which users follow each other, and what’s trending among their follows? That’s a graph traversal and an aggregation across documents, and the store could do neither without dragging half the dataset into the application and joining it by hand. The schema they “avoided” hadn’t disappeared — it had moved out of the database and into a thousand fragile code paths that each had to remember the shape. They didn’t pick a database; they deferred a decision, and the bill came due with interest.
The relational default and what it buys
By the end of this lesson you’ll know exactly which database property makes that deferred decision a lawsuit risk — and which one you can trade away safely.
A relational database (Postgres, MySQL) stores data as tables of typed rows, and the relationships between them are first-class: you normalize to remove duplication, then reassemble with joins at query time. The engine enforces a schema (columns, types, constraints) and gives you ACID transactions — atomic all-or-nothing writes, consistency against your constraints, isolation between concurrent transactions, durable commits. This is not legacy baggage; it is a contract. The database guarantees that a transfer debiting one account and crediting another either fully happens or fully doesn’t, that a foreign key never dangles, that two concurrent bookings can’t both grab the last seat. That contract is why relational is the correct default for anything with money, inventory, or relationships you care about — you write the invariant once and the engine holds it for every code path forever.
The cost is that the relational model assumes you’ll ask flexible, ad-hoc questions — JOIN whatever you need at read time — and that flexibility has a price at scale: joins across a sharded dataset are expensive (the rows live on different machines), and a single primary handling all writes has a ceiling (the lesson from 04-data-distribution/02-sharding-and-partitioning). Relational is excellent until your write volume or your data size pushes past what one primary plus read replicas can hold.
The NoSQL families: four different bets
Before you reach for a NoSQL store, ask yourself: what is the specific access shape that makes the relational engine the wrong tool here? The answer determines which family you need — and they are not interchangeable.
“NoSQL” is not one thing — it’s four families, each optimized for a different access shape:
- Document (MongoDB, DocumentDB) — stores self-contained JSON-ish documents. You denormalize: embed related data inside one document so a read is a single lookup, no join. Great when an entity is read as a whole (a product page, a user profile).
- Key-value (DynamoDB in its simplest mode, Redis) — a giant hash map: give a key, get a blob. The simplest, fastest model; no querying inside the value. Great for sessions, carts, lookups by a known id.
- Wide-column (Cassandra, Bigtable) — rows keyed by a partition key, with sparse, flexible columns; built to absorb enormous write throughput across a cluster with no single primary. Great for time-stamped event firehoses and feeds.
- Graph (Neptune, Neo4j) — nodes and edges as first-class citizens, so traversals (“friends of friends who like X”) are cheap. Great for social graphs, fraud rings, recommendation paths — exactly the question the hook’s team couldn’t answer.
When you see this list, notice what all four share: each one wins on a specific, known read shape and loses whenever you ask a question it wasn’t designed for.
The unifying theme: NoSQL stores are built to scale out on commodity clusters and to be fast for a known access pattern, by giving up the relational engine’s ability to answer questions you didn’t plan for. AWS frames the family the same way — purpose-built engines, flexible schemas, horizontal scale — in exchange for relaxing some of the consistency guarantees a relational engine enforces.
ACID vs BASE: the consistency you’re trading
The deepest difference isn’t the data shape — it’s the consistency contract. Relational systems aim for ACID. Many distributed NoSQL stores instead offer BASE: Basically Available, Soft state, Eventually consistent. Under BASE, a write may not be visible to every reader immediately; replicas converge “eventually” (usually within milliseconds). This is not sloppiness — it’s the direct consequence of CAP/PACELC (the lesson from 04-data-distribution/04-cap-and-pacelc): a store that stays available during a network partition cannot also be strongly consistent. NoSQL stores that prioritize availability and partition tolerance choose eventual consistency on purpose, and many (DynamoDB, Cassandra) let you opt into stronger consistency per request at a latency and cost premium.
So the SQL-vs-NoSQL question is really: can your domain tolerate eventual consistency on this data? A “like” count that’s stale for 200 ms is fine. An account balance that’s stale for 200 ms during a withdrawal is a lawsuit.
▸Why this works
Why does “we picked Mongo to avoid schemas” so reliably backfire? Because schema-on-write (relational) and schema-on-read (document) don’t remove the schema — they relocate who enforces it. With schema-on-write, the database rejects malformed data at the door, once. With schema-on-read, every reader must defensively handle every historical shape the data has ever taken: the document written in v1 with no country field, the v2 one where country was a string, the v3 one where it became an object. Five years and forty deploys later, your “schema-less” collection has a dozen implicit shapes, and the validation logic is smeared across the codebase instead of declared in one place. Flexibility at write time is a loan against read time, and the interest is paid by every engineer who touches the data afterward.
Access-pattern-first design: the real NoSQL skill
Using NoSQL well is not “relational without joins” — it’s an inverted design process. In relational modeling you design the data (normalize into clean entities), then write whatever queries you need. In NoSQL modeling you design the queries first: list every access pattern the application will have, then shape the storage so each one is a single, cheap lookup. AWS’s DynamoDB guidance is explicit about this — you model the table around access patterns, often collapsing many entity types into one table with carefully chosen partition and sort keys, so that a query returns exactly what one screen needs, pre-joined.
This is genuinely powerful — it’s how a store serves a personalized feed to hundreds of millions of users at single-digit-millisecond latency. But it has a hard edge: it only works if you know the access patterns up front, and it punishes you brutally when a new one appears. A new query that doesn’t match your key design forces either an expensive full scan, a secondary index (more cost, more eventual consistency), or a data migration. Relational’s flexibility is exactly what NoSQL trades away — which is why NoSQL shines for well-understood, high-scale patterns and bites hard for evolving, exploratory products.
Polyglot persistence: stop picking one
The mature answer to “SQL or NoSQL?” is usually both. Polyglot persistence means using more than one store in a single system, each for what it’s best at: the relational primary for orders and money (ACID), a key-value cache for sessions, a wide-column store for the event firehose, a search engine for full-text, a graph store for the social edges. The cost is operational — more systems to run, monitor, and keep in sync — and the dangerous part is keeping data consistent across stores (the dual-write problem, covered in 03-time-series-and-search). But the alternative — forcing every workload through one engine — guarantees you’re using the wrong tool for at least half of them. Senior design is about composing stores, not crowning one.
A fintech startup is building a ledger that records every debit and credit between accounts. The schema is fixed and well-understood. They need atomic multi-row transactions (debit A, credit B) and strong consistency on every read. Which storage choice fits?
▸Common mistake
The most expensive SQL-vs-NoSQL mistake is choosing on scale you don’t have yet. A team reads that Cassandra serves millions of writes per second and reaches for it on day one — when they have 50 writes per second and no idea what their access patterns are. They’ve bought the operational burden and the access-pattern rigidity of a horizontally-scaled store to solve a problem they won’t have for years, while giving up the joins, transactions, and query flexibility that an early-stage product needs most (its requirements change weekly). The honest default is a single relational database until a measured bottleneck — a real write ceiling, a real latency wall — names the specific NoSQL store that solves it. Premature NoSQL is premature optimization wearing a buzzword.
A payments feature must guarantee that debiting one account and crediting another either both happen or neither does, with no intermediate state visible to other transactions. Which store property is non-negotiable here?
A team chose a document database 'to avoid schemas.' Two years in, every read path is littered with checks for missing and differently-typed fields. What actually happened?
In relational modeling you design the data first and query freely; in NoSQL modeling you invert it — you design the _______ first, then shape the storage so each one is a single cheap lookup, which is why a new, unplanned one is expensive.
- 01Name the four NoSQL families and the access shape each is built for.
- 02What is the ACID-vs-BASE trade, and how does CAP force it?
- 03Why does access-pattern-first design cut both ways, and what is polyglot persistence?
Relational is the correct default: a schema and ACID transactions let you declare an invariant once and have the engine enforce it for every code path forever — essential for money, inventory, and relationships you care about. Its cost is that joins across a sharded dataset are expensive and a single write primary has a ceiling. NoSQL is four distinct bets — document (denormalized, read-as-a-whole), key-value (hash map, lookup by id), wide-column (huge write throughput, no single primary), and graph (cheap traversals) — all built to scale out and be fast for a known access pattern by trading away ad-hoc query flexibility and, usually, strong consistency (BASE, forced by CAP). The “we picked Mongo to avoid schemas” failure is the tell: the schema doesn’t vanish, it moves from one enforced write-time check to defensive re-validation in every reader. NoSQL’s real skill is access-pattern-first design — powerful at known scale, brutal when patterns change. The senior answer is rarely one store: polyglot persistence composes the right engine per workload, paying for it in operational surface and the cross-store dual-write problem — chosen by measured cost, not by which database is in fashion. Now when you face a “SQL or NoSQL?” question in a design review, your first move is to name the specific access pattern and consistency tolerance — and let that answer the question for you.
Practice
Start at the top. Tasks go easiest → hardest: recall a fact, apply it to a case, then a senior-level stretch. Open one, attempt it, then reveal.
Something unclear?
Ask a question about this lesson. Questions are anonymous and go straight to the author to make the lesson better.
Apply this
Put this lesson to work on a real build.