Databases: SQL vs NoSQL — Part 1: Foundations

maang.io System Design Series

What is this?

Walk into an old, well-run office and you'll see two ways people keep records.

There's the accountant's ledger: ruled lines, fixed columns, every entry the same shape — date, account, debit, credit. Nothing goes in a row unless it fits the columns, and because everything is uniform you can add it all up, cross-check it, and trust the totals. That discipline is annoying when you're in a hurry and liberating when the numbers have to be right. That's a relational (SQL) database.

Then there's everything else people actually use to get work done: a drawer of labeled folders you grab by name without reading them (key-value), a filing cabinet of paper forms where no two forms have exactly the same fields filled in (document), a giant spreadsheet so wide you only ever look at a few columns at a time (wide-column), and a detective's corkboard of photos connected by string (graph). None of these enforce the ledger's rigid columns. They each trade some of that discipline for a different superpower — speed, flexibility, scale, or the ability to follow connections.

The one-line idea: "SQL vs NoSQL" isn't a fight between an old database and a new one — it's a choice of data model, and the right model is the one whose natural shape matches the questions your application actually asks.

Across all four parts we'll keep coming back to the ledger and its rivals — the office records room — because every architectural decision in this topic is really a question of which way of keeping records fits this job. Let's earn the vocabulary.

💡 Scope note for this topic: we're focused on data models and how to choose a database. The deep mechanics of how databases scale and survive failure — replication, sharding, indexing internals (B-trees vs LSM-trees), and storage engines — get their own dedicated topics in tier 201. Here we'll name them and stay at altitude.

1. The relational model: the ledger, formalized

The relational model came out of a 1970 paper by Edgar Codd, and its idea is deceptively plain: store data in tables (relations), where each row is one record and each column is one attribute with a fixed type. A users table has columns id, email, created_at; every user row has exactly those columns, exactly those types.

Three ideas make this more than just "a grid":

Schema first. You declare the shape — columns, types, constraints — before you insert data. The database refuses anything that doesn't fit. This is the ledger's ruled lines: rigid on purpose.
Keys and relationships. Each row has a primary key (a unique handle, like user_id). Other tables point at it with a foreign key. An orders table doesn't copy the customer's name and address into every order — it stores user_id and references the users table. Data lives in exactly one place.
Joins. Because data is split across tables linked by keys, you reassemble it at query time with a JOIN: "give me each order together with the user who placed it." The relational model's bet is that you'd rather store data once and pay to stitch it together on read than copy it everywhere.

Relational model: users and orders tables linked by foreign key user_id

The query language is SQL (Structured Query Language) — declarative, meaning you describe what you want, not how to fetch it, and the database's query planner figures out the rest:

SELECT u.email, SUM(o.total) AS spent
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.email;

💡 "Relational" is named after the relation (the mathematical name for a table), not after the relationships between tables. A common interview slip — it's worth getting right.

2. What SQL actually guarantees (and why people pay for it)

The reason the relational model has survived fifty years isn't nostalgia — it's the guarantees. Four matter for our purposes:

Strong schema / data integrity. Types, NOT NULL, uniqueness, foreign-key constraints — the database enforces that your data stays valid. You cannot create an order for a customer who doesn't exist. The structure of your data is a contract the engine keeps for you.
ACID transactions. A group of changes either all happen or none do, and concurrent transactions don't corrupt each other. This is the property that makes moving money safe. We only touch it here — the ACID Transactions chapter is its sibling and goes deep on what each letter means and how it's enforced.
A powerful, standard query language. Joins, aggregations, sub-queries, window functions — you can ask rich, ad-hoc questions the schema designer never anticipated, without changing your data layout.
Decades of tooling and operational knowledge. Every ORM, BI tool, and ops playbook speaks SQL. That's not a technical guarantee but it's a real one in practice.

The mental model: a relational database trades write-time convenience and raw scale for correctness and queryability. You do extra work up front (design a schema, normalize) and the database pays you back with safety and flexibility of questioning.

⚠️ The classic over-correction is to hear "NoSQL scales better" and conclude SQL is obsolete. It isn't. Postgres and MySQL run enormous systems. The honest framing is fit, not generation — which is the whole point of this topic.

3. Why NoSQL appeared at all

By the mid-2000s, companies like Google, Amazon, and Facebook hit two walls the ledger model strained against:

Scale. A single relational server can only grow so big (vertical scaling has a ceiling — more on that in Part 2). Spreading a heavily-joined, strongly-consistent relational database across hundreds of machines is genuinely hard, because joins and transactions want data to be close together and coordinated.
Shape. A lot of real data doesn't fit neat uniform columns. User profiles where everyone fills in different fields, deeply nested product catalogs, a social graph of who-follows-whom. Forcing these into rigid tables felt like fighting the data.

