spb/ultra-sharp-agent-skills Public

1<!--2Author: Simon-Pierre Boucher3Contact: contact@spboucher.ai4-->56# Migration Patterns — Recipes78## Contents9- Expand → migrate → contract (column rename)10- Adding NOT NULL without a long lock (PostgreSQL)11- Index creation without blocking writes12- Batched backfill13- Safe destructive migrations14- Divergent heads15- Gotchas1617## Expand → migrate → contract (column rename)1819Renaming `users.phone` → `users.phone_number` across releases:2021```sql22-- Migration 1 (expand): release N23ALTER TABLE users ADD COLUMN phone_number text;2425-- Release N code: write both columns, read phone_number26-- COALESCE(phone_number, phone) during transition.2728-- Migration 2 (backfill): separate migration, batched (see below)29-- Migration 3 (contract): release N+1, after all rows copied30ALTER TABLE users DROP COLUMN phone;31```3233Down paths: 1 → `DROP COLUMN phone_number`; 3 is irreversible — document it.3435## Adding NOT NULL without a long lock (PostgreSQL)3637A plain `ALTER TABLE ... SET NOT NULL` scans the whole table under an38ACCESS EXCLUSIVE lock. Split it:3940```sql41-- Step 1: instant, does not validate existing rows42ALTER TABLE orders ADD CONSTRAINT orders_customer_id_nn43  CHECK (customer_id IS NOT NULL) NOT VALID;4445-- Step 2: scans without blocking writes (SHARE UPDATE EXCLUSIVE)46ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_nn;4748-- Step 3 (PG 12+): SET NOT NULL is instant because the valid CHECK proves it49ALTER TABLE orders ALTER COLUMN customer_id SET NOT NULL;50ALTER TABLE orders DROP CONSTRAINT orders_customer_id_nn;51```5253## Index creation without blocking writes5455```sql56-- PostgreSQL: must run OUTSIDE a transaction block57CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id);58```5960- Alembic: `op.create_index(..., postgresql_concurrently=True)` with61  `with op.get_context().autocommit_block():`62- If it fails it leaves an INVALID index — drop it and retry:63  `DROP INDEX CONCURRENTLY idx_orders_customer_id;`64- MySQL (InnoDB): plain `CREATE INDEX` is already online (`ALGORITHM=INPLACE`).6566## Batched backfill6768Never one giant UPDATE. Batch by primary key with a pause per batch:6970```sql71-- Repeat until 0 rows affected (driver loop or DO block):72UPDATE users73SET phone_number = phone74WHERE id IN (75  SELECT id FROM users76  WHERE phone_number IS NULL AND phone IS NOT NULL77  ORDER BY id78  LIMIT 5000            -- 5000 keeps each transaction < ~1s on typical rows79);80-- sleep 100ms between batches to let replication and vacuum breathe81```8283Run as a repeatable script or data migration — separate from schema migrations.8485## Safe destructive migrations8687Dropping a table/column:88891. Confirm zero references: grep the codebase AND check90   `pg_stat_user_tables.seq_scan/idx_scan` deltas over a week if possible.912. Rename first, drop later (rename is instantly reversible):9293```sql94-- Release N: soft-drop95ALTER TABLE legacy_events RENAME TO legacy_events_dropped_20260805;96-- Release N+2 (weeks later): real drop, after nothing broke97DROP TABLE legacy_events_dropped_20260805;98```99100## Divergent heads101102Two branches each added a migration on the same parent:103104- Alembic: `alembic merge -m "merge heads" <rev1> <rev2>`105- Rails/Prisma/Flyway: re-timestamp the UNAPPLIED migration only106  (`prisma migrate dev` handles resolution; Flyway: bump the `V` number).107- Never renumber a migration that has been applied to any shared environment.108109## Gotchas110111- **PostgreSQL DDL is transactional; MySQL DDL is not.** A failed multi-statement112  MySQL migration leaves partial state — write idempotent statements113  (`ADD COLUMN IF NOT EXISTS`) so re-running converges.114- **`CREATE INDEX CONCURRENTLY` inside a transaction fails** — migration tools115  wrap migrations in transactions by default; use the tool's autocommit escape.116- **Default values:** PG 11+ adds `ADD COLUMN ... DEFAULT ...` instantly117  (metadata-only) for constant defaults; volatile defaults (`now()`, `uuid()`)118  still rewrite the table — add the column first, set the default after.119- **Down migrations that drop data are lies** — a `down` for `DROP COLUMN`120  restores the column, not its contents. Say so in the migration header.121- **Foreign keys on busy tables:** add as `NOT VALID`, then `VALIDATE CONSTRAINT`122  separately (same trick as NOT NULL).123- **Renaming with views/triggers attached** — PG updates them automatically;124  MySQL does not for triggers referencing the old name. Check `information_schema`.125