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;