SQL vs NoSQL
Relational, key-value, document, wide-column and graph databases compared: what each is good at, ACID versus BASE, and a practical way to choose.
On this page
The problem: one database, five very different jobs
An online shop needs to store several kinds of data:
- Orders and payments: money moves, and an order must never be half-saved.
- Shopping carts and sessions: read and written on every click, by key, millions of times a minute.
- Product catalogue: a phone has a screen size and battery, a shirt has a size and fabric. Every category has different fields.
- Click events: 200,000 per second, appended and rarely changed.
- "Customers who bought this also bought": a question about relationships, several hops deep.
Putting everything in one table design makes some of these painful: 300 mostly-empty columns for products, a write rate the database can't take for events, five-way self-joins for recommendations. Different database families exist because these jobs have different shapes.
The idea: pick the data model that matches how you read and write
Relational databases: tables, joins and transactions
- Every app needs to remember things: users, orders, messages. A database stores that data safely, so it survives restarts and can be searched quickly.
- Relational databases, the SQL kind, store data in tables: rows and fixed columns, like a very strict spreadsheet. Rows point to rows in other tables using IDs.
- Two superpowers. Joins combine tables in one query, like every order with its customer’s name. And transactions: a group of changes either all happen or none do, so money never disappears halfway through a transfer.
A relational database (PostgreSQL, MySQL, SQL Server, Oracle) stores rows in tables with a fixed set of columns (the schema). Rows refer to each other with IDs (foreign keys), and SQL lets you ask almost any question with joins, filters and grouping, including questions nobody planned for when the tables were designed.
NoSQL ("not only SQL") is a loose name for databases that drop some of those features (usually joins, a fixed schema, or transactions across many records) in exchange for a simpler model, flexible records, or easier spreading across many machines.
The NoSQL family: documents, key-value, graph, wide-column
- NoSQL is a family of databases that relax some of those rules for flexibility or huge scale. A document store keeps each record as a self-contained document, and each one can have different fields.
- A key-value store is like a giant dictionary: give it a key, get the value back, extremely fast. Great for sessions, carts and caches.
- A graph database stores relationships directly, perfect for questions like “friends of my friends”. Wide-column and time-series databases swallow enormous streams of writes, like sensor readings.
The five families
| Family | Stores | Examples | Good at | Weak at |
|---|---|---|---|---|
| Relational | Rows in tables, joined by keys | PostgreSQL, MySQL | Ad-hoc queries, joins, transactions, constraints | Scaling writes beyond one primary needs sharding |
| Key-value | key → value (value is opaque) | Redis, DynamoDB, Memcached | Very fast get/put by key; scales out easily | Any query not by key |
| Document | JSON-like documents, each with its own fields | MongoDB, Couchbase, Firestore | Records that are read whole; varied fields | Joins across documents; data duplicated in many documents |
| Wide-column | Rows grouped by a partition key, sorted by a clustering key | Cassandra, ScyllaDB, HBase, Bigtable | Huge write rates, time-ordered data, multi-datacenter | Queries you didn't design the table for; no joins |
| Graph | Nodes and edges with properties | Neo4j, Amazon Neptune | Multi-hop relationship queries | Bulk scans and aggregates; harder to shard |
Two more you will meet: time-series databases (TimescaleDB, InfluxDB) for metrics, and search engines (Elasticsearch, OpenSearch) for full-text queries. Both usually sit beside a main database, not instead of it.
How a wide-column table works
Wide-column stores confuse people because they look like SQL tables. The difference is that you design the table for one query. In Cassandra:
CREATE TABLE events_by_user (
user_id bigint,
ts timestamp,
event_type text,
payload text,
PRIMARY KEY ((user_id), ts) -- partition key: user_id; clustering key: ts
) WITH CLUSTERING ORDER BY (ts DESC);
-- fast: one partition, already sorted newest first
SELECT * FROM events_by_user WHERE user_id = 42 LIMIT 50;
All rows for user 42 live together on the same few nodes, sorted by time, so "latest 50 events for this user" reads one contiguous slice. "All events of type purchase yesterday" is not supported efficiently; you would create a second table keyed for that query and write every event to both. Denormalising like this (storing the same data in several shapes) is normal outside the relational world.
ACID and BASE
Scaling out often costs joins or cross-record transactions
- Many NoSQL databases spread easily across lots of machines. The trade-off: they often give up joins, or strict transactions across many records.
A transaction is a group of reads and writes the database treats as one unit. Relational databases give ACID transactions:
| Letter | Meaning | Example |
|---|---|---|
| Atomicity | All the changes happen, or none do | Debit Ana and credit Ben: never only one |
| Consistency | Constraints hold before and after (unique emails, foreign keys, balance >= 0) | An order can't reference a missing customer |
| Isolation | Concurrent transactions don't see each other's half-done work, to the degree the isolation level promises | Two people booking the last seat don't both succeed (with the right level or a lock) |
| Durability | Once committed, it survives a crash (written to a log on disk) | The confirmed payment is still there after a power cut |
Isolation has levels. PostgreSQL defaults to Read Committed and MySQL's InnoDB to Repeatable Read; neither is fully serializable (as if transactions ran one at a time) unless you ask for it, so "it's in a transaction" does not by itself prevent every race.
Many distributed NoSQL systems were built around BASE: Basically Available, Soft state, Eventually consistent. They stay up and accept writes when nodes or links fail, and replicas converge over time (see consistency and CAP). This is a design emphasis, not a law: the lines have blurred.
How to choose
Ask these questions in order:
- What are the access patterns? List the top 3–5 queries. If they are "get by key" or "latest N for this user", a key-value or wide-column store fits. If you need flexible filtering, reporting or joins, use relational.
- What must never be wrong? Money, inventory, bookings and unique usernames need transactions and constraints. Start relational.
- What is the scale, really? Do the arithmetic before reaching for NoSQL "for scale".
- How varied is the shape? Many optional, per-category fields suggest documents (or a
jsonbcolumn in Postgres). - What does the team know how to run? Operating a database well matters more than its feature list.
Worked example: the shop, by the numbers
| Data | Load | Choice | Why |
|---|---|---|---|
| Orders, payments | 500 orders/s at peak, ~50 GB/year | PostgreSQL | Well inside one primary (thousands to low tens of thousands of writes/s); needs transactions |
| Carts, sessions | 50,000 reads/s, small values | Redis or DynamoDB | Pure get/put by key; Redis handles ~100 K ops/s per node |
| Catalogue | 2 M products, varied fields, read-heavy | Postgres jsonb or MongoDB | Varied attributes; read whole; cache in front |
| Click events | 200,000 events/s × 300 B ≈ 60 MB/s, ~5 TB/day | Cassandra (or Kafka into a warehouse) | Write-heavy, append-only, partitioned by user and time |
| Recommendations | Multi-hop relationship queries | Graph DB, or precomputed in a batch job | Joins several hops deep are slow in SQL |
Only one row actually needed NoSQL for scale: the click events. Using several databases, each for what it is good at, is called polyglot persistence. Its cost is more systems to run and keep in sync, usually via events or change data capture.
When not to use NoSQL (and when not to use SQL)
Don't pick NoSQL when the data is highly connected and you need joins, when correctness across several records matters, or when the scale fits comfortably in one relational database. Don't force relational when writes clearly exceed what one primary can take and the access pattern is simple (sensor data, logs, chat messages by conversation), or when you need active writes in several regions at once.
In the interview
Pick a database for each: (1) rider and driver accounts, (2) trips and payments, (3) driver GPS pings every 4 seconds from 500,000 active drivers, (4) "which drivers are within 2 km right now". Justify each in one line.
Compare with our answer
- Accounts: relational. Modest size, unique constraints (email, phone), joins with trips.
- Trips and payments: relational with transactions; a trip's state changes and its charge must agree. Shard by city or rider id later if needed.
- GPS pings: 500,000 / 4 = 125,000 writes/s, append-only, read by driver and time: a wide-column store (Cassandra) or a stream (Kafka) into cheap storage, with a short retention.
- Nearby drivers: an in-memory geospatial index (Redis
GEOSEARCH, or a geohash grid) holding only the latest position, updated on each ping.
A strong answer mentions: the write rate calculated (125 K/s) · transactions for trips and payments · a wide-column or log store for pings · an in-memory geo index for the live query · that the latest position and the history are stored separately.
A team stores orders in MongoDB, one document per order with the customer's name and address copied inside. Finance asks for "total revenue per customer region, per month". The customer also updates their address. What goes wrong, and what would you suggest?
Compare with our answer
Copying the address into every order is reasonable (an order should keep the address it shipped to), but if the region is meant to be the customer's current region, those copies are now stale and disagree with each other. "Revenue per region per month" means scanning every order document and grouping, which an aggregation pipeline can do but which competes with live traffic. Suggest: decide which address is meant (shipping-time address is usually correct for orders); add an index or run the report on a secondary or, better, stream orders into a warehouse via change data capture for analytics; if many reports like this are coming, consider making orders relational.
A strong answer mentions: denormalised copies go stale on update · deciding which copy is the truth · analytics competing with live traffic · a warehouse or change-data-capture feed for reporting · that unplanned queries are where document stores hurt.
- Relational: tables, joins, constraints, ACID. Default for anything with money or unpredictable queries.
- Key-value: get/put by key, fastest and simplest (Redis, DynamoDB). Document: varied fields, read whole (MongoDB). Wide-column: huge writes, partition key + clustering key, one table per query (Cassandra). Graph: multi-hop relationships (Neo4j).
- ACID = atomic, consistent (constraints), isolated (to a level), durable. Postgres default Read Committed, MySQL Repeatable Read.
- BASE = basically available, soft state, eventually consistent. An emphasis, not a rule: many NoSQL stores now have limited transactions.
- One relational primary: thousands to low tens of thousands of writes/s. Do the arithmetic before choosing NoSQL "for scale".
- NoSQL modelling = list access patterns first, denormalise, design keys per query.
- Unplanned queries → change data capture into a warehouse or search index.
- Polyglot persistence is normal: Postgres for orders, Redis for sessions, Cassandra for events.
1. A fintech app moves money between user wallets and must never lose or double-count a transfer. Traffic is 800 transfers per second. Which choice fits best?
Money needs atomic multi-record changes and constraints, and 800 writes/s is well within one relational primary. The others either lack multi-record transactions or need awkward workarounds.
2. An IoT platform receives 300,000 sensor readings per second, and always reads "the last hour of readings for sensor X". What fits best?
The write rate is far beyond one relational primary, and the read is one partition in time order: exactly what a wide-column layout serves. A document per sensor grows without bound and hits size limits.
3. Your team chose Cassandra for orders, keyed by customer id. Product now wants "all orders over $500 in the last week". What is the realistic answer?
Wide-column tables serve the queries they were designed for. A new query needs a new table (with a backfill) or a separate analytics system fed by change data capture. Cassandra has no joins.
4. Which statement about ACID isolation in PostgreSQL is accurate?
Postgres defaults to Read Committed. Lost updates and write skew are possible unless you use SELECT … FOR UPDATE, constraints, or the Serializable level.
5. A startup expects 2 million users with 2 KB profiles, mostly read by id, plus orders with payments. A senior engineer proposes MongoDB "for scale". What is the best response?
2 M × 2 KB = 4 GB, which fits in memory. Orders need transactions. NoSQL should be chosen for a specific need, not for scale that isn't there.
6. A social app needs "friends of friends who also like jazz, within 3 hops" answered quickly. Which store is the natural fit?
Multi-hop traversal is what graph databases are built for. Relational self-joins grow expensive with depth, and key-value lookups need one round trip per hop.
Score 60% or more and the lesson is marked complete.