Databases: SQL vs NoSQL:
Relational vs Document Stores

1. The Core Storage Paradigms: Entity-Relational vs Aggregate-Document

Every database conversation should start with a mental-model question: how does the engine want you to slice your data? The relational family, formalized by E. F. Codd in 1970, treats data as a set of relations (tables) over tuples (rows). Truth is decomposed into the smallest non-redundant units and reassembled at read time with joins. The schema is declared up front and enforced by the catalog before any write is accepted (schema-on-write).

The document family (MongoDB, Couchbase, RavenDB) is aggregate-oriented. Instead of decomposing, it localizes: one document encapsulates an entire aggregate that is typically read or written together (such as an order with its line items or a blog post with its comments). Structure lives inside the data itself, field names are stored per-document, and shape can vary row-to-row (schema-on-read, or more precisely, application-enforced schema).

 RELATIONAL MODEL (decomposed truth, reassembled with JOINs)

 ┌────────────────┐    ┌──────────────────┐    ┌───────────────────┐
 │ customers      │    │ orders           │    │ order_items       │
 ├────────────────┤    ├──────────────────┤    ├───────────────────┤
 │ id     = 41    │◄───┤ customer_id = 41 │    │ order_id   = 9007 │
 │ name   = "Ana" │    │ id          = 9007│◄──┤ product_id = 7734 │
 │ tier   = "gold"│    │ status   = "paid"│    │ qty        = 2    │
 └────────────────┘    │ total    = 71.98 │    │ unit_price = 29.99│
                       └──────────────────┘    └─────────┬─────────┘
              ▲ FOREIGN KEY                ▲ FOREIGN KEY  │ FOREIGN KEY
                       ┌──────────────────┐    ┌─────────▼─────────┐
                       │ payments         │    │ products          │
                       │ order_id = 9007  │    │ id = 7734         │
                       │ amount   = 71.98 │    │ name = "Mech Keyb"│
                       └──────────────────┘    └───────────────────┘

 DOCUMENT MODEL (one aggregate = one read/write unit)

 ┌───────────────────────────────────────────────┐
 │ {                                             │
 │   "_id": 9007,                                │
 │   "customer": { "id": 41, "tier": "gold" },   │
 │   "status": "paid",                           │
 │   "items": [                                  │
 │     { "sku": 7734, "qty": 2, "price": 29.99 },│
 │     { "sku": 8810, "qty": 1, "price": 12.00 } │
 │   ],                                          │
 │   "total": 71.98                              │
 │ }                                             │
 └───────────────────────────────────────────────┘

 Relational: 4-way JOIN to rehydrate the order.
 Document:   ONE round trip returns the whole aggregate.

2. Under the Hood: Slotted Pages, B+ Trees, BSON & WiredTiger

The paradigm differences are philosophical; the storage-engine differences are physical. Understanding what actually hits the disk explains why each system behaves the way it does under load.

Relational Storage: Heap Files of Slotted Pages

PostgreSQL and MySQL/InnoDB do not store "rows" as free-floating text. Rows live inside fixed-size pages (8 KB in PostgreSQL, 16 KB in InnoDB) organized as a slotted page: a header with bookkeeping metadata, a grow-down array of line pointers, and tuples appended in the opposite direction. A row's physical address is simply (page_number, slot_number), or in PostgreSQL jargon, its ctid.

 offset 0
 ┌────────────────────────────────────────────────┐
 │ PageHeader (LSN, flags, free-space pointers)   │
 ├────────────────────────────────────────────────┤
 │ Line Pointer [1] ──▶ tuple @ 8040              │
 │ Line Pointer [2] ──▶ tuple @ 7912              │
 │ Line Pointer [3] ──▶ tuple @ 7784              │
 ├─────────────── ▼ free space grows down ────────┤
 │                  FREE SPACE                    │
 ├─────────────── ▲ tuples grow up ───────────────┤
 │ Tuple 3: { id=3, name="Cy",  tier="free"  }    │
 │ Tuple 2: { id=2, name="Ben", tier="gold"  }    │
 │ Tuple 1: { id=1, name="Ana", tier="gold"  }    │
 └────────────────────────────────────────────────┘ offset 8192

 UPDATE = insert a NEW version + mark old one dead (MVCC).
 Dead tuples are reclaimed later by VACUUM; nothing is
 ever mutated in place. Durability comes from the WAL.

