Engineering Atlas › System Design Interviews

Choosing a database for the job

14 min read · last reviewed 2026-10-17

Open in the interactive app: practice, quiz, mock interview →

In one minute

How it works

The families and when they win

A decision framework

  1. Data shape. Relational, nested documents, key to value, time series, graph, blobs?
  2. Access pattern. Point lookups, range scans, ad-hoc joins, full-text, aggregations over many rows, traversals?
  3. Consistency. Which operations need transactions or linearizable reads? Can others be eventually consistent?
  4. Scale. Writes per second, data size in five years, read-to-write ratio, number of regions.
  5. Latency. p99 budgets for the hot paths; sub-millisecond suggests memory.
  6. Operations. Managed or self-run, team expertise, backup and restore, cost.

Say the answer as a chain: "Orders need multi-row transactions and the volume is a few thousand writes per second, so Postgres, sharded by customer later if needed. Product search needs relevance ranking, so an OpenSearch index fed by CDC. Clickstream is 300k events per second of append-only data queried in aggregate, so Kafka into ClickHouse."

What interviewers look for

A reason for each store tied to a stated requirement, awareness of what you give up (joins and ad-hoc queries in Cassandra, transactions across items in most key-value stores before recent additions, write latency in Spanner), a clear source of truth, and the pipeline that keeps derived stores in sync.

Common mistakes

Down the stack

Layer 1The contract

Each family promises different things. A relational database promises ACID transactions with a defined isolation level, constraints, and arbitrary queries with an optimiser. Cassandra promises tunable consistency per request and high availability, but only single-partition atomicity (with lightweight transactions via Paxos at much higher cost). DynamoDB promises single-digit millisecond latency at any scale for key access, which it held at a Prime Day 2021 peak of 89.2 million requests per second (USENIX ATC 2022), with optional strongly consistent reads and limited multi-item transactions: up to 100 items and 4 MB in one Region, with every item read or written twice, once to prepare and once to commit (DynamoDB docs). Elasticsearch promises near-real-time search, with documents typically visible after a refresh interval of one second by default, not immediately. A shard with no searches for 30 seconds stops refreshing in the background until a search arrives, so the first query after a quiet spell pays for the refresh (Elastic docs). Object storage promises very high durability (S3 advertises eleven nines) and, on S3 since December 2020, strong read-after-write consistency for GET, PUT and LIST at no extra cost (AWS), but per-request latency in the tens of milliseconds.

Layer 2The mechanism

The storage engine explains the promises. Postgres and InnoDB store rows in B-tree-organised pages with a WAL and MVCC, ideal for point reads and updates in place. Cassandra, ScyllaDB, HBase and RocksDB-based stores use LSM trees: memtable, commit log, immutable SSTables and compaction, giving cheap writes and costlier reads. Columnar stores such as ClickHouse write each column separately in compressed blocks (its MergeTree engine sorts by a primary key and keeps a sparse index of one entry per granule of 8,192 rows by default), so a query reading 3 of 100 columns reads about 3 percent of the bytes, and vectorised execution processes column chunks in tight CPU loops. Search engines build inverted indexes (term to postings list) in immutable Lucene segments that are merged in the background. Graph databases store adjacency directly so a traversal follows pointers instead of joining tables. Object stores split data into chunks spread across many disks with replication or erasure coding, with a separate metadata index.

Layer 3The machine

Row stores read whole pages, 8 KB in Postgres and 16 KB in InnoDB, so fetching one row is efficient but scanning one column of a billion rows reads every page. A column store compresses similar values together, often achieving 5 to 10 times compression or more on real data, and reads only the needed columns sequentially, so a scan runs at disk or memory bandwidth (GB/s) instead of IOPS. LSM stores turn random writes into sequential ones, which suits SSDs and HDDs alike, but compaction consumes background I/O and can double space temporarily. In-memory stores avoid disk on the read path entirely, so a lookup is a hash probe of about 100 ns plus the network hop, but RAM costs much more per GB than SSD, and object storage costs less still per GB, which is why blobs never belong in a database row.

