Choosing the Right Database

One of the most common system design interview questions isn't "how do you store this?" — it's "why did you pick that database?" The answer is never "because it's popular." It comes down to your access patterns, consistency requirements, scale, and query shape.

This guide gives you the mental model to answer that question confidently.

---

The Core Questions to Ask First#

Before naming any database, answer these:

Question	Why it matters
Is the data structured or unstructured?	Structured → SQL; schema-less/nested → NoSQL
Read-heavy or write-heavy?	Redis/replicated SQL for reads; Cassandra/Kafka for writes
Do you need ACID transactions?	Cross-row/cross-table consistency → relational DB
What are the access patterns?	Key lookups, range scans, graph traversals, full-text — each has a winner
How large is the dataset?	< few TB → single node fine; larger → think about sharding or distributed DBs
Do relationships matter?	Yes + deep traversals → graph DB; Yes + joins → relational
Is data time-ordered and append-only?	Logs, events, metrics → Cassandra or time-series DB
Do you need full-text search?	Elasticsearch or similar

---

Database Types at a Glance#

Type	Best For	Examples
Relational (SQL)	Structured data, joins, ACID, complex queries	MySQL, PostgreSQL
Key-Value	Fast lookups by key, caching, session data	Redis, DynamoDB
Wide-Column	Write-heavy, append-only, time-series-like data at scale	Cassandra, HBase
Document	Flexible/nested schemas, JSON-like data	MongoDB, Couchbase
Graph	Relationship traversals (social graphs, recommendations)	Neo4j, Amazon Neptune
Time-Series	Metrics, telemetry, sensor readings	InfluxDB, TimescaleDB
Search Engine	Full-text search, ranking, autocomplete	Elasticsearch, Solr
Blob / Object Storage	Binary files — images, video, audio, backups	AWS S3, GCS, Azure Blob
Message Queue	Async event streaming, decoupling producers/consumers	Kafka, RabbitMQ

---

Relational Databases (SQL)#

MySQL, PostgreSQL, Aurora

Use when:

• Data has a well-defined, stable schema
• You need joins across multiple tables
• ACID transactions are required (banking, order management, user accounts)
• Strong consistency is a priority

Strength	Weakness
ACID guarantees	Hard to scale writes horizontally
Powerful query language (SQL)	Schema changes can be painful at scale
Mature ecosystem, tooling	Vertical scaling has a ceiling
Excellent for complex aggregations	Not great for deeply nested or variable-schema data

Scale pattern: Single master + read replicas for read-heavy workloads. Shard by user_id or tenant_id for write-heavy workloads. Use connection pooling (PgBouncer, ProxySQL) to avoid connection exhaustion.

---

Key-Value Stores#

Redis, DynamoDB, Memcached

Use when:

• You need sub-millisecond lookups by a single key
• Caching frequently read data (user sessions, config, hot objects)
• Simple data: strings, counters, lists, sets
• Rate limiting, leaderboards, pub/sub

Strength	Weakness
Extremely fast (in-memory)	No complex queries — only key lookups
Simple horizontal scaling	Not a source of truth (Redis is cache-first)
Built-in data structures (sorted sets, bitmaps)	Memory is expensive at large scale
TTL support for expiring data	No joins

Redis as more than a cache: Sorted sets → leaderboards and feed ID ordering. Atomic INCR → rate limiting and counters. Pub/sub → lightweight messaging. But Redis is not a primary database — always back it with a durable store.

---

Wide-Column Stores#

Cassandra, HBase, ScyllaDB

Use when:

• Very high write throughput is the primary concern
• Data is append-only or time-ordered (logs, events, audit trails, notifications)
• You can define access patterns ahead of time (denormalize for reads)
• You need geographic distribution with tunable consistency

Strength	Weakness
Massive write throughput (LSM-tree)	No joins; queries must be designed around partition keys
Scales horizontally with no single point of failure	Eventual consistency by default
Great for time-series and append-only patterns	Schema design requires knowing access patterns upfront
Tunable consistency (ONE / QUORUM / ALL)	No ACID transactions across rows

Why Cassandra for notification logs? 10M push + 5M email + 1M SMS/day = 16M appends daily. Cassandra's LSM-tree is purpose-built for this: high write throughput, time-ordered access, and no updates to existing rows. A relational DB would buckle under that write load.

---

Document Stores#

MongoDB, Couchbase, Firestore

Use when:

• Data is naturally nested or JSON-like (product catalogs, user profiles with variable fields)
• Schema evolves frequently and different records have different shapes
• You need rich query support but not strict relational joins

Strength	Weakness
Flexible schema — each document can differ	No true joins (simulate with application-side joins or $lookup)
Good for hierarchical/nested data	Consistency guarantees vary by configuration
Scales horizontally via sharding	Can lead to data duplication to avoid joins
Intuitive for object-oriented applications	Transactions across documents added later (less mature than SQL)

When NOT to use MongoDB: If your data is clearly relational (users, orders, line items, products with foreign keys and joins), a relational DB is simpler and safer. Document stores shine when schema flexibility genuinely matters.

---

Graph Databases#

Neo4j, Amazon Neptune, JanusGraph

Use when:

• The relationships between entities are as important as the entities themselves
• You need to traverse multi-hop relationships efficiently (friends-of-friends, shortest path, recommendations)
• The data forms a network: social graphs, org charts, knowledge graphs, fraud rings