Every mutation is first appended to the Write-Ahead Log (WAL), a sequential, fsync'd journal, before the touched data pages are written. That inversion is what makes crash recovery possible: after a power failure the engine replays the WAL to restore a consistent state. Sequential appends also make writes fast even on spinning disks.

B+ Tree Indexes: How Lookups Stay O(log n)

Tables are scanned page-by-page, so finding one row among billions requires an index. Relational engines overwhelmingly use the B+ Tree: internal nodes hold only routing keys, while all values live in leaf nodes linked left-to-right. Point queries descend the tree once; range scans descend once and then chase sibling pointers for sequential I/O.

                          ┌───────────────────┐
             root ──────▶ │   [30]     [70]   │   keys only:
                          └────┬────────┬─────┘   routing signposts
                 ┌─────────────┘        └─────────────┐
      ┌──────────▼─────────┐             ┌──────────▼─────────┐
      │  [12]       [22]   │             │  [50]       [85]   │
      └───┬──────────┬─────┘             └────────────────────┘
          │          │
   ┌──────▼───┐ ┌────▼─────┐        Range scan (id BETWEEN 20 AND 40):
   │ leaves:  │→│ leaves:  │ → …     1) descend to leaf [12,22]
   │ 10,11,12 │ │ 22,25,31 │         2) follow sibling pointers ─▶
   └──────────┘ └──────────┘         Sequential, prefetch-friendly I/O.

 InnoDB: leaf of the PRIMARY key index holds the full row
 ("clustered index"); secondary indexes store PK copies.
 PostgreSQL: all indexes store ctids pointing into heap pages.

Document Storage: BSON Payloads & WiredTiger

MongoDB serializes documents as BSON (Binary JSON): a length-prefixed byte stream where every element is a {type byte, field name, value} triple. Because type tags travel inside each document, BSON can faithfully represent types JSON cannot (int32/int64, decimal128, Date, raw binary) and lets the engine skip fields it doesn't need while decoding.

 { title: "Mongo", stock: 42 }  as stored bytes:

 offset   bytes                        meaning
 ────────────────────────────────────────────────────────
 0x0000   46 00 00 00                  doc length = 70 B
 0x0004   02                           element type: UTF-8 string
 0x0005   74 69 74 6c 65 00            field name "title" + \0
 0x000B   06 00 00 00 4D 6F 6E 67 6F   len=6 + "Mongo"
 0x0014   10                           element type: int32
 0x0015   73 74 6f 63 6b 00            field name "stock" + \0
 0x001B   2A 00 00 00                  value = 42
 0x001F   00                           EOO (end-of-object)
 ────────────────────────────────────────────────────────
 Field names repeat in EVERY document: that is the
 cost of self-description. WiredTiger stores these
 payloads in copy-on-write B-trees with MVCC version
 chains, compresses blocks (snappy/zlib + prefix
 compression), and fsyncs a journal between checkpoints.

MongoDB's default engine, WiredTiger, uses copy-on-write: updates never mutate a page in place; they write new versions and readers pin a consistent snapshot (MVCC). Dirty pages accumulate in cache and are flushed as compressed checkpoints; between checkpoints, durability is provided by an on-disk journal, the same conceptual trick as a relational WAL.

3. Data Modeling: Normalization (1NF–3NF) vs Query-Driven Embedding

The two camps model in opposite directions. Relational design starts from the data and normalizes until redundancy is gone, then answers whatever queries arrive later. Document design starts from the queries ("what does this screen show?") and shapes documents to make those exact access patterns one round trip.

The Relational Path: Normal Forms as Anomaly Insurance

