Start practising

SYSTEM DESIGN · TECHNOLOGIES · 12 OF 12

Which Database? A Decision Guide from Access Pattern to the Right Tool

A decision guide for choosing storage in a design interview: the four questions to ask (access pattern, consistency, scale, data shape), why a relational database is the default, the thresholds that justify something else, a side-by-side comparison of PostgreSQL, MySQL, MongoDB, Cassandra, DynamoDB, Redis, Kafka, Elasticsearch, etcd, object storage and ClickHouse, common pairings and the mistakes to avoid.

  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
  11. 11
  12. 12
1 / 16

Press play to hear the narration, or step through at your own pace.

Notes

Four questions

Before naming anything, ask four questions. Access: how will the data be read? By key, by range, by free text, by aggregates, or by following relationships. Consistency: is a few seconds of staleness acceptable, and can you ever lose a write? Scale: how many reads and writes a second, how much data, how many regions? Shape: rows, documents, events, large files or a graph? The answers point at the tool.

Ask these first
  • 1. ACCESS: how is data read?
  • by key, by range, by text,
  • by aggregate, by relation
  • 2. CONSISTENCY: is stale or
  • lost data acceptable?
  • 3. SCALE: reads/s, writes/s,
  • total size, regions
  • 4. SHAPE: rows, documents,
  • events, blobs, graphs

The default

Start from a relational database, PostgreSQL or MySQL. You get transactions, constraints and flexible queries, including the ones nobody has thought of yet. One node handles tens of thousands of writes a second and a few terabytes, and replicas, caches, partitioning and sharding take it much further. So the burden of proof is on the alternative. Say what requirement rules out SQL before you reach for anything else.

Why default to SQL
  • • transactions + constraints
  • • any query, including ones
  • you have not thought of
  • • one node: tens of thousands
  • of writes/s, a few TB
  • • replicas, cache, partitions
  • and sharding extend it
  • • every engineer knows it
  • burden of proof is on the
  • alternative

Access pattern

The access pattern is the strongest signal. Joins and ad-hoc filters: SQL. Get and put by key at large scale: DynamoDB. A key plus a time range under huge write volume, like messages per chat: Cassandra. A whole nested record at once: MongoDB. Free text with relevance ranking: Elasticsearch. Sums and counts over billions of rows: ClickHouse. A sub-millisecond hot value: Redis. A stream read in order: Kafka. A large file: object storage.

Access pattern → store
joins, ad-hoc filters     SQL
get/put by key at scale   DynamoDB
key + time range, huge    Cassandra
  write volume
whole nested record       MongoDB
free text, relevance      Elasticsearch
sum/count over billions   ClickHouse
hot key, sub-ms           Redis
read a stream in order    Kafka
large file by key         S3

Consistency

Consistency is the second filter. When correctness is strict, like account balances, inventory counts, seat bookings or unique usernames, you want transactions, so SQL, or Spanner and CockroachDB if it must be global. When a few seconds of lag is fine, like like counts, view counts and feeds, Cassandra and DynamoDB give you scale and availability. And when machines must agree on a leader or a lock, that is etcd or ZooKeeper. Concepts episodes 25 and 26 cover the theory.

Consistency need → store
  • STRICT (money, stock, seats,
  • unique usernames)
  • → SQL transactions;
  • global: Spanner, CockroachDB
  • EVENTUAL OK (likes, views,
  • feeds, timelines)
  • → Cassandra, DynamoDB
  • AGREEMENT (leader, locks)
  • → etcd / ZooKeeper

Scale thresholds

Now the numbers, as rough rules. Under about ten thousand writes a second and a few terabytes, one well-run relational primary is fine. Between ten and a hundred thousand writes a second, either shard the relational database or move to Cassandra or DynamoDB. Beyond that, a partitioned NoSQL store is the natural fit. Reads almost never force a change: add replicas and a cache. Writes accepted in several regions at once point to Cassandra or DynamoDB global tables.

When numbers force a change
writes  < ~10k/s      one SQL primary
        10k - 100k/s   shard SQL, or
                       Cassandra/Dynamo
        > 100k/s       partitioned NoSQL
