Skip to main content
PreparationZone Logo
PrepZone

Choosing the Right Database

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

Pass checkpoint below

Read these first

The decision framework

Work through these dimensions in order during an interview:

Five selection dimensions

  1. Data shape — relational, document, time-series, graph, blob?
  2. Access pattern — primary key lookups, range scans, full-text search, aggregations?
  3. Scale — read/write QPS, storage growth, geographic distribution?
  4. Consistency — strong transactions or eventual OK?
  5. 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

WorkloadRecommended storeWhy
User accounts, billingPostgres / MySQLACID, joins, mature ecosystem
Session tokens, rate limitsRedisSub-ms latency, TTL native
Video files, imagesS3 / GCSCheap durable blobs, CDN integration
Search autocompleteElasticsearch / OpenSearchInverted index, prefix queries
Billions of view events/dayCassandra / BigtablePartitioned writes, TTL rows
Real-time analytics dashboardClickHouse / DruidColumnar 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

AspectManaged (RDS, DynamoDB, Atlas)Self-hosted (Postgres on EC2, Cassandra cluster)
Ops burdenBackups, patches, failover automatedYour team owns everything
Cost at small scaleHigher per GB-hourCheaper if you have DBAs
ScalingClick to resize or on-demandManual sharding, tuning
Interview defaultPrefer unless specific reason not toOnly 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

The rest of this guide is part of Pro

Free covers every fresher guide in full. Pro unlocks Senior, Staff and Architect guides across every track.

Still to come · 4 sections

  • StreamHub selection walkthrough
  • Questions to ask before committing
  • Anti-patterns to avoid
  • Evolution over time

Test yourself

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