Back to System design

SQL vs NoSQL: When to Pick Which

easy
Scale: Postgres: 5-50K QPS/node; Cassandra/Dynamo: 1M+ scales linearly Storage: Postgres: TB single node; NoSQL clusters: PB-scale Amazon, Netflix, Uber
FundamentalsDatabasesData Modeling

The SQL/NoSQL choice is really an access-pattern choice dressed up as a technology debate. Start from the top 3-5 queries and the answer usually falls out.

ScalePostgres: 5-50K QPS/node; Cassandra/Dynamo: 1M+ scales linearly
StoragePostgres: TB single node; NoSQL clusters: PB-scale

Key Concepts

1
1. The decision framework. Default to Postgres until you have a concrete reason to leave. Most products never outgrow well-tuned Postgres + read replicas. Migrate (or add a specialized store alongside) only when a specific workload would actually hurt — 1M+ writes/s on one table, 100+ TB of time-series, or single-key lookups at hundreds of K QPS with strict tail latency.
1. The decision framework.
2
2. SQL's strengths. ACID transactions across rows and tables. Joins. Ad-hoc queries via SQL. Mature tooling. Rigid schema enforced before write. Vertical scale is excellent (50K QPS / TB-class on one node); horizontal via read replicas, Vitess / Citus, or NewSQL evolutions.
2. SQL's strengths.
3
3. NoSQL families. Key-value (Redis, DynamoDB, etcd): single-key lookups at scale, predictable latency, minimal query surface. Document (MongoDB, Couchbase): hierarchical objects keyed by ID, secondary indexes, flexible schema. Wide-column (Cassandra, Bigtable): huge write throughput, time-series-style access. Graph (Neo4j, Neptune): relationship traversals that would be 10-table joins in SQL.
3. NoSQL families.
4
4. NewSQL bridges both. Spanner, CockroachDB, YugabyteDB, TiDB offer SQL + ACID + horizontal scale + strong consistency — paid for with operational complexity and per-region write latency. Right answer for global financial ledgers and multi-region SaaS control planes; overkill for everything else.
4. NewSQL bridges both.
5
5. The polyglot reality. Successful products usually run Postgres (or MySQL) for primary OLTP, Redis for cache and sessions, Elasticsearch for search, ClickHouse for analytics, S3 for blobs. Each store is good at exactly one access pattern. The cost is operating several stores instead of one — worth it once scale demands it.
5. The polyglot reality.

Related Problems

CAP Theorem and PACELCeasyLoad Balancing: L4 vs L7, Algorithms, Health CheckseasyCaching Strategies: Cache-aside, Write-through, Write-backeasySharding Strategies: Range, Hash, Directory, GeomediumConsistent Hashingmedium

Approach

  1. Enumerate the top access patterns explicitly: 'get user by id', 'list user's orders by date', 'count clicks per ad per minute'.
  2. Default to Postgres until you have a concrete reason to leave. Most products never outgrow a well-tuned Postgres + read replicas.
  3. Identify the workload that would actually hurt Postgres: 1M+ writes/s on a single table, 100+ TB of time-series data, single-key lookups at hundreds of K QPS with strict tail latency.
  4. Add a specialized store for that workload. Don't replace the relational store — add alongside it.
  5. Plan write paths to keep stores in sync. CDC (Debezium, Postgres logical replication) is the production-grade way to derive views.
  6. Only adopt NewSQL when you need multi-region ACID. Spanner/Cockroach add real operational overhead.
  7. Evaluate managed vs self-hosted honestly. Until you have a 20-person infra team, managed wins.

Family-by-family

Relational (Postgres, MySQL): ACID, joins, ad-hoc SQL, rich indexes (B-tree, GIN, GiST, BRIN). Vertical scale up to ~64-128 cores; horizontal via read replicas, Vitess/Citus, or partitioning.

Key-value (Redis, Memcached, DynamoDB): single-key get/put. Scales horizontally. Limited query surface. Predictable latency.

Document (MongoDB, Couchbase, Firestore): JSON-like documents keyed by ID, secondary indexes. Schema flexibility; transactions added later (Mongo 4+). Strong for hierarchical data.

Wide-column (Cassandra, Bigtable, HBase): sparse sorted map; partition key + clustering columns. Massive write throughput; range scans within partition. Eventual consistency default; LSM storage engine.

Graph (Neo4j, Neptune): nodes + relationships first-class; traversal is O(degree). Cypher / Gremlin query languages. Smaller-scale than wide-column but unique strength.

NewSQL (Spanner, CockroachDB, TiDB): SQL + ACID + horizontal scale + multi-region. Built on Paxos/Raft per shard. Highest operational complexity.

Components

  • Schema design — driven by query patterns; in NoSQL design per-query, in SQL normalize first then denormalize for performance.
  • Indexing strategy — primary key, secondary indexes, covering indexes, partial indexes (Postgres).
  • Replication — sync, async, multi-master, leaderless quorum.
  • Sharding — range, hash, directory, geo.
  • Connection pooling — PgBouncer, ProxySQL, RDS Proxy.
  • Migrations — Flyway, Liquibase, Atlas for schema evolution.
  • Backups + PITR — base backups + WAL/binlog for point-in-time recovery.
  • CDC pipeline — Debezium, Postgres logical decoding, Mongo change streams to derive read models.

Trade-offs

SQL: ACID and joins are powerful; vertical scale ceiling and rigid schema are the costs.

NoSQL: scale-out and flexibility; you give up ad-hoc query power and design per access pattern from day one.

NewSQL: bridges both; higher ops complexity and per-write latency.

Polyglot: best technical fit per workload; engineering cost of operating N stores instead of 1.

Managed vs self-hosted: managed loses some configurability and costs more at scale; self-hosted requires real DBA / SRE investment.

Anti-patterns

  • Picking MongoDB because you 'don't know the schema yet'. The schema exists; you just haven't acknowledged it. Rigorous Postgres + JSONB columns is usually better.
  • Picking Cassandra for a write-light workload. The operational tax isn't worth it under 10K writes/s sustained.
  • Using Redis as a primary store without persistence and HA. Cache, lease, queue — yes; system of record — no.
  • Splitting across N stores 'for purity' before you have the traffic to justify the ops cost.
  • Treating NewSQL as a drop-in Postgres replacement. Cross-region writes have real latency; queries need explicit hints.