1NF demands atomic values: one cell, one value, no repeating groups. 2NF moves partial dependencies out: any column that depends on only part of a composite key belongs in its own table. 3NF removes transitive dependencies: non-key columns must depend on the key, "the whole key, and nothing but the key." Each step eliminates a class of write anomaly:

 BEFORE: everything crammed into one sheet:

 ┌───────────────────────────────────────────────────────────┐
 │ customer │ cust_tier │ items                              │
 ├───────────────────────────────────────────────────────────┤
 │ Ana      │ gold      │ "Mech Keyboard x2 @29.99; USB-C x1"│
 └───────────────────────────────────────────────────────────┘
   ✗ UPDATE anomaly: rename tier → rewrite every row
   ✗ INSERT anomaly: can't add product nobody ordered yet
   ✗ DELETE anomaly: deleting last order erases the product

 AFTER (3NF): every fact stored exactly once

 ┌──────────────┐      ┌──────────────┐      ┌──────────────┐
 │ customers    │      │ orders       │      │ products     │
 │ PK id        │◄──┐  │ PK id        │◄──┐  │ PK id = 7734 │
 │    name      │   ├──│ FK customer  │   ├──│    name      │
 │    tier ─────┼─┐ │  │    status    │   │  │    price     │
 └──────────────┘ │ │  └──────┬───────┘   │  └──────▲───────┘
                  │ │         │ FK        │         │ FK
                  │ │  ┌──────▼───────┐   │         │
                  │ └──│ tiers        │   │  ┌──────┴───────┐
                  │    │ PK tier_name │   └──│ order_items  │
                  │    └──────────────┘      │ FK order     │
                  └── lookup table           │ FK product   │
                     (tier perks once)       │ qty          │
                                             └──────────────┘
 The engine now ENFORCES truth: FKs reject orphans,
 CHECK constraints reject garbage, UNIQUE rejects dupes.

The Document Path: Model What You Read

In a document store the first question is not "what are my entities?" but "what is the shape of my top queries?" If the UI always renders an order together with its line items and buyer snapshot, that whole unit becomes one document. The remaining relationships (anything too big, too shared, or too volatile to duplicate) are kept as references resolved by a second query or a $lookup. The craft is knowing where to draw that line, which is why practitioners classify relationships by cardinality:

4. Transaction Models: ACID Isolation Levels vs BASE Semantics

ACID is the relational contract: Atomicity (all-or-nothing), Consistency (constraints hold before and after), Isolation (concurrent transactions don't see each other's half-work), Durability (committed means survives a crash). Document stores historically traded the middle two letters for availability and partition tolerance, offering BASE: Basically Available, Soft state, Eventually consistent. The interesting engineering is in what "isolation" actually delivers at each level:

 Snapshot: xmin = 100, xmax = 120, active_xids = [105, 112]
 
 1. If tuple.xmin < snapshot.xmin AND committed:
    - If tuple.xmax is not set (or > snapshot.xmax): TUPLE IS VISIBLE.
 2. If tuple.xmin >= snapshot.xmax:
    - Created after snapshot was taken: TUPLE IS INVISIBLE.
 3. If tuple.xmin is in active_xids:
    - Transaction was uncommitted when query started: TUPLE IS INVISIBLE.

*PostgreSQL's REPEATABLE READ actually rejects serialization conflicts that MySQL tolerates (same SQL name, subtly different contract). Always check your engine's fine print.

The three phenomena in the table define the ladder: a dirty read sees another transaction's uncommitted data (which might be rolled back); a non-repeatable read re-reads one row and finds it changed between two statements; a phantom read re-runs a range predicate (WHERE amount > 100) and new matching rows have appeared. Engines prevent these with two mechanisms: Two-Phase Locking (readers block writers, serial but contention-prone) or MVCC (readers get a consistent snapshot without blocking anyone; writers detect conflicts at commit time).

BASE: Trading Locks for Availability

In a distributed document store, strict serializability across nodes means waiting on the slowest participant, exactly what you can't afford when a partition splits your cluster. BASE systems instead make per-document writes atomic and fast, then propagate changes asynchronously. Between write and convergence, replicas disagree (soft state); given time with no new writes, they agree (eventual consistency). Crucially, MongoDB lets you tune this dial per operation with write and read concerns:

   client ── insert {_id: 9007} ──▶ PRIMARY (replica set)
                                      │ 1) apply write locally
                                      │ 2) append to oplog
                 oplog replication ▼  │ 3) await majority ack
        ┌──────────────┐   ┌──────────────┐
        │ SECONDARY A  │   │ SECONDARY B  │      3-node set:
        │ applied ✓    │   │ applying…    │      majority = 2 nodes
        └──────────────┘   └──────────────┘
                                      │
        PRIMARY replies "ack" only after A (or B) confirms ◀───┘
        → the write now survives an election that kills
          any ONE node. With w:1 it would NOT.

 Failover: secondaries elect a new PRIMARY via Raft-style
 majority vote (priority + latest oplog position win).
 Reads marked "majority" never observe rolled-back writes.

5. Query Performance: B-Tree Lookups, Join Algorithms & Compound Indexes

An unindexed query is a full scan: the engine reads every page of the table and filters in memory. An indexed point lookup descends a B+ Tree (roughly 3–4 page reads even for billions of rows, since each node fans out hundreds of ways). Three index superpowers matter in production:

