Making SQL vs NoSQL Decisions
1. Analyzing Data Structure Requirements
| Need | Choose |
|---|
| Strict schema, relations | SQL (Postgres, MySQL) |
| Flexible / nested docs | Document (MongoDB) |
| Wide, sparse rows | Column-family (Cassandra) |
| Deep relationships | Graph (Neo4j) |
| Simple key lookup | Key-Value (Redis, DynamoDB) |
| Full-text search | Elasticsearch / OpenSearch |
2. Evaluating Query Complexity
| Pattern | Best Fit |
|---|
| Ad-hoc joins, aggregations | SQL |
| Single-key lookup | KV / Document |
| Known access patterns only | NoSQL (model per query) |
| Traversals | Graph |
3. Assessing Consistency Requirements
| Need | Pick |
|---|
| Strong ACID | SQL (Postgres, MySQL) / NewSQL (Spanner, CockroachDB) |
| Tunable | Cassandra, DynamoDB, MongoDB (read/write concerns) |
| Eventually consistent OK | Most NoSQL |
| Linearizable | Spanner, etcd, FoundationDB |
4. Considering Scalability Needs
| Scale Pattern | Pick |
|---|
| Vertical (small/medium) | Traditional SQL |
| Read-heavy → replicas | SQL with read replicas |
| Horizontal writes | Cassandra, DynamoDB, Citus, CockroachDB |
| Massive globally distributed | Spanner, CockroachDB, DynamoDB Global |
5. Evaluating Transaction Requirements
| Need | Pick |
|---|
| Multi-row ACID | SQL — strongest |
| Single-document atomic | MongoDB, DynamoDB single-item |
| Multi-doc tx | MongoDB 4.0+ (with cost), Postgres always |
| Distributed tx | Spanner, CockroachDB, YugabyteDB |
| Sagas | Pattern for cross-service workflows |
| Workload | Winner |
|---|
| Complex joins | SQL |
| Write-heavy time-series | Cassandra, Influx |
| Massive KV lookups | Redis, DynamoDB |
| Mixed OLTP | SQL (Postgres) excellent |
| Graph traversals | Neo4j |
7. Assessing Operational Complexity
| DB | Ops Cost |
|---|
| Managed SQL (RDS, Cloud SQL) | Low |
| Self-hosted SQL | Medium |
| Cassandra self-hosted | High |
| DynamoDB / Firestore | Very low (serverless) |
| Multi-system polyglot | Higher overall |
8. Evaluating Team Expertise
| Factor | Detail |
|---|
| SQL ubiquity | Every engineer knows basics |
| NoSQL learning curve | Modeling paradigms differ per engine |
| Tooling maturity | SQL ecosystem deepest |
| Hiring | Postgres / MySQL skill is common |
9. Choosing Polyglot Persistence
| Use Case | Store |
|---|
| Transactional core | Postgres / MySQL |
| Cache / sessions | Redis |
| Search | Elasticsearch / OpenSearch |
| Analytics | ClickHouse / Snowflake / BigQuery |
| Time-series | TimescaleDB / Influx |
| Graph | Neo4j |
Note: Each store adds operational and data-sync overhead. Start with one and add only when justified.
10. Making Database Migration Decisions
Migration Decision Workflow
- Quantify pain (latency, scale ceiling, cost)
- POC target on representative workload
- Estimate migration effort + risk
- Plan dual-write / shadow read phase
- Cutover with rollback path
- Decommission source after stabilization
| Anti-Pattern | Why Bad |
|---|
| Resume-driven migration | Replaces working system with risk |
| NoSQL because "scale" | Most apps fit on a single Postgres for years |
| Big-bang cutover | No rollback, high blast radius |
| Skipping POC | Surprises at production load |