Why it's built this way

Different access patterns stress different hardware resources: point lookups stress random I/O, scans stress bandwidth, high write rates stress write amplification, and traversals stress pointer chasing. No single layout is optimal for all of them, so each family picks a layout for its target pattern. Distribution then adds the consistency cost, so systems that need scale relax either transactions or latency. That is why one design ends up with several stores, each a copy organised for one access pattern, connected by a change log.

Diagram

flowchart TD
    S["Workload requirements"] --> T{"Multi-row transactions?"}
    T -->|"yes, moderate scale"| R["Relational"]
    T -->|"yes, huge or multi-region"| N["Distributed SQL"]
    T -->|"no"| K{"Access only by key?"}
    K -->|"yes, fits in RAM"| M["In-memory KV"]
    K -->|"yes, very high writes"| W["Wide-column"]
    K -->|"yes"| KV["Key-value"]
    K -->|"no"| X{"Special shape?"}
    X -->|"time series"| TS["Time-series"]
    X -->|"deep traversals"| G["Graph"]
    X -->|"full text or analytics"| D["Search or columnar, fed by CDC"]

Worked example

Design the storage for a ride-hailing platform. Requirements: trips and payments must be correct; driver locations update 1.25 million times per second; riders search for places by name; the business wants dashboards over all trips.

Trips and payments: a few thousand trip state changes per second at peak, multi-row transactions (trip, payment, driver earnings). Relational, sharded by city or rider later; Postgres or a distributed SQL store if multi-region writes are required. This is the source of truth.

Driver locations: 1.25 million small writes per second, only the latest location matters, and it is read by geospatial cell. An in-memory key-value store (Redis with geo commands, sharded by cell), with a TTL so stale drivers disappear. History is appended to Kafka and archived to object storage in batches.

Place search: name and address search with typo tolerance across about 50 million places. An OpenSearch index built from the places table via CDC; a few seconds of staleness is fine.

Dashboards: all trips ever, aggregated by city, hour and product. A columnar warehouse fed from Kafka and CDC; queries scan billions of rows in seconds.

Each store has one job, one source, and a stated staleness. That last sentence is what interviewers want to hear.

In the wild

Trade-offs

OptionStrengthsWeaknessesChoose when
RelationalTransactions, constraints, flexible queriesWrite scale beyond one node needs shardingSystem of record, most apps
Distributed SQLSQL plus horizontal scale and multi-regionHigher write latency and costGlobal transactional data
Key-valuePredictable latency at any scaleQueries limited to keysSessions, carts, profiles, lookups
Wide-columnMassive writes, multi-DCQuery by key only, tuning burdenMessaging, feeds, IoT, event logs
DocumentFlexible nested recordsCross-document joins and transactions limitedCatalogues, content, varied schemas
ColumnarFast large scans and aggregatesSlow point updatesAnalytics and reporting
SearchRelevance, fuzzy text, facetsNot a source of truth, eventualSearch boxes, log exploration

Pitfalls in production

Leadership lens

Check yourself

  1. Why is Elasticsearch a poor source of truth even though it stores the documents?

    Show answer

    It is near-real-time and eventually consistent, has weaker durability and transaction guarantees, and reindexing or mapping changes often require rebuilding from another source.

  2. Why does a columnar store answer "revenue by city per day over a year" so much faster than a row store?

    Show answer

    It reads only the few needed columns, compressed and sequential, and processes them in vectorised batches, instead of reading every full row page.

  3. When would you choose distributed SQL over sharded Postgres?

    Show answer

    When you need cross-shard or multi-region transactions with strong consistency and want the database, not the application, to handle resharding, and you accept higher write latency and cost.

  4. What do you lose by choosing Cassandra for user messages?

    Show answer

    Multi-partition transactions and ad-hoc queries; every query must be designed around a partition key, and secondary access patterns need extra tables.

Sources and further study

Primary

Systems docs

Books

Other curricula

Watch

Free resources

Open in the interactive app: practice, quiz, mock interview →