database-migrationslisted
Install: claude install-skill Mixard/fable-pack
# Database Migrations
## PostgreSQL Facts
- `CREATE INDEX CONCURRENTLY` cannot run inside a transaction block. Most migration tools wrap each migration in a transaction by default, so this needs per-migration opt-out (or a hand-written migration file, see Prisma below).
- Postgres 11+: `ADD COLUMN ... NOT NULL DEFAULT x` is instant (default stored in catalog, no table rewrite). Before 11 it rewrote the whole table. `NOT NULL` without a default on an existing table still fails/rewrites -- add nullable, backfill, then `SET NOT NULL`.
- Plain `CREATE INDEX` blocks writes for the duration of the build; use `CONCURRENTLY` on live tables.
### Expand-Contract (Zero-Downtime Rename)
Never rename a column in place on a live system:
1. Add new column (nullable or with default).
2. Deploy app writing to both columns.
3. Backfill old rows (separate data migration).
4. Deploy app reading from new column only.
5. Drop old column in a later migration.
Same order for dropping a column: remove all application references and deploy first, drop the column in the next migration.
### Batched Backfill
```sql
DO $$
DECLARE
batch_size INT := 10000;
rows_updated INT;
BEGIN
LOOP
UPDATE users
SET normalized_email = LOWER(email)
WHERE id IN (
SELECT id FROM users
WHERE normalized_email IS NULL
LIMIT batch_size
FOR UPDATE SKIP LOCKED
);
GET DIAGNOSTICS rows_updated = ROW_COUNT;
EXIT WHEN rows_updated = 0;
COMMIT;
END LOOP;
END $$;
```
K