SQL vs NoSQL: How to Choose
Relational databases give you tables, joins and transactions; NoSQL databases trade some of that for flexible data models and easy horizontal scaling. Learn the real differences and a simple way to choose.
In a nutshell
- Relational (SQL) databases store rows in tables with a fixed schema, and offer joins and ACID transactions.
- NoSQL is a family: key-value, document, wide-column and graph stores, each built for a specific access pattern.
- Pick from your access patterns and consistency needs, not from fashion. A relational database is the safe default.
- Many real systems use both: SQL for the core records, NoSQL or caches for specific high-scale paths.
Two ways to store the same order
A relational database splits data into tables (customers, orders, order items) linked by keys, and reassembles it with joins when you query. Each fact is stored once, so updates are simple and consistent.
Relational: normalised tables, joined at read time
SELECT o.id, c.name, i.product_id, i.qty
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items i ON i.order_id = o.id
WHERE o.id = 1042;A document database (one kind of NoSQL) would usually store the whole order as one document, with the customer name and items inside it. One read returns everything, and the shape can differ between documents, but the customer name is now copied into every order.
Document: everything the order page needs, in one record
{
"_id": 1042,
"customer": { "id": 7, "name": "Priya" },
"items": [
{ "product_id": 31, "qty": 2 },
{ "product_id": 58, "qty": 1 }
],
"status": "paid"
}The real differences
| Relational (SQL) | NoSQL (typical) | |
|---|---|---|
| Data model | Tables, rows, fixed schema | Key-value, documents, wide rows or graphs; flexible schema |
| Queries | SQL: any filter, join or aggregate, decided later | Fast for the access patterns you designed for; others are hard |
| Transactions | Multi-row ACID transactions | Often single-record atomicity; some offer more |
| Scaling writes | Vertical first; sharding is extra work | Built to spread across many machines |
| Consistency | Strong by default | Often tunable; eventual consistency is common |
| Examples | PostgreSQL, MySQL, SQL Server | Redis, MongoDB, Cassandra, DynamoDB, Neo4j |
How to choose
Ask these questions in order:
- Do you need transactions across several records? Money, stock, bookings: lean relational.
- Do you know your queries up front? If people will ask new questions of the data later (reports, admin filters), SQL is far more forgiving.
- What are the access patterns? "Get everything about X by its ID" fits key-value or document stores. "Friends of friends" fits a graph. Huge volumes of time-ordered writes fit wide-column stores.
- How big, how fast? Tens of thousands of writes per second across regions push you towards databases built to spread data out.
- What does the team know? A database you can operate well beats a better-fitting one nobody understands.
Common mistakes
- Choosing NoSQL for scale the product will never reach, then rebuilding joins and transactions in application code.
- Choosing a document store without knowing the access patterns, then needing queries the model cannot serve.
- Treating "schemaless" as "no schema": the schema still exists, it just lives in your code.
- Assuming a relational database cannot scale past one machine.
Interview questions
Try answering out loud before you open each one.
Which database would you use for this system, and why?
Start from the data and access patterns. Name the core entities and whether they need multi-record transactions. Choose a relational database for transactional core data, and a specialised store where an access pattern demands it (key-value for sessions and counters, wide-column for huge write-heavy time series, search engine for full-text). Justify each choice with the requirement it serves.
What do you give up with a typical NoSQL database?
Usually joins, ad-hoc queries and multi-record transactions, and often strong consistency by default. In return you get a flexible schema and data models that partition across many machines easily.
Check yourself
Related topics
Further reading
Your notes
Saved in this browser as you type.
Finished this topic?
Mark it read to track your progress, then carry on with the next one.