# Migration Patterns — Recipes ## Contents - Expand → migrate → contract (column rename) - Adding NOT NULL without a long lock (PostgreSQL) - Index creation without blocking writes - Batched backfill - Safe destructive migrations - Divergent heads - Gotchas ## Expand → migrate → contract (column rename) Renaming `users.phone` → `users.phone_number` across releases: ```sql -- Migration 1 (expand): release N ALTER TABLE users ADD COLUMN phone_number text; -- Release N code: write both columns, read phone_number -- COALESCE(phone_number, phone) during transition. -- Migration 2 (backfill): separate migration, batched (see below) -- Migration 3 (contract): release N+1, after all rows copied ALTER TABLE users DROP COLUMN phone; ``` Down paths: 1 → `DROP COLUMN phone_number`; 3 is irreversible — document it. ## Adding NOT NULL without a long lock (PostgreSQL) A plain `ALTER TABLE ... SET NOT NULL` scans the whole table under an ACCESS EXCLUSIVE lock. Split it: ```sql -- Step 1: instant, does not validate existing rows ALTER TABLE orders ADD CONSTRAINT orders_customer_id_nn CHECK (customer_id IS NOT NULL) NOT VALID; -- Step 2: scans without blocking writes (SHARE UPDATE EXCLUSIVE) ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_nn; -- Step 3 (PG 12+): SET NOT NULL is instant because the valid CHECK proves it ALTER TABLE orders ALTER COLUMN customer_id SET NOT NULL; ALTER TABLE orders DROP CONSTRAINT orders_customer_id_nn; ``` ## Index creation without blocking writes ```sql -- PostgreSQL: must run OUTSIDE a transaction block CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id); ``` - Alembic: `op.create_index(..., postgresql_concurrently=True)` with `with op.get_context().autocommit_block():` - If it fails it leaves an INVALID index — drop it and retry: `DROP INDEX CONCURRENTLY idx_orders_customer_id;` - MySQL (InnoDB): plain `CREATE INDEX` is already online (`ALGORITHM=INPLACE`). ## Batched backfill Never one giant UPDATE. Batch by primary key with a pause per batch: ```sql -- Repeat until 0 rows affected (driver loop or DO block): UPDATE users SET phone_number = phone WHERE id IN ( SELECT id FROM users WHERE phone_number IS NULL AND phone IS NOT NULL ORDER BY id LIMIT 5000 -- 5000 keeps each transaction < ~1s on typical rows ); -- sleep 100ms between batches to let replication and vacuum breathe ``` Run as a repeatable script or data migration — separate from schema migrations. ## Safe destructive migrations Dropping a table/column: 1. Confirm zero references: grep the codebase AND check `pg_stat_user_tables.seq_scan/idx_scan` deltas over a week if possible. 2. Rename first, drop later (rename is instantly reversible): ```sql -- Release N: soft-drop ALTER TABLE legacy_events RENAME TO legacy_events_dropped_20260805; -- Release N+2 (weeks later): real drop, after nothing broke DROP TABLE legacy_events_dropped_20260805; ``` ## Divergent heads Two branches each added a migration on the same parent: - Alembic: `alembic merge -m "merge heads" ` - Rails/Prisma/Flyway: re-timestamp the UNAPPLIED migration only (`prisma migrate dev` handles resolution; Flyway: bump the `V` number). - Never renumber a migration that has been applied to any shared environment. ## Gotchas - **PostgreSQL DDL is transactional; MySQL DDL is not.** A failed multi-statement MySQL migration leaves partial state — write idempotent statements (`ADD COLUMN IF NOT EXISTS`) so re-running converges. - **`CREATE INDEX CONCURRENTLY` inside a transaction fails** — migration tools wrap migrations in transactions by default; use the tool's autocommit escape. - **Default values:** PG 11+ adds `ADD COLUMN ... DEFAULT ...` instantly (metadata-only) for constant defaults; volatile defaults (`now()`, `uuid()`) still rewrite the table — add the column first, set the default after. - **Down migrations that drop data are lies** — a `down` for `DROP COLUMN` restores the column, not its contents. Say so in the migration header. - **Foreign keys on busy tables:** add as `NOT VALID`, then `VALIDATE CONSTRAINT` separately (same trick as NOT NULL). - **Renaming with views/triggers attached** — PG updates them automatically; MySQL does not for triggers referencing the old name. Check `information_schema`.