PostgreSQL Migrations

Overview

Migrations are append-only history that must replay identically on every environment, forever. The cardinal sin is editing a migration that has already run somewhere — it makes environments diverge silently. The second most common mistake is DDL that locks a live table for longer than the app can tolerate.

Core principle: a wrong migration is fixed by a NEW migration, never by editing the old one; and rollback_release reverts code only, so the schema your migration leaves behind must keep working with the previous release's code until a later task removes what it replaced.

Detect the repo's migration tool first

Tool	How to recognise it	Down migration	Outside a transaction
golang-migrate	*.up.sql / *.down.sql pairs	its own .down.sql file	each CONCURRENTLY statement needs its own migration file (no implicit transaction wrapping across files)
goose	-- +goose Up / -- +goose Down in one file	same file, Down section	-- +goose NO TRANSACTION directive
Flyway	V<n>__name.sql	none in the open-source edition	follow Flyway's own non-transactional DDL rules
Liquibase	changesets (XML/YAML/SQL)	rollback block	per changeset runInTransaction
Atlas	atlas.hcl + versioned migrations	—	—
Up-only numbered SQL	plain numbered .sql files, no down files (e.g. this repo's own server/migrations/)	none exists — don't invent one	—

Never introduce a different migration tool into a repo that already has one, and never add down files to a repo that has none — follow what's there (rule repo-conventions-win).

Rules

• Never edit an applied migration. History replays; a divergent edit corrupts environments that already ran it. A wrong migration gets a new one that corrects it.
• Never rely on Hibernate/JPA auto-DDL (hbm2ddl=update, Quarkus schema generation) outside tests — schema comes from migrations only (java-persistence).
• Idempotent where it matters: IF NOT EXISTS, ON CONFLICT, WHERE guards on backfills so a re-run does no harm.
• Add an explicit index for every new query path the code introduces — Postgres does not auto-index foreign-key columns.
• Start DDL in a live-table migration with SET lock_timeout = '5s'; so a blocked ALTER fails fast and visibly instead of queueing behind other locks and blocking every other statement on the table.

Rollback reality: what rollback_release actually does

rollback_release reverts the application code only — it never runs a down migration. Two consequences:

1. Expand/contract. Ship additive changes first (new nullable/defaulted column, new table, dual-write) so the schema works with both the old and new code; drop what's no longer needed in a later task once the old code is gone everywhere.
2. rollback_plan must say whether the down migration is actually safe to run, because by the time anyone reaches for it, the new schema may already hold data the down migration would silently drop. Write what you'd tell the human, e.g. "Migration 045 is additive; reverting the code is enough. Do NOT run its down migration — it drops the priority column and any values written to it since deploy." A migration that is purely additive needs no down-migration caveat at all; say that instead.
3. golang-migrate caveat: if a commit is reverted and that removes migration 045's files, a database already at version 45 then fails migrate up with "no migration found for version 45" — decide whether to keep the schema (most common) and run migrate ... force 44 to re-sync the tool's bookkeeping, rather than trying to re-apply a file that no longer exists.

Lock-safety: unsafe vs safe DDL on a live table

Change	Unsafe (locks / rewrites the table)	Safe
Add index	CREATE INDEX	CREATE INDEX CONCURRENTLY IF NOT EXISTS (its own migration, outside a transaction)
Foreign key	ADD CONSTRAINT ... FOREIGN KEY ... (validates existing rows under lock)	... NOT VALID, then VALIDATE CONSTRAINT in a later migration
NOT NULL on an existing column	ALTER COLUMN ... SET NOT NULL (full scan under lock on PG <12)	CHECK (col IS NOT NULL) NOT VALID → VALIDATE CONSTRAINT → SET NOT NULL (PG ≥12 skips the re-scan once validated); PG 18 also has SET NOT NULL ... NOT VALID directly
Add column	A volatile default (DEFAULT now(), DEFAULT random()) rewrites the table	A constant default is metadata-only since PG 11; for a volatile default, add the column nullable, backfill, then add the default/constraint
Change column type	ALTER COLUMN ... TYPE ... (table rewrite)	New column + backfill + switch reads/writes, drop the old one later
Rename or drop column/table	RENAME breaks the previous release's code immediately	Expand/contract: add the new name, dual-write/read, switch, drop later
Backfill existing rows	A single large UPDATE (long lock, huge transaction)	Batched UPDATE ... WHERE id IN (SELECT id FROM t WHERE ... LIMIT 5000), looped, outside the DDL transaction

Types and keys (new tables; existing tables follow the repo's own conventions)

timestamptz (never bare timestamp); text with a CHECK constraint instead of varchar(n); bigint generated always as identity or uuid DEFAULT uuidv7() (PG 18+, time-ordered) / gen_random_uuid(); numeric for money, never float/money; UNIQUE NULLS NOT DISTINCT where NULL should count toward the uniqueness check (PG 15+).

Worked Example

Adding a required priority column with an index, on golang-migrate:

-- 043_task_priority.up.sql
ALTER TABLE tasks ADD COLUMN priority TEXT NOT NULL DEFAULT 'medium';

-- 043_task_priority.down.sql
ALTER TABLE tasks DROP COLUMN IF EXISTS priority;

-- 044_task_priority_index.up.sql
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_tasks_priority ON tasks (priority);

-- 044_task_priority_index.down.sql
DROP INDEX CONCURRENTLY IF EXISTS idx_tasks_priority;

NOT NULL ... DEFAULT 'medium' is a constant default: metadata-only on PG 11+, no table rewrite, every existing row reads as 'medium'. The index is a separate migration because CREATE INDEX CONCURRENTLY cannot run inside the transaction golang-migrate wraps each file in. rollback_plan: "045/044 are additive; reverting the code is enough, do not run the down migrations unless the column is confirmed unused."

❌ CREATE INDEX idx_tasks_priority ON tasks (priority); inside the same migration as the column add — locks the table for the full index build, and squawk's require-concurrent-index-creation flags exactly this.

Verify

• Lint: npx --yes squawk-cli <new-file>.sql (or pip install squawk-cli) — fix or explicitly justify every warning it raises (rule IDs like require-concurrent-index-creation, constraint-missing-not-valid, adding-required-field, changing-column-type, renaming-column, ban-drop-column, prefer-identity, prefer-text-field, prefer-timestamptz, require-timeout-settings double as a checklist even without running the tool).
• Apply up → down → up (or the repo's own migration test) against a disposable postgres:18 container before calling it done.
• psql -c '\d+ <table>' to confirm the resulting schema matches what you intended.

Common Mistakes

• Editing an already-applied migration to "fix" it.
• CREATE INDEX (not CONCURRENTLY) on a table already receiving traffic.
• A new query path with no supporting index.
• A non-idempotent backfill that double-applies on re-run, or one large UPDATE instead of batches.
• rollback_plan left blank on a migration task, or claiming the down migration is safe without checking whether data was written since deploy.
• hbm2ddl/auto-DDL thinking — schema comes from migrations only.

Red Flags

• The diff modifies an existing migration file instead of adding a new one.
• A NOT NULL or type-change migration with no expand/contract story and no mention in rollback_plan.
• A column or table rename with no plan for the previous release's code still running against it.
• squawk warnings left unaddressed with no comment explaining why.