SQL Databases

A SQL database stores data as typed rows inside tables and answers declarative queries by feeding them to a cost-based query planner that turns your SELECT into a physical plan over B-tree indexes, while a write-ahead log (WAL) makes every transaction all-or-nothing and crash-durable. You say what you want; the engine decides how to fetch it and guarantees the data never lands in a half-written state.

The four glossary terms are not vocabulary — each is a mechanism that buys a specific guarantee:

• Table + typed columns — a fixed shape the planner can reason about; knowing a column is BIGINT NOT NULL lets it pick scans, joins, and storage layout ahead of time.
• Primary key (PK) — a uniqueness constraint backed by a unique index. "Unique" is enforced by that index rejecting a duplicate on insert, not by a scan.
• Foreign key (FK) — a rule that a value in one table must already exist as a PK in another. On every insert/delete the engine probes the parent's index; a delete that would orphan children is blocked (or cascades). This is referential integrity, enforced by the storage engine, not your app code.
• Index — a sorted auxiliary structure (usually a B-tree) so a lookup is O(log n) page reads instead of an O(n) full table scan.
• Normalization — storing each fact in exactly one place so an update touches one row, killing the update anomaly (shown below).

Worked example: how a JOIN actually runs

Two tables. Note the FK and the index on the FK column — that index is the whole reason the join is fast.

CREATE TABLE customers (
  id      BIGINT PRIMARY KEY,        -- unique index, auto-created
  email   TEXT NOT NULL UNIQUE,      -- unique index
  country TEXT NOT NULL
);

CREATE TABLE orders (
  id           BIGINT PRIMARY KEY,
  customer_id  BIGINT NOT NULL REFERENCES customers(id),  -- FK
  amount_cents INT    NOT NULL
);

CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_customers_country ON customers(country);

Sample data:

The query — total spend per Indian customer:

SELECT c.email, SUM(o.amount_cents) AS total_cents
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.country = 'IN'
GROUP BY c.email;

What the planner does, step by step (an index nested-loop join):

1. Filter the small side first. WHERE country = 'IN' hits idx_customers_country, returning ids {1, 3} without scanning bob's row. (With 1M customers, this reads a handful of index pages, not a million rows.)
2. Probe the FK index once per driving row. For id=1, walk idx_orders_customer to customer_id=1 → orders {101, 102}: amounts 500, 300. For id=3 → order 104: amount 250. Each probe is O(log n) page reads.
3. Aggregate. GROUP BY email sums each group: alice → 500+300, cara → 250.

Drop idx_orders_customer and step 2 collapses into a full scan of orders for every driving customer — the join goes from ~O(k·log n) to O(k·n). This is the single most common cause of a slow join in production.

Why normalization exists: the update anomaly

Suppose you flatten everything into one table, repeating the customer's country on every order row:

Alice moves to Singapore. Her country now lives in two rows. Update one and miss the other and the database disagrees with itself — country is both IN and SG for the same person, and no query can tell which is true. Normalizing to separate customers and orders tables stores the country once; the update touches a single row and cannot go half-done. That one-fact-one-place rule is the entire point of PK/FK and normal forms.

What ACID buys you: the WAL trace

Move ₹100 between accounts:

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

The mechanism behind Atomicity + Durability is the write-ahead log:

1. Both updates are appended as records to the WAL and the change is made in memory.
2. COMMIT does not return until the WAL up to this transaction is fsync-ed to disk. Only then is the client told "done."
3. If the server crashes after the debit but before the credit, restart recovery replays committed WAL records and rolls back the uncommitted transaction. You never observe ₹100 that vanished — the account pair is either fully moved or untouched.

Isolation is the sibling guarantee: while your transaction runs, other sessions don't see your half-applied writes (via locking or MVCC snapshots). Together this is why banks, inventory, and order systems reach for SQL by default.

The four SQL sub-languages, with real statements

SQL is one language split by what it acts on:

