Stage 3 · Build
NoSQL & Distributed Databases
Choosing the Right Database
Decision framework: read/write ratio, schema flexibility, consistency requirements.
Decision Framework
Choosing a database is a 5-10 year decision. The wrong choice is expensive to fix. Start with your data model and query patterns, then evaluate operational requirements and team expertise.
data_questions:
- "What is the primary data model? (relational, document, key-value, graph, time-series)"
- "Is the schema fixed or evolving frequently?"
- "What is the data volume at launch and at 10x scale?"
- "What are the consistency requirements? (strong, eventual, causal)"
query_questions:
- "What is the read/write ratio? (10:1, 1:1, 1:10)"
- "What are the most frequent query patterns?"
- "Do you need complex joins or aggregations?"
- "What is the required query latency? (<1ms, <10ms, <100ms)"
operational_questions:
- "What is the team's database expertise?"
- "What is the deployment model? (cloud, on-prem, hybrid)"
- "What are the availability requirements? (99.9%, 99.99%)"
- "What is the budget for database infrastructure?"Answer these questions before evaluating specific databases. The answers narrow your choices from dozens to 2-3 candidates. Then evaluate those candidates against operational requirements.
Workload Analysis
| Workload | Characteristics | Best Fit |
|---|---|---|
| OLTP | Many small reads/writes, ACID required | PostgreSQL, MySQL |
| OLAP | Complex aggregations, read-heavy | PostgreSQL (with Citus), ClickHouse |
| Caching | Key-value, high throughput, volatile | Redis, Memcached |
| Time-series | Append-heavy, time-range queries | TimescaleDB, InfluxDB |
| Document | Semi-structured, nested data | MongoDB, CouchDB |
| Wide-column | Massive scale, write-heavy | Cassandra, HBase |
| Graph | Relationship traversal, recommendations | Neo4j, ArangoDB |
Database Comparison Matrix
| Database | Consistency | Scale | Operational Complexity |
|---|---|---|---|
| PostgreSQL | Strong (ACID) | Vertical + read replicas | Low |
| Redis | Eventual (async replication) | Cluster (16K slots) | Medium |
| MongoDB | Configurable (majority reads) | Sharding (auto) | Medium |
| Cassandra | Tunable (per-query) | Linear horizontal | High |
| DynamoDB | Strong (strongly consistent reads) | Auto-scaling | Low (managed) |
| TimescaleDB | Strong (ACID) | Hypertables + compression | Low |
Polyglot Persistence
Polyglot persistence means using different databases for different data types. This is the norm in production — most systems use 2-4 databases, each optimized for a specific workload.
polyglot_architecture:
user_data:
database: PostgreSQL
reason: "ACID transactions, relational queries, strong consistency"
session_cache:
database: Redis
reason: "Sub-millisecond reads, TTL expiry, high throughput"
product_catalog:
database: MongoDB
reason: "Schema flexibility, nested documents, fast iteration"
metrics:
database: TimescaleDB
reason: "Time-series optimization, compression, continuous aggregates"
audit_log:
database: Cassandra
reason: "Write-heavy, append-only, massive scale, 7-year retention"
social_graph:
database: Neo4j
reason: "Relationship traversal, friend recommendations, pattern matching"Each database is chosen for its strength. PostgreSQL handles transactions. Redis handles caching. MongoDB handles flexible schemas. The tradeoff is operational complexity — you need expertise in each database.
Every additional database means: separate monitoring, separate backup procedures, separate security patches, and separate team expertise. Start with one database and add others only when the performance benefit justifies the operational cost.
Migration Cost Analysis
- Schema migration: DDL changes, data transformation, constraint recreation
- Data migration: ETL pipeline, backfill, validation
- Application rewrite: ORM changes, query rewrites, connection management
- Testing: regression tests, load tests, chaos tests
- Training: team learning curve, operational procedures
- Dual-write period: running both databases during transition
Decision Checklist
- Data model matches your primary use case
- Query patterns are efficient (no full scans for hot paths)
- Scale requirements fit within the database's capabilities
- Team has expertise or can hire for it
- Operational tooling exists (monitoring, backup, failover)
- Licensing and cost fit the budget
- Community and ecosystem are active
- Migration path exists if you outgrow it
PostgreSQL handles OLTP, time-series (TimescaleDB), JSON documents (JSONB), full-text search, and geospatial queries. Start with PostgreSQL and add specialized databases only when you have measured evidence that PostgreSQL cannot meet your requirements.
Mark this lesson complete to store local progress and unlock a cleaner resume path the next time you visit.