Storing data · Chapter 5

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.

IntermediateInterview priority: High30 min
On this page
  1. The problem: one database, five very different jobs
  2. The idea: pick the data model that matches how you read and write
  3. The five families
  4. ACID and BASE
  5. How to choose
  6. When not to use NoSQL (and when not to use SQL)
  7. In the interview
  8. Quiz

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

  1. Every app needs to remember things: users, orders, messages. A database stores that data safely, so it survives restarts and can be searched quickly.
  2. 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.
  3. 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

  1. 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.
  2. 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.
  3. 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

FamilyStoresExamplesGood atWeak at
RelationalRows in tables, joined by keysPostgreSQL, MySQLAd-hoc queries, joins, transactions, constraintsScaling writes beyond one primary needs sharding
Key-valuekey → value (value is opaque)Redis, DynamoDB, MemcachedVery fast get/put by key; scales out easilyAny query not by key
DocumentJSON-like documents, each with its own fieldsMongoDB, Couchbase, FirestoreRecords that are read whole; varied fieldsJoins across documents; data duplicated in many documents
Wide-columnRows grouped by a partition key, sorted by a clustering keyCassandra, ScyllaDB, HBase, BigtableHuge write rates, time-ordered data, multi-datacenterQueries you didn't design the table for; no joins
GraphNodes and edges with propertiesNeo4j, Amazon NeptuneMulti-hop relationship queriesBulk 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

  1. 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:

LetterMeaningExample
AtomicityAll the changes happen, or none doDebit Ana and credit Ben: never only one
ConsistencyConstraints hold before and after (unique emails, foreign keys, balance >= 0)An order can't reference a missing customer
IsolationConcurrent transactions don't see each other's half-done work, to the degree the isolation level promisesTwo people booking the last seat don't both succeed (with the right level or a lock)
DurabilityOnce 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:

  1. 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.
  2. What must never be wrong? Money, inventory, bookings and unique usernames need transactions and constraints. Start relational.
  3. What is the scale, really? Do the arithmetic before reaching for NoSQL "for scale".
  4. How varied is the shape? Many optional, per-category fields suggest documents (or a jsonb column in Postgres).
  5. 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

DataLoadChoiceWhy
Orders, payments500 orders/s at peak, ~50 GB/yearPostgreSQLWell inside one primary (thousands to low tens of thousands of writes/s); needs transactions
Carts, sessions50,000 reads/s, small valuesRedis or DynamoDBPure get/put by key; Redis handles ~100 K ops/s per node
Catalogue2 M products, varied fields, read-heavyPostgres jsonb or MongoDBVaried attributes; read whole; cache in front
Click events200,000 events/s × 300 B ≈ 60 MB/s, ~5 TB/dayCassandra (or Kafka into a warehouse)Write-heavy, append-only, partitioned by user and time
RecommendationsMulti-hop relationship queriesGraph DB, or precomputed in a batch jobJoins 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

Your turn: a ride-hailing app

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.

Your turn: spot the problem

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.

SQL vs NoSQL on one page
  • 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.
Interactive labPractise it: choose the databaseOpens in the ByteAtlas lab. Your progress is saved in this browser.
Check what stuck

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?

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?

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?

4. Which statement about ACID isolation in PostgreSQL is accurate?

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?

6. A social app needs "friends of friends who also like jazz, within 3 hops" answered quickly. Which store is the natural fit?

Score 60% or more and the lesson is marked complete.

Passing the quiz also marks it complete. Progress is saved in this browser only.