reads   any            replicas + cache
size    < ~5 TB        one node is fine
        > 10s of TB    shard / partition
regions active-active  Cassandra, Dynamo
                       global tables

Data shape

Finally, data shape. Related rows with foreign keys belong in SQL. Self-contained nested documents with varying fields suit MongoDB, or a JSONB column in Postgres. A stream of events goes through Kafka and then into whichever store serves the reads. Files bigger than about a megabyte go to object storage, with their metadata in a database. And deep graph traversals, like friends of friends, may justify a graph database such as Neo4j.

Data shape → store
  • related rows → SQL
  • self-contained nested
  • documents → MongoDB
  • (or Postgres JSONB)
  • events, changes over time
  • → Kafka, then a store
  • files over ~1 MB → S3,
  • metadata in a database
  • graphs (friends of friends)
  • → a graph DB, or SQL
  • with care

Comparison 1

Here is the first half of the table, the primary stores. Postgres and MySQL are relational and strongly consistent, scaling writes with one primary, then sharding, with Vitess for MySQL. MongoDB is documents with tunable consistency and built-in sharding. Cassandra is wide-column, tunable, scaling roughly linearly. DynamoDB is managed key-value with eventual or strong reads. Only the relational ones do real joins, and only Cassandra and DynamoDB do multi-region writes natively.

Primary data stores
store      model       consist.  scale
Postgres   relational  strong    1 primary
MySQL      relational  strong    Vitess
MongoDB    document    tunable   shards
Cassandra  wide-col    tunable   linear
DynamoDB   key-value   evt/str   managed

joins: SQL only (Mongo $lookup)
multi-region writes: Cass, Dynamo

Comparison 2

And the specialists. Redis for caches and counters at sub-millisecond latency. Elasticsearch for text search in tens of milliseconds. Kafka as the event log. ClickHouse for aggregates over billions of rows in under a second. Object storage for files. etcd for coordination. Apart from object storage for files, none of these should be the only copy of your core data; they sit beside a primary store.

Specialist stores
store       job             latency
Redis       cache, counters  sub-ms
Elastic     text search      10-100 ms
Kafka       event log        5-50 ms
ClickHouse  aggregates       100ms-1s
S3          blobs            10-100 ms
etcd        coordination     1-10 ms

none of these should be
your only copy of core data*
*except S3 for files

Polyglot

Real systems use several stores, which is called polyglot persistence. An online shop might keep products and orders in Postgres as the source of truth, cache product pages and sessions in Redis, store images in object storage behind a CDN, publish every change to Kafka, and from there feed Elasticsearch for the search box and ClickHouse for the sales dashboard. Each store is there because a specific requirement needs it.

One shop, six stores
  • Postgres: products, orders
  • (source of truth)
  • Redis: product page cache,
  • sessions, rate limits
  • S3 + CDN: product images
  • Kafka: change events
  • Elasticsearch: search box
  • ClickHouse: sales dashboard
  • each one is justified by
  • a requirement

Keeping in sync

More stores mean more ways to disagree, so follow a few rules. Each piece of data has exactly one source of truth. Search indexes, caches and analytics tables are derived from it through change data capture or an outbox, Concepts episode 33, not through dual writes in application code, which drift when one write fails. Every derived store must be rebuildable from the source. And say the lag out loud: search is about a second behind.

Rules for many stores
  • • one source of truth per
  • piece of data
  • • derived stores (search,
  • cache, analytics) are fed
  • by CDC or an outbox
  • • no dual writes from app
  • code: they drift
  • • every derived store must
  • be rebuildable from truth
  • • say the lag out loud

Common pairings

Some typical pairings. A URL shortener: DynamoDB or SQL keyed by code, plus Redis for hot links. Chat: Cassandra partitioned by conversation, Redis for presence. A news feed: SQL for the social graph, Redis for precomputed timelines, Cassandra for posts at scale. Ride hailing: Redis geo for live driver locations, SQL for trips and payments. Payments: SQL with strict transactions. Video: object storage, a CDN and SQL for metadata. Metrics: Prometheus or ClickHouse.

