Choosing the Right Database

📘 Chapter 4: Databases & Storage ⏱️ 9 min read 🏗️ Lesson 018

There is no "best" database. There is only the best fit for your requirements. This lesson gives you a decision framework: systematic questions that lead to the right choice for your specific workload.

Decision Axes

Five Dimensions to Evaluate

  • Data model — Is your data structured (tables), semi-structured (documents), key-value, graph, or time-series?
  • Access patterns — How will you read and write? By key? Complex queries? Range scans? Graph traversals?
  • Consistency needs — Can you tolerate eventual consistency, or do you need strict ACID?
  • Scale requirements — How much data? How many reads/writes per second? Will it grow 10x?
  • Operational complexity — What can your team operate? Do you have DBA expertise? Prefer managed services?

Decision Tree

Database Selection Decision Tree What's your primary need? Structured data + transactions? Complex queries? → PostgreSQL Read-heavy, simple? → MySQL Flexible schema + scale? → MongoDB / DynamoDB Speed / caching / sessions? → Redis / Memcached Time-series / IoT / logs? → Cassandra / TimescaleDB Full-text search? → Elasticsearch / Meilisearch Deep relationships? → Neo4j / Neptune Analytics / OLAP? → ClickHouse / BigQuery 💡 Most real systems use 2-4 databases (polyglot persistence) SQL for core data • Redis for cache • Elasticsearch for search • Analytics DB for reporting OLTP (Online Transaction Processing) Many small reads/writes, low latency PostgreSQL, MySQL, DynamoDB OLAP (Online Analytical Processing) Few complex queries, scan large datasets ClickHouse, BigQuery, Redshift
No single database handles all workloads optimally. Match the database to the access pattern.

Polyglot Persistence

Modern systems rarely use a single database. Instead, they use multiple databases, each handling what it's best at:

Airbnb's Data Architecture

  • MySQL — Core transactional data: bookings, payments, user accounts. Needs ACID guarantees.
  • Elasticsearch — Search: "Find apartments in Paris, 2 bedrooms, under $150/night, with WiFi." Full-text + faceted search.
  • Redis — Caching: session data, rate limiting, real-time availability. Sub-millisecond reads.
  • Hive/Spark — Analytics: "What's our average booking value by city by month?" Scan billions of rows.
  • Kafka — Event streaming: connecting all these systems, ensuring data flows between them.

Each database handles a specific workload. The complexity is in keeping them synchronized — which is why event-driven architectures matter.

When to Use What

Workload Best Choice Why
E-commerce ordersPostgreSQL / MySQLACID for money + inventory
User sessionsRedisFast by-key access, TTL expiry
Product searchElasticsearchFull-text, facets, fuzzy matching
Social feedCassandra / DynamoDBWrite-heavy, partition by user
RecommendationsNeo4jTraverse relationships efficiently
IoT sensor dataTimescaleDB / InfluxDBTime-series optimized writes + queries
Business reportsClickHouse / BigQueryColumnar scan over billions of rows
CMS contentMongoDBFlexible schema, nested documents

Migration Strategies

Sometimes you realize you picked the wrong database (or outgrew it). Migrating without downtime requires careful orchestration:

The Dual-Write Migration Pattern

  1. Set up new database alongside the old one
  2. Dual-write — Write to both old and new DB on every operation
  3. Backfill — Copy all historical data from old to new
  4. Verify — Read from both, compare results (shadow reads)
  5. Switch reads — Point read traffic to new DB
  6. Remove old writes — Stop writing to old DB
  7. Decommission — Remove old database

Critical: Steps 2-4 can take weeks. You need tooling to detect inconsistencies between the two databases.

Interactive: Database Selection Wizard

Answer questions about your system and get a database recommendation.