SQL, joins,
and the index that saves you.

A relational database organizes data into tables — rows (records) and columns (fields) with a fixed schema. Two ideas make it "relational." A primary key uniquely identifies each row (a user's id). A foreign key in one table references the primary key of another, encoding a relationship (an orders row has a user_id pointing at users).

This lets you store each fact once and link records instead of duplicating them: a user's name lives in one place, and a thousand orders reference it by id. Relationships come in shapes — one-to-many (a user has many orders), many-to-many (students and courses, via a join table). The whole power of SQL is querying across these relationships, which is what joins are for.

A join combines rows from two tables by matching a condition, usually a foreign key equal to a primary key. The join type decides what happens to rows that don't match.

The most common bug is expecting a LEFT JOIN but writing an INNER JOIN and silently dropping rows that have no match — a user with zero orders vanishes from the report. Pick the type by asking: "do I want to keep rows that have no partner on the other side?"

Without an index, finding rows where email = ... means a full table scan: read every row and check. On a million rows, that's slow. An index is a separate, sorted data structure — almost always a B-tree — that maps a column's values to the rows containing them, so the database can jump straight to matches in O(log N) instead of O(N).

Why a B-tree? It keeps keys sorted and balanced, so lookups, range scans (BETWEEN, >), and ordered reads (ORDER BY) are all fast. But indexes aren't free: they take space, and every INSERT/UPDATE/DELETE must also update the index — so indexes speed reads and slow writes. Index the columns you filter, join, and sort on; don't index everything.

You write what you want (declarative SQL); the database's query planner decides how to get it — which indexes to use, join order, and algorithm. It estimates the cost of each plan using table statistics and picks the cheapest. The same query can run in milliseconds or minutes depending on the plan.

EXPLAIN (and EXPLAIN ANALYZE) shows you the plan the database chose. The single most important thing to spot: a sequential scan on a big table where you expected an index scan — that usually means a missing index, or a query written so the index can't be used (a function on the column, a leading wildcard LIKE '%x', an implicit type cast). Reading EXPLAIN is how you turn "the query is slow" from a mystery into a fix.

A transaction groups several statements so they succeed or fail as a unit. Relational databases guarantee ACID:

The classic example is a money transfer: debit one account and credit another must both happen or neither. Atomicity makes that safe; durability makes it stick. This is why financial and inventory systems live on relational databases — ACID is the guarantee.

Perfect isolation (every transaction as if it ran alone) is expensive, so databases offer isolation levels that trade correctness against concurrency. Each level prevents certain anomalies:

The levels — READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE — progressively prevent more of these. Most databases default to READ COMMITTED (no dirty reads), and you raise it where you need stronger guarantees. SERIALIZABLE prevents all anomalies but costs the most concurrency. Pick the weakest level that keeps your logic correct.

Normalization means structuring tables so each fact is stored once, without redundancy — separate users, orders, products tables linked by keys. It keeps data consistent (change a name in one place) and avoids anomalies, and it's the right default. The cost: answering a question often requires joining several tables.

Denormalization deliberately duplicates data (e.g. storing a product name on the order row) to avoid joins and speed reads — at the cost of consistency (now you must update the name in many places) and storage. It's an optimization you apply after measuring, on hot read paths, not a starting point. Normalize first; denormalize surgically when the read cost demands it.

A handful of mistakes cause most SQL performance pain.

Underneath all of them is one habit: know what the database is actually doing. Read the plan, index intentionally, fetch in bulk not in loops, and reach for transactions when correctness matters. That's the difference between SQL that works on your laptop and SQL that works in production.