Chapter 13: SQL vs NoSQL — Choosing the Right Store
You covered this in the DB guide. Here's the system design decision framework:
Decision Matrix
| Need | Choose | Why |
|---|---|---|
| Complex queries, JOINs, ACID | SQL (PostgreSQL) | Relational model, strong consistency |
| Flexible schema, document-oriented | Document (MongoDB) | Schema-less, nested data |
| Ultra-fast cache, simple lookups | Key-Value (Redis) | In-memory, sub-ms latency |
| Massive write throughput | Wide-Column (Cassandra) | LSM-tree, distributed writes |
| Relationship traversal | Graph (Neo4j) | O(1) relationship hops |
| Full-text search | Search (Elasticsearch) | Inverted index, relevance scoring |
| Time-series metrics | TSDB (InfluxDB, TimescaleDB) | Optimized for time-ordered data |
Polyglot Persistence
Real systems use MULTIPLE databases:
User Service → PostgreSQL (profiles, auth)
Session Service → Redis (fast lookups, TTL)
Search → Elasticsearch (full-text)
Analytics → ClickHouse (OLAP queries)
File metadata → MongoDB (flexible schema)
Notifications → Kafka (event streaming)
💡 Default Choice
When in doubt: PostgreSQL + Redis. Covers 90% of use cases. Add specialized stores only when you have a specific need PostgreSQL can't handle efficiently.