Lesson 23 · PostgreSQL in Go

Migrations & the schema lifecycle

How a schema change ships — versioned, ordered, forward-only.

Your win: explain how schema changes are applied in this repo, and why forward-only migrations are a deliberate choice.

Migrations = versioned schema changes

You don't hand-run ALTER TABLE in production. Instead, each change is a numbered SQL file checked into the repo; a tool applies pending ones in order and records what's been applied. Ours uses golang-migrate (v4, pgx driver).¹

migrations/notificationmgmt/1004_migrate.up.sql

CREATE TABLE emails ( email_id text, content jsonb, ...,
    CONSTRAINT pk__emails PRIMARY KEY (email_id) );
-- later files add indexes, columns, policies…
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx__... ON email_recipients (...);  -- 1026

⚠️ Forward-only in this repo There are no .down.sql files anywhere — only <NNNN>_migrate.up.sql, and the runner calls only m.Up(). A mistake is corrected by a new forward migration, not a rollback. The applied version is tracked in a schema_migrations table, so each file runs exactly once, in order.

Anchor Migrations live per database: migrations/notificationmgmt/, migrations/tom/, migrations/bob/ (recall your services don't own their DBs — Lesson 1). Big indexes use CREATE INDEX CONCURRENTLY to avoid locking writes (Lesson 13). Run locally with ./local/run.bash start -m migration -s <service>. A single migration can create a table and its RLS policies (Lesson 24) together.

Interview note "No down migrations?" — that's an increasingly common production stance: down migrations are often wrong or dangerous under real data, so many teams go forward-only and roll forward to fix. Know both the trade-off and that CONCURRENTLY can't run inside a transaction (so such a migration isn't atomic).

golang-migrate + Postgres DDL

The migration tool's model (versions, up/down, drivers), and the official ALTER TABLE/DDL reference for what goes in a migration.

→ github.com/golang-migrate/migrate
→ postgresql.org/docs — Modifying Tables

Check yourself (from memory)

Q1. In this repo, migrations are…

Only *.up.sql; the runner calls m.Up(). Fixes are new forward migrations.

Q2. A migration tool tracks applied versions in…

A bookkeeping table records the current version, so each file runs once, in order.

Q3. Big indexes are added in migrations with…

CONCURRENTLY builds without locking writes on a live table (slower, and can't run in a transaction).

How do schema changes ship in this repo?

recall, then click to reveal

Via golang-migrate (v4, pgx driver): each change is a versioned migrations/<db>/<NNNN>_migrate.up.sql. This repo is FORWARD-ONLY — no .down.sql; the runner calls only m.Up() and records the version in a schema_migrations table (mistakes are fixed by a new forward migration). A migration holds CREATE TABLE (with constraints + RLS), CREATE INDEX CONCURRENTLY IF NOT EXISTS (no write lock; can't be transactional), and ALTERs. Run locally: ./local/run.bash start -m migration -s <service>.

Want to write a safe migration for a new column with a default on a huge table (the lock-avoidance dance)? Ask me.

1. golang-migrate; repo: repo-postgres-map.md.