Problem → main stores
URL shortener    DynamoDB/SQL + Redis
chat messages    Cassandra + Redis
news feed        SQL + Redis timelines
                 + Cassandra for posts
ride hailing     Redis geo + SQL trips
payments         SQL, strict txns
video platform   S3 + CDN + SQL meta
metrics          Prometheus/ClickHouse
web search       custom inverted index

Mistakes

Mistakes to avoid. Picking NoSQL because it scales, without any numbers that require it. Using MongoDB for data that is mostly relationships and joins. Treating Elasticsearch or Redis as the only copy of important data. Querying Kafka like a database. Storing large files inside database rows. Running Cassandra for ten gigabytes. And adding a new store for every feature: each one is something to operate, monitor and keep in sync.

Common mistakes
  • ✗ "NoSQL because it scales"
  • with no numbers
  • ✗ MongoDB for highly
  • relational, joined data
  • ✗ Elasticsearch or Redis as
  • the only copy of data
  • ✗ Kafka as a query database
  • ✗ blobs inside SQL rows
  • ✗ Cassandra for 10 GB
  • ✗ a new store per feature

Why not X?

Expect the follow-up: why not something else? Answer with a requirement, not a preference. Why not Cassandra for orders? Because placing an order must update the order and the stock in one transaction, and writes are around two thousand a second, so one Postgres primary is plenty. Why not Postgres for chat? Because two hundred thousand message writes a second in three regions needs a store that partitions by chat and accepts writes everywhere.

Answering "why not X?"
  • "Why not Cassandra here?"
  • → "We need a transaction
  • across order and stock, and
  • writes are ~2k/s, so one
  • Postgres primary is enough."
  • "Why not just Postgres?"
  • → "200k message writes/s
  • across 3 regions; Cassandra
  • partitions by chat_id and
  • accepts writes everywhere."

Say it like this

Here is a template that works for any storage choice. The main reads are this access pattern, we need strict or eventual consistency, we expect this many writes a second and this much data a year, so I will use this store. The trade-off is this cost, which is acceptable because of this reason. Then add how derived stores like search and analytics are fed. Four sentences, and the interviewer hears reasoning, not brand names.

A template that works
  • "The main reads are <access
  • pattern>, we need <strict /
  • eventual> consistency, and
  • we expect <N> writes/s and
  • <size> per year, so I’ll use
  • <store>. The trade-off is
  • <cost>, which is fine because
  • <reason>. Search and
  • analytics are derived via CDC."

Interview questions

How do you choose a database in a system design interview?

Work from the access pattern, the consistency requirement, the expected scale and the data shape, default to a relational database, and switch only when one of those clearly rules it out. State the trade-off of the choice you make.

When is SQL the wrong choice?

When writes exceed what one primary or a manageable set of shards can handle, when writes must be accepted in several regions at once, or when the only access is simple key lookups at enormous scale, where DynamoDB or Cassandra are simpler and cheaper.

SQL or NoSQL for a chat application?

For message storage at large scale, a wide-column store like Cassandra partitioned by conversation and clustered by time fits the write volume and the newest-first reads; user accounts and group membership can stay in SQL.

Which store would you use for a leaderboard?

A Redis sorted set, which keeps members ordered by score and returns the top N or a player’s rank in logarithmic time, backed by a durable database for the underlying scores.

Where would you store uploaded images and videos?

In object storage such as S3, uploaded directly by clients via presigned URLs and served through a CDN, with metadata such as owner, size and status in a database.

How do you keep a search index consistent with the database?

Keep the database as the source of truth and feed the index through change data capture or a transactional outbox into a queue, with a worker that bulk-indexes changes; accept and state about a second of lag.

What is polyglot persistence and what is its cost?

Using several specialised stores in one system, each for the job it does best. The cost is operating, monitoring and synchronising each one, so every additional store should be justified by a requirement.

Which database for strongly consistent data across regions?

A globally distributed SQL database such as Google Spanner or CockroachDB, which use consensus per data range to offer serializable transactions across regions at the cost of cross-region write latency.