Start practising

SYSTEM DESIGN · CONCEPTS · DATABASES · EPISODE 24

Database Types: Which Database for What

Relational, key-value, document, wide-column, graph, time-series and search: what each one is built around, the query it makes cheap, real products, and how to pick one (or three) for a system design answer.

  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
  11. 11
  12. 12
  13. 13
  14. 14
  15. 15
  16. 16
  17. 17
  18. 18
  19. 19
  20. 20
  21. 21
  22. 22
  23. 23
  24. 24
  25. 25
  26. 26
  27. 27
  28. 28
  29. 29
  30. 30
  31. 31
  32. 32
  33. 33
  34. 34
  35. 35
  36. 36
  37. 37
  38. 38
  39. 39
  40. 40
  41. 41
1 / 11

Press play to hear the narration, or step through at your own pace. Every picture here is the same one used in the YouTube video.

Notes

Relational

Relational. Tables with a fixed schema, linked by keys. What it makes cheap: joins, ad-hoc queries you didn't plan for, and transactions across many rows. PostgreSQL and MySQL. Use it for users, orders, payments, anything with relationships or money. It's the default; you need a reason to pick something else.

Relational
  • data: tables with a schema
  • cheap: joins, ad-hoc queries,
  • multi-row transactions
  • PostgreSQL, MySQL, SQL Server
  • use for: users, orders,
  • payments, anything with
  • relationships and money

Key-value

Key-value. The simplest model: put a value under a key, get it back by that key. No queries on the value. In exchange, lookups are sub-millisecond and scale almost without limit. Redis in memory, DynamoDB on disk. Use it for sessions, caches, shopping carts, rate-limit counters, feature flags.

Key-value
put(key, value)
get(key) → value

cheap: lookup by exact key,
  sub-millisecond, huge scale

Redis (RAM), DynamoDB,
etcd, Memcached

use for: sessions, caches,
carts, rate limits, config

Document

Document. Each record is a JSON-like tree: a product with its variants and specs nested inside. What's cheap: reading or writing the whole object in one go, and records that don't all share the same fields. MongoDB, Firestore. Use it for product catalogs, content, user profiles. What's expensive: joins across documents.

Document
{ "id": 42, "name": "Lamp",
  "variants": [ {...}, {...} ],
  "specs": { ... } }

cheap: read/write a whole
  nested object, flexible shape

MongoDB, Firestore, Couchbase
use for: catalogs, CMS, profiles

Wide-column

Wide-column. Rows grouped by a partition key and sorted inside it: all messages for one chat, ordered by time. It sits on the LSM trees from last episode, so it swallows enormous write rates and makes 'latest fifty for this key' cheap. Cassandra, ScyllaDB, Bigtable. Discord stores trillions of messages this way. The catch: you design tables per query, up front.

Wide-column
partition key → sorted rows
(user_id) → messages by time

cheap: huge write rates,
  "latest N for this key"

Cassandra, ScyllaDB, HBase,
Bigtable
use for: chat history, feeds,
activity logs, IoT

Graph

Graph. Data as nodes and edges. What's cheap is following relationships: friends of friends who like this band, the shortest path between two accounts. In SQL each hop is another join and it gets slow fast; a graph database just follows pointers. Neo4j, Neptune. Use it for social graphs, fraud-ring detection, recommendations.

Graph
  • data: nodes + edges
  • cheap: traversals,
  • "friends of friends who
  • like X", shortest paths
  • in SQL: one JOIN per hop;
  • in a graph DB: follow pointers
  • Neo4j, Amazon Neptune
  • use for: social, fraud rings,
  • recommendations

Time-series

Time-series. Every point is a value at a timestamp, with tags. Writes are append-only, and the cheap queries are averages and maxima over time windows, plus automatic downsampling and expiry of old data. Prometheus, TimescaleDB, InfluxDB. Use it for metrics, monitoring, sensor data, price ticks.

Time-series
(metric, tags, time) → value

cheap: append-only writes,
  avg/max over time windows,
  downsampling, expiry

Prometheus, TimescaleDB,
InfluxDB
use for: metrics, monitoring,
sensors, stock ticks

Search

Search. An inverted index maps every word to the documents containing it, so full-text queries, relevance ranking, typo tolerance and facet counts are cheap. Elasticsearch, OpenSearch. One important habit: it's almost always a secondary copy, fed from your main database, not the source of truth.

Search engine
inverted index:
"red"   → doc 3, 9, 41
"shoes" → doc 9, 41, 77

cheap: full-text search,
  relevance ranking, typos,
  facets ("size: 9 (124)")

Elasticsearch, OpenSearch
usually a copy, not the truth

Polyglot

Real systems mix them. A shop keeps orders and users in PostgreSQL as the source of truth, sessions and carts in Redis, and product search in Elasticsearch, fed by change data capture from the main database. That's called polyglot persistence: each store does the one job it's built for.

Polyglot persistence
  • e-commerce example:
  • PostgreSQL: orders, users
  • (source of truth)
  • Redis: sessions, cart, cache
  • Elasticsearch: product search
  • (fed by change data capture)
  • each store does one job well

Decision

How to choose, in order. Start relational. Need sub-millisecond reads by key: add a key-value cache. A firehose of writes per key: wide-column. Deep relationship queries: graph. Metrics over time: time-series. Full-text: a search index. The trick in an interview is to name the query first, then the store that makes it cheap.

How to choose
  • 1. start relational
  • 2. need sub-ms by key? + KV cache
  • 3. firehose writes per key?
  • wide-column
  • 4. deep relationships? graph
  • 5. metrics over time? time-series
  • 6. full-text? search index
  • name the query, then the store

Interview questions

When is a relational database the right default?

When data has relationships, you need joins or ad-hoc queries you have not planned for, and you need multi-row transactions (orders, payments, users). Most systems should start here and add other stores only for queries it cannot serve well.

What does a key-value store give up, and what does it gain?

It gives up querying by anything other than the exact key (no filters on the value, no joins). In exchange it offers sub-millisecond lookups and near-unlimited horizontal scale, which suits sessions, caches, carts and counters.

When would you choose a document database over relational?

When records are naturally nested objects read and written as a whole (a product with variants and specs, a CMS page) and their shape varies between records. Avoid it when you need many cross-record joins or multi-entity transactions.

Why do chat apps often use wide-column stores like Cassandra?

Messages are written at very high rates and read as "latest N messages for this channel". Partitioning by channel and sorting by time makes that read a single sequential slice, and the LSM storage engine absorbs the write load.

Why is a graph database faster than SQL for friends-of-friends queries?

In SQL each hop is another join over large tables, so cost grows quickly with depth. A graph database stores edges as direct references and traverses them by following pointers, so multi-hop queries stay fast.

What makes time-series databases different?

They are optimised for append-only writes keyed by timestamp and for aggregations over time windows, with built-in downsampling and retention policies, which suits metrics, monitoring and sensor data.

Why is a search engine usually not the source of truth?

Search indexes are built for retrieval and ranking, not transactional guarantees, and are typically eventually consistent. Systems keep the authoritative data in a primary database and feed the index through change data capture or a queue.

Design the storage for an e-commerce site.

PostgreSQL for users, orders, payments and inventory (source of truth with transactions), Redis for sessions, carts and hot caches, and Elasticsearch for product search and facets, kept in sync from PostgreSQL via change data capture.