System design · SQL vs NoSQL
The two most-argued-about databases, side by side. How they store data, how each handles ACID, what only one of them can do, when to choose which, and the SQL-vs-NoSQL interview questions with answers. Companion to S4 · Data at Scale and the data stores cheat sheet.
It is not SQL vs NoSQL, it is: match the data model to how the data is read and written. Postgres shines when you have invariants and ad-hoc queries; Mongo shines when you read one aggregate as a unit and the shape is still moving. Pick the shape, not the brand.
Same order, stored two ways. Postgres normalizes: each fact in one place, across tables, joined at read time. Mongo embeds: store the whole aggregate you read together as one document.
Read an order: JOIN three tables. Change a customer’s email once, everywhere sees it. The database enforces “no order without a customer.”
{
"_id": "order_42",
"customer": { "id": "u7", "email": "a@b.com" },
"items": [
{ "product": "Pen", "qty": 2 },
{ "product": "Pad", "qty": 1 }
],
"total": 1299
}
Read an order: one fetch, no joins. But the customer’s email is copied into every order, so changing it means touching many documents.
Normalizing keeps one source of truth and pays a join on every read. Embedding makes reads one round trip and pays a fan-out write (and possible drift) when shared data changes. Choose by which you do more of.
The same dimensions, two answers. No row is “better,” each is a different bet.
| Dimension | PostgreSQL | MongoDB |
|---|---|---|
| Data model | tables & rows, normalized | BSON documents, often embedded |
| Schema | enforced (DDL, migrations) | flexible (app-enforced) |
| Joins | native, any direction | $lookup, limited; embed instead |
| Transactions | full ACID, multi-row, default | multi-doc since 4.0, costlier |
| Query language | SQL | MQL + aggregation pipeline |
| Invariants | FK, unique, check, for you | in-document only, no cross-doc FK |
| Ad-hoc queries | anything, unplanned | best on planned access patterns |
| Scale writes | vertical, then Citus / partition | sharding built in |
| Scale reads | read replicas | replica-set secondaries |
| Consistency | strong (single primary) | tunable (read / write concern) |
| Best fit | invariants, relationships, ad-hoc | aggregate reads, evolving shape, scale |
Postgres’ JSONB narrows the gap: it can store documents inside a relational table. It is not a document database, but it means “I might want documents” is rarely a reason to leave Postgres on its own.
Postgres gives you ACID by default. MongoDB can give you most of it too, you just opt in, and you model for it.
| Guarantee | PostgreSQL | MongoDB |
|---|---|---|
| Atomicity | BEGIN…COMMIT, all-or-nothing across rows | a single document is always atomic; multi-document via transactions (4.0+) |
| Consistency | foreign keys, unique & check constraints | schema validation ($jsonSchema); no cross-document foreign keys |
| Isolation | MVCC, up to serializable | snapshot isolation inside a transaction |
| Durability | write-ahead log + fsync | write concern w:"majority", j:true for the journal |
Single-document writes are atomic with zero ceremony, that is the original NoSQL guarantee. So if you embed the things that must change together into one document, you get atomicity for free. Reach for multi-document transactions sparingly; for durability add w:"majority". And the honest tell: if you need multi-document transactions everywhere, your data probably wanted a relational model.
Enforce an invariant across entities for you, for example “every order row points at a real customer” or “this email is unique across the whole dataset.” In Mongo that rule lives in your application code, and a bug there lets bad data in. That is the real meaning of “Postgres protects the invariant.”
The capabilities that actually decide it. Pick the side whose list you cannot live without.
Postgres can, Mongo can’t (easily)
Mongo can, Postgres finds awkward
Caveat: Postgres JSONB closes much of this gap (documents, flexible fields, GIN indexes). Sharding and effortless schema-drift are the parts it truly cannot match.
Answer what you need, and the choice falls out. When unsure, the safe default is Postgres; earn the exit.
| If you need… | Choose | Why |
|---|---|---|
| Strong invariants / transactions across entities | PostgreSQL | it enforces them for you |
| Ad-hoc queries you can’t predict | PostgreSQL | joins + SQL, no pre-modeling |
| Relationships are the point | PostgreSQL | joins, foreign keys |
| You read one aggregate as a unit | MongoDB | one document, one read |
| The schema is still moving fast | MongoDB | no migration to change shape |
| Easy built-in horizontal sharding | MongoDB | sharding is first-class |
| You’re not sure yet | PostgreSQL | the boring, correct default |
Default to Postgres when…
You have money, orders, users, or anything with rules that must hold; you can’t predict every query; relationships matter; or you just don’t know the shape yet. It carries you far further than people expect, and JSONB covers most “but I want documents” cases.
Reach for Mongo when…
A screen always loads one self-contained aggregate (a product, a user profile, an event); the shape changes constantly; cross-entity invariants are weak; and you want sharding to be a checkbox, not a project. Then the document model is a genuine fit, not a shortcut.
The questions that actually come up, with the short answer and the senior nuance that scores the point. Click to expand.
w:"majority", j:true).
Senior nuance: “The idiomatic move is to embed what changes together into one document so you never need a multi-document transaction. If you need them everywhere, the data wanted a relational model.”$lookup in the aggregation pipeline (a left outer join), embed the related data so no join is needed, or join app-side in code. Joins are not Mongo’s strength; you model around the read.
Senior nuance: “If I find myself reaching for $lookup on every query, that is a signal the data is relational and I picked the wrong store.”$jsonSchema validation.
Senior nuance: “Schema-less just means the database stops catching your mistakes, so the discipline has to live in code and review instead.”Companion to S4 · Data at Scale and the data stores cheat sheet. Further reading: Postgres transactions · MongoDB transactions · MongoDB data modeling · Postgres JSONB.