SQL vs NoSQL (2)

The real choice between SQL and NoSQL is when you pay to assemble an entity: a relational engine stores each fact exactly once in a normalized table and reconstructs an entity at read time by joining B-tree indexes, whereas a NoSQL store pre-shapes data around one dominant access pattern at write time so that a read touches a single key on a single shard — no join, no cross-node coordination. Everything else (schema flexibility, scaling, consistency) follows from that one mechanical difference.

This page goes past the cabinet analogy into the data-model mechanics that page 002 introduces: how each of the four NoSQL families physically lays out data, and how that dictates the sharding, join, and consistency trade-offs you inherit.

The four NoSQL families are four physical access patterns

"NoSQL" is not one thing. Each family is a different on-disk shape optimized for one kind of lookup, and each is bad at the lookups it was not shaped for. That weakness is the price of its speed.

A relational database refuses to specialize: it keeps every column indexable and every table joinable, so it can answer questions you did not anticipate. That generality is exactly what forces read-time joins and makes horizontal write-sharding hard.

Worked example: rendering one order page

Schema for an e-commerce order screen, normalized the relational way:

users(user_id PK, name, city)
orders(order_id PK, user_id FK, status, created_at)
order_items(order_id FK, product_id FK, qty, unit_price)
products(product_id PK, name, list_price)

Render order 5001 (user 42, two line items). The engine assembles it at read time:

1. Index seek orders on order_id = 5001 → row (user_id = 42, status = SHIPPED).
2. Index seek users on user_id = 42 → (Asha, Chennai).
3. Index range scan order_items on order_id = 5001 → 2 rows: (prod 88, qty 1), (prod 91, qty 3).
4. Index seeks products on 88 and 91 → ("USB-C cable", 299), ("Laptop stand", 1499).
5. Join and nest the rows into one result. Total: 5 index lookups across 4 tables for one screen.

The document store pays that cost once, at write time. One BSON document keyed by order_id embeds a snapshot of the user and copies each product's name and price into the line item:

db.orders.findOne({ _id: 5001 })
// {_id:5001, user:{id:42, name:"Asha", city:"Chennai"}, status:"SHIPPED",
//  items:[ {product_id:88, name:"USB-C cable",  qty:1, unit_price:299},
//          {product_id:91, name:"Laptop stand", qty:3, unit_price:1499} ]}

One read by _id → the whole page, on one shard. But the bill arrives later: rename product 88 and you must now update every order document that embedded it (update fan-out / write amplification), and by default there is no transaction spanning those documents — so different orders show the old and new name for a window. The relational version renames one row in products and every join instantly reflects it.

Wide-column worked example: model the query, not the entity

The document example pre-joins one aggregate. Wide-column (Cassandra) goes further: you model each table around one read, and the primary key has two jobs. Take a chat app's "latest messages in a room":

CREATE TABLE messages_by_room (
  room_id     text,
  created_at  timeuuid,
  sender      text,
  body        text,
  PRIMARY KEY ((room_id), created_at)
) WITH CLUSTERING ORDER BY (created_at DESC);

• Partition key (room_id) — hashed to decide which node holds the data, and it groups every row for that room onto one partition. It answers "where does this live?"
• Clustering key created_at DESC — sorts rows within the partition, so newest-first is physically pre-ordered on disk.

The target read is then one contiguous slice of a single partition on a single node — no join, no scatter-gather:

SELECT * FROM messages_by_room WHERE room_id = ? LIMIT 50;   -- cheap: one partition slice

A second access pattern needs a second table. Ask "all messages by sender X" and this table cannot help — sender is neither the partition nor the clustering key, so Cassandra can only answer with ALLOW FILTERING, a full-cluster scan you must never ship. The query-first fix is to write a second, denormalized table keyed for that read, populated by the same write (ideally in one BATCH):

CREATE TABLE messages_by_sender (
  sender text, created_at timeuuid, room_id text, body text,
  PRIMARY KEY ((sender), created_at)
) WITH CLUSTERING ORDER BY (created_at DESC);

