Measuring systems
FoundationsSQL vs NoSQL
Choosing a data store is choosing what the database will do for you — joins, transactions, a schema — versus what you will do yourself for scale or flexibility.
Normalise and the database keeps every fact once and joins on demand; denormalise and reads are one lookup while every write updates every copy.
Try it
Move the dials — the sentence under the picture changes.In plain words
A relational (SQL) database stores facts once, in tables with a fixed shape, and joins them together when you ask. A NoSQL store — the name covers several very different things — usually stores each record as a self-contained blob, in whatever shape you give it, and does not join. The first does more work for you. The second gets out of your way, and hands you the work back when you need what it dropped.
The four kinds of "NoSQL"
The term hides four families that have almost nothing in common except not being relational:
get(key), put(key, value). Blindingly fast, scales sideways forever, and it can only find things by their key. See the key-value store problem.
JSON records with a flexible shape, queryable by their fields, indexed. The natural fit when a record really is one thing — a product with its variants, an order with its lines.
Rows with a partition key and a sort key; enormous write throughput; queries only along the sort key. Time series, event logs, anything write-heavy that reads by range within a partition.
Nodes and edges, for questions like "friends of friends who liked this". A join-heavy query that would be five self-joins in SQL is a traversal here.
What a relational database does for you
- Joins
Store each fact once; combine on demand. No copies to keep in sync.
- Transactions
"Debit A and credit B, or neither." Several rows change together or not at all, even if the process crashes halfway. Most NoSQL stores give you this for one record only.
- A schema
Every row has the columns you declared, of the types you declared. A bug cannot write a string into the price. Migrations are explicit.
- Ad-hoc queries
The question you did not plan for — "average order value by country last quarter" — is a query, not a new table.
When to reach for something else
| Reach for | When | What you give up |
|---|---|---|
| Key-value | Sessions, caches, counters, anything looked up by one key at very high rate | Queries by anything but the key |
| Document | Records that are naturally one blob and rarely joined; a schema that genuinely varies per record | Cross-document transactions and joins |
| Wide-column | Write rates one server cannot take; time-ordered data read by range per key | Any query not along the sort key; ad-hoc questions |
| Search engine (Elasticsearch) | Full-text search, faceting, "fuzzy" matching | It is an index, not a source of truth |
Usually the answer is both: Postgres as the source of truth, plus Redis for sessions and Elasticsearch for search, kept in sync from the primary.
Normalised or denormalised: the real trade
Underneath "SQL vs NoSQL" is a trade you make in either: normalise (one copy of each fact, joined on read) or denormalise (copies of the fact wherever it is read, updated on write). The widget above is this trade with the dials exposed.
SELECT p.id, p.body, u.name, u.avatar_url
FROM posts p JOIN users u ON u.id = p.author_id
WHERE p.id = ANY($1); -- one query; a rename is one row// Read: one lookup, no join.
db.posts.find({ _id: { $in: ids } });
// { _id, body, author: { id, name: "Ana", avatar_url } }
// Write: a rename fans out to every post — do it as a background job.
db.posts.updateMany({ "author.id": 42 }, { $set: { "author.name": "Ana Silva" } });Facts change often, correctness matters, the read is a few tables, and one database can take the reads. The default.
Reads vastly outnumber writes, the join would cross shards or services, and a little staleness is acceptable. The news feed's timeline is denormalisation at scale.
Where it goes wrong
- Picking NoSQL for "scale" at 1,000 rows. You give up joins and transactions for a problem you do not have. Postgres scales further than the app usually gets.
- A document store with relational data. Orders reference products reference categories… and every screen does joins in application code, slowly and wrongly.
- Schemaless as "no schema". There is always a schema; it is just in the code, undocumented, and different in every version that ever wrote a record.
- Denormalising without a plan to update. Copies that are never refreshed are a bug with a delay. Know which job fixes them and how long it takes.
- Two sources of truth. Search index and database both "own" a field and disagree. One owner; everything else is derived and rebuildable.
Take this with you
- The one idea: relational does the joining and the guaranteeing for you; NoSQL hands that back in exchange for shape, speed or scale. Under both is the normalise/denormalise trade.
- In an interview, default to relational for the truth, add a specialised store per specific need, and name what each one gives up.
- At work, find the copied fields. Each one needs an owner and a job that refreshes it, or it is quietly wrong somewhere already.