SQL vs. NoSQL
- Lesson 9 of 40
- 11 min
- Beginner
- 10 steps
There is no winning database: each family handles one kind of data well and pays for it elsewhere. Here you will see when each one makes sense.
- SQL DB load
- 40%
- Data stores
- 4
- request
- response
- slow request
- waiting in queue
- batch
Step 1 of 10
Concept
Data lives in tables
Almost every app starts with a relational (SQL) database such as PostgreSQL. Each table has columns defined up front: that contract is the schema.
Tables link to each other through keys: orders.user_id points at users.id. A JOIN combines both in a single query, for example "every order with the name of whoever placed it".
Step 2 of 10
Technique
All or nothing: transactions
Moving 30 from account 17 to account 42 takes two writes: subtract from one, add to the other. If the server dies right between them, the money vanishes.
A transaction groups them: both are committed or neither is applied. That is why payments, orders and inventory usually live in a relational database.
What changed
- Two writes inside one transaction
Step 3 of 10
Problem
The schema and the server fall short
The product catalog moves into the same database. A boot has a size, a tablet has a screen in inches and a book has an author, so the products table piles up hundreds of mostly empty columns.
Every new product type needs another migration on a 200M-row table, and some of them rewrite or lock the table for hours. On top of that, every write still goes through a single server, already at 90%. The figures are example values.
What changed
- Catalog inside the SQL database
- Products table with hundreds of columns
- DB load: 35% → 90%
Step 4 of 10
Solution
Each record with its own shape
A document store keeps each record as a complete document, much like JSON. The boot and the tablet carry different fields and there is no migration to run.
The price: your code has to understand every shape it has ever stored. In return, everything about a product sits together, is read in one go, and documents spread well across several servers.
What changed
- Document store for the catalog
- The SQL database goes back to orders and users
- DB load: 90% → 45%
Step 5 of 10
Technique
Look up by key, nothing else
The simplest store maps a key to a value. No queries, no joins: you only read and write by key, in about a millisecond when the data lives in memory.
Since every key stands alone, spreading the data over several nodes comes almost for free: the key itself decides which node it lives on.
What changed
- Key-value cluster for sessions
- DB load: 45% → 40%
Step 6 of 10
Trade-off
What NoSQL leaves behind
Much of how easily NoSQL scales comes from giving things up: joins across records, transactions spanning several records and, often, strict consistency.
These are tendencies, not laws. PostgreSQL stores and queries JSON, several NoSQL stores now offer multi-document transactions, and distributed SQL databases spread writes out without losing joins or transactions, paying for it with more latency and complexity.
Step 7 of 10
Solution
Each piece of data where it fits best
Real systems mix stores: this is polyglot persistence. Orders and payments stay in SQL for the transactions, the catalog lives in documents and sessions in key-value.
Start with a single database and add another only when a kind of data clearly asks for it.
What changed
- Orders and payments → SQL
- Catalog → documents
- Sessions and carts → key-value
Step 8 of 10
Problem
Case: "red sneakers"
Customers type free text into the search box. In SQL that ends up as WHERE name LIKE '%red%': with the wildcard at the start a regular index cannot help, millions of rows get scanned, and there is still no relevance ranking, no typo tolerance, and "sneaker" does not find "sneakers".
A search engine builds an inverted index: for every word, the list of documents that contain it. It is fed from the catalog by a change stream.
What changed
- Search engine for the catalog
- Change stream: documents → index
Step 9 of 10
Concept
Stores built for a single job
The search engine is one example of a store designed for a specific access pattern. There are more, and each beats a general database at its job and falls behind at everything else.
Add them when a workload clearly does not fit, not out of habit: every extra store has to be run, secured, backed up and kept in sync.
Step 10 of 10
Summary
Summary
SQL: tables, joins and transactions; a safe place to start.
Documents: flexible, complete records that spread well across servers.
Key-value: the simplest and fastest, but you only read by key.
Choose by kind of data; many systems use several stores.
Specialists such as search are copies fed from the source of truth.
← → step · R replay · Space pause
You will learn
- Tables, joins and transactions (covered)
- Where a relational database gets stuck (covered)
- Document and key-value stores (covered)
- Choosing by kind of data (covered)
- Search engines and other specialised stores (covered)