system-design

Choosing a Database: A Decision Guide

A practical flowchart, the questions that actually determine the answer, and permission to pick Postgres.

Prerequisites: Databases Overview, NoSQL Modeling Time to read: ~20 minutes

Start here

🚨 Default to PostgreSQL. Move away from it only when you can name the specific thing it can’t do.

This isn’t laziness — it’s the correct engineering position, and stating it confidently in an interview is a strength, not a weakness. Postgres gives you:

ACID transactions and referential integrity
Rich SQL, ad-hoc queries, and a mature planner
JSONB with indexing — document-store flexibility without a second system
Full-text search — good enough for a large fraction of search needs
PostGIS — best-in-class geospatial
Arrays, ranges, and exclusion constraints
Logical replication, enabling CDC and zero-downtime upgrades
Extensions: pgvector (embeddings), TimescaleDB (time series), Citus (sharding)
A vast talent pool and excellent managed offerings

📐 A well-tuned Postgres on modern hardware handles tens of thousands of writes/second and several terabytes comfortably. Most systems you will ever build never exceed that.

🎙️ “I’d start with Postgres. It covers the relational core, JSONB handles the semi-structured fields, and full-text search is adequate for now. I’d add a specialized store only when a specific workload demonstrably outgrows it — each additional datastore is real operational cost.”

The five questions that decide it

Ask these in order. The answers usually pick the database for you.

1. What are the access patterns?

Known, fixed, key-based → NoSQL is viable.
Evolving, ad-hoc, analytical → relational. You need the join as an escape hatch.

🚨 The most common cause of a failed NoSQL adoption is choosing it before the access patterns stabilized. → NoSQL Modeling

2. What’s the scale — actually?

Do the estimation before answering.

Data / write rate	Answer
< 1 TB, < 10k writes/s	One Postgres. Not a debate.
1–10 TB, < 20k writes/s	Postgres + read replicas + cache
> 10 TB or > 50k writes/s	Shard, or NoSQL, or NewSQL
Petabytes, write-dominated	Wide-column (Cassandra, Bigtable)

3. What consistency does each piece of data need?

Payments and inventory: strong. View counts and recommendations: eventual. This is per-field, and it may mean two databases. → Consistency Models

4. What shape is the data?

Relational with many-to-many → relational. Self-contained aggregates → document. Time-ordered events per entity → wide-column or time-series. Relationships are the query → graph.

5. What can the team operate at 3 a.m.?

🚨 The most underweighted factor. A database nobody can debug during an incident is a liability regardless of benchmarks. Managed services (RDS, Aurora, Atlas, DynamoDB) shift much of this, and choosing one is a legitimate senior answer.

The decision tree

flowchart TB
    S{Is it relational data<br/>with evolving queries?} -->|yes| P[(PostgreSQL)]
    S -->|no| T{What kind?}
    T -->|Key lookups only,<br/>ephemeral| R[(Redis)]
    T -->|Huge write volume,<br/>known patterns| C[(Cassandra /<br/>ScyllaDB)]
    T -->|Self-contained documents| M[(MongoDB or<br/>Postgres JSONB)]
    T -->|Time-stamped metrics| TS[(TimescaleDB /<br/>InfluxDB / Prometheus)]
    T -->|Traversing relationships| G[(Neo4j)]
    T -->|Analytics over<br/>billions of rows| A[(ClickHouse /<br/>BigQuery)]
    T -->|Text relevance search| E[(Elasticsearch)]
    T -->|Large immutable blobs| O[(S3)]
    P -->|outgrew one node?| N{Need SQL + ACID<br/>at horizontal scale?}
    N -->|yes| NS[(CockroachDB /<br/>Spanner / Vitess)]
    N -->|no| C

The candidates

Database	Pick it when	Don’t pick it when
PostgreSQL	Almost always, to start	Write volume genuinely exceeds one node
MySQL	Team knows it; Vitess sharding path	You want Postgres’s richer features
Redis	Cache, sessions, rate limits, leaderboards, queues	It’s your source of truth for durable data
MongoDB	Documents, rapid iteration, sharded scale	You need cross-document transactions frequently
Cassandra / ScyllaDB	Massive writes, time-ordered, known patterns	Ad-hoc queries; delete-heavy; small scale
DynamoDB	AWS-native, predictable single-digit-ms, serverless	Query flexibility matters; cost at high read volume
ClickHouse	Analytics over billions of rows, fast	Transactional workloads; frequent updates
Elasticsearch	Text relevance, faceting, log analytics	Source of truth (it’s a derived store)
Neo4j	Deep graph traversal is the core query	General-purpose storage
TimescaleDB / InfluxDB	Metrics, IoT, time-range queries + downsampling	Non-temporal data
CockroachDB / Spanner / TiDB	SQL + ACID at horizontal scale, multi-region	Cost and latency are dominant concerns
S3 / object storage	Blobs, backups, data lake	Anything needing partial updates or low latency

