PostgreSQL for people who write Go

You're a new backend developer at Brew, a fictional chain of coffee shops. A hands-on PostgreSQL 18 course for application developers, in Go: from the first connection and data types to indexes, MVCC, JSONB, window functions, and server-side logic — plus capstones with outbox/CDC. One data model runs through the whole course; the finale is you opening a PUBLICATION yourself and handing Brew's change stream to the sibling kafka-cookbook. You write SQL by hand, type it with sqlc, and every snippet runs on a local sandbox (postgres:18 + Adminer) and leaves an observable effect — working scenarios, not whiteboard lectures.

Start from the first lesson→Continue · lesson 1→Start over
↳ Meet Brew
/ contents

Course outline

11 modules · 63 lessons
  1. 01

    Getting connected

    The course on-ramp: who you are, what Brew is, and what we'll build (an intro map unit), then the first technical layer — client-server, the local sandbox (postgres:18 + Adminer), psql as a working tool, connecting from Go via pgxpool, and typed queries via sqlc. After this module you have a working pipeline — "SQL by hand → sqlc generate → typed pgx code" — that every other topic builds on.

    not startedin progresscomplete0/6
    6 lessons2 h 10 min
  2. 02

    Data types

    Which type to pick and why: numeric vs float for money, text/boolean/null, timestamptz for time, uuid and PG18 uuidv7, enums/arrays, and an intro to jsonb. The right type up front removes a whole class of production bugs.

    not startedin progresscomplete0/5
    5 lessons1 h 55 min
  3. 03

    Schema

    Identity vs serial, NOT NULL, primary and foreign keys, UNIQUE/CHECK, generated columns and domains (PG18 virtual vs stored), and a migration mindset — which ALTERs are instant and which rewrite the table and block writes.

    not startedin progresscomplete0/6
    6 lessons2 h 15 min
  4. 04

    CRUD fluency

    Confident CRUD: INSERT ... RETURNING, SELECT with WHERE/ORDER/LIMIT and keyset pagination, safe UPDATE/DELETE, upsert via ON CONFLICT, PG18 RETURNING old/new, and sober NULL semantics (the NOT IN + NULL trap, COALESCE/NULLIF/IS DISTINCT FROM).

    not startedin progresscomplete0/6
    6 lessons2 h 15 min
  5. 05

    Querying across tables

    Tying data together: joins (inner/left/right/full), self-joins, aggregation with GROUP BY/HAVING, DISTINCT ON (the latest order per customer), EXISTS vs IN subqueries, and CTEs with materialization. This is where data turns into answers to business questions.

    not startedin progresscomplete0/6
    6 lessons2 h 25 min
  6. 06

    Transactions

    How Postgres behaves under concurrency: ACID and transactions, an MVCC mental model (visible xmin/xmax), row locks and lost updates, isolation levels in practice, retries on 40001, and deadlocks with advisory locks.

    not startedin progresscomplete0/6
    6 lessons2 h 25 min
  7. 07

    Indexing and EXPLAIN

    Performance through reading plans: EXPLAIN ANALYZE with buffers (on by default in PG18), B-tree and column order (PG18 skip-scan), when indexes don't help (expression index), partial/covering/unique and Index-Only Scan, GIN for jsonb/arrays, CREATE INDEX CONCURRENTLY.

    not startedin progresscomplete0/6
    6 lessons2 h 20 min
  8. 08

    JSONB, arrays, and search

    Semi-structured data and in-database search: jsonb access and containment (-> ->> @> ?), when not to use jsonb, SQL/JSON path and building, arrays vs a junction table, full-text search (tsvector + GIN), and fuzzy search via pg_trgm — with an FTS/trgm/engine decision matrix.

    not startedin progresscomplete0/6
    6 lessons2 h 20 min
  9. 09

    Analytics

    Analytics right inside SQL: window functions (running total, ranking, top-N per group), lag/lead and frames (day-over-day, moving average), recursive CTEs (a category tree), LATERAL joins (top-3 per customer — the N+1 killer), and grouping sets/rollup/cube.

    not startedin progresscomplete0/6
    6 lessons2 h 25 min
  10. 10

    Writes

    Advanced writes and database-side logic: MERGE and COPY, a job queue via SKIP LOCKED, the transactional outbox, LISTEN/NOTIFY, triggers and function volatility (IMMUTABLE/STABLE/ VOLATILE) — with an explicit "when NOT to put logic in the database" section.

    not startedin progresscomplete0/5
    5 lessons2 h
  11. 11

    Use cases

    End-to-end capstones with integration tests that tie the whole course into working apps: building the Brew schema, a price-and-promo engine (PG18 temporal), an app anti-patterns clinic, pooling from the app (pgbouncer caveats), and the CDC seam — a byte-compatible handoff into kafka-cookbook.

    not startedin progresscomplete0/5
    5 lessons2 h 55 min