Write Safe Migrations

Write Safe Migrations

Quick Start

Default approach: expand → migrate → contract.

Goal: reduce lock time, keep rollback easy, and prove correctness with checks.

When to use this skill

• any production schema change (especially large tables)
• adding columns/indexes/constraints, renames, backfills, or data moves
• when rollback must be possible without restoring from backup

Before you start (inputs)

• database engine + version
• migration goal (what changes, why)
• table size + write rate + peak hours
• lock tolerance (seconds) and downtime tolerance
• app deploy constraints (can you do multiple deploys?)

Common safe patterns

add column (nullable) + backfill + enforce

1. add new nullable column
2. deploy app writing both old + new (or only new with fallback)
3. backfill in batches
4. add constraint / set not null (engine-specific)
5. remove old column in a later deploy

add index safely

• postgres: create index concurrently
• mysql: use online ddl when supported; validate it is online for your engine/version

add foreign key / check constraint safely

• validate data first (no orphans, no invalid values)
• add constraint in a way that avoids long blocking where possible
• validate separately if engine supports it

Workflow (default)

1. design
   • write the migration plan and rollback plan
2. prechecks
   • confirm table sizes, existing indexes, constraint violations
3. dry run
   • run on staging with production-like data volume if possible
4. execute
   • apply schema change, then data movement in batches
5. verify
   • run correctness queries and app-level smoke checks
6. monitor
   • lock waits, replication lag, error rates
7. contract
   • remove old paths only after adoption is complete

PostgreSQL templates

add column:

ALTER TABLE public.your_table
ADD COLUMN new_col text;

backfill in batches (example using id ranges):

UPDATE public.your_table
SET new_col = <expression>
WHERE id >= :min_id AND id < :max_id
  AND new_col IS NULL;

add index safely:

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_your_table_new_col
ON public.your_table (new_col);

set not null (do this only after backfill + verification):

ALTER TABLE public.your_table
ALTER COLUMN new_col SET NOT NULL;

MySQL templates

add column:

ALTER TABLE your_table
ADD COLUMN new_col VARCHAR(255) NULL;

backfill in batches (example):

UPDATE your_table
SET new_col = <expression>
WHERE id BETWEEN ? AND ?
  AND new_col IS NULL;

add index:

CREATE INDEX idx_your_table_new_col ON your_table (new_col);

Verification queries (examples)

null rate after backfill:

SELECT COUNT(*) AS null_count
FROM your_table
WHERE new_col IS NULL;

row count consistency (when moving data):

SELECT COUNT(*) FROM old_table;
SELECT COUNT(*) FROM new_table;

fk integrity (orphan check):

SELECT COUNT(*) AS orphan_count
FROM child c
LEFT JOIN parent p ON p.id = c.parent_id
WHERE c.parent_id IS NOT NULL AND p.id IS NULL;

Rollback rules of thumb

• prefer additive changes first (new columns/tables/indexes) to keep rollback easy
• avoid destructive changes in the same deploy (drop/rename) unless you can restore quickly
• keep old read path working until you prove new path is correct

Output format (runbook)

## migration: [name]

### objective

- what changes:
- why:

### risks

- locks:
- long running work:
- replication lag:

### plan

- [ ] step 1:
- [ ] step 2:
- [ ] step 3:

### verification

- query checks:
- app checks:

### rollback

- exact rollback steps: