Curriculum

The parts everyone thinks they know. Three-valued logic, what a join does with the rows that found no partner, and why COUNT(*) and COUNT(col) are different numbers.

1. 1
   SELECT, and what a column really is

   A select list is a list of expressions, not a list of columns. Once that lands, half of SQL stops being a special case.

   8 min
2. 2
   NULL, and the third truth value

   NULL is not a value. It is the absence of one, and every comparison it touches returns neither true nor false. Half of all real SQL bugs live here.

   14 min
3. 3
   Joins, as sets and as loops

   A join is a nested loop with a condition. Every join type is a rule about what to do with the rows that found no partner.

   12 min
4. 4
   Grouping, and the two ways to count

   COUNT(*) counts rows. COUNT(col) counts values. They are different numbers, and knowing which one you asked for is most of the skill.

   12 min
5. 5
   Subqueries, and when they are secretly a loop

   A subquery that mentions the outer query runs once per outer row. One that does not runs once. The difference does not show in the SQL.

   12 min
6. 6
   Set operations, and what they cost

   UNION removes duplicates and UNION ALL does not — which is not a stylistic difference. One of them has to remember every row it has already emitted.

   8 min
7. 7
   The order clauses actually run in

   Written in one order, evaluated in another. Almost every “why can’t I use my alias there” question is answered by this, and so are several performance ones.

   10 min

Normalisation to the point where it stops paying. What a foreign key costs on write. Surrogate against natural keys, soft deletes, and time zones stored wrong.

1. 1
   Constraints are correctness, not decoration

   A constraint is the one guarantee that survives every bug in every application that will ever talk to this database. It also has a cost, and the cost is on the write path.

   12 min
2. 2
   What a data JSONB column costs

   Every AI-written schema has one. It is not free, it is not indexed, and the planner has no idea what is inside it — and the plan will say all three out loud.

   14 min

Selectivity, composite column order and the left-prefix rule, covering indexes, partial and expression indexes, and the corollary that a function around a column throws the index away.

1. 1
   What an index is, and what it costs

   A B-tree is a sorted structure with a real height. Reading it is cheap; going back to the table for each row it found is not, and that is the whole story.

   12 min
2. 2
   Column order, and the index that answers on its own

   An index on (a, b) can seek on a, and an index on (b, a) cannot. And an index that carries every column the query wants never touches the table.

   12 min
3. 3
   Functions, and the index that only holds some rows

   Wrapping a column in a function throws its index away, because the index stores the column and you asked about something else.

   10 min

Estimated against actual. The three join algorithms and when each wins. Stale statistics, correlated predicates, and the case where the planner is right and the query is wrong.

1. 1
   Reading a plan

   A plan is a tree of numbers. Estimated against actual is one column, and it localises more slow queries than everything else put together.

   14 min
2. 2
   Where the estimate comes from

   ANALYZE takes a snapshot. Everything the planner knows is from that moment, and the gap between it and now is where bad plans come from.

   12 min
3. 3
   The three join algorithms

   Nested loop, hash and merge. Each wins somewhere, and the plan tells you which one you got and what it cost.

   12 min

Isolation levels and what each costs. Lost updates and the SELECT … FOR UPDATE that prevents them. Deadlock as a wait-for graph, and the bloat a long transaction leaves behind.

1. 1
   What a transaction actually guarantees

   Not that nothing else happens. Only that your work lands all at once or not at all — and that what you read stays still for as long as your isolation level says it does.

   12 min
2. 2
   The four anomalies, and what each level costs

   Dirty read, non-repeatable read, phantom, and write skew. Postgres never permits the first, permits the next two at READ COMMITTED, and refuses all four at SERIALIZABLE.

   16 min
3. 3
   Locks, deadlock, and what a long transaction leaves behind

   Writers block writers. Two of them waiting for each other is a deadlock, and the fix is lock ordering. And any open transaction stops the whole table from being cleaned up.

   14 min

Keyset against offset pagination, measured. Migrations that lock and the patterns that do not. Connection pools, and EXPLAIN triage as a procedure.

1. 1
   The N+1, from the database side

   One query for the list and one for each row. From the application it looks like a loop; from the database it looks like a thousand identical statements, and the plan shows you the loops.

   12 min
2. 2
   Pagination, at page 1 and at page 20,000

   OFFSET is not a seek. It reads every row it skips, so page 20,000 costs twenty thousand pages more than page 1 — and the last page is the slowest thing your app does.

   12 min
3. 3
   Migrations that lock, and the ones that do not

   Most of what makes a migration dangerous is which lock it takes and for how long. A few of them take a lock that stops every read on the table, and they look identical in the file.

   12 min
4. 4
   EXPLAIN triage, as a procedure

   It is three in the morning and something is slow. Here is the order to look in, and what each answer rules out.

   10 min

The thesis track. The ORM's N+1, the SELECT * in a hot path, the IN list with four thousand elements, the migration with no CONCURRENTLY, and the query that is correct and takes ninety seconds.

1. 1
   A query an agent wrote

   It compiles, it returns the right rows on your laptop, and it passes review because the reviewer read the SQL and not the plan. The thesis track.

   14 min