SQL vs NoSQL System Design Guide
Uploaded by
akash guptaSQL vs NoSQL System Design Guide
Uploaded by
akash guptaDatabase Design
Decision Guide
SQL vs NoSQL · Every Tradeoff That Matters
A complete reference for choosing databases in system design:
relational and every NoSQL family, the CAP / PACELC theorems,
consistency models, scaling strategies, and a practical
decision framework for real-world architecture.
SYSTEM DESIGN REFERENCE SERIES For architects, backend & platform engineers
SYSTEM DESIGN REFERENCE SQL vs NoSQL · Tradeoffs & Decisions
1 · Foundations
Every system-design database decision reduces to a small set of forces: the shape of your data, the access patterns of
your queries, the consistency your business can tolerate, and the scale you must reach. "SQL vs NoSQL" is shorthand
for two different sets of default answers to those forces — neither is universally better. The goal of this guide is to make
the tradeoffs explicit so the choice becomes an engineering decision rather than a preference.
The two paradigms at a glance
Dimension SQL (Relational) NoSQL (Non-relational)
Data model Tables with rows & columns; rigid, predefined Document, key-value, wide-column, or graph;
schema flexible/dynamic schema
Schema Schema-on-write — structure enforced at insert Schema-on-read — application interprets structure
time at read time
Query language Standardized SQL (declarative, joins, Varies per engine (APIs, JSON queries, CQL,
aggregates) Cypher, etc.)
Relationships Native joins & foreign keys across tables Denormalized/embedded; joins avoided or done in
the app layer
Scaling default Vertical (scale up); harder to shard Horizontal (scale out); sharding is a first-class
concern
Consistency Strong (ACID transactions) Often eventual/tunable (BASE); some offer ACID
default
Best fit Structured data, complex queries, transactional Large scale, high write throughput, evolving or
integrity semi-structured data
Key mental model: SQL optimizes for consistency and query flexibility on a well-understood schema; NoSQL
optimizes for scale, availability, and schema flexibility, usually by giving up some consistency or ad-hoc query power.
You trade one set of guarantees for another.
2 · ACID vs BASE — the consistency contract
The deepest philosophical split between the paradigms is the transactional guarantee they promise. ACID favors
correctness; BASE favors availability and scale.
ACID (typical of SQL)
• Atomicity — a transaction is all-or-nothing; partial writes never persist.
• Consistency — every committed transaction moves the DB from one valid state to another (constraints, triggers, FKs
hold).
• Isolation — concurrent transactions don't see each other's uncommitted work; controlled by isolation levels.
• Durability — once committed, data survives crashes (written to non-volatile storage).
BASE (typical of NoSQL)
• Basically Available — the system stays responsive even under partial failure.
• Soft state — data may be in flux; the system doesn't guarantee immediate consistency.
• Eventual consistency — given no new writes, all replicas converge to the same value over time.
Isolation levels (SQL) are themselves a tradeoff dial — from weakest to strongest: Read Uncommitted → Read
Committed → Repeatable Read → Serializable. Stronger isolation prevents more anomalies (dirty reads,
non-repeatable reads, phantom reads) but reduces concurrency and throughput.
Database Selection Guide Page 2
SYSTEM DESIGN REFERENCE SQL vs NoSQL · Tradeoffs & Decisions
Anomaly Read Read Repeatable Serializable
Uncommitted Committed Read
Dirty read Possible Prevented Prevented Prevented
Non-repeatable read Possible Possible Prevented Prevented
Phantom read Possible Possible Possible* Prevented
Concurrency / speed Highest High Medium Lowest
*Some engines (e.g. MySQL InnoDB) prevent phantoms at Repeatable Read via next-key locking.
Database Selection Guide Page 3
SYSTEM DESIGN REFERENCE SQL vs NoSQL · Tradeoffs & Decisions
3 · CAP theorem & PACELC — the distributed reality
Once data is distributed across nodes, the CAP theorem states you can guarantee at most two of three properties
simultaneously when a network partition occurs:
• Consistency (C) — every read sees the most recent write (or an error).
• Availability (A) — every request receives a non-error response (not necessarily the latest data).
• Partition tolerance (P) — the system keeps working despite dropped/delayed messages between nodes.
Because network partitions are unavoidable in any real distributed system, P is non-negotiable. The real choice
under partition is C vs A.
Choice Behavior under partition Example systems Use when
CP Rejects/blocks requests that can't MongoDB (default), HBase, Correctness is critical:
(Consistency + Partition guarantee latest data — sacrifices Redis (single), Zookeeper, banking, inventory,
tol.) availability etcd, Spanner bookings
AP Always responds, possibly with stale Cassandra, DynamoDB, Uptime matters most:
(Availability + Partition data — sacrifices strong consistency CouchDB, Riak social feeds, carts,
tol.) telemetry, catalogs
CA Only possible with no partitions — Traditional single-node Not achievable in a truly
(Consistency + i.e. single node / non-distributed RDBMS distributed setting
Availability)
PACELC — the extension CAP forgets
CAP only describes behavior during a partition. PACELC completes the picture: if Partition (P) then choose
Availability or Consistency (A/C); Else (E), choose Latency or Consistency (L/C). Even with a healthy network,
replicating writes for strong consistency costs latency. This is why 'fast' databases often default to weaker consistency.
System During partition Normal operation Classification
DynamoDB / Cassandra Availability Latency PA / EL — speed-first
MongoDB Consistency Latency PC / EL — consistent,
low-latency reads
Google Spanner Consistency Consistency PC / EC — consistency at all
costs
PostgreSQL (single) n/a (CA) Consistency EC — strong by default
Database Selection Guide Page 4
SYSTEM DESIGN REFERENCE SQL vs NoSQL · Tradeoffs & Decisions
4 · The four NoSQL families
"NoSQL" is not one thing. Each family has a distinct data model and a distinct sweet spot. Choosing NoSQL means
first choosing which NoSQL.
4.1 · Key-Value stores
Model: a giant distributed hash map — opaque value retrieved by a unique key.
Strengths Weaknesses / limits
• Fastest possible lookups (O(1) by key) • Cannot query by value or across records
• Trivial horizontal scaling & partitioning • No relationships or joins
• Ideal for caching, sessions, feature flags, rate-limiting • Value is opaque — no server-side filtering
Examples: Redis, Amazon DynamoDB, Memcached, Riak KV, etcd.
4.2 · Document stores
Model: self-describing documents (JSON/BSON) grouped in collections; nested structures allowed.
Strengths Weaknesses / limits
• Flexible schema — fields vary per document • Multi-document joins are weak/manual
• Maps naturally to application objects (no ORM • Denormalization causes data duplication
impedance) • Consistency across documents is harder
• Rich secondary indexes & ad-hoc queries • Large embedded docs can hurt performance
• Good for content, catalogs, user profiles, CMS
Examples: MongoDB, Couchbase, Amazon DocumentDB, Firestore.
4.3 · Wide-column (columnar) stores
Model: rows keyed by a partition key, each holding a dynamic set of columns grouped into families; optimized for
massive write throughput and range scans.
Strengths Weaknesses / limits
• Enormous write & read throughput at scale • Data model driven by queries — must design tables per
• Linear horizontal scalability across clusters access pattern
• Tunable consistency per query • No joins; ad-hoc queries are painful
• Great for time-series, IoT, logging, event data • Poor fit for highly relational data
Examples: Apache Cassandra, ScyllaDB, HBase, Google Bigtable.
4.4 · Graph databases
Model: nodes (entities) connected by edges (relationships), both carrying properties; relationships are first-class
citizens.
Strengths Weaknesses / limits
• Traverses deep/complex relationships in constant time • Not built for bulk analytical scans over all nodes
per hop • Horizontal scaling/sharding is genuinely hard
• Ideal for fraud detection, recommendations, social • Niche — overkill for simple, tabular data
graphs, knowledge graphs
• Expressive traversal languages (Cypher, Gremlin)
Examples: Neo4j, Amazon Neptune, ArangoDB, JanusGraph, TigerGraph.
Database Selection Guide Page 5
SYSTEM DESIGN REFERENCE SQL vs NoSQL · Tradeoffs & Decisions
5 · Scaling & performance tradeoffs
5.1 · Vertical vs horizontal scaling
Aspect Vertical (scale up) Horizontal (scale out)
Method Add CPU/RAM/SSD to one machine Add more machines to a cluster
Ceiling Hardware limit of a single box Effectively unbounded
Complexity Simple — no app changes Complex — sharding, rebalancing,
coordination
Cost curve Grows steeply at the high end Commodity hardware; near-linear
Fault tolerance Single point of failure Redundant; survives node loss
Typical of SQL / RDBMS NoSQL (and modern NewSQL)
5.2 · Replication & sharding tradeoffs
• Replication improves read throughput & availability but introduces replication lag — a direct consistency-vs-latency
tradeoff (the 'E' in PACELC).
• Leader-follower (primary-replica): simple, strong-ish consistency at the leader, but the leader is a write bottleneck &
failover point.
• Multi-leader / leaderless: higher write availability, but risks write conflicts requiring resolution (last-write-wins, vector
clocks, CRDTs).
• Sharding (partitioning) spreads data by key across nodes for write scale — but cross-shard queries/transactions
become expensive, and a bad shard key causes hotspots.
5.3 · Normalization vs denormalization
Approach Wins Costs
Normalized No duplication; consistent updates in one Reads need joins → slower at scale; more
(SQL default) place; storage-efficient complex queries
Denormalized Reads are single-lookup & fast; scales Data duplicated; updates must touch many
(NoSQL default) horizontally; no joins copies; risk of drift
Rule of thumb: normalize for write-heavy, integrity-critical systems; denormalize for read-heavy, latency-sensitive
systems at scale. Model NoSQL schemas around your queries, not your entities.
Database Selection Guide Page 6
SYSTEM DESIGN REFERENCE SQL vs NoSQL · Tradeoffs & Decisions
6 · Master tradeoff matrix
Side-by-side across every dimension that typically drives a system-design decision.
Consideration Favors SQL when… Favors NoSQL when…
Data structure Structured, uniform, well-defined relationships Semi/unstructured, sparse, or rapidly evolving
Schema stability Schema is stable & known upfront Schema changes often; fields differ per record
Query patterns Complex, ad-hoc, analytical, multi-table joins Simple, known, key-based access patterns
Transactions Multi-record ACID integrity is essential Single-record ops; eventual consistency
acceptable
Scale (volume) Moderate; fits vertical scaling Massive; needs horizontal scale-out
Write throughput Moderate write rates Very high, distributed write volume
Read latency Acceptable with proper indexing Ultra-low latency at scale is required
Consistency need Strong consistency mandatory Tunable/eventual consistency is fine
Availability need Can tolerate brief downtime for correctness Must stay available under partition/failure
Team & ecosystem Mature tooling, SQL skills, reporting/BI needs Cloud-native, flexible, rapid iteration
Cost model Predictable; licensing may apply Commodity/horizontal; pay-as-you-grow
Database Selection Guide Page 7
SYSTEM DESIGN REFERENCE SQL vs NoSQL · Tradeoffs & Decisions
7 · A practical decision framework
When designing any system, walk these questions in order. Each answer narrows the field; the combination points to a
family — or to using multiple stores together.
Step-by-step
• 1. What is the shape of the data? Tabular & relational → lean SQL. Nested documents → document store. Pure
lookups → key-value. Deep relationships → graph. Time-series/event floods → wide-column.
• 2. What are the dominant access patterns? Design around the 80% of queries. Ad-hoc analytics → SQL. Known
key-based reads → NoSQL.
• 3. What consistency does the business truly require? Money/inventory/bookings → strong (CP / ACID).
Feeds/telemetry/carts → eventual (AP / BASE) is usually fine.
• 4. What scale & throughput must you reach? If a single beefy node (plus read replicas) suffices for years → SQL. If
you need to shard across many nodes for write volume → NoSQL / NewSQL.
• 5. What are the availability & latency SLAs? Map them onto CAP/PACELC: 'always-on, low-latency, global' pushes
toward AP/EL systems.
• 6. How stable is the schema & how fast must the team iterate? Volatile schema + rapid iteration favors flexible
NoSQL; regulated, stable domains favor SQL.
• 7. What does the team/ecosystem already know & operate well? Operability and skills are a real tradeoff — the
'best' engine you can't run reliably is the wrong choice.
Polyglot persistence — you don't have to pick one
Most large systems use multiple databases, each for what it does best: e.g. PostgreSQL for transactional core data,
Redis for caching/sessions, Elasticsearch for search, Cassandra for event/time-series data, and Neo4j for a
recommendation graph. The tradeoff is added operational complexity and data-synchronization effort (often via CDC or
event streams) in exchange for using the right tool per workload.
NewSQL — a note on the middle ground
NewSQL systems (Google Spanner, CockroachDB, TiDB, YugabyteDB) aim to deliver SQL's ACID guarantees and
query model with NoSQL-style horizontal scalability. The tradeoff is operational complexity and, often, higher write
latency (distributed consensus like Raft/Paxos) — but they can dissolve the classic 'consistency vs scale' dilemma for
many workloads.
Bottom line: There is no 'best' database — only the best fit for a specific set of data shapes, access patterns,
consistency needs, and scale targets. Make the tradeoffs explicit, map them to CAP/PACELC, and let the
requirements choose the engine.
Appendix · One-line cheat sheet
If you need… Reach for…