Choosing a database

The first question — what's the access pattern?

Everything else hangs off this. Five access patterns that map cleanly onto storage families:

OLTP — when the answer is "Postgres"

Online transaction processing. Many small reads and writes, foreign keys, multi-statement transactions, joins. The default for almost any product database. Default to Postgres unless you can defend something else.

KV — when the answer is "DynamoDB" (or Cassandra, or Redis)

Pure key-value access: get, put, delete by key. No joins, no range scans, no transactions across keys. The shape that scales horizontally with the least fuss.

OLAP — when the answer is "BigQuery" (or Snowflake, or ClickHouse)

Analytical workloads: large scans, aggregations, joins over billions of rows, long-running queries. Columnar storage, parallel execution, and decoupled compute / storage.

Time-series — when the answer is "Prometheus" or "TimescaleDB"

A narrow but important case: each row keyed by (timestamp, label-set, value). Heavy writes, append-only, range reads dominate.

Search — when the answer is "Elasticsearch" or a vector DB

Inverted-index lookups (full-text), faceted search, autocomplete, and the new class of vector / embedding retrieval. A different shape from any of the above: the index lives in RAM, and the query is a ranking function.

Graph — when the answer is "actually, you don't need a graph DB"

The contrarian answer that interviewers like. Most "graph" use cases (friend lists, follower graphs, permission inheritance) are recursive joins solved cleanly with adjacency tables in Postgres. A real graph database earns its keep only when:

• Queries traverse many hops (5+) and the depth is variable.
• The data has rich edge properties and edge types matter for the query.
• The product needs Cypher / Gremlin / SPARQL ergonomics.

The decision tree, in five questions

Walk these in order. The first that doesn't fit narrows the choice.

1. Is the workload analytical (large scans, aggregations) or transactional (small reads/writes)? Analytical → OLAP family. Transactional → next question.
2. Do you need joins and multi-statement transactions? Yes → OLTP (Postgres / MySQL). No → next question.
3. Is the data shape pure key-value, or do you need range scans / secondary indexes? Pure KV → DynamoDB / Cassandra / Redis. Range / indexes → Postgres anyway, or column-family stores like Bigtable.
4. Is search (full-text, ranking, vector) the dominant access pattern? Yes → Elasticsearch / pgvector / Pinecone. No → previous answer holds.
5. Do you need cross-region transactions or strict global ordering? Yes → Spanner / CockroachDB / YugabyteDB. No → single-region Postgres.

And for time-series specifically: heavy time-keyed writes + range reads = the time-series family. Don't try to retrofit Postgres for hundreds of millions of metrics-per-second; you'll lose.

Polyglot persistence — and the trap

Real systems use multiple databases. The OLTP store, the OLAP warehouse, the cache, the search index, the time-series metrics. The pattern is:

1. OLTP is the source of truth. Everything else is a derived view.
2. CDC (change-data-capture) feeds the derived stores. Debezium for Postgres / MySQL → Kafka → consumers populate ES, ClickHouse, Redis.
3. One write, many reads. Application writes only to the OLTP. Reads go wherever the access pattern is best served.
4. Reconciliation jobs catch drift. The derived stores eventually fall out of sync with reality; nightly or weekly jobs validate and repair.

The rewrite cost — when the choice is wrong

Picking the wrong database is one of the most expensive mistakes a system can make. The cost isn't the database. It's the rewrite to escape it.

The interview answer that lands

In a system-design round, "what database would you use" is the cue. The candidate who passes:

1. States the access pattern explicitly. "This is read-heavy OLTP with occasional analytical queries."
2. Picks a default and defends it. "I'd start with Postgres because I have ≤ 50K writes/s, Postgres handles that on a single primary, and the team knows it."
3. Names what would change the answer. "If write throughput crossed 100K/s I'd shard with Citus or move to CockroachDB. If we needed cross-region, Spanner."
4. Explains the analytics path separately. "Analytics queries go to a warehouse fed by CDC — BigQuery or ClickHouse depending on cloud."

And the closing thought: "The default is Postgres until I have a reason it doesn't work. The interesting question isn't 'which database' — it's 'which constraint forces me off Postgres'." That sentence is the senior framing.

Further reading

• Kleppmann — Designing Data-Intensive Applications, Chapters 3–4 & 7. The textbook on storage engines and isolation. Read these chapters before any design round.
• Petrov — Database Internals. Pairs with DDIA; goes deeper on B-trees, LSM, and replication primitives.
• Helland — "Life Beyond Distributed Transactions" (2007). The canonical "stop trying to do this" essay. Aged extremely well.
• Helland — "Immutability Changes Everything" (CIDR 2015). The CDC + derived-store architecture, articulated.
• Spanner paper (Corbett et al, 2012). The reference for "yes you can do globally consistent transactions; here's the cost".
• Dynamo paper (DeCandia et al, 2007). The reference for the KV family.
• Adjacent: Databases study path. The full curriculum.
• Adjacent: Distributed KV store. The KV-family system design walkthrough.
• Adjacent: Replication. The replication-shape choice that's downstream of database choice.