System designCore10 min

Relational vs NoSQL

"SQL or NoSQL?" is usually asked as if one were modern and the other old, or one scaled and the other didn't. The real difference is how each one stores data and which questions it can answer cheaply. Pick by the shape of your data and the questions you will ask of it.

1 · The question

Choose by the shape of the data and the questions you ask

A car-sharing service runs 5,000 cars for 300,000 members in one city. It keeps four very different kinds of data:

Data	Shape	The question it must answer
Members, cars, bookings, invoices	Many small records that point at each other	Any question, including ones nobody planned
Each car's listing page	One rich object with nested lists	Show this car, fast, thousands of times a day
Telemetry from every car	An endless stream of readings over time	This car's readings for this day
Who shares cards and phones	A web of connections	Is this new member linked to known fraud?

A relational database stores data as tables of rows with a fixed set of columns, linked by keys, and answers questions in SQL. NoSQL is a loose label for the databases that use other models: documents, key-value pairs, wide-column tables and graphs. Each model makes some questions cheap and others expensive, so the same service can sensibly use more than one. The sections below take the car-sharing data one shape at a time.

2 · Relational

Relational databases store each fact once and join on demand

The core business data goes into tables. Each fact lives in one place: a member's email is stored once, in members, and every booking refers to the member by key:

Bookings point at members and cars by key. Changing an email updates one row, and every booking sees it.

Storing facts once, called normalization, means there are no copies to drift apart. The cost is that most answers combine several tables with a join:

Last month's bookings for members who drove an electric car

sql

SELECT m.email, c.plate, b.starts_at, b.ends_at
FROM bookings b
JOIN members m ON m.id = b.member_id
JOIN cars c    ON c.id = b.car_id
WHERE c.fuel = 'electric'
  AND b.starts_at >= DATE '2026-09-01'
  AND b.starts_at <  DATE '2026-10-01';

Three strengths come with this model:

Questions nobody planned for. The query above was written by the finance team last week; the tables didn't have to be designed for it.
The database enforces the rules. A schema is checked on every write (schema-on-write): a booking for a car that doesn't exist is refused by a foreign key, and one that ends before it starts by a check constraint.
Transactions across rows and tables. Charging an invoice and closing a booking succeed or fail together (Transactions & isolation levels).

The catch is that joins cost work on every read, and a single relational server has a ceiling. Both have answers, covered below.

3 · Documents

A document keeps together what is read together

Each car's listing page shows the model, its features, photos, the current location and a rating summary. Normalized, that is five or six tables joined on every view. A document database stores it as one JSON-like document, fetched with a single read:

One car as a document

json

{
  "_id": "car-1187",
  "model": "Compact EV",
  "plate": "KX-4471",
  "features": ["child seat", "bike rack"],
  "photos": ["1187-front.jpg", "1187-inside.jpg"],
  "location": {"lat": 52.52, "lon": 13.40},
  "rating": {"average": 4.7, "count": 312}
}

This is locality: everything the page needs sits in one place, in the same shape as the object in the code. Documents in one collection don't all need the same fields, so a van can carry cargo_volume without a schema change. The structure is checked when the data is read and used (schema-on-read), unless you turn on the database's optional validation.

Documents struggle where the relational model shines: many-to-many relationships. A booking links one member and one car, and members book many cars. Embed bookings in the car, and a member's history is scattered across thousands of car documents; embed them in the member, and the car's calendar is scattered instead. Either you store references and join in your own code, or you copy data into both and keep the copies in step (Data modeling & denormalization).

Updating one document is atomic in document databases. Changing several at once needs a multi-document transaction, which MongoDB has offered since version 4.0 (4.2 across shards). Such transactions cost more than single-document writes, and good modeling, mostly embedding, removes the need for most of them.

4 · Key-value and wide-column

Key-value and wide-column stores answer known questions at any scale

Every car reports its position, speed and battery every 5 seconds. That is 5,000 ÷ 5 = 1,000 writes a second, or 1,000 × 86,400 = 86.4 million rows a day, and almost every read asks the same thing: show this car's trip on this day.

A key-value store is the simplest model: put a value under a key, get it back by the key, nothing else. It is ideal for the unlock code sent to a member's phone, or for sessions. A wide-column store such as Cassandra adds structure: rows are grouped into partitions by a partition key and kept sorted within each partition. For telemetry, partition by car and day, and sort by time:

