SQL vs. NoSQL

  • Lesson 9 of 40
  • 11 min
  • Beginner
  • 10 steps

There is no winning database: each family handles one kind of data well and pays for it elsewhere. Here you will see when each one makes sense.

SQL DB load
40%
Data stores
4
1M usersApp serverApp serverSQL databaseDocumentsKey-value · 4 nodesSearchfull-textSearchTablesusers(id, name, email)orders(id, user_id, total)orders.user_id → users.ida JOIN combines them in one queryACIDBEGIN … COMMITOne transactionBEGINUPDATE … balance − 30 · account 17UPDATE … balance + 30 · account 42COMMIT: both or neither90%ALTER TABLE · 200M rows · hoursproducts · orders · users45%product catalogTwo documents{ "sku": "boot-38", "size": 38, "colors": ["blue", "grey"] }{ "sku": "tablet-3", "inches": 11, "memory_gb": 128 }same collection, different shapes40%sessionsKey → valuesession:a71e → { user: 9, cart: [3, 8] }GET / SET by key · ~1 msno queries: reads by key only40%SQL vs NoSQL, by defaultSQLNoSQLdata shapefixed tablesflexiblejoinsyesin your codetransactionsmany rowsusually 1 recordscaling writeshardbuilt in40%orders · paymentscatalogsessions · carts40%"red sneakers"change stream40%Built for one jobsearchfree text, relevance, typostime seriesmetrics, sensorsgraphmulti-hop relationshipswide columnmassive write volumesobjectfiles, images, backups40%source of truth
  • request
  • response
  • slow request
  • waiting in queue
  • batch

Step 1 of 10

Concept

Data lives in tables

Almost every app starts with a relational (SQL) database such as PostgreSQL. Each table has columns defined up front: that contract is the schema.

Tables link to each other through keys: orders.user_id points at users.id. A JOIN combines both in a single query, for example "every order with the name of whoever placed it".

Step 2 of 10

Technique

All or nothing: transactions

Moving 30 from account 17 to account 42 takes two writes: subtract from one, add to the other. If the server dies right between them, the money vanishes.

A transaction groups them: both are committed or neither is applied. That is why payments, orders and inventory usually live in a relational database.

What changed

  • Two writes inside one transaction

Step 3 of 10

Problem

The schema and the server fall short

The product catalog moves into the same database. A boot has a size, a tablet has a screen in inches and a book has an author, so the products table piles up hundreds of mostly empty columns.

Every new product type needs another migration on a 200M-row table, and some of them rewrite or lock the table for hours. On top of that, every write still goes through a single server, already at 90%. The figures are example values.

What changed

  • Catalog inside the SQL database
  • Products table with hundreds of columns
  • DB load: 35% → 90%

Step 4 of 10

Solution

Each record with its own shape

A document store keeps each record as a complete document, much like JSON. The boot and the tablet carry different fields and there is no migration to run.

The price: your code has to understand every shape it has ever stored. In return, everything about a product sits together, is read in one go, and documents spread well across several servers.

What changed

  • Document store for the catalog
  • The SQL database goes back to orders and users
  • DB load: 90% → 45%

Step 5 of 10

Technique

Look up by key, nothing else

The simplest store maps a key to a value. No queries, no joins: you only read and write by key, in about a millisecond when the data lives in memory.

Since every key stands alone, spreading the data over several nodes comes almost for free: the key itself decides which node it lives on.

What changed

  • Key-value cluster for sessions
  • DB load: 45% → 40%

Step 6 of 10

Trade-off

What NoSQL leaves behind

Much of how easily NoSQL scales comes from giving things up: joins across records, transactions spanning several records and, often, strict consistency.

These are tendencies, not laws. PostgreSQL stores and queries JSON, several NoSQL stores now offer multi-document transactions, and distributed SQL databases spread writes out without losing joins or transactions, paying for it with more latency and complexity.

Step 7 of 10

Solution

Each piece of data where it fits best

Real systems mix stores: this is polyglot persistence. Orders and payments stay in SQL for the transactions, the catalog lives in documents and sessions in key-value.

Start with a single database and add another only when a kind of data clearly asks for it.

What changed

  • Orders and payments → SQL
  • Catalog → documents
  • Sessions and carts → key-value

Step 8 of 10

Problem

Case: "red sneakers"

Customers type free text into the search box. In SQL that ends up as WHERE name LIKE '%red%': with the wildcard at the start a regular index cannot help, millions of rows get scanned, and there is still no relevance ranking, no typo tolerance, and "sneaker" does not find "sneakers".

A search engine builds an inverted index: for every word, the list of documents that contain it. It is fed from the catalog by a change stream.

What changed

  • Search engine for the catalog
  • Change stream: documents → index

Step 9 of 10

Concept

Stores built for a single job

The search engine is one example of a store designed for a specific access pattern. There are more, and each beats a general database at its job and falls behind at everything else.

Add them when a workload clearly does not fit, not out of habit: every extra store has to be run, secured, backed up and kept in sync.

Step 10 of 10

Summary

Summary

SQL: tables, joins and transactions; a safe place to start.

Documents: flexible, complete records that spread well across servers.

Key-value: the simplest and fastest, but you only read by key.

Choose by kind of data; many systems use several stores.

Specialists such as search are copies fed from the source of truth.

You will learn

  • Tables, joins and transactions
  • Where a relational database gets stuck
  • Document and key-value stores
  • Choosing by kind of data
  • Search engines and other specialised stores