Quick decision matrix for choosing a database family. See Databases for detail.

Decision Matrix

CategoryExamplesData ModelConsistency & CAP/PACELCScalingBest For
Relational (SQL)PostgreSQL, MySQLNormalised tables, joins, strict schemaFull ACID; strong consistency on primary (async replicas lag)Vertical first; read replicas, manual shardingGeneral-purpose OLTP, complex queries, relational integrity
Distributed SQL (NewSQL)CockroachDB, Spanner, TiDBRelational SQL with distributed consensusDistributed ACID; strong consistency (PC/EC)Horizontal out of the box across nodes/regionsGlobal-scale OLTP requiring SQL and strong consistency
NoSQL — Document / Key-ValueMongoDB, DynamoDB, Redis, ValkeyJSON/BSON documents or key-value pairsVaries widely (not uniformly AP): MongoDB and DynamoDB support multi-document/item ACID and strong or tunable reads; Redis async replication can lose writes on failoverHorizontal partitioning / managed auto-scalingFlexible schemas, known access patterns, caching, serverless scale
NoSQL — Wide-Column (OLTP)Cassandra, ScyllaDB, BigtableDynamic columns keyed by partition/clustering keyTunable consistency; Cassandra/ScyllaDB default PA/EL, Bigtable strongly consistentLinear horizontal write scalingMassive write throughput, key-range operational lookups
Columnar (OLAP)ClickHouse, DuckDB, BigQuery, SnowflakeColumn-oriented storageEventual / batch-oriented or snapshot consistencyHorizontal / cloud-elastic analytical queriesAggregations, 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