The Complete SQL Migration Checklist: 12 Steps Before Production

April 30, 2026 · 9 min read · SchemaLens Team

At 11:47 AM on a Thursday, a team at a fintech company deployed what looked like a harmless migration: adding a NOT NULL column to a 200-million-row table. The migration locked the table for 14 minutes. Payment processing ground to a halt. The rollback failed because the column already existed. They spent six hours in incident response, lost $40,000 in transaction fees, and earned a permanent spot in the company's post-mortem hall of fame.

This wasn't a complex migration. It was a simple one that skipped a checklist. In this guide, we give you the 12-step checklist that prevents these disasters. Print it. Bookmark it. Run it before every deployment.

Step 1: Diff your schemas visually

Before you even look at the migration SQL, understand what changed at a structural level. A text diff of two schema dumps is noisy and error-prone. A semantic diff tells you exactly which tables, columns, constraints, and indexes changed.

Tools like SchemaLens make this instant. Paste two schemas, get a color-coded visual diff, and generate the migration SQL — all in your browser.

Step 2: Score the migration risk

Not all migrations are equal. Some are zero-risk. Others are "page the CEO" territory. Classify every migration before it ships:

High-risk migrations need a second reviewer, a tested rollback plan, and deployment during a scheduled maintenance window. Never ship high-risk changes in a Friday deploy.

Step 3: Verify backward compatibility

Your application code and your database schema rarely deploy in perfect lockstep. During a rolling deployment, old code runs against the new schema, and new code runs against the old schema. Both combinations must work.

The golden rule: never break the version of the code that is currently running in production.

Step 4: Check for missing indexes

New foreign keys, new query patterns, and new join conditions often need indexes that the migration author forgot to add. A missing index on a foreign key in a 50-million-row table turns a 5ms query into a 45-second table scan.

Use the SQL Index Analyzer to audit your schema for missing indexes before deployment.

Step 5: Estimate lock time and table size

Some migrations are fast on development databases and catastrophically slow in production. The difference is data volume. Before deploying, estimate how long the migration will hold locks:

-- PostgreSQL: check table size
SELECT pg_size_pretty(pg_total_relation_size('users'));

-- MySQL: check table size and row count
SHOW TABLE STATUS LIKE 'users';

-- Estimate: adding a column with DEFAULT on 100M rows
-- PostgreSQL 11+: ~instant (metadata-only if not touched)
-- PostgreSQL <11: rewrite entire table, could be hours

If the estimated lock time exceeds your tolerance, restructure the migration:

Step 6: Write idempotent SQL

Migrations fail. When they do, you don't want to spend 30 minutes manually cleaning up a half-applied state. Idempotent migrations can be retried safely:

-- Instead of this:
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Do this:
ALTER TABLE users ADD COLUMN IF NOT EXISTS phone VARCHAR(20);

-- For indexes:
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_email ON users(email);

Not all dialects support IF NOT EXISTS for every operation. When they don't, wrap the migration in a transaction or use a migration framework (Flyway, Liquibase) that tracks applied migrations in a metadata table.

Step 7: Test on production-like data

A migration that takes 200ms on a 1,000-row development database might take 40 minutes on a 50-million-row production database. Test on realistic data:

If you can't restore production data for compliance reasons, generate realistic test data with the SQL Test Data Generator that matches your production row counts and cardinality.

Step 8: Have a tested rollback plan

Every migration is a one-way door unless you have a rollback plan. Before deploying, ask: "If this breaks production, how do I undo it in under 5 minutes?"

"Hope is not a rollback strategy. If you can't undo a migration in five minutes, don't ship it during business hours."

Step 9: Schedule during a low-traffic window

Even low-risk migrations can cause brief locks. Schedule them when traffic is minimal:

Step 10: Monitor during and after deployment

The deploy isn't done when the migration finishes. It's done when you've confirmed the system is healthy:

Step 11: Verify constraint integrity

New constraints can fail if existing data violates them. Always validate before adding:

-- Check for NULLs before adding NOT NULL
SELECT COUNT(*) FROM users WHERE phone IS NULL;

-- Check for duplicates before adding UNIQUE
SELECT phone, COUNT(*) FROM users GROUP BY phone HAVING COUNT(*) > 1;

-- Check for orphans before adding FOREIGN KEY
SELECT COUNT(*) FROM orders WHERE user_id NOT IN (SELECT id FROM users);

-- PostgreSQL: add constraint as NOT VALID, then validate
ALTER TABLE users ADD CONSTRAINT chk_phone_format
  CHECK (phone ~ '^\+?[0-9\- ]+$') NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT chk_phone_format;

The NOT VALID → VALIDATE pattern in PostgreSQL lets you add a constraint without locking the table for validation. The validation step scans the table, but doesn't block reads or writes.

Step 12: Update documentation and runbooks

The final step is the most commonly skipped — and the one that saves your future self. After every migration:

Future you — debugging a production issue at 2 AM — will thank present you for keeping the docs current.

The 12-step checklist (printable)

Make it automatic

Running this checklist manually for every migration is valuable but time-consuming. The teams that scale safely automate the checks:

✅ Run the checklist on your next migration

Compare your schemas, detect breaking changes, and generate migration SQL — all in one tool.

Open SchemaLens →

Related articles