Relational vs NoSQL

1Two Different Bets About Your Data

A relational database (Postgres, MySQL, SQL Server) bets that your data has a stable, well-understood shape that's worth declaring up front, and that relationships between entities (customers who place orders which contain line items) are first-class things you'll query across constantly. A NoSQL database bets the opposite: that the shape will keep changing, that you'll mostly fetch data by a single key or a narrow access pattern, and that horizontal scale-out matters more than cross-entity querying.

"NoSQL" is not one thing — it's an umbrella over several different data models:

This topic focuses mainly on relational vs document stores, since that's the most common head-to-head comparison in application design.

2Schema-on-Write vs Schema-on-Read

-- Relational: the shape is declared once, enforced on every write
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  age INT CHECK (age >= 0),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO users (email, age) VALUES ('bad', -5);
-- ERROR: new row for relation "users" violates check constraint "users_age_check"

// Document store: the shape is whatever you insert. Nothing stops
// two "users" documents from having wildly different fields.
db.users.insertOne({ email: "a@x.com", age: 30 });
db.users.insertOne({ email: "b@x.com", age: -5, favoriteColor: "teal" }); // succeeds

Relational engines reject the invalid row at write time, inside the same transaction, before it ever lands on disk. Document stores typically accept whatever you send them; if you want validation, you either enforce it in application code or configure an optional schema validator the engine checks per-write — but the default posture is schema-on-read: the shape is whatever the reading application decides to interpret it as.

3Relationships and Joins

-- Relational: relationships are foreign keys, joined at query time
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.id = 42;

// Document: relationships are usually either embedded (denormalized copy)
// or referenced by an ID with no engine-enforced join
db.orders.find({ customerId: 42 }); // app code fetches customer 42 separately, or...
{
  _id: 501,
  customerId: 42,
  customerNameSnapshot: "Ana Petrova", // embedded copy, avoids a second lookup
  total: 129.99
}

Document databases generally don't offer a real cross-collection JOIN (some, like MongoDB, offer $lookup, but it's not optimized the way a relational planner optimizes joins). The standard document-model answer to "avoid the second query" is to embed the data you'll need together, accepting duplication.

{ "_id": "order-9",
  "customer": { "id": 42, "name": "Ana", "email": "a@x.io" },
  "items": [ { "sku": "tree-1", "price": 45 } ] }

customers(id, name, email)
orders(id, customer_id → customers.id)
order_items(order_id, sku, price)
SELECT … FROM orders JOIN customers …

4Consistency Models Are the Real Divide

The deeper difference isn't "tables vs documents" — it's what each system guarantees when data is replicated across multiple nodes. That's covered precisely below.

Under the HoodHow it works internally▾

CAP, with the actual math instead of the buzzword

CAP theorem says a distributed system can't simultaneously guarantee Consistency (every read sees the latest write), Availability (every request gets a response), and Partition tolerance (the system keeps working when nodes can't talk to each other) — and since network partitions will happen, you're really choosing between C and A during a partition.

The concrete mechanism behind this choice is quorum replication. Say a piece of data is replicated across N nodes. Define:

• W = number of nodes that must acknowledge a write before it's considered successful
• R = number of nodes a read must consult before returning a result

If W + R > N, every read quorum overlaps with every write quorum by at least one node — so a read is mathematically guaranteed to see at least one copy of the most recent write. That's strong consistency, bought at the cost of latency (every write/read waits on multiple nodes) and availability (if not enough nodes are reachable to form a quorum, the operation fails rather than serving stale data).

textCopy

N = 3 replicas
W = 2, R = 2   ->   W + R = 4 > 3   -> strong consistency, tolerates 1 node down
W = 1, R = 1   ->   W + R = 2 < 3   -> fast, available, but a read can miss the latest write

• CP example — HBase / MongoDB with majority write concern: a write isn't acknowledged until a majority of replica set members have it. If the primary is partitioned from the majority, it steps down and stops accepting writes rather than risk divergence — you lose availability to keep consistency.
• AP example — Cassandra / DynamoDB with tunable consistency: you can set W=1, R=1 (ONE consistency level in Cassandra terms) so every node keeps accepting reads and writes even during a partition, and reconciles conflicting versions later (via mechanisms like last-write-wins or vector clocks) — you keep availability and accept temporary inconsistency.

Why relational systems default to CP-like behavior on a single node

A single-instance relational database sidesteps the whole CAP trade-off for as long as it stays single-instance: there's only one copy of the data, so there's nothing to reconcile — every transaction is immediately, trivially consistent. The trade-off only reappears once you add replicas (see the Replication and Sharding topics), at which point Postgres/MySQL make the same real choice between synchronous replication (CP-leaning, safer, slower) and asynchronous replication (AP-leaning, faster, replicas can lag).

⚠️ Common Pitfalls

1. Treating "NoSQL" as a single technology — a key-value store, a document store, and a graph database solve completely different problems; picking "NoSQL" without picking a specific data model is not a decision.
2. Choosing a document store for data that's actually deeply relational — if your access patterns constantly need to join across many entities, you'll end up re-implementing joins in application code, slower and with none of the query planner's optimizations.
3. Assuming "eventually consistent" means "wrong sometimes" — it means "correct, but possibly briefly stale," which is fine for a like counter and unacceptable for a bank balance.
4. Ignoring embedding vs referencing trade-offs in document design — embedding avoids a second query but duplicates data that now must be kept in sync everywhere it's copied; there's no free lunch, just a different place the cost shows up.
5. Picking based on scale you don't have yet — a single well-tuned Postgres instance comfortably handles far more load than most projects will ever produce; "we might need to scale like Google" is rarely a good reason to give up joins and transactions on day one.

📋 Quick Reference

Relational: schema-on-write, enforced constraints, joins across normalized tables, strong single-node consistency
Document/KV/wide-column/graph: schema-on-read (usually), duplication over joins, tunable consistency at scale

CAP: pick 2 of {Consistency, Availability, Partition tolerance} during an actual partition
Quorum: W + R > N -> strong consistency (every read/write quorum overlaps)
        W + R <= N -> faster, more available, can read stale data

🎤 Interview Q&A

🛠️ Practice exercises

Each exercise is a file in this topic folder. Code exercises are self-checking: run them and make the checks pass.

✅ Check yourself

Source: dbfund_relational_vs_nosql_theory.md · generated by tools/build_lessons.py