Choosing the Right Database

A decision framework for picking Postgres, DynamoDB, Cassandra or Redis for your workload.

IntermediateTheory12 min read

Read these first

SQL vs NoSQL

The decision framework

Work through these dimensions in order during an interview:

Five selection dimensions

Data shape — relational, document, time-series, graph, blob?
Access pattern — primary key lookups, range scans, full-text search, aggregations?
Scale — read/write QPS, storage growth, geographic distribution?
Consistency — strong transactions or eventual OK?
Operations — managed service vs self-hosted, team experience?

Java

# Template you can sketch on a whiteboard
workload:
  entities: [users, videos, view_events]
  hot_query: "get feed for user_id, last 50 items"
  read_write_ratio: 100:1
  consistency: eventual_ok_for_feed
  scale_5yr: 500M users, 10B videos

Database selection on AWS

DATABASERDS / AuroraACID · joins

DATABASEDynamoDBkey-value scale

ANALYTICSOpenSearchfull-text · autocomplete

DATABASEElastiCachesessions · rate limits

Pick the store that matches access pattern — not the other way around.

Mapping workloads to stores

Workload	Recommended store	Why
User accounts, billing	Postgres / MySQL	ACID, joins, mature ecosystem
Session tokens, rate limits	Redis	Sub-ms latency, TTL native
Video files, images	S3 / GCS	Cheap durable blobs, CDN integration
Search autocomplete	Elasticsearch / OpenSearch	Inverted index, prefix queries
Billions of view events/day	Cassandra / Bigtable	Partitioned writes, TTL rows
Real-time analytics dashboard	ClickHouse / Druid	Columnar aggregations

User accounts, billing
Recommended storePostgres / MySQL
WhyACID, joins, mature ecosystem
Session tokens, rate limits
Recommended storeRedis
WhySub-ms latency, TTL native
Video files, images
Recommended storeS3 / GCS
WhyCheap durable blobs, CDN integration
Search autocomplete
Recommended storeElasticsearch / OpenSearch
WhyInverted index, prefix queries
Billions of view events/day
Recommended storeCassandra / Bigtable
WhyPartitioned writes, TTL rows
Real-time analytics dashboard
Recommended storeClickHouse / Druid
WhyColumnar aggregations

Managed vs self-hosted

Aspect	Managed (RDS, DynamoDB, Atlas)	Self-hosted (Postgres on EC2, Cassandra cluster)
Ops burden	Backups, patches, failover automated	Your team owns everything
Cost at small scale	Higher per GB-hour	Cheaper if you have DBAs
Scaling	Click to resize or on-demand	Manual sharding, tuning
Interview default	Prefer unless specific reason not to	Only with strong justification

Ops burden
Managed (RDS, DynamoDB, Atlas)Backups, patches, failover automated
Self-hosted (Postgres on EC2, Cassandra cluster)Your team owns everything
Cost at small scale
Managed (RDS, DynamoDB, Atlas)Higher per GB-hour
Self-hosted (Postgres on EC2, Cassandra cluster)Cheaper if you have DBAs
Scaling
Managed (RDS, DynamoDB, Atlas)Click to resize or on-demand
Self-hosted (Postgres on EC2, Cassandra cluster)Manual sharding, tuning
Interview default
Managed (RDS, DynamoDB, Atlas)Prefer unless specific reason not to
Self-hosted (Postgres on EC2, Cassandra cluster)Only with strong justification

StreamHub selection walkthrough

Users and subscriptions → Postgres on RDS

Java

CREATE TABLE subscriptions (
  id UUID PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES users(id),
  plan TEXT NOT NULL CHECK (plan IN ('free', 'pro', 'studio')),
  expires_at TIMESTAMPTZ
);

Hot video metadata → Redis cache-aside, Postgres source of truth

Java

SETEX video:meta:v_9182 3600 '{"title":"Sunset timelapse","views":128400}'

Media files → S3 with lifecycle rules (Standard → Glacier for old archives)

Search → Elasticsearch index rebuilt from change-data-capture events

View analytics (at 3M+ DAU) → Cassandra partitioned by (video_id, day)

Questions to ask before committing

Sanity checks

Can one primary handle write QPS for five years? If not, plan sharding early.
Do you need cross-entity transactions? If yes, avoid splitting across NoSQL partitions.
Is the query pattern stable? Search and analytics often need separate indexes.
What is the blast radius if this store fails? Cache loss vs payment DB loss differ.
Does the team know how to operate it on call at 3 AM?

Anti-patterns to avoid

Java

✗ DynamoDB for heavy relational reporting (scan costs explode)
✗ Postgres single table with 50 TB and 1M writes/sec (shard or specialise)
✗ Redis as primary database without persistence strategy
✗ Elasticsearch as system of record (rebuild from source, not sole copy)

Evolution over time

Databases change as the product grows. StreamHub's path:

Stage	DAU	Data layer
MVP	500	Single Postgres on VPS
Growth	50K	RDS + Redis + S3
Scale	500K	+ Elasticsearch, read replicas
Global	3M+	+ Cassandra events, shard planning

MVP
DAU500
Data layerSingle Postgres on VPS
Growth
DAU50K
Data layerRDS + Redis + S3
Scale
DAU500K
Data layer+ Elasticsearch, read replicas
Global
DAU3M+
Data layer+ Cassandra events, shard planning

Start simple; migrate when metrics force it — not when blog posts suggest it.

Quick recall

Everything you need if you only revisit this box.

Select by data shape, access pattern, scale, consistency, and ops capability.
Postgres for transactional core; Redis for cache; S3 for blobs; ES for search.
Prefer managed services in interviews unless cost or control demands otherwise.
Document Entity → Store → Reason for each component in your design.
Avoid using search engines or caches as systems of record.
Plan evolution: start simple, add specialised stores when metrics require it.

Test yourself

Answer these before moving on — recall is what makes it stick.