Quick decision matrix for choosing a database family. See Databases for detail.
Decision Matrix
| Category | Examples | Data Model | Consistency & CAP/PACELC | Scaling | Best For |
|---|---|---|---|---|---|
| Relational (SQL) | PostgreSQL, MySQL | Normalised tables, joins, strict schema | Full ACID; strong consistency on primary (async replicas lag) | Vertical first; read replicas, manual sharding | General-purpose OLTP, complex queries, relational integrity |
| Distributed SQL (NewSQL) | CockroachDB, Spanner, TiDB | Relational SQL with distributed consensus | Distributed ACID; strong consistency (PC/EC) | Horizontal out of the box across nodes/regions | Global-scale OLTP requiring SQL and strong consistency |
| NoSQL — Document / Key-Value | MongoDB, DynamoDB, Redis, Valkey | JSON/BSON documents or key-value pairs | Varies widely (not uniformly AP): MongoDB and DynamoDB support multi-document/item ACID and strong or tunable reads; Redis async replication can lose writes on failover | Horizontal partitioning / managed auto-scaling | Flexible schemas, known access patterns, caching, serverless scale |
| NoSQL — Wide-Column (OLTP) | Cassandra, ScyllaDB, Bigtable | Dynamic columns keyed by partition/clustering key | Tunable consistency; Cassandra/ScyllaDB default PA/EL, Bigtable strongly consistent | Linear horizontal write scaling | Massive write throughput, key-range operational lookups |
| Columnar (OLAP) | ClickHouse, DuckDB, BigQuery, Snowflake | Column-oriented storage | Eventual / batch-oriented or snapshot consistency | Horizontal / cloud-elastic analytical queries | Aggregations, BI dashboards, event analytics, data warehousing |
When Choosing
- Workload type (OLTP vs OLAP): Transactional operational workloads suit SQL, Distributed SQL, or operational NoSQL; large-scale analytical scans belong in Columnar (OLAP) stores.
- Schema & access patterns: Known, relational data models with ad-hoc joins favour SQL; rapidly evolving structures or fixed key-based access patterns suit document/KV/wide-column stores.
- Consistency & transactions: Modern NoSQL is not simply “AP” — evaluate exact replication semantics, tunable read/write levels, and latency trade-offs (PACELC) rather than assuming SQL = CP and NoSQL = AP.
- Scale & operational overhead: Default to a single well-indexed relational database (e.g. PostgreSQL) until scale, geography, or workload shape justifies distributed or specialised engines.
See also: Databases, System Design