Worked decisions

E-commerce platform

Orders, payments, inventory   → PostgreSQL      (ACID is non-negotiable)
Sessions, cart, hot cache     → Redis           (speed, ephemeral)
Product search                → Elasticsearch   (relevance, faceting)
Product images                → S3 + CDN        (size)
Clickstream events            → Kafka → ClickHouse  (volume, analytics)

🎙️ “Five stores, but each earns its place: transactional integrity, latency, relevance, size, and analytical volume are genuinely different problems. I’d start with Postgres and S3 only, and add the others as each need becomes real.”

Chat application

Message history               → Cassandra       (huge write volume, time-ordered, known access)
User profiles, groups         → PostgreSQL      (relational, modest volume)
Presence / online status      → Redis           (ephemeral, TTL, very high churn)
Media                         → S3 + CDN
Message search                → Elasticsearch

The split is instructive: messages are the high-volume, append-mostly, partition-by-conversation workload that Cassandra exists for. Users and groups are ordinary relational data at ordinary scale. Using one database for both would compromise one of them.

IoT platform

Sensor readings               → TimescaleDB     (time-series, compression, downsampling, retention)
Device registry, config       → PostgreSQL
Real-time dashboards          → Redis / materialized views
Long-term archive             → S3 (Parquet)

Early-stage startup, unknown product

Everything                    → PostgreSQL

🚨 This is the correct answer, and giving it takes more confidence than listing six databases. Access patterns aren’t known yet, scale is small, and engineering time is the scarcest resource. Adding datastores early is how small teams end up spending their time on operations instead of product.

The polyglot persistence tax

Every additional datastore costs you:

Operations — monitoring, backups, upgrades, security patching, capacity planning, on-call expertise. Multiply by N.
Synchronization — data must flow between stores, usually via CDC or events. This is eventually consistent, and it’s a common source of subtle bugs.
No cross-store transactions — you need sagas or compensating logic.
Cognitive load — every engineer must learn every data model.
Drift — duplicated data diverges, and you need reconciliation jobs to detect it.

⚖️ The rule: each datastore must be justified by a workload the existing ones genuinely cannot serve. “It would be a bit faster” isn’t enough.

Managed vs self-hosted

	Managed (RDS, Atlas, Aurora)	Self-hosted
Ops burden	Minimal	Substantial — you’re on call for it
Cost	2–3× the raw compute	Cheaper in dollars, expensive in engineer-hours
Control	Limited (versions, extensions, config)	Full
Backups, failover, patching	Automatic	Yours to build and test
Scaling	Often a click	Yours to build

🎙️ The default answer for most teams is managed, and saying so is not a cop-out: “I’d use RDS rather than self-hosting. Automated backups, multi-AZ failover, and patching are things I’d otherwise have to build and — more importantly — actually test. Self-hosting makes sense at a scale where the cost difference funds a dedicated team.”

⚖️ Trade-offs

Choice	Gain	Cost
Start with Postgres	Simplicity, flexibility, one system to operate	Write ceiling on one node
Add a specialized store	Right tool for a specific workload	Operational multiplication; sync complexity
NewSQL	SQL + ACID at scale	Cost, coordination latency, smaller ecosystem
Managed service	No ops burden	Cost, less control, lock-in
Multiple stores	Each workload optimized	Drift, no cross-store transactions, cognitive load

🚨 Interview traps

Naming a database before asking about access patterns and scale.
Choosing NoSQL “for scale” when the estimate says 200 QPS.
Listing six databases for a simple system. Over-engineering is a negative signal.
Ignoring operational cost of a polyglot design.
Not knowing NewSQL exists when the requirement is SQL semantics at horizontal scale.
Using Elasticsearch or Redis as a source of truth. Both are derived/ephemeral stores.
Not mentioning managed services when the design has an obvious ops burden.

🎙️ Soundbites

