db-migration-safety

name	db-migration-safety
description	Reviews and writes database migrations for safety — lock contention, blocking DDL on large tables, data-loss/destructive operations, missing indexes, and rollback plans. Use before running any schema change against a real or production database.
when_to_use	เขียน/รัน migration; เปลี่ยน schema; เพิ่ม/ลบ column/index/constraint บนตารางจริง

When to Use

Trigger this skill before writing or running ANY of these against a real/production DB:

• New migration file, or editing an existing one
• ALTER TABLE, CREATE INDEX, DROP, ADD CONSTRAINT, type changes, SET NOT NULL
• Backfilling / updating data across a large table (≥ ~100k rows, or unknown size)
• Renaming a column/table, or changing a column's type
• Any change reviewed by another engineer touching schema

Skip only for: brand-new empty tables, dev-only throwaway DBs, or pure read/query work with no DDL.

Steps

1. Classify every statement as additive (safe), lock-risky, or destructive:

   • additive: ADD COLUMN (nullable, no default OR constant default on PG11+), CREATE INDEX CONCURRENTLY, new table, add nullable FK without validation
   • lock-risky: CREATE INDEX (non-concurrent), ADD COLUMN ... NOT NULL with volatile default, ALTER COLUMN TYPE, ADD CONSTRAINT (validating), SET NOT NULL on existing column
   • destructive: DROP TABLE/COLUMN, TRUNCATE, type narrowing (e.g. bigint→int, text→varchar(n)), DROP CONSTRAINT relied on by app Print this classification before doing anything else.

2. Get the row count before judging lock risk: SELECT reltuples::bigint FROM pg_class WHERE relname = '<table>'; (fast estimate). Treat "unknown" as large. Lock duration scales with rows.

3. Kill blocking index builds — replace CREATE INDEX with CREATE INDEX CONCURRENTLY (Postgres) / ALGORITHM=INPLACE, LOCK=NONE (MySQL 8). CONCURRENTLY cannot run inside a transaction block, so it must be its own migration file with transaction wrapping disabled (e.g. disable_ddl_transaction! / no BEGIN).

4. Batch every backfill — never one UPDATE over the whole table (holds row locks + bloats WAL). Loop in chunks (1k–10k rows) keyed on PK, commit per batch, optional sleep between. Run the backfill as a SEPARATE step from the DDL that depends on it.

5. Apply expand–contract for rename / type change (3 deploys, never rename in place):

   • Expand: add the new column/type. Deploy app that writes BOTH old + new.
   • Migrate: backfill new column in batches; switch reads to new column.
   • Contract: drop the old column in a LATER migration, after the new code is fully deployed and stable.

6. Make NOT NULL safe — don't SET NOT NULL directly on a big table (full scan under lock). Instead: ADD CONSTRAINT ... CHECK (col IS NOT NULL) NOT VALID, then VALIDATE CONSTRAINT (lighter lock), then optionally promote.

7. Write the rollback — every migration needs a working down / inverse. If the op is irreversible (dropped column = lost data), say so explicitly and require an explicit confirmation + backup before running.

8. Dry-run on a copy — run the migration against a clone/staging snapshot of prod data (real volume), time it, and check it completes without long locks. Report measured duration.

Common Errors / Gotchas

• CREATE INDEX CONCURRENTLY inside a transaction → fails or silently degrades to a normal locking build. Each CONCURRENTLY index needs its own non-transactional migration. A failed CONCURRENTLY build leaves an INVALID index — drop it before retrying.
• ADD COLUMN ... DEFAULT <volatile> rewrites the whole table under an ACCESS EXCLUSIVE lock. Constant defaults are metadata-only on PG11+/MySQL8 and are safe; now(), gen_random_uuid(), sequences are NOT.
• ALTER TABLE takes ACCESS EXCLUSIVE which queues behind AND blocks all reads/writes — even a fast ALTER blocks the table while waiting for a slow query to finish (lock queue pile-up). Set a short lock_timeout (e.g. SET lock_timeout = '3s') and retry, so the migration fails fast instead of freezing the table.
• Adding a foreign key validates the whole referencing table under lock. Use ADD CONSTRAINT ... NOT VALID then VALIDATE CONSTRAINT in a second step.
• Type narrowing = silent data loss / errors (bigint→int overflows, varchar(n) truncates, timestamp→date drops time). Block these unless data is proven to fit and a backup exists.
• Dropping a column the running app still references → errors until the new deploy lands. Drop only AFTER the code that stopped using it is fully deployed (contract phase).
• Renaming a column in one shot breaks the currently-running app version during deploy. Always expand–contract.
• Long single-statement backfill holds locks + inflates replication lag; replicas fall behind and reads time out.

Verify

A migration is safe to run only when ALL hold:

• Every statement classified; no destructive op runs without explicit confirmation + a backup taken
• No non-concurrent index build on a non-trivial table; CONCURRENTLY indexes are in their own non-transactional file
• All backfills are batched with per-batch commits, separate from dependent DDL
• Rename/type-change uses expand–contract across separate deploys
• A working down/rollback exists, or irreversibility is flagged and confirmed
• lock_timeout set so the migration fails fast instead of hanging the table
• Dry-run on prod-sized copy completed; measured duration and lock impact reported

If you cannot verify lock duration on real data volume, do NOT run against production — escalate for a maintenance window or a tested staging run first.