The split matters operationally: DDL often takes stronger locks than DML (an ALTER TABLE can block reads/writes on a hot table), and TCL is what actually triggers the WAL fsync above.

Pitfalls

• Missing index on the FK column. A join or a cascading delete then scans the child table fully for every parent row. The classic silent O(n) blowup — the join was fast in dev with 1k rows, unusable at 10M.
• N+1 queries. An ORM loop that fires one SELECT per parent (100 customers → 101 round-trips) instead of a single join. Network latency, not the DB, becomes the bottleneck.
• A function on an indexed column kills the index. WHERE lower(email) = 'a@x.com' can't use a plain index on email — it scans. Fix with a functional/expression index or store normalized. Same trap: implicit type casts (WHERE id = '5' when id is BIGINT).
• OFFSET pagination. LIMIT 20 OFFSET 100000 makes the engine read and discard 100,000 rows every page. Use keyset pagination (WHERE id > :last_seen).
• Long-running transactions. An open transaction holds locks and, under MVCC, prevents cleanup of old row versions — causing lock waits and table bloat. Keep transactions short.
• SELECT * everywhere. Pulls wide/TOASTed columns you don't need and prevents index-only scans. Select the columns you use.

When to use SQL — and when not to

SQL scales vertically beautifully (one big primary + read replicas) but resists horizontal write scaling: sharding breaks cross-shard joins and multi-row ACID transactions, which are the very things you chose SQL for. That tension drives the decision.

Reach for a relational database (PostgreSQL / MySQL) when:

• Data has real relationships and you run ad-hoc, multi-table queries (joins, aggregations, reporting) whose shape you can't predict up front.
• You need multi-row transactional integrity — money, inventory, bookings — where a half-applied write is unacceptable.
• Write volume and dataset fit one strong primary with replicas (the overwhelming majority of apps, up to many TB and tens of thousands of writes/s).

Prefer an alternative when:

• vs a document store (MongoDB): your access is a single self-contained aggregate fetched by key, the schema varies per record, and you want denormalized reads without joins. You gain schema flexibility and simpler horizontal sharding; you pay by losing cheap cross-entity joins and, historically, weaker multi-document transactions.
• vs a wide-column store (Cassandra) or key-value (DynamoDB): you need massive horizontal write throughput and multi-region availability with predictable single-key latency. You gain linear write scaling and AP-style availability; you pay with eventual consistency, no ad-hoc queries (you model tables per query pattern), and no joins.
• vs a cache/KV (Redis): the workload is hot single-key reads that don't need durability or querying.

Rule of thumb: choose SQL when correctness and query flexibility dominate and you're not yet forced to shard writes; prefer a NoSQL store when horizontal write scale, schema fluidity, or single-key latency at scale dominate and you can live without joins and strong cross-row transactions. Most systems should start on SQL and only move specific hot paths off it when a measured scaling wall appears — not before.

Takeaways

• SQL's power is declarative + planned: you describe the result, a cost-based planner uses B-tree indexes to fetch it, and the WAL guarantees all-or-nothing durability.
• PK/FK/index/normalization aren't vocabulary — each enforces a concrete guarantee (uniqueness, referential integrity, O(log n) lookup, one-fact-one-place).
• Almost every "slow SQL" incident is a missing index on a join/filter column, an N+1 loop, or an index defeated by a function/cast — check those first.
• Default to SQL for relational, transactional, query-diverse workloads; move off it only when you hit a measured horizontal-write wall and can trade away joins and cross-row transactions.

Re-authored and deepened for this guide. Mechanism and internals draw on Alex Petrov, Database Internals; Martin Kleppmann, Designing Data-Intensive Applications (Ch. 2–3); the PostgreSQL documentation (query planner, WAL, and index chapters); and Markus Winand's Use The Index, Luke! for the indexing and pagination pitfalls. Normalization framing follows C. J. Date, An Introduction to Database Systems.