“Before picking a store — what are the access patterns, and roughly what scale? Those two answers determine most of this.”
“At 3 TB and 5,000 writes per second, this is one Postgres with replicas. I’d revisit at 10× growth.”
“Postgres for the transactional core, S3 for media. I’d add Elasticsearch when search relevance becomes a real requirement, and not before — each extra datastore is a system we have to operate, monitor, back up, and keep in sync.”
“Messages go to Cassandra because they’re high-volume, append-mostly, and partitioned by conversation. Users and groups stay in Postgres — relational data at ordinary scale.”
“I’d use the managed offering. Backups, failover, and patching are things I’d otherwise have to build *and test, and testing restore procedures is the part teams skip.”*

🛠️ Try it

1. Justify a real choice. Take a system you’ve built. Write down its access patterns, its actual data volume and write rate, and its consistency requirements per field. Then ask: given those numbers, is the database you chose the right one? Most people find they over- or under-engineered, and the gap is instructive.

2. Find the crossover. Load 10 million rows into Postgres. Benchmark writes with pgbench at increasing concurrency. Find where throughput plateaus. That number is your real “when would we need something else?” threshold — and it’s usually higher than expected.

3. Try the same workload two ways. Model a time-series workload in plain Postgres and in TimescaleDB. Compare insert throughput, storage size (compression), and the speed of a time-range aggregate. The difference tells you when a specialized store earns its keep.

4. Price it. For a realistic scenario, compare the monthly cost of: RDS Postgres, DynamoDB on-demand, and a self-hosted cluster on EC2 (including a fraction of an engineer’s salary). Cost arguments carry weight in interviews and almost nobody makes them.

Check yourself

1. Why is "start with Postgres" a strong answer rather than a lazy one?

Because it's usually correct, and because it demonstrates you understand the cost of alternatives. Postgres covers relational data, documents (JSONB), search (full-text), geospatial (PostGIS), time series (TimescaleDB), and vectors (pgvector), with ACID transactions and ad-hoc queries throughout — and one system to operate, monitor, back up, and hire for. It handles tens of thousands of writes per second and several terabytes, which exceeds most systems' lifetime requirements. Choosing a specialized store before you've demonstrated Postgres can't do the job means paying operational cost for capability you don't yet need, and locking in access patterns you haven't validated.

2. What are the five questions that determine a database choice?

(1) **Access patterns** — known and fixed, or evolving and ad-hoc? Fixed patterns permit NoSQL; evolving ones need the relational escape hatch. (2) **Scale** — actual data volume and write rate, estimated properly, not assumed. (3) **Consistency requirements per field** — strong for money and inventory, eventual for counters and recommendations; this may mean more than one store. (4) **Data shape** — relational, document, time-ordered events, or graph. (5) **What the team can operate** at 3 a.m. during an incident, which is the most underweighted factor and the strongest argument for managed services.

3. What does each additional datastore actually cost you?

Operational multiplication: monitoring, alerting, backups (and tested restores), version upgrades, security patching, capacity planning, and on-call expertise — all × N. Data synchronization between stores, typically via CDC or events, which is eventually consistent and a frequent source of subtle bugs. Loss of cross-store transactions, so consistency across systems requires sagas or compensating logic. Cognitive load, since every engineer must understand every data model. And drift — duplicated data diverges over time, requiring reconciliation jobs to detect it. Each store must be justified by a workload the existing ones genuinely cannot serve.

4. When is a chat app's message store a good fit for Cassandra but its user table not?

Messages are extremely high-volume, append-mostly, naturally partitioned by conversation, queried by a known pattern (recent messages in a channel, in time order), and tolerant of eventual consistency — exactly the LSM/wide-column profile. Users and groups are comparatively tiny (millions of rows, not billions), relational (memberships, permissions, friendships are many-to-many), need transactional correctness for things like unique usernames, and are subject to evolving product queries. Forcing both into one store compromises one of them: Postgres would struggle with the message write volume, and Cassandra would make user management painful and unsafe.

5. When would you choose a NewSQL database like CockroachDB or Spanner?

When you need SQL semantics and ACID transactions but have genuinely outgrown a single node's write capacity, or need multi-region deployment with strong consistency and automatic failover — and when sharding a traditional database (giving up cross-shard joins and transactions) would require significant application rework. Typical triggers: global user base with data residency requirements, a financial system needing distributed transactions, or a workload past ~50k writes/second that still requires ad-hoc queries. The costs are real: higher price, coordination latency on every transaction (cross-region commits pay round trips), a smaller ecosystem and talent pool, and some SQL incompatibilities. It's the right answer less often than its marketing suggests, but genuinely right sometimes.