Database Migration Checklist
A comprehensive, stack-agnostic checklist to safely plan, review, execute, backfill, and recover from production database schema and data migrations.
1. Migration Scope & Impact Assessment
Understand the target table size, expected row counts, and application dependencies.
2. Strict Backward Compatibility & Expand-Contract
Ensure old and new application versions work seamlessly during zero-downtime deployments.
3. Table Locks, Availability & Timeout Semantics
Control lock acquisition times and statement timeouts to prevent production query blocking.
4. Data Integrity, Constraints & Dirty Data
Validate existing records before applying constraints to prevent lockups or failed migrations.
5. Asynchronous Data Migration & Chunked Backfills
Execute large data transformations safely without replication lag or CPU spikes.
6. Index Strategy, Query Planner & Build Method
Build indexes online without blocking write traffic or overwhelming buffer pool memory.
7. Rollback & Irreversible Recovery Strategy
Distinguish between safe reversible DDL and destructive irreversible operations.
8. Realistic Staging & Volume Testing
Test migration lock durations and performance against production-scale datasets.
9. Deployment & Execution Ordering
Establish exact deployment sequence, responsible leads, and stop conditions.
10. Post-Migration Live Verification
Verify schema state, row counts, query latency, and database replication health.
11. Multi-Stage Cleanup & Artifact Removal
Decommission legacy columns, dual-write shims, and temporary backfill code in future releases.
Dangerous Database Operations & Mitigation Strategies
Certain DDL statements trigger exclusive table locks or full table rewrites that can bring down production services. Use safe multi-phase alternatives:
1. Dropping or Renaming Columns in Active Use
Executing ALTER TABLE DROP COLUMN or RENAME COLUMN while older application instances are running causes immediate 500 query errors. Use the Expand-Contract pattern instead.
2. Adding NOT NULL Constraints to Populated Tables
Adding NOT NULL requires scanning every row in the table, acquiring an exclusive table lock. On Postgres, use CHECK (col IS NOT NULL) NOT VALID and validate asynchronously.
3. Synchronous Index Creation on Large Tables
Standard CREATE INDEX blocks all write queries on the table for the entire duration of the build. Always use non-blocking builds: CONCURRENTLY (Postgres) or ONLINE=ON (SQL Server).
4. In-Place Column Data Type Alterations
Executing ALTER COLUMN TYPE rewrites the entire physical table file on disk. Instead, create a new column with the target type, dual-write in application code, backfill data, and switch reads.
The 4-Phase Expand-Contract Migration Lifecycle
To achieve zero-downtime database changes, decouple schema DDL deployments from application code releases across four distinct phases:
Add new optional/nullable columns or tables. Old application servers continue reading and writing to old schema without error.
Deploy application code that writes new incoming data to both old and new columns, while continuing to read from old schema.
Run batched background scripts to populate historical records into new columns in small primary-key ranges (500–2,000 rows).
Switch application reads to new column. In a separate follow-up deployment, drop the old legacy column.
Engine-Specific Table Locking Pitfalls
Database engines handle DDL locking and online schema changes differently. Always configure lock timeouts for your specific engine:
Postgres supports DDL inside transactions. Always configure SET lock_timeout = '5s'; before DDL so blocked queries do not stack up behind lock requests.
MySQL DDL statements auto-commit implicit transactions. Use ALGORITHM=INPLACE, LOCK=NONE or tools like gh-ost / pt-online-schema-change for large tables.
Use ONLINE = ON for non-blocking index creation and rebuilds. Monitor tempdb allocation and transaction log size during online index operations.
When to Use This Checklist
- Before running DDL schema migrations or DML data transformations on production databases.
- When adding, altering, or dropping columns, tables, foreign keys, or unique constraints.
- During asynchronous data backfills, table refactoring, or zero-downtime expand-contract deployments.
- To evaluate exclusive table locks, statement timeouts, and point-in-time recovery readiness.
Common Pitfalls to Avoid
- Adding NOT NULL constraints or non-constant DEFAULT values on populated tables, causing extended exclusive table locks.
- Creating indexes synchronously without CONCURRENTLY (Postgres) or ONLINE=ON (SQL Server), blocking all write traffic.
- Dropping or renaming active columns in the same release as application updates, breaking older running app instances.
- Executing un-chunked UPDATE scripts that exhaust transaction logs, cause high replication lag, or lock rows for minutes.
- Relying on automatic DOWN migrations for destructive DDL without verifying point-in-time backups.
Connected Workflows & Tools
Complementary prompts, agent skills, and interactive tools in SprintKit.
Production Deployment Checklist
Pre-flight checks and live telemetry verification for shipping services.
API Release Checklist
Ensure contract stability and zero breaking changes when releasing endpoints.
Code Review Checklist
Peer review guide covering security, performance, and SQL query inspection.
PR Review Queue
Workflow tool for tracking and prioritizing pull request reviews.