One table per query is normal here — the opposite of relational, where one normalized schema answers every query via joins. Hot-partition trap: the partition key must be high-cardinality and evenly hit. A low-cardinality key (or a date-only key like PRIMARY KEY ((day), created_at)) funnels all of today's traffic onto the single node owning that partition; likewise one giant broadcast room concentrates its whole load on one node. The fix is a higher-cardinality or bucketed key (e.g. ((room_id, day_bucket))) so writes and reads spread across the ring.

Pitfalls a working engineer hits

• "Schemaless" is a lie the app pays for. The schema does not disappear; it moves into application code. Documents written last year with a different shape still live in the collection, and every reader must defensively handle both. You trade a migration you can see for read-path branching you cannot.
• Denormalization = update fan-out + windows of inconsistency. Every field you copy for read speed is a field you must chase down on write. Without a distributed transaction (most NoSQL stores scope atomicity to a single document/partition) readers briefly see mixed versions.
• Access pattern is chosen at design time and cannot be added later for free. Cassandra will not run the ad-hoc WHERE non_key_column = ? you forgot to model; the fix is a full re-partition/migration. Relational lets you add an index and ask the new question tomorrow.
• Reaching for NoSQL "to scale" before you have the scale. A single Postgres primary with read replicas comfortably serves tens of thousands of writes/sec and terabytes. Picking NoSQL prematurely forfeits joins and cross-entity transactions to solve a problem you do not yet have.
• The N+1 query is the join tax in disguise. ORMs that lazy-load relations re-create the 5-lookup pattern above per row in a list — 1 + N round trips. It is the relational read cost leaking into the app; fix with eager joins or batching, not by fleeing to NoSQL.

When to use which — and what it costs

Choose relational (SQL) when your access patterns are varied or not yet known, you need multi-entity ACID transactions (money movement, inventory decrement, double-entry), you want ad-hoc joins and analytics, and your scale fits one write primary plus read replicas — which covers the large majority of applications. What it costs vs NoSQL: a single write primary is a ceiling; sharding writes across nodes is real operational work (Vitess, Citus) and cross-shard transactions get expensive.

Choose NoSQL when you have one dominant, known access pattern, write/scale volume that genuinely exceeds a single primary, evolving or sparse fields, and you can tolerate eventual consistency or confine transactions to one partition. Then pick by family: document for aggregate-oriented apps (a catalog item, an order), key-value for caches/sessions/feature flags, wide-column for high-volume time-series and event logs, graph for relationship traversal (social, fraud rings). What it costs vs SQL: you lose ad-hoc joins, cross-entity atomicity, and the freedom to ask unanticipated questions; denormalization brings update fan-out. Large systems frequently go polyglot rather than forcing one engine to do everything — a relational store for users and money movement alongside a wide-column store for the high-volume timeline and a key-value store for sessions — accepting the operational cost of running several engines in exchange for letting each workload hit the store shaped for it.

The other named alternative — NewSQL (Google Spanner, CockroachDB, Vitess-on-MySQL): horizontal write scale and SQL and distributed ACID. It resolves the dilemma — at the cost of higher write latency (cross-region consensus / two-phase commit) and operational complexity. Reach for it only when you truly need both relational semantics and multi-node write scale.

Decide: Choose relational when correctness across entities and query flexibility matter more than horizontal write scale. Prefer a specific NoSQL family when a single known access pattern at massive scale matters more than joins and cross-entity transactions. Choose NewSQL only when you provably need both.

Takeaways

• The core trade-off is read-time assembly (join) vs write-time assembly (denormalize) — every other difference is downstream of that.
• Each NoSQL family is fast at exactly one access pattern and structurally weak everywhere else; model your queries before you pick, because the choice is not free to reverse.
• Relational buys query flexibility and cross-entity ACID at the price of hard horizontal write-sharding; NoSQL buys linear scale and schema flexibility at the price of joins, cross-entity transactions, and update fan-out.
• Default to relational until a concrete, measured constraint (a proven access pattern at a scale one primary can't serve) pushes you off it — then choose the specific family or NewSQL that constraint demands.

Re-authored and deepened for this guide. Draws on Martin Kleppmann, Designing Data-Intensive Applications (ch. 2–3, data models & storage engines); the MongoDB and Apache Cassandra data-modeling documentation; and the Amazon Dynamo paper (DeCandia et al., 2007). Complements page 002, "SQL vs NoSQL," with the data-model mechanics and family-level access patterns.