SQL vs NoSQL — Part 1: Foundations & Core Concepts

maang.io System Design Series

---

What is this?

Imagine a town with one magnificent central library. Every book is catalogued on strict index cards — same fields on every card (title, author, year, shelf) — and a superb cross-reference system lets a librarian answer almost any question you invent on the spot: "every book by an author born in Paris, published after 1990, currently on loan." One authoritative copy, one meticulous catalogue, endless ways to slice it. That library is a relational (SQL) database.

Now the town becomes a country, then the whole internet. Millions of people want books at the same second, and there are billions of books. One building — however grand — physically cannot hold them or serve the crowd. So the town starts opening specialized branches, each optimized for one job: a coat-check where you hand over a ticket and instantly get your bag back; a room where every patron has one fat personal folder holding everything about them; a colossal ledger split across many warehouses; a detective's cork-board of who-knows-whom. Those branches are the NoSQL family. They each gave up the single strict catalogue to win something the central library couldn't give: raw scale, or speed, or flexibility.

That's the whole debate in one image. SQL is the strict, brilliant central library. NoSQL is the set of specialized branches you open when one library can't cope. Neither is "better" — they make different bets. This chapter is about seeing which bet fits which problem.

The one-line idea: SQL databases optimize for a rigid, normalized schema you can query any way you like, with strong transactional guarantees on one authoritative copy. NoSQL databases relax one or more of those — the schema, the joins, or the single copy — to buy horizontal scale, flexibility, or per-access-pattern speed. The right question is never "which is better?" but "which set of costs fits my workload?"

We'll carry the library-and-branches picture through all three parts.

---

1. Why the split exists — what was painful before

For roughly thirty years (think 1980s–2000s) the relational database was the database. Postgres, MySQL, Oracle, SQL Server — one tool, and you bent every problem to fit it. It's a genuinely great tool: the relational model (tables of rows and columns, related by keys) plus SQL (a declarative query language) plus ACID transactions is one of computer science's great successes.

The pain arrived with the web's scale explosion. Two things broke at once:

• You couldn't scale writes past one machine easily. A classic relational database runs on a single primary node that owns the authoritative copy. You can make that machine bigger (vertical scaling — more CPU, more RAM), and you can add read replicas to spread out reads, but writes still funnel through the one primary. When Amazon or Facebook needed to absorb millions of writes per second, "buy a bigger single box" ran out of road. There's a ceiling, and it's expensive to approach.
• The rigid schema became a tax. Relational tables demand you declare every column and type up front (schema-on-write), and changing that shape later (ALTER TABLE on a billion-row table) can be a scary, locking, multi-hour operation. Fast-moving products with messy, evolving, semi-structured data (a user profile that grows new fields every sprint) found the ceremony painful.

The NoSQL movement (the name means "Not Only SQL," not "no SQL ever") was the industry's answer: a family of stores that scale horizontally by design — spread data across many cheap machines (sharding, the partitioning topic in this tier) — and often relax the schema so data can be flexible. Amazon's Dynamo paper (2007) and Google's Bigtable paper (2006) lit the fuse; Cassandra, MongoDB, DynamoDB, Redis, HBase, and Neo4j followed.

The catch — and it's the whole reason this is an interview topic — is that horizontal scale and flexibility aren't free. To spread writes across many machines and stay available during failures, most NoSQL stores loosen the strong consistency and rich querying that the single-copy relational world gave you for free. That trade is the CAP theorem made concrete (see the CAP chapter in the 101 tier), and it's the tension every question in Part 3 circles back to.

---

2. Core concepts & vocabulary — the mental model

Five ideas do almost all the work. Get these and the rest follows.

2.1 The relational model — tables, keys, and normalization

A SQL database stores data as tables (rows and columns). The magic is normalization: you store each fact once, in one place, and link tables by keys instead of copying data around. A users table, an orders table, an order_items table — an order points at a user by user_id rather than duplicating the user's name and address into every order.

