Schema Migrations

The bug report says production is missing an index that staging has had for a month. Investigation shows staging's table was altered by hand during a demo, production's was altered from a three-month-old script, and two developers' local databases have a column nobody remembers adding. The schema is not a thing the team has — four environments each have their own, and nobody can say which is current.

The fix is treating schema the way code is treated: versioned, ordered, and applied from one place. A migration is a file containing one change; a migration history is a sequence; each database records which migrations it has applied, and "current schema" means "the result of running migrations 1 through N in order."

-- migrations/000012_add_orders_customer_index.sql
-- +up
CREATE INDEX CONCURRENTLY idx_orders_customer_created
    ON orders (customer_id, created_at DESC);

-- +down
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_customer_created;

The numbering is the contract: monotonically increasing, no gaps reused, applied in order. Tools like golang-migrate or goose keep a bookkeeping table — conventionally schema_migrations — holding the highest applied version, so "which migrations does this database need" is arithmetic, not archaeology:

applied: 1..11        files: 000001..000013     pending: 12, 13

What this buys is measurable. Fresh environment: run all pending, done — identical to production's history. Deploy: apply pending before (or as part of) releasing code. New developer: migrate up and the schema exists. The manual ALTER TABLE in a runbook — the thing that caused the drift — stops being a valid way to change anything, because the next migrate up either reproduces it deliberately or the checklist catches that it is missing.

Three disciplines separate migrations from SQL notes. One change per file, immutable once applied — editing 000004 after it ran in production means two databases disagree while both claim version 4; the mistake needs a new 000014 that undoes and redoes. Migration SQL lives in the repository next to the code that depends on it, so a schema change and the query that requires it arrive in the same pull request (repository boundary). Every migration ships with its inverse for local and CI use — production rarely rolls back (the forward-fix habit), but developers revert constantly and need migrate down to work.

The mechanics of running them — where, when, and how many at once — are where teams still break production, and they deserve their own attention.

The service reads migrate.Up() at startup. The deploy rolls out five replicas; all five see pending migrations and race: two run 000012, one crashes halfway through 000013, and the pod that applied 000012 first then dies while another waits on a lock. The schema may end correct, or the deploy may end in CrashLoopBackOff — the design gives both outcomes.

Migration application needs an authority: exactly one actor, at a defined point. The two established shapes:

Option A (preferred):  CI/CD runs `migrate up` as a deploy step, BEFORE new code serves traffic
Option B:              app runs migrations at startup, guarded by an advisory lock

Option A keeps migration failures where they belong — the pipeline — and never lets a half-migrated schema meet new code. It also makes rollback a deploy decision, not a mystery. Option B exists for platforms where controlling the pipeline is impractical; it must then be correct:

func ensureSchema(ctx context.Context, db *sql.DB) error {
	// Postgres advisory lock: one process migrates, others wait then see "done"
	conn, err := db.Conn(ctx)
	if err != nil {
		return fmt.Errorf("acquire conn: %w", err)
	}
	defer conn.Close() // return the session; the lock dies with it

	var got bool
	err = conn.QueryRowContext(ctx, `SELECT pg_try_advisory_lock($1)`, migrateLockID).Scan(&got)
	if err != nil {
		return fmt.Errorf("lock: %w", err)
	}
	if !got {
		// someone else is migrating; wait for them, then verify applied == expected
		return waitForCurrent(ctx, conn)
	}
	defer conn.ExecContext(context.WithoutCancel(ctx), `SELECT pg_advisory_unlock($1)`, migrateLockID)

	return runPending(ctx, conn) // apply files > schema_migrations version, record each
}

The db.Conn detail matters: advisory locks are session scoped, so the lock and the migration must run on the same connection — checking out a random connection for the lock and running migrations through the pool would hold nothing. Note also what the lock does not give you: it prevents concurrent application, not stale application. New code arriving before old schema is a sequencing problem, and only Option A's ordering solves it fully.

Where the files come from: embed them in the binary with //go:embed migrations/*.sql, so the artifact that ships is self-describing and a migration can never be missing from a release. The runner reads the FS, compares versions against schema_migrations, applies pending ones in order, and stops on the first failure — never "best effort" partial application. Record each applied file in the same transaction as its DDL where the statement allows it, so "applied" and "effect" cannot disagree.

Whatever runner you choose — hand-rolled, goose, golang-migrate — the properties to verify are the same: single authority, ordered application, recorded versions, failure stops the line. The last open question is what the migration files should contain when the change itself is dangerous at scale.

The migration reads innocently: ALTER TABLE orders ADD COLUMN customer_id BIGINT NOT NULL. On a table with 200 million rows, this rewrites (or at minimum exclusively locks) the table for the duration — minutes during which every order write blocks. The deploy window assumed seconds. Traffic backs up behind the lock, health checks fail, and the rollback button cannot help: the ALTER is still running.

The pattern that survives rolling deploys splits any risky change into three phases that each leave the system runnable:

EXPAND     add nullable column / new table
           old code runs untouched; new code writes both (dual-write)

MIGRATE    backfill old rows in small batches, during off-peak
           UPDATE orders SET customer_id = ... WHERE id BETWEEN $1 AND $2
           (loop with sleeps; each statement is short, locks are brief)

CONTRACT   add NOT NULL / constraint, switch reads fully,
           stop dual-writing, drop the old column — in a LATER deploy

The property that matters: at every point, old and new code both work. A rolling deploy runs them side by side for minutes, so no migration may assume the fleet switched atomically. Adding a column is expand-only and safe; adding it NOT NULL with an instant default (Postgres 11+ handles fixed defaults without rewrite) is often safe too — the dangerous moves are backfills, rewrites, renames, and constraint validation on live data, each of which belongs in its own phase.

Some statements cannot be jailed in a transaction at all:

-- runs outside any transaction block — do NOT wrap it yourself
CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);

CONCURRENTLY builds the index without blocking writes by accepting the cost of doing it transaction-lessly — if it fails halfway it leaves an invalid index that must be dropped and retried. Migration tools handle this with per-file directives (goose's -- +no-transaction, golang-migrate's splitting), which is a concrete reason not to hand-roll db.Exec(string(fileContents)) around a wrapping transaction: your wrapper turns a supported statement into a runtime error.

Verification closes the loop. CI builds a database from zero — migrate up to head, run the test suite against it, optionally migrate down one step and back up — proving fresh installs work, not just incremental ones. Production's next migration then runs on a schema CI has already reproduced. The test costs a pipeline minute and deletes the entire class of "works on the databases we migrated incrementally." Schema changes now arrive with the same evidence code does — which is the whole point of versioning them next to it (pool implications of long DDL are the remaining consideration on big tables).