Back to Cookbook

Migration Guardian

Catch the ALTER TABLE that would lock your production database for 45 minutes

Reviews database migration scripts for dangerous operations — long-running locks, data loss risks, and backward-incompatible changes. Suggests safe alternatives using online schema change tools.

House RecipeWork2 min

INGREDIENTS

🐙GitHub

PROMPT

Create a skill called "Migration Guardian". Review database migration scripts for safety: 1. Parse migration files (SQL, Flyway, Liquibase, Alembic, Rails, Prisma, etc.) 2. Flag dangerous operations: - ALTER TABLE that acquires long-running locks (adding columns with defaults on old MySQL, adding indexes without CONCURRENTLY on Postgres) - DROP TABLE or DROP COLUMN (data loss) - Large data backfills - Changes that break backward compatibility with the current application version 3. For each dangerous operation, suggest a safe alternative: - Use CREATE INDEX CONCURRENTLY instead of CREATE INDEX - Use online schema change tools (gh-ost, pt-osc) for large MySQL tables - Use expand-contract pattern for breaking changes 4. Estimate lock duration if table size info is available 5. Generate a rollback script for each migration 6. Verify migration ordering and dependency chain

How It Works

A seemingly innocent `ALTER TABLE ADD COLUMN WITH DEFAULT` can lock a large

table for minutes or hours. This skill reviews migration scripts and flags

operations that could cause downtime, suggesting safe alternatives.

What You Get

  • Migration script review for lock-acquiring operations
  • Estimated lock duration based on table size (if available)
  • Safe alternatives for dangerous operations (online schema change, pt-osc, gh-ost)
  • Backward-compatibility check (will this break the current running application?)
  • Rollback script generation
  • Migration ordering and dependency analysis

Setup Steps

  1. Paste your migration SQL or point your Claw at migration files
  2. Optionally provide table sizes for lock duration estimation
  3. Review the flagged operations and alternatives
  4. Apply the safe migration strategy

Tips

  • Adding a column with a default value is safe in Postgres 11+ but dangerous in older versions and MySQL
  • Always test migrations against a production-sized dataset, not your tiny dev database
  • Consider using expand-contract pattern for backward-compatible changes
  • Generate a rollback script for every migration before applying
  • Run during low-traffic windows when possible, even with online migration tools
Tags:#database#migrations#safety#devops