To answer "show this user's orders with their line items," the database joins these tables back together at query time. Normalization keeps data consistent (change an address in one row, everyone sees it) and storage lean (no duplication). The price is that reads must do the join work — reassembling the pieces on every query.

2.2 The four NoSQL families

"NoSQL" isn't one thing — it's four distinct shapes. Knowing them by name (and one real product each) is table stakes in an interview:

• Key-Value — the coat-check. You hand over a key, you get back an opaque value; the store doesn't look inside. Blindingly fast, dead simple, no querying by value. Redis (in-memory, see the Redis topic), DynamoDB at its core.
• Document — the personal folder. The value is a structured document (usually JSON/BSON) the database can look inside and index. You keep everything about one entity embedded together. MongoDB, Couchbase.
• Wide-Column — the giant ledger split across warehouses. Rows are addressed by a key and can have millions of sparse columns; built to shard across hundreds of nodes and soak up writes. Cassandra (its own topic in this tier), HBase, Bigtable.
• Graph — the detective's cork-board. Data is nodes and edges, and the edges are first-class; built to answer "how is A connected to B, three hops away?" cheaply. Neo4j (its own topic), JanusGraph.

💡 A useful rule of thumb: key-value and document stores are for known-key access to one entity at a time (the OLTP web workload); wide-column is for massive write volume keyed by a partition; graph is for relationship traversal. If your access pattern doesn't match one of those shapes, you probably want SQL.

2.3 ACID vs BASE — the consistency bet

This is the single most-tested contrast. SQL databases classically guarantee ACID transactions:

• Atomicity — all-or-nothing (the whole transaction commits, or none of it does).
• Consistency — every transaction moves the DB from one valid state to another (constraints hold).
• Isolation — concurrent transactions don't step on each other (they appear serial).
• Durability — once committed, it survives a crash (this is the write-ahead log — see the WAL topic and the ACID chapter in 101).

Many NoSQL stores instead offer BASE:

• Basically Available — the system stays up and answers, even during failures.
• Soft state — the data may be in flux; replicas can temporarily disagree.
• Eventual consistency — given no new writes, all replicas converge to the same value... eventually.

BASE is what you accept in exchange for staying available and writable across many machines and regions. It's the AP side of the CAP theorem: under a network partition, a BASE store keeps serving (possibly stale) data rather than refusing the request.

⚠️ Don't over-simplify this into "SQL = consistent, NoSQL = eventual." The line has blurred hard. DynamoDB and Cassandra offer tunable consistency (you can pay for strong reads per query). MongoDB does multi-document ACID transactions since 4.0. Distributed SQL databases (Spanner, CockroachDB) give you ACID and horizontal scale. The honest framing is: strong consistency is cheap and default on a single machine, and increasingly expensive as you distribute — and each store lets you dial where you land.

2.4 Schema-on-write vs schema-on-read

SQL is schema-on-write: you declare the shape before you insert, and the database rejects anything that doesn't fit. That's a feature — it's a guardrail that keeps garbage out and lets the optimizer plan queries. NoSQL document/KV stores are typically schema-on-read (or "schemaless," a slight misnomer): you write whatever shape you like, and the application interprets the structure when it reads. Flexible and fast to iterate, but the discipline moves from the database into your code — nothing stops two documents in the same collection from disagreeing about what status means.

2.5 Scaling: vertical + replicas vs horizontal sharding

The last core idea, and the one that historically drove teams to NoSQL:

• SQL, classically: scale up (bigger box) and add read replicas for read load — but writes still go through one primary. Scaling writes means sharding by hand, which is real work.
• NoSQL, by design: scale out — partition the keyspace across many nodes (consistent hashing, the partitioning topic), each owning a slice, so both reads and writes spread horizontally. Add nodes, add capacity, roughly linearly.

We unpack the machinery behind all of this in Part 2.

---

3. How you actually use it — a concrete taste

The same data, "a user and their two orders," expressed both ways. First relational — normalized, queried with a join:

