# Relational vs NoSQL

## 1. Two 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:

| Model | Example systems | Shape |
|---|---|---|
| Document | MongoDB, Couchbase | nested JSON-like documents, self-contained |
| Key-value | Redis, DynamoDB | opaque value behind a key, no query into structure (mostly) |
| Wide-column | Cassandra, HBase, Bigtable | rows with dynamic, sparse sets of columns, partitioned by row key |
| Graph | Neo4j | nodes and edges, optimized for traversal |

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

## 2. Schema-on-Write vs Schema-on-Read

```sql
-- 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"
```

```js
// 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.

## 3. Relationships and Joins

```sql
-- 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;
```

```js
// 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.

## 4. Consistency 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.

## 5. Under the Hood

### CAP, with the actual math instead of the buzzword

CAP theorem says a distributed system can't simultaneously guarantee **C**onsistency (every read sees the latest write), **A**vailability (every request gets a response), and **P**artition 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).

```
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.

![Quorum writes and reads overlapping on at least one node when W+R>N](diagrams/01-quorum.svg)

### 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).

## 6. 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.

## 7. 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
```