database-migrations

Database Migrations

Every schema change is a migration file, never a manual psql session against production. Migrations are forward-only once deployed — a mistake is fixed with a new migration, not by editing or deleting the one that ran.

Core rules

1. Schema changes (DDL) and data backfills (DML) are separate migrations — mixing them makes a migration slow, lock-prone, and hard to roll back independently.
2. NOT NULL on a new column requires a default, or a nullable column followed by a backfill and a later constraint migration — adding NOT NULL with no default to an existing table locks it for a full rewrite.
3. Indexes on existing tables use CREATE INDEX CONCURRENTLY — a plain CREATE INDEX blocks writes for the duration of the build.
4. Test migrations against a production-sized copy, not a 100-row dev database — lock duration and rewrite cost scale with table size, and a migration that's instant at 100 rows can lock for minutes at 10M.

Zero-downtime pattern (expand → migrate → contract)

EXPAND:   add new column/table (nullable or defaulted); app writes to BOTH old and new
MIGRATE:  backfill; app reads from NEW, still writes to BOTH; verify consistency
CONTRACT: app uses NEW only; drop the OLD column/table in a separate, later migration

Renaming a column in production always goes through this pattern — never a direct ALTER TABLE ... RENAME COLUMN on a table any running application code still references by the old name.

Go (golang-migrate) — this org's default

migrate create -ext sql -dir migrations -seq add_reservation_notes
migrate -path migrations -database "$DATABASE_URL" up
migrate -path migrations -database "$DATABASE_URL" down 1

-- 000004_add_reservation_notes.up.sql
ALTER TABLE reservations ADD COLUMN notes TEXT;
CREATE INDEX CONCURRENTLY idx_reservations_notes ON reservations (notes) WHERE notes IS NOT NULL;

-- 000004_add_reservation_notes.down.sql
DROP INDEX IF EXISTS idx_reservations_notes;
ALTER TABLE reservations DROP COLUMN IF EXISTS notes;