Engineering Atlas › System Design Fundamentals

When to pick SQL and when NoSQL

14 min read · last reviewed 2026-10-17

Open in the interactive app: practice, quiz, mock interview →

In one minute

How it works

Where the split came from

By the mid-2000s web companies had outgrown single relational servers. Google's Bigtable (OSDI 2006) and Amazon's Dynamo (SOSP 2007) papers described stores that dropped joins, cross-row transactions and fixed schemas to get predictable latency at scale and stay writable during failures. Cassandra, HBase, MongoDB and others followed, and "NoSQL" began as a 2009 meetup hashtag. It names a set of trade-offs, not a technology.

Axis 1: data model

A relational schema is normalized: order, lines, customer and products are four tables joined at read time. A document or key-value store keeps an aggregate (a term from domain-driven design): the order with its lines and copies of product names and prices, stored and fetched as one unit. Reading the aggregate is one lookup; reading across aggregates ("all orders containing product 42") needs a secondary index, another copy, or a scan.

Schemas still exist in NoSQL; they move into the code that reads the data ("schema on read"). Adding a field is trivial, and old records with missing fields become every reader's problem.

Axis 2: query model

SQL lets you write a new query tomorrow and the planner works out how to run it. DynamoDB and Cassandra expect you to list access patterns first and design keys so each is one request to one partition: a partition key grouping what is read together and a sort key ordering it (PK = CUSTOMER#17, SK = ORDER#2026-10-01#981). A new pattern means a new index or copy, backfilled. MongoDB sits between: rich queries and secondary indexes, but queries without the shard key go to every shard.

Axis 3: consistency scope

Relational databases give multi-row transactions with chosen isolation (see ACID Transactions). Classic NoSQL stores made one item or document the unit of atomicity, which works when the aggregate boundary matches the transaction boundary. MongoDB later added multi-document transactions (4.0 for replica sets, 4.2 for sharded clusters) (MongoDB docs) and DynamoDB added TransactWriteItems (up to 100 items and 4 MB). They work, but cost more and conflict more: DynamoDB does two underlying writes per item, one to prepare and one to commit, charges for them even when the transaction is cancelled, and gives no atomicity across Regions in a global table (AWS docs). Reads are often eventually consistent by default: a DynamoDB GetItem is unless you set ConsistentRead, which costs twice the read capacity (AWS docs).

Axis 4: scaling model

A relational primary scales up (a 64-core machine handles a great deal), then out for reads with replicas, then by sharding through the application or a layer such as Vitess or Citus. Partitioned NoSQL stores shard from the first row, so growth is adding nodes. The constraint moves into key design: each partition has a throughput ceiling, so hot keys hurt.

Convergence

Postgres has jsonb with GIN indexes. Going the other way, Bigtable now accepts read-only GoogleSQL queries, though without joins (Google Cloud docs), and DynamoDB global tables can run in a multi-Region strong consistency mode that replicates each write to another Region before acknowledging it (AWS docs). Distributed SQL systems (Spanner, CockroachDB, YugabyteDB, TiDB) shard relational tables and run serializable transactions across them, paying a consensus round trip per write. The useful question is which axis binds for this data.

The same shape elsewhere

This is "finding things fast" versus "multi-step consistency": precompute the answer's shape, or keep facts once and assemble answers on demand. A frontend's normalized Redux store versus a per-screen view model is the same trade-off in a browser.

Down the stack

Layer 1The contract

Postgres promises a correct answer to any SQL query whatever indexes exist; only speed varies. DynamoDB promises single-digit-millisecond requests addressed by key, item atomicity, optional strongly consistent reads on the base table, and conditional writes (ConditionExpression). Items are capped at 400 KB, attribute names included (AWS docs), and each physical partition at about 3,000 read units and 1,000 write units per second (AWS docs). MongoDB caps documents at 16 MB, with atomic updates within one document.

Layer 2The mechanism

In Postgres, the order join might be an index scan on orders, a nested loop into order_lines and a hash join to products, each reading B-tree and heap pages from the buffer pool. A jsonb value is a parsed binary tree; a GIN index maps keys and values to rows for @> queries. Changing one field rewrites the whole value and takes a lock on the whole row, so two sessions updating different keys of one document still queue behind each other (Postgres docs).

In DynamoDB, the partition key is hashed to choose a partition; each partition is a B-tree ordered by sort key, replicated to three storage nodes across availability zones with a Multi-Paxos leader that alone serves writes and strongly consistent reads (DynamoDB paper, ATC 2022). A Query on PK = CUSTOMER#17 with begins_with(SK, "ORDER#") is one range read on one partition. A global secondary index is a separate partitioned table that writes propagate to asynchronously, which is why it is only eventually consistent.

In sharded MongoDB, mongos routes by the shard key's chunk ranges; a query without the shard key is scatter-gather across all shards.

Layer 3The machine

A three-table join for one order might touch a dozen 8 KB pages, microseconds each from RAM but about 100 microseconds each from NVMe on a miss. A contiguous document is one read, so aggregates win when the working set exceeds memory: random I/Os per request drop from many to one.

Scatter-gather costs at the tail. If each of 20 shards answers within 10 ms 99% of the time, a query that waits for all 20 exceeds 10 ms with probability 1 - 0.99^20, about 18%. A sorted limit 20 across 20 shards also fetches up to 400 rows.

Denormalized writes cost bytes. Renaming a product embedded in 2 million documents rewrites 2 million documents; in Postgres it is one row update.

Why it's built this way

  1. A single machine has a ceiling, and cross-machine coordination costs network round trips, so stores that must scale out make the common request touch one machine.
  2. One machine per request means the data a request needs must live together, so data is grouped by access pattern into aggregates, and the schema follows the queries.
  3. Grouping by one pattern disadvantages every other pattern, so secondary indexes and copies are added, maintained asynchronously to avoid cross-partition coordination on every write.
  4. Asynchronous copies are eventually consistent, so applications get conditional writes and, later, opt-in transactions for the cases where that is not enough.
  5. Relational engines made the opposite bet: keep each fact once and let a planner assemble answers, which is unbeatable for changing questions and integrity, and hardest to spread across machines.

Diagram

flowchart LR
  Q["Request: order 981 for customer 17"] --> R{"Data model?"}
  R -->|relational| J["Planner: index scan orders"]
  J --> L["Nested loop: order_lines by order_id"]
  L --> H["Hash join: products"]
  H --> A1["Answer assembled from 3 tables"]
  R -->|"key-value aggregate"| P["hash(CUSTOMER#17) picks partition"]
  P --> S["Range read SK = ORDER#...#981"]
  S --> A2["One item, lines and names embedded"]

Worked example

An e-commerce order-history feature serves 20,000 reads per second at peak and 500 order writes per second. An order averages 3 KB with its lines.

On Postgres. "Show order 981" is a three-table join over indexes, well under a millisecond with the working set cached. Spread over a primary and three replicas, that is 5,000 reads per second per node, comfortable for 16-core machines; 500 writes per second is trivial. When finance asks for "revenue by category, last 30 days", it is one SQL query on a replica.

On DynamoDB. Each order is one item keyed PK = CUSTOMER#id, SK = ORDER#date#id, with product names and prices copied in. A read is one GetItem at 0.5 read capacity units (one per 4 KB, halved for eventual reads), 10,000 RCU per second in total; writes cost 3 WCU each (one per 1 KB), 1,500 per second. Latency stays flat to tens of terabytes with no shard management. But finance's question has no key: the team streams changes into a warehouse, one more pipeline to own. Copied product names are correct here, since an order should keep the name at purchase time.

The decision. Postgres handles this load on four machines and keeps ad hoc queries. DynamoDB wins with 20 times the growth, multi-region active writes, or a mandate for zero database operations. Many teams do both: Postgres for orders and payments, a key-value store for single-key session or cart data.

In the wild

Trade-offs

OptionStrengthsWeaknessesChoose when
Relational (Postgres, MySQL)Ad hoc queries, joins, constraints, multi-row transactionsScale-out is manual work; schema changes need migrationsMost systems of record, until a measured limit
Key-value or wide-column (DynamoDB, Cassandra)Flat latency at any size, partitioned from day oneEvery access pattern designed up front; no joinsKnown patterns, very high volume, multi-region writes
Document (MongoDB, Firestore)Natural aggregates, flexible fields, rich queriesCross-document integrity is the app's job; scatter-gather without the shard keyAggregate-shaped data with varied fields
Distributed SQL (Spanner, CockroachDB)SQL and transactions across shards and regionsConsensus latency per write, cost, operational depthRelational needs beyond one primary
Relational with jsonbOne store, flexible attributes beside strict columnsLarge documents rewrite fully; weaker statistics on JSONA few flexible fields in a relational model

Pitfalls in production

Leadership lens

Check yourself

  1. Why does a DynamoDB global secondary index only offer eventually consistent reads?

    Show answer

    It is a separate partitioned table updated asynchronously from the base table's changes, so keeping it synchronous would need cross-partition coordination on every write.

  2. A MongoDB query filters on email but the collection is sharded by user_id. What happens, and why does p99 suffer?

    Show answer

    The router sends it to every shard and waits for all of them, so the query's latency is the slowest shard's, and the chance of hitting some shard's tail grows with the shard count.

  3. When is copying a product's name into each order document correct, not a consistency bug?

    Show answer

    When the order should record the name at time of purchase. The copy is then historical fact, not a cache of the current name.

  4. What single question best decides between relational and key-value for a new service?

    Show answer

    Whether all access patterns are known and stable. If new questions will keep arriving, a query planner over normalized data is worth its scaling cost.

Sources and further study

Primary

Systems docs

Books

Other curricula

Watch

Free resources

Open in the interactive app: practice, quiz, mock interview →