Join Algorithms: How Tables Actually Get Combined

Joins are the relational superpower and the reason document stores embed instead. When you do join, the planner picks one of three algorithms:

 SELECT c.name, SUM(o.total)
 FROM customers c JOIN orders o ON o.customer_id = c.id;

 BUILD PHASE (small side → hash table)     PROBE PHASE (large side)

 ┌────────────────────┐                    ┌──────────────────────┐
 │ customers          │   hash(id)         │ orders row: cust=10  │
 │ id=10 "Ana"  ──┐   │   ┌─────────┐      │   → bucket 0 ✓ JOIN  │
 │ id=42 "Ben"  ──┤   ├──▶│ bkt [10]│◀──┐  │ orders row: cust=99  │
 │ id=44 "Dee"  ──┘   │   │ bkt [42]│   ├──┤   → bucket 9 ✗ skip  │
 └────────────────────┘   │ bkt [44]│───┤  │ orders row: cust=44  │
                          └─────────┘   └──┤   → bucket 2 ✓ JOIN  │
                                           └──────────────────────┘
 Cost ≈ O(M + N): one pass each side; ideal when the build
 side fits in work_mem.

 NESTED LOOP: for each outer row, probe inner index
              O(N × log M) with index; O(N × M) without.
              Wins for small outer sets (point lookups).
 MERGE SORT: both inputs sorted on key → linear merge.
             Great for pre-sorted/indexed large ranges.

The Document Answer: Multikey Indexes & the Aggregation Pipeline

MongoDB indexes work the same way (B-trees), with two document-specific twists. A multikey index automatically indexes every element of an array field (one product tagged ["wireless", "gaming", "rgb"] contributes three index entries), so array membership queries are indexed lookups. And for compound indexes, practitioners use the E-S-R rule: Equality fields first, then Sort fields, then Range fields. That ordering lets the index both find and return rows pre-sorted without a blocking SORT stage.

 db.products.createIndex({ tags: 1, price: 1 })

 documents                        B-tree entries (tags asc, price asc)
 ┌─────────────────────────┐      ┌──────────────────────────────┐
 │ _id: 1                  │      │ ("gaming",   49.99) → doc 1  │
 │ tags: ["gaming","rgb"]  │ ───▶ │ ("rgb",      49.99) → doc 1  │
 │ price: 49.99            │      │ ("office",   19.99) → doc 2  │
 ├─────────────────────────┤      │ ("rgb",      89.00) → doc 3  │
 │ _id: 2                  │ ───▶ │ ("wireless", 19.99) → doc 2  │
 │ tags: ["office",        │      │ ("wireless", 59.00) → doc 4  │
 │        "wireless"]      │      └──────────────────────────────┘
 │ price: 19.99            │
 └─────────────────────────┘      Query: { tags: "rgb",
                                    price: { $lt: 60 } }
                                  → E-S-R fit: equality "rgb" bounds
                                    the scan; range prunes inside it.

Where SQL composes joins and GROUP BY, MongoDB composes the aggregation pipeline: stages like $match → $unwind → $group → $sort transform documents stream-style. Cross-collection references are resolved by a $lookup stage (a join, but one that runs as pipeline stages rather than a planner-optimized relational algebra tree). It works well for modest fan-outs and analytics; for hot request paths it is usually a design smell telling you to embed instead.

6. Scaling Mechanics: Vertical Scale-Up vs Horizontal Auto-Sharding

Every single-node database eventually hits a ceiling: CPU cores, RAM for caching, or NVMe IOPS. The two families take philosophically different exits. Relational engines scale up first (bigger machine, connection pooling, read replicas) because correctness is easy on one node. MongoDB was designed to scale out natively: collections are transparently partitioned into chunks across shards.

 RELATIONAL: SCALE UP, THEN FEDERATE READS

 app ──▶ PgBouncer (pool 10k conns ──▶ ~100 backends)
              │
              ▼
        ┌───────────┐  streaming   ┌─────────────┐
        │ PRIMARY   │─────────────▶│ REPLICA 1..n│◀── reports / reads
        │ (writes)  │  replication └─────────────┘
        └───────────┘
 Ceiling = the biggest box money can rent. Replicas ease
 reads but every write still funnels through ONE primary.

 MONGODB: HORIZONTAL AUTO-SHARDING (partition by shard key)

                 ┌───────────────┐        ┌────────────────┐
 client ────────▶│ mongos router │◀──────▶│ CONFIG SERVERS │
                 └───┬───┬───┬───┘        │ (chunk map,    │
        shard key    │   │   │            │  metadata)     │
        routing      ▼   ▼   ▼            └────────────────┘
        ┌─────────┐ ┌─────────┐ ┌─────────┐
        │ SHARD A │ │ SHARD B │ │ SHARD C │   each shard =
        │ chunk   │ │ chunk   │ │ chunk   │   an independent
        │ [minKey │ │ [-500,  │ │ [250 →  │   replica set
        │  , -500)│ │  250)   │ │ maxKey] │   (HA built-in)
        └─────────┘ └─────────┘ └─────────┘
 Writes/reads fan out across machines; throughput grows with
 each shard added. A balancer migrates chunks when skews.