-- SQL: store each fact once, join at read time
SELECT u.name, o.order_id, o.created_at
FROM users u
JOIN orders o ON o.user_id = u.user_id
WHERE u.user_id = 42;

Now the same thing in a document store — the orders embedded inside the user, read back in a single key lookup, no join:

// Document (MongoDB): everything about the user in one self-contained doc
db.users.insertOne({
  _id: 42,
  name: "Ada",
  orders: [
    { order_id: 1001, created_at: "2026-07-01", items: [/* ... */] },
    { order_id: 1002, created_at: "2026-07-03", items: [/* ... */] }
  ]
});
// read the whole user + orders in ONE lookup — no join
db.users.findOne({ _id: 42 });

Notice the fundamental trade already visible here. The relational version stores each order once and can slice it any way later ("all orders on July 1st across all users" is trivial). The document version makes this one access pattern — "give me a user with their orders" — a single blazing-fast read, but "all orders on July 1st" now means scanning every user document. SQL optimizes for query flexibility; NoSQL optimizes the access patterns you designed for, and punishes the ones you didn't. That sentence is the heart of the whole topic.

---

4. When to reach for each — and when not

Reach for SQL when you need real transactions (money, inventory, anything where a half-applied change is a disaster), ad-hoc and analytical queries you can't predict up front, joins across many entities, strong consistency by default, or — very common and underrated — when the data simply fits comfortably on one well-provisioned machine. A single modern Postgres box handles tens of thousands of transactions per second and terabytes of data. Most products never outgrow it, and "we started with Postgres" is rarely the wrong first answer.

Reach for NoSQL when the workload's shape matches a family: enormous write throughput keyed by a partition (wide-column), sub-millisecond known-key lookups or caching (key-value), a flexible evolving document you always read whole (document), or deep relationship traversal (graph) — and you've genuinely outgrown a single primary or need multi-region always-on availability.

The honest reality is polyglot persistence — real systems use several of these at once. A typical product: Postgres for the transactional core (users, orders, payments), Redis for caching and sessions, Cassandra or DynamoDB for the high-volume activity feed, Elasticsearch for search, and maybe Neo4j for the "people you may know" graph. The Staff-level skill isn't picking the database — it's decomposing a system into the few stores whose shapes fit its parts, and owning the cost of running each. We'll design exactly that in Part 3.

🚫 The classic junior mistake in an interview is reaching for NoSQL because it sounds "scalable" and "modern," on a problem that screams for a join and a transaction. Reaching for MongoDB to store financial ledgers, or refusing Postgres for a product with 10,000 users, both signal pattern-matching on buzzwords instead of reasoning about the workload. Defaulting to a relational database and justifying the move away from it is the senior move.

---

Key takeaways

1. SQL = the strict central library: a normalized, rigid schema you can query any way you like, with ACID transactions on one authoritative copy. It trades write-scalability and schema flexibility for query power and correctness.
2. NoSQL = specialized branches in four families — key-value (coat-check), document (personal folder), wide-column (sharded ledger), graph (who-knows-whom board) — each relaxing the schema, the joins, or the single copy to buy horizontal scale, speed, or flexibility.
3. ACID vs BASE is the consistency bet, and it's a dial, not a binary anymore: strong consistency is cheap on one machine and gets expensive as you distribute, and modern stores let you choose where you land per query.
4. SQL optimizes for query flexibility; NoSQL optimizes the access patterns you designed for — and punishes the ones you didn't. Model query-first in NoSQL; model the data-first in SQL.
5. Default to relational; justify the move away from it. Real systems are polyglot — the skill is matching each part of a workload to the store whose shape fits, not crowning one winner.

Next, Part 2 goes under the hood: the B+tree that makes SQL's flexible queries possible versus the LSM-tree behind write-heavy NoSQL, how a join actually executes, the machinery of an ACID commit (WAL, MVCC, locks, two-phase commit), and how a sharded write really travels — with worked numbers on join cost and scaling ceilings.