A course grounded in your services
PostgreSQL, from newcomer to interview-ready
From the relational model to MVCC, indexes & the planner — anchored to your code.
You use Postgres through pgx every day, but you're newer to SQL and the internals — so this course ramps from the relational model and basic SQL up to the advanced areas that win interviews: how data is stored, MVCC, indexes, and the query planner. Then it ties it all back to how your Go services actually talk to the database. Seven parts, 31 lessons, built one at a time. Lessons are short — one win each. Read them in order.
Part 1 — Relational & SQL foundations available now
Start here: the model everything rests on, and the SQL you'll read in every repo.
1 · Postgres & the relational model
Tables, rows, relations — and why an RDBMS, told through your emails table.
2 · Data types, schema & constraints
Columns, types, NULL, primary/foreign keys, and constraints that protect data.
3 · Querying: SELECT, JOIN, aggregate
Reading data — WHERE, joins, GROUP BY — the everyday SQL.
4 · Writing data & UPSERT
INSERT/UPDATE/DELETE, ON CONFLICT upserts, and a first look at transactions.
Part 2 — Internals & data structures available now
Under the surface — how Postgres stores, versions, and protects your data.
5 · Storage: pages & tuples
A table is a file of 8 KB pages; a tuple is a row version.
ready6 · MVCC
The big one — new versions, snapshots, readers don't block writers.
ready7 · TOAST
How big JSONB/arrays live out-of-line — and why SELECT * costs I/O.
8 · WAL
Durability & crash recovery — and the log that feeds your Kafka CDC.
ready9 · VACUUM & bloat
Why MVCC needs cleanup; autovacuum, HOT, and the "still huge" gotcha.
readyPart 3 — Indexes available now
The side structures that skip the sequential scan — built on Part 2's storage model.
10 · Why indexes exist: the B-tree
Avoiding the seq scan; the balanced tree you'll use 90% of the time.
ready11 · Beyond B-tree
GIN, GiST, BRIN, Hash — your trigram/array search is GIN.
ready12 · Advanced indexing
Composite (leading-prefix), partial, covering, expression.
ready13 · Index trade-offs
Write cost, when not to index, and why a present index isn't used.
readyPart 4 — Query performance available now
Where indexes become visible — see a plan get chosen, and fix it.
14 · The planner & statistics
Cost-based planning from statistics — and why stale stats make queries slow.
ready15 · Reading EXPLAIN / ANALYZE
The single most useful skill — read the tree, spot estimate-vs-actual gaps.
ready16 · Scans & joins
Seq/Index/Bitmap; Nested-Loop/Hash/Merge — and when each is chosen.
ready17 · Tuning slow queries
A repeatable method — the span → /query-db → EXPLAIN → fix loop.
Part 5 — Transactions & concurrency available now
Correctness under load — all resting on the MVCC from Part 2.
18 · ACID & isolation levels
The four guarantees; Read Committed / Repeatable Read / Serializable.
ready19 · Locks & deadlocks
Reads don't lock, writes do; FOR UPDATE; how deadlocks form & resolve.
20 · Concurrency anomalies & SSI
Lost update, write skew — and exactly which level/technique stops each.
readyPart 6 — PostgreSQL in Go (this repo) available now
Everything you learned, back in Go — pgx, the DB layer, migrations, and RLS.
21 · pgx & the connection pool
The driver and pooling your services use — and where per-request security is injected.
ready22 · The database layer
Ext/Tx, entities, ExecInTx — the Go course made concrete.
23 · Migrations & schema lifecycle
golang-migrate, forward-only, CONCURRENTLY — how schema changes ship.
24 · RLS & multi-tenancy
resource_path from JWT to row — where all four courses meet.
Part 7 — Senior PostgreSQL backend engineering new
The senior stretch — planner trade-offs, bloat and autovacuum, migration safety, and interview-grade answers.
25 · Query shape and index choice
Choose B-tree, composite, partial, covering, or expression indexes from the actual SQL shape.
new26 · Planner misestimates & cardinality
Why Postgres chooses the wrong plan, and how row-count thinking explains it.
new27 · Bloat, HOT & autovacuum
The storage cost of write-heavy systems and why cleanup pressure matters.
new28 · Locks, DDL risk & safe schema changes
Why migrations are operational events, not just DDL files.
new29 · Production query debugging workflow
The evidence-first loop for turning a slow-query complaint into the right fix.
new30 · Senior PostgreSQL interview questions
High-signal prompts with the best answer shape for planner, storage, and backend topics.
new31 · Mock senior PostgreSQL interview pack
A retrieval-first mock round with scoring and strong answer points for every question.
newReference shelf
Cheat sheet
Dense revision sheet + interview one-liners.
Glossary
The canonical vocabulary, opinionated.
Repo Postgres map
Ground truth: our pgx layer, migrations, indexes, RLS.
Resources
Official docs, Use The Index Luke, Rogov internals, pgx, locking, and vacuuming.
Senior backend playbook
The compressed version of the new senior-only part: planner, storage cost, migration safety, and debugging workflow.