Migration Mastery Guide

The 5-Minute Schema Review

Before every deploy, spend 5 minutes reviewing your schema diff. This simple habit prevents 90% of migration-related outages.

The Checklist

1. Destructive changes — Are any columns or tables being dropped? If so, confirm nothing depends on them (foreign keys, views, triggers, application code).
2. Constraint additions — Are you adding NOT NULL or UNIQUE to large tables? These can lock tables for minutes or hours.
3. Type changes — Is a column being narrowed (e.g., VARCHAR(255) → VARCHAR(100))? This can truncate existing data.
4. Missing indexes — Are new foreign keys created without backing indexes? Unindexed foreign keys cause full table scans on parent updates/deletes.
5. Default values — Are new columns added without defaults? Existing rows will have NULL values, which may break application logic.

Pro tip

Don't just review the migration file. Review the diff between the old schema and the new schema. Migration files can be misleading — they show what the developer intended to change, but a semantic diff shows what actually changed.

Zero-Downtime Migration Patterns

Not all schema changes require downtime. Understanding which changes are safe and which need special handling is the foundation of reliable deploys.

Safe changes (no special handling needed)

• Adding a new table
• Adding a new column with a default value (PostgreSQL 11+)
• Adding an index with CONCURRENTLY (PostgreSQL) or ALGORITHM=INPLACE, LOCK=NONE (MySQL 5.6+)
• Creating a new view or function

Unsafe changes (require caution)

• Dropping a column, table, or index
• Adding NOT NULL without a default
• Renaming a column or table
• Changing a column type
• Adding a unique constraint on a large table

The expand/contract pattern

For unsafe changes, use the expand/contract pattern:

1. Expand: Add the new structure while keeping the old one. Example: add email_address column, keep email.
2. Migrate: Backfill data and update application code to write to both columns.
3. Contract: After confirming everything works, drop the old column in a later deploy.

The 12 Changes That Break Production

After analyzing thousands of schema diffs, we've identified the changes that cause outages most often. Know these by heart.

1. Dropping a column — Breaks views, triggers, stored procedures, and application code that reference it.
2. Dropping a table — Cascades to foreign keys, views, and sometimes ORM models.
3. Adding NOT NULL without a default — Fails on existing rows or locks the table during validation.
4. Narrowing a column type — VARCHAR(255) → VARCHAR(50) silently truncates data in some databases.
5. Changing a column type — INT → BIGINT is safe; TEXT → VARCHAR may not be.
6. Renaming a column — Breaks every query, view, and procedure that references the old name.
7. Renaming a table — Breaks foreign keys and ORM mappings.
8. Dropping an index — Causes query performance regressions that are hard to detect in staging.
9. Dropping a foreign key — Removes referential integrity protection; orphaned rows accumulate.
10. Adding a unique constraint on existing data — Fails if duplicates exist; locks the table during validation.
11. Changing a primary key — Breaks foreign key relationships and often requires table recreation.
12. Removing a default value — Causes INSERT failures in applications that relied on the database default.

Rollback Strategies That Work

Every migration should have a rollback plan. But not all migrations can be rolled back cleanly.

Reversible changes

• Adding a column → drop the column
• Adding an index → drop the index
• Adding a table → drop the table
• Adding a default → remove the default

Irreversible changes

• Dropping a column (data is gone)
• Dropping a table (data is gone)
• Narrowing a column type (truncated data cannot be restored)
• Renaming a column or table (requires another rename to undo)

The rollback script rule

Before running any migration in production, write the rollback script first. If you can't write a rollback script, reconsider the migration. For irreversible changes, take a database backup or snapshot before running the migration.

CI/CD Schema Diff Integration

The best time to catch a bad migration is before it merges. Automating schema diff in your CI pipeline ensures every change is reviewed.

The GitHub Actions workflow

Add a schema diff step to your CI workflow that compares the schema on the PR branch against the schema on main:

1. Dump the schema from your main branch (pg_dump --schema-only or mysqldump --no-data)
2. Dump the schema from the PR branch
3. Run a diff tool to compare them
4. Post the diff as a PR comment so reviewers can see what changed

Pre-commit hooks

For teams that want faster feedback, a pre-commit hook can run a local schema diff before the developer even commits. This catches issues in seconds, not minutes.

Team Workflows at Scale

As teams grow, schema reviews become a bottleneck. The solution isn't to skip reviews — it's to make them faster and more consistent.

The schema change RFC

For large or risky changes, write a one-paragraph RFC that answers:

• What is changing and why?
• What is the rollback plan?
• Are there any breaking changes for consumers?
• What is the expected impact on query performance?

PR templates

Add a schema review section to your pull request template:

• [ ] I have reviewed the schema diff
• [ ] I have confirmed no breaking changes for downstream consumers
• [ ] I have tested the migration on a copy of production data
• [ ] I have written a rollback script

Ownership and alerts

Assign a "schema owner" for each service or domain. This person reviews all schema changes for that domain and is responsible for migration safety. When something goes wrong, they know the schema best.

Your Migration Safety Checklist

Print this checklist and use it before every deploy. It takes 2 minutes and prevents hours of incident response.

Pre-deploy checks

• [ ] Run the schema diff and review all changes
• [ ] Identify any destructive changes (drops, renames, type changes)
• [ ] Check for missing indexes on new foreign keys
• [ ] Verify new columns have appropriate defaults
• [ ] Confirm the migration will not lock large tables
• [ ] Test the migration on a copy of production data
• [ ] Write the rollback script
• [ ] Notify the team if the migration is risky

Post-deploy checks

• [ ] Monitor error rates for 30 minutes after deploy
• [ ] Check slow query logs for new performance issues
• [ ] Verify application health checks pass
• [ ] Confirm rollback script is ready if needed

What's next?

You now have the knowledge to ship schema changes safely. The next step is to make the process effortless. SchemaLens Pro automates the diff, risk scoring, and migration generation so you can focus on shipping.