The Shard Key Is a Lifetime Commitment

Sharding quality lives and dies on the shard key. It must have high cardinality (many distinct values), distribute writes evenly, and match your dominant query pattern. A monotonically increasing key like timestamp or auto-increment id routes every insert to the last chunk, creating a hot shard that nullifies the cluster. Hashed shard keys flatten distribution but destroy range queries; ranged keys preserve them but risk skew. And historically, changing the key meant rebuilding the entire dataset (modern MongoDB offers online resharding, at real operational cost).

7. The Modern Convergence: PostgreSQL JSONB vs MongoDB ACID Transactions

The 2010s holy war has quietly ended in a handshake. Relational engines absorbed the document model; document stores absorbed transactions. The question in 2026 is less "SQL or NoSQL?" and more "which engine's default guarantees match this workload?"

PostgreSQL: Document Store Inside a Relational Engine

The JSONB type stores JSON as a decomposed binary structure (keys deduplicated, values pre-parsed), indexable with GIN inverted indexes that make containment queries fast across millions of heterogeneous documents. You get document flexibility and the full relational arsenal (joins, ACID, constraints, mature tooling) in one engine:

CREATE TABLE product_catalog (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  seller_id   BIGINT NOT NULL REFERENCES sellers(id),
  attributes  JSONB   NOT NULL DEFAULT '{}'::jsonb,
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  -- schema-on-write governance over the flexible blob:
  CONSTRAINT attrs_price_numeric CHECK (
    (attributes ->> 'price') IS NULL
    OR jsonb_typeof(attributes -> 'price') = 'number'
  )
);

-- GIN index: index EVERY key/value inside EVERY document
CREATE INDEX idx_attrs_gin
  ON product_catalog USING GIN (attributes jsonb_path_ops);

-- Indexed containment query (no full scan):
SELECT * FROM product_catalog
WHERE attributes @> '{"color": "midnight", "connectivity": "wireless"}';

-- And still: real joins to relational tables when needed
SELECT p.id, p.attributes ->> 'price' AS price, s.name
FROM product_catalog p JOIN sellers s ON s.id = p.seller_id;

MongoDB: Transactions Inside a Document Store

Since version 4.0 (replica sets) and 4.2 (sharded clusters), MongoDB runs genuine multi-document ACID transactions with snapshot isolation: commit or abort across many documents and collections, coordinated through its Raft-based consensus. The engine even grew schema governance: optional $jsonSchema validation rules can reject malformed documents at write time. The trade-off that remains: transactions add latency and are best kept short; single-document atomic updates are still the fast path.

const session = client.startSession();
try {
  session.startTransaction({
    readConcern:    { level: "snapshot" },   // consistent view
    writeConcern:   { w: "majority" },      // survives failover
    readPreference: "primary",
  });

  const accounts = client.db("fintech").collection("accounts");
  accounts.updateOne({ _id: "A" }, { $inc: { balance: -100 } }, { session });
  accounts.updateOne({ _id: "B" }, { $inc: { balance:  100 } }, { session });

  await session.commitTransaction();  // atomic across BOTH docs
} catch (err) {
  await session.abortTransaction();   // all-or-nothing rollback
} finally {
  await session.endSession();
}

// Optional schema-on-write governance:
db.createCollection("products", { validator: { $jsonSchema: {
  required: ["sku", "price"],
  properties: { price: { bsonType: "decimal", minimum: 0 } }
} } } });

8. Production Case Studies, Trade-offs & the Decision Matrix

Abstract trade-offs become concrete in four workloads that almost every product team eventually builds:

The Reference Decision Matrix