#SQL vs NoSQL: the real trade-offs
Added"SQL vs NoSQL" is a misleading label. MongoDB has a query language and joins ($lookup); MySQL stores JSON documents; PostgreSQL does both very well. The differences that actually decide a design are about the data model (normalised tables you join at read time, or aggregates stored together), where the schema is enforced (by the database on every write, or by your code), what transactions and consistency you get by default, and how the system scales out.
A mental model: a relational database is a filing cabinet with cross-references. Each fact lives in exactly one drawer — the customer's name in customers, the product's price in products — and any question can be answered by following the references. A document database is a folder per order with everything about that order inside: you grab one folder and have it all, but the customer's name is now written in every folder they own, and changing it means visiting all of them.
| Question | Relational (MySQL, PostgreSQL) | Document (MongoDB) |
|---|---|---|
| Shape of the data | rows in normalised tables, related by keys | nested documents shaped like your objects |
| Schema | enforced on write (types, NOT NULL, ENUM, foreign keys, CHECK) | flexible by default; optional $jsonSchema validation |
| Reading an aggregate | join at read time | one document read, no join |
| Changing shared data | update one row | update every document that copied it |
| Ad-hoc questions you didn't plan for | SQL handles almost anything | easy if the data is embedded the right way, otherwise $lookup and pipelines |
| Transactions | multi-row ACID is the default model | single-document atomic; multi-document transactions since 4.0 |
| Scaling writes | one primary; sharding is external or manual (Vitess, Citus) | built-in sharding |
SELECT o.id, c.name AS customer, o.status, p.name AS product, oi.quantity, oi.unit_price
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.id = 1;+----+----------+---------+---------------+----------+------------+
| id | customer | status | product | quantity | unit_price |
+----+----------+---------+---------------+----------+------------+
| 1 | Rohit | shipped | ThinkBook 14 | 1 | 899.00 |
| 1 | Rohit | shipped | USB-C Charger | 2 | 29.00 |
+----+----------+---------+---------------+----------+------------+db.orders.findOne({ _id: 1 }){
_id: 1,
status: 'shipped',
ordered_at: ISODate('2025-02-01T10:00:00.000Z'),
customer: { id: 1, name: 'Rohit', city: 'Toronto' },
items: [
{
product_id: 1,
name: 'ThinkBook 14',
category: 'Laptops',
qty: 1,
unit_price: 899
},
{
product_id: 7,
name: 'USB-C Charger',
category: 'Accessories',
qty: 2,
unit_price: 29
}
]
}What’s happening
- Both databases hold the site's sample data in
ch25: MySQL has the normalised tables, and the MongoDBorderscollection was built from them by a small Node script, one document per order with its customer and items embedded. - MySQL assembles order 1 at read time by joining
orders → customers,orders → order_items → products. The result is flat: one row per item, with the order and customer columns repeated on each row. Your code (or an ORM) turns those rows back into one object. - MongoDB stores the order already assembled.
findOnereturns the nested object your app wants, in one read, with no join. That's the document model's main win: data that's read together is stored together. - The price of that win is in the embedded copies:
customer.name, the product names and the category names now live inside every order. MySQL keeps each of those facts once. Both choices are reasonable — order lines are often meant to be a snapshot of what was sold at the time. - Neither result is "faster" in any meaningful way at this size — "Joins aren't the slow part" below measures it. The real differences show up when the data changes and when new questions arrive.
AdvancedMyth: NoSQL scales and SQL does not
AdvancedJoins aren't the slow part
A common claim is that documents are faster because joins are expensive. Measured on the same data, 2,000 reads each, warm connections:
import mysql from 'mysql2/promise';
import { MongoClient } from 'mongodb';
const db = await mysql.createConnection({ host: '127.0.0.1', port: 3307, user: 'root', password: 'study', database: 'ch25' });
const mongo = await MongoClient.connect('mongodb://127.0.0.1:27018/?directConnection=true');
const orders = mongo.db('ch25').collection('orders');
const SQL = `SELECT o.id, c.name AS customer, o.status, p.name AS product, oi.quantity, oi.unit_price
FROM orders o JOIN customers c ON c.id = o.customer_id JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id WHERE o.id = ?`;
const N = 2000;
await db.query(SQL, [1]); await orders.findOne({ _id: 1 }); // warm up
let t = performance.now();
for (let i = 0; i < N; i++) await db.query(SQL, [1 + (i % 8)]);
const my = (performance.now() - t) / N * 1000;
t = performance.now();
for (let i = 0; i < N; i++) await orders.findOne({ _id: 1 + (i % 8) });
const mg = (performance.now() - t) / N * 1000;
console.log(`MySQL 4-table join by order id: ${my.toFixed(0)} µs`);
console.log(`MongoDB findOne by _id: ${mg.toFixed(0)} µs`);
await db.end(); await mongo.close();
// MySQL 4-table join by order id: 111 µs
// MongoDB findOne by _id: 140 µs
// (second run: 115 µs and 233 µs)What’s happening
- Each loop reads all eight orders round-robin, one round trip per read, so both databases do comparable work: find one order and everything in it.
- The four-table join took about 111–115 µs; the single-document read 140–233 µs. Every join here walks a primary key or the
order_itemsprimary key(order_id, product_id), so each step is an index lookup on data already in memory. - At this scale both are dominated by the round trip and driver overhead, not by joining. Joins on indexed keys are cheap; what's expensive is joining unindexed columns or joining millions of rows — and that's just as expensive with
$lookup. - Where embedding really wins is at large scale and under sharding: one document lives on one shard, while a join across shards means talking to several machines. That's a scaling argument, not a "joins are slow" argument.
AdvancedSchema on write vs schema on read
Insert an order with a typo in its status in each database:
INSERT INTO orders (customer_id, status, ordered_at) VALUES (1, 'shippd', NOW());ERROR 1265 (01000) at line 1: Data truncated for column 'status' at row 1db.orders.insertOne({ _id: 99, status: 'shippd', customer: { id: 1, name: 'Rohit' }, items: [] })
db.orders.countDocuments({ status: 'shipped' })
db.orders.deleteOne({ _id: 99 }){ acknowledged: true, insertedId: 99 }
4
{ acknowledged: true, deletedCount: 1 }What’s happening
orders.statusis anENUM('pending','paid','shipped','cancelled'), and MySQL 9.7's default strict SQL mode rejects anything else withERROR 1265 … Data truncated for column 'status'. The bad data never gets in. (In non-strict mode — the default before MySQL 5.7 — it would have stored an empty string with only a warning.)- MongoDB accepts the document: no collection-level rules exist unless you add them. The typo'd order is now stored, and a query for shipped orders counts 4 — it silently doesn't see order 99 at all.
- This is "schema on write" vs "schema on read": in a document store the schema still exists, but it lives in your application code and in every reader's assumptions. Mongoose schemas and MongoDB's
$jsonSchemavalidators move it back into a checked place (see Schema validation). - Flexibility is useful when documents genuinely vary (product attributes that differ per category, third-party payloads). For core business entities, most teams end up adding validation anyway.
AdvancedDuplication moves cost from reads to writes
Rename the "Accessories" category in both:
UPDATE categories SET name = 'Gadgets' WHERE id = 5;
SELECT ROW_COUNT();+-------------+
| ROW_COUNT() |
+-------------+
| 1 |
+-------------+db.orders.updateMany(
{ 'items.category': 'Accessories' },
{ $set: { 'items.$[i].category': 'Gadgets' } },
{ arrayFilters: [{ 'i.category': 'Accessories' }] }
){
acknowledged: true,
insertedId: null,
matchedCount: 5,
modifiedCount: 5,
upsertedCount: 0
}What’s happening
- In MySQL the category name exists once, so the rename touches 1 row, and every query that joins to
categoriessees the new name immediately. - In MongoDB the name was copied into every order item in that category, so the same rename has to find and modify 5 documents (and, with
arrayFilters, every matching item inside each). With 50 million orders that's a background migration, not a one-row update. - Both renames were reverted straight afterwards, so the sample data is unchanged.
- The design rule that falls out: embed data that's read together and rarely changes (or should be frozen, like the price and name on a past order), and reference data that changes often or is shared widely. That's the core of Embedding vs referencing.