Strength	Weakness
Multi-hop traversals are fast by design	Not for bulk tabular data
Natural fit for social/recommendation models	Smaller ecosystem, fewer engineers who know it
Expressive query languages (Cypher, Gremlin)	Horizontal scaling is more complex than relational DBs
No JOIN overhead — relationships are first-class	Overkill if you just need parent-child hierarchies

SQL vs Graph for social connections: Finding mutual friends with SQL requires self-joins that get exponentially expensive. In a graph DB, it's a simple 2-hop traversal. At millions of users, this is the difference between a 10-second query and a sub-millisecond one.

---

Time-Series Databases#

InfluxDB, TimescaleDB, Prometheus, Amazon Timestream

Use when:

• Data is timestamped measurements: metrics, telemetry, IoT sensor readings, stock prices, monitoring data
• Queries are time-range based ("what was CPU usage between 2pm and 3pm?")
• Data volume is high and retention policies matter (auto-expire old data)

Strength	Weakness
Optimized for time-range queries	Not general purpose
Efficient compression for repeated numeric values	Poor for non-time-based access patterns
Built-in downsampling and retention policies	Limited joins and relationship modeling
High ingest throughput	Less mature tooling than SQL

Prometheus vs InfluxDB: Prometheus is pull-based and integrates natively with Kubernetes/cloud-native stacks. InfluxDB is push-based and suits application-level custom metrics. Both use PromQL or Flux for queries.

---

Search Engines#

Elasticsearch, Solr, OpenSearch, Typesense, Meilisearch

Use when:

• Users type free-form queries and you need relevance ranking
• You need autocomplete or fuzzy matching
• Searching across multiple fields simultaneously
• Log aggregation and full-text search over documents

Strength	Weakness
Inverted index = blazing fast full-text search	Not a primary database — sync from a real DB
Relevance scoring, fuzzy match, typo tolerance	Eventual consistency with source of truth
Aggregations and analytics over text data	Resource intensive (RAM + disk for index)
Scales horizontally	Complex to tune for relevance

Pattern: Primary data in PostgreSQL or MySQL → sync to Elasticsearch on write (via Change Data Capture or a background sync job). Never make Elasticsearch your system of record.

---

Blob / Object Storage#

AWS S3, Google Cloud Storage, Azure Blob Storage

Use when:

• Storing binary files: images, videos, audio, PDFs, backups, logs
• Files are written once and read many times
• You need cheap, durable, infinitely scalable storage
• CDN sits in front to serve files globally

Strength	Weakness
Extremely cheap per GB	Not queryable — you need the exact key
99.999999999% (11 nines) durability	No indexing, no search
Integrates natively with CDNs	High latency for small random reads
Pre-signed URLs for secure direct uploads	Object immutability — updates mean re-upload
Unlimited scale	Not suitable for frequently updated data

Never store binary files in a database. Databases are for structured, queryable data. A single video in a DB row bloats the DB, kills backups, and destroys query performance. Use S3 + store the URL in the DB.

---

Message Queues / Event Streams#

Kafka, RabbitMQ, AWS SQS, AWS SNS, Google Pub/Sub

Use when:

• Decoupling producers from consumers (fanout, async processing)
• Guaranteed at-least-once delivery of events
• Buffering spikes in traffic without overwhelming downstream services
• Event sourcing or audit log patterns

	Kafka	RabbitMQ
Model	Log-based (consumers read from offset)	Message queue (messages deleted on ack)
Retention	Configurable (days, weeks) — replayable	Ephemeral by default
Throughput	Very high (millions/sec)	High (thousands/sec)
Use case	Event streaming, activity feeds, CDC	Task queues, job workers, notifications
Consumer groups	Multiple groups read independently	Competing consumers share a queue

Kafka vs RabbitMQ in one sentence: Use Kafka when you need to replay events or have multiple independent consumers. Use RabbitMQ when you need traditional task queues with routing rules.

---

Polyglot Persistence#

Most real systems use multiple databases — each serving the role it's best at. This is called polyglot persistence.

Component	Database	Why
User accounts, orders	PostgreSQL	Relational, ACID, joins
Session / auth tokens	Redis	Sub-ms lookup, TTL expiry
Product search	Elasticsearch	Full-text, faceted filtering
Activity feed events	Cassandra	High write throughput, time-ordered
User-uploaded images	S3	Cheap binary storage
Social graph (followers)	Neo4j	Traversal efficiency
Background jobs	Kafka	Durable async processing

The key insight: no single database wins every category. Design for access patterns, not habit. The most common mistake is defaulting to MySQL for everything, then hitting a wall at scale on a workload Cassandra would handle trivially.

---

Quick Decision Table#

If your data looks like…	Use
Users, orders, payments with relationships	PostgreSQL / MySQL
Session cache, hot counters, leaderboards	Redis
10M+ event appends per day	Cassandra
Images, videos, backups	S3 / Blob Storage
"Search for products matching…"	Elasticsearch
Who follows whom, friend recommendations	Neo4j
CPU metrics, sensor readings over time	InfluxDB / Prometheus
Async job queue	Kafka / RabbitMQ
Config / feature flags with key lookups	DynamoDB / Redis
Product catalog with varying attributes	MongoDB