Skip to content

All lessons

Module 03 // DataLesson 09 / 3420 min read

SQL vs NoSQL

SQL databases store data in tables with strict structure and powerful queries; NoSQL databases trade some of that for flexible shapes and easier scaling. The right choice depends on how you'll read and write the data.

Think of it like this

Analogy

A SQL database is a well-run filing cabinet with labeled folders and forms. Every form has the same boxes, cross-references between folders are checked, and you can ask complicated questions like “every customer in Madrid who ordered twice last month”.

A NoSQL database is more like a set of specialist containers: a key cabinet where you fetch anything instantly by its tag, a box of notebooks where each page can be laid out differently, a huge ledger that's easy to split across many rooms, or a corkboard of pins and strings showing who's connected to whom.

The problem it solves

The database is usually the hardest part of a system to change later. Pick a store that fights your access patterns and you'll pay for it constantly: slow queries, painful migrations, or a system that can't grow past one machine. Pick one that gives up guarantees you needed, like transactions, and you'll get subtle data bugs.

A real example

Example

An online bookstore picks its databases

Orders and payments go in PostgreSQL. Placing an order takes money from a gift card, reduces stock and creates the order, and all of it must happen together or not at all. That's what transactions are for.

The shopping cart lives in a key-value store. It's read and written constantly, always by cart ID, and losing one occasionally isn't a disaster.

The catalog is a document store, because a book has an author and ISBN while a gift voucher has neither. Search runs on a search engine built for text. One shop ends up with four kinds of database, each chosen for how its data is actually used.

How it works

Relational (SQL) databases

Data lives in tables with a fixed schema. Relationships are expressed with keys, and SQL lets you join tables and filter however you like.

SELECT u.name, COUNT(o.id) AS orders
FROM users u JOIN orders o ON o.user_id = u.id
WHERE o.created_at > now() - interval '30 days'
GROUP BY u.name;

Relational databases offer ACID transactions: a group of changes either all happen or none do, and concurrent transactions don't see each other's half-finished work. Examples: PostgreSQL, MySQL, SQL Server.

They scale up well and handle a lot of traffic with good indexes and read replicas. Spreading writes across many machines (sharding) is possible but takes work, though newer distributed SQL databases like Spanner and CockroachDB do it for you.

NoSQL databases

NoSQL means “not only SQL”. It covers four main types:

TypeData modelGreat atExamples
Key-valuekey → valueLookups by exact key at huge scale: sessions, carts, cachesDynamoDB, Redis
Documentkey → JSON documentObjects read as a whole, with changing shapesMongoDB, Firestore, Cosmos DB
Wide-columnpartition key → sorted rowsMassive write volumes, time series, event logsCassandra, Bigtable
Graphnodes and edgesWalking relationships: social graphs, fraud, recommendationsNeo4j, Neptune

Most NoSQL databases are designed to partition data across many machines from the start. The price is usually fewer query options: you design tables around the questions you'll ask, often duplicating data so each common read is a single lookup.

Design around access patterns

With SQL you design the data model first and write queries later. With most NoSQL stores you list your queries first, then design keys to answer each one quickly.

For a key-value or wide-column store holding chat messages, you might use:

  • Partition key: conversation_id. All messages in a chat live together.
  • Sort key: sent_at. Messages are already in time order.

“Last 50 messages in this chat” becomes one efficient read. “All messages containing the word pizza” isn't supported at all; you'd add a search index for that.

How to choose

  • Default to a relational database for most products. It's flexible, safe, and handles far more scale than most apps reach.
  • Pick key-value or document when access is mostly by ID, the shape varies, or you need huge, predictable scale with little operational work.
  • Pick wide-column for very high write throughput with simple access by key and time.
  • Pick a graph database when the questions are about multi-step relationships.
  • Mix them. Real systems often use a relational database as the source of truth, a key-value cache, a search index and a data warehouse together.

Trade-offs

What you gain

  • SQL: transactions, strong consistency, flexible queries and joins, mature tools.
  • NoSQL: scales out easily, flexible schemas, very fast key lookups, built-in replication.

What it costs you

  • SQL: scaling writes past one machine is hard; schema changes on huge tables need care.
  • NoSQL: limited queries and joins, data is often duplicated, transactions are narrower or more expensive, and schema discipline moves into your code.

When not to use it

  • Avoid NoSQL just to “be web scale” when the data is relational and fits on one machine.
  • Avoid forcing a relational database to be a high-volume event log or a graph walker when a specialized store does it far better.

In the cloud

Relational (SQL) databasesEvery major cloud sells this as a managed service, so you configure it rather than build it.

AWS2

Azure2

Google Cloud2

How you’d set it up

One writable primary with a standby in another zone for failover, plus read replicas.

  1. App servers
  2. RDS primary (Multi-AZ)
  3. Read replicas

NoSQL databasesEvery major cloud sells this as a managed service, so you configure it rather than build it.

AWS3

Azure1

Google Cloud2

Interview questions

Try answering out loud first, then open the model answer.

SQL or NoSQL for a new social app? Justify it.

Model answer

I'd start with a relational database for users, posts, follows and likes. The data is relational, I need constraints and transactions (for example creating a post and incrementing a counter), and PostgreSQL with indexes, read replicas and caching goes a very long way. I'd add specialized stores where access patterns demand it: a key-value store or cache for timelines and counters, a search index for text search, and maybe a wide-column store for the activity feed once write volume gets very large.

What is denormalization and why do NoSQL designs use it?

Model answer

Denormalization means storing the same data in more than one place so that a read needs one lookup instead of a join. For example, storing the author's name inside each post. NoSQL stores usually can't join across partitions efficiently, so you shape data around your reads. The cost is that updates must change every copy, and copies can briefly disagree.

What does ACID stand for and why does it matter?

Model answer

Atomicity: all of a transaction happens or none of it does. Consistency: the database moves from one valid state to another, respecting constraints. Isolation: concurrent transactions don't see each other's partial results. Durability: once committed, data survives crashes. It matters for anything where partial updates would be wrong: money transfers, inventory, bookings.

Quiz

Pick an answer to see whether it’s right and why.

  1. 01You're building a bank's account system with transfers between accounts. What's the safest default?
  2. 02Which access pattern fits a key-value store best?
  3. 03What does "schema-on-read" mean in a document database?
  4. 04Which database type is designed for very high write volumes of time-ordered data, like sensor readings, spread across many machines?
  5. 05You need flexible reports like “sales by region per month”, joining orders, customers and products. What fits best?
  6. 06A catalog has items with very different attributes: books have authors, TVs have screen sizes. What fits naturally?
  7. 07What does atomicity (the A in ACID) guarantee for a money transfer?

0 of 7 answered

Key terms

SQL
The language for querying relational databases, which store data in tables with fixed columns.
NoSQL
Databases that don't use relational tables, such as key-value, document, wide-column and graph stores.
ACID
Atomic, Consistent, Isolated, Durable. The guarantees that make database transactions all-or-nothing and safe.
Schema
The defined shape of your data, such as table columns and their types.
Key-value store
A database that stores values under unique keys, like a giant dictionary. Very fast lookups by key.
Document database
A database that stores JSON-like documents, each of which can have its own nested structure.
Wide-column store
A database that groups data by a partition key and sorts rows inside each partition; built for huge write volumes.
Graph database
A database that stores things and the connections between them, made for questions like "friends of friends".
Database
Software that stores data on disk in an organized way so it can be queried and survives restarts.

Progress is saved in this browser only.