"NoSQL" was the umbrella for databases that relaxed the relational constraints — usually a fixed schema, joins, and/or strict ACID — in exchange for flexible data shapes and easier horizontal scale. The name is a historical accident (it originally meant "no SQL," now widely read as "Not Only SQL"). The useful thing isn't the label — it's that NoSQL is not one thing. It's four distinct families, each solving a different problem.

4. The four NoSQL families

Here's the whole landscape in one picture. We'll then take each family in turn.

Databases tree: SQL vs NoSQL and the four NoSQL families

Key-value: the drawer of labeled folders

The simplest possible database. You hand it a key, it hands you back an opaque value (a string, a blob, a serialized object). It doesn't look inside the value; it just stores and retrieves by exact key, extremely fast. No joins, no querying by content — you must know the key.

Use it when: you need blistering-fast lookups by a known key — sessions, feature flags, caches, leaderboards, rate-limit counters. Examples: Redis, DynamoDB in its simplest mode. (Redis is the workhorse of the Caching chapter — the two topics are close cousins.)

Document: the cabinet of paper forms

Stores documents — self-contained JSON-like objects that can be nested and that don't all have to share the same fields. Crucially, unlike key-value, the database can see inside the document, so you can query and index on fields within it. A users collection can hold one user with a phone and another without, one with three addresses and one with none.

Use it when: your data is naturally object-shaped and self-contained, and the shape varies or evolves — user profiles, product catalogs, content/CMS, event payloads. Examples: MongoDB, Couchbase, DynamoDB (document mode).

Wide-column: the enormous sparse spreadsheet

Data is stored in rows, but each row can have a huge and varying number of columns, and the engine is built to read a few columns across a colossal number of rows, fast, while spread over many machines. Think a table with billions of rows and millions of possible columns where any given row only fills a handful. Built for write-heavy, massive-scale workloads.

Use it when: you have enormous write volume and well-known access patterns at scale — time-series, event logging, sensor/IoT data, messaging history. Examples: Cassandra, HBase, Google Bigtable.

Graph: the detective's corkboard

Stores nodes (entities) and edges (relationships) as first-class citizens, and is optimized to traverse those connections cheaply — "friends of friends of friends," "shortest path between A and B." In a relational database, those many-hop relationship queries mean many expensive self-joins; a graph database makes following an edge a constant-time hop.

Use it when: the relationships are the point — social networks, recommendation engines, fraud rings, knowledge graphs, network/dependency maps. Examples: Neo4j, Amazon Neptune.

5. Rigid schema vs flexible schema — the real dividing line

If you internalize one distinction from this chapter, make it this one, because it underlies nearly every SQL-vs-NoSQL decision.

Schema-on-write (SQL) vs schema-on-read (document NoSQL)

Schema-on-write (relational): the shape is enforced when you write. The database is the guardian of structure. Cost: changing the shape means a migration; flexibility is low. Benefit: every reader can trust the data is well-formed.
Schema-on-read (document/key-value): you can write almost any shape; the reading code is responsible for making sense of it. Cost: the database guarantees nothing about structure, so a typo'd field or a missing one is your problem at read time. Benefit: you move fast and let the shape evolve.

⚠️ "Schemaless" is the most over-sold word in databases. NoSQL doesn't remove the schema — it moves the schema from the database into your application code. The structure still exists and still has to be honored; the only question is who enforces it. A senior engineer never says "we don't have a schema"; they say "we have an implicit schema enforced in the service layer."

Here's the same user, both ways:

-- SQL: shape declared up front, enforced forever
CREATE TABLE users (
  id    BIGINT PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  phone TEXT            -- nullable, but the column must exist
);

// Document: two users, two shapes, both legal
{ "id": 1, "email": "ada@x.io", "phone": "555-0100" }
{ "id": 2, "email": "lin@x.io", "addresses": [ {"city": "Pune"} ] }

Neither is "better." The SQL version guarantees every user has the same fields and a unique email — at the cost of a migration to add a field. The document version lets these two users diverge freely — at the cost of your code handling "what if phone is missing?" everywhere.

Key takeaways

SQL vs NoSQL is a choice of data model, not a choice of era. The relational ledger is rigid on purpose and pays you back in integrity, ACID transactions, and a powerful query language.
NoSQL is four families, not one thing: key-value (lookup by known key), document (self-contained, queryable objects), wide-column (massive-scale sparse rows), and graph (relationships as first-class citizens).
Each family has a natural fit. Match the model's natural shape to the questions your app asks: sessions → key-value, evolving profiles → document, huge write streams → wide-column, many-hop relationships → graph.
The deepest divide is schema-on-write vs schema-on-read. "Schemaless" doesn't delete the schema; it relocates it from the database into your application code — and someone still has to enforce it.
We're staying at the data-model altitude. How these databases replicate, shard, and index to achieve their scale is the subject of dedicated 201 topics; Part 2 introduces those ideas only at a high level.

Foundations