Design choice	Telemetry table
Partition key	car_id + day: all of one car's readings for one day live together
Sort order	timestamp: a trip is one contiguous slice
Partition size	86,400 ÷ 5 = 17,280 readings per car per day
Cheap question	Car 1187 on 2 October, 08:00 to 09:00
Expensive question	Every car that went over 120 km/h last week: it touches every partition

This is query-first design: you write down the questions before designing the tables, and shape the data so each common question reads one partition. There are no joins and no foreign keys. A new question, like the speeding report, usually means a new table, written alongside the first, holding the same data organized for that query. In return, the data spreads evenly across many machines, and adding machines adds capacity (Sharding & partitioning).

5 · Graphs

Graphs make many-hop relationships cheap to follow

Fraudsters open many accounts, but they reuse things: a card, a phone number, a device. The fraud team wants to know, for each new member, whether they connect to a known fraudster within three hops:

Two hops through shared details link a new sign-up to a known fraudster. Each extra hop in SQL is another self-join over every member.

A graph database stores nodes (members, cards, phones) and relationships between them as first-class data, kept next to the nodes. Following a relationship is a direct step rather than a lookup in a join, so a three-hop search costs roughly the number of connections it visits, not the size of the tables. Graphs suit social networks, recommendations, fraud rings and road networks: anywhere the question is how things connect rather than what they are.

6 · The myths

Most of the old trade-offs have blurred

Older comparisons list differences that have since faded. Check each one against the product you are actually considering:

The claim	Today
NoSQL has no transactions	Single-record writes are atomic everywhere. MongoDB has multi-document transactions; DynamoDB groups up to 100 items in one all-or-nothing write.
Relational databases don't scale out	Replicas spread reads and sharding spreads writes; distributed SQL such as Spanner splits tables across machines automatically, keeping SQL and ACID.
NoSQL is schemaless	The schema moves into your code. Document databases can validate documents on write.
Relational databases can't hold JSON	PostgreSQL stores JSON in a binary format and can index inside it.
NoSQL is always eventually consistent	Many offer strongly consistent reads; DynamoDB does on request (Consistency models).

The differences that remain are real, but they are about cost, not possibility. A transaction in DynamoDB writes every item twice, once to prepare and once to commit, and covers one region only. A join in a document database happens in your code. A query a wide-column table wasn't designed for scans everything. Know which of your questions will pay those costs, and how often.

7 · In practice

Start from the questions, and add a store only for a question that needs it

The car-sharing service ends up with four kinds of store behind one app:

The relational database stays the source of truth for members; the graph is fed from its changes. Every store is one more system to run, secure and keep in step.

Data	Store	Why
Members, bookings, invoices	Relational	Constraints, transactions, questions nobody planned
Car listing pages	Relational with JSON columns, or a document store	One read per page; flexible fields per car type
Telemetry	Wide-column	1,000 writes a second, read by car and day
Unlock codes and sessions	Key-value	Read by key, expire on their own
Fraud links	Graph	Three-hop searches on every sign-up

Every extra database is another system to run, back up, secure and keep in step with the others, usually by publishing changes as events (Event-driven architecture). So a sensible default is to start with one relational database, which keeps the most options open while the questions are still changing, and add a specialized store when one specific question outgrows it.

Questions to ask:

What are the main reads and writes, and which of them are hot?
Are relationships one-to-many (documents fit) or many-to-many (relational or graph)?
Do the rules need to be enforced by the database: constraints, multi-record transactions?
How big and how fast: will one machine's writes ever be the limit?
Will new questions keep arriving? Flexible queries favor relational; known queries at huge scale favor wide-column or key-value.

Check yourself

5 questions

1. Finance asks a new question that joins bookings, members and cars in a way nobody planned. Which model handles that most easily?

2. Telemetry is partitioned by car and day, sorted by time. A new report needs every car that exceeded 120 km/h last week. What is the usual answer in a wide-column store?

3. Which statement about NoSQL databases and transactions is true today?

4. Why does the car's listing page suit a document?

5. The fraud team checks every sign-up for links to known fraudsters within three hops through shared cards, phones and devices. Which model fits best?

Takeaways

Remember this

Choose a database by the shape of your data and the questions you'll ask, not by SQL versus NoSQL.
Relational databases store each fact once, enforce rules, and answer questions nobody planned for, at the cost of joins.
Documents keep together what is read together but struggle with many-to-many relationships.
Key-value and wide-column stores answer known questions at huge scale; a new question usually needs a new table.
Transactions, scale-out, schemas and JSON now exist on both sides; the remaining differences are costs, so start relational and add stores for specific needs.