Database Migration Pipeline Blueprint

Database Migration Pipeline Blueprint

Database migrations in production are the most anxiety-inducing part of any deployment. At Cigna, a migration that added an index to a 200-million-row table locked the table for 47 minutes during business hours. The application threw connection timeout errors. The on-call engineer could not roll back because the migration had already modified the schema, and the rollback script had not been tested. This template treats database changes as first-class pipeline citizens with dry-run validation, lock detection, rollback verification, and execution time estimation.

Pipeline Stages

• migration-lint — squawk (PostgreSQL) or skeema lint (MySQL) checks migration SQL for dangerous patterns: ALTER TABLE ... ADD COLUMN ... DEFAULT (rewrites table in PostgreSQL < 11), CREATE INDEX without CONCURRENTLY, DROP COLUMN without data backup.
• dry-run — flyway migrate -dryRun or liquibase updateSQL generates the SQL that would execute without applying it. Output posted as PR comment. Reviewers see exact DDL and DML statements.
• rollback-verify — Applies migration to a test database, then applies the rollback script, then verifies the schema matches the pre-migration state. If the rollback script is missing or produces a different schema, the pipeline fails.
• estimate-execution — Runs EXPLAIN ANALYZE (dry run) against a staging database with production-equivalent data volume. Estimates lock duration and execution time. Migrations exceeding 60 seconds are flagged for off-hours execution.
• deploy-dev — flyway migrate or liquibase update against dev database. Runs application integration tests against the migrated schema to verify ORM compatibility.
• deploy-staging — Migration against staging database with production-equivalent data. Manual approval required. Timing metrics compared against estimates.
• deploy-prod — Two approvals required. Maintenance window notification sent via Slack. Migration executed with statement timeout. Automatic rollback if any statement exceeds the timeout. Post-migration health check verifies application connectivity.

Security Gates

• No raw SQL in application code — Migration files are the only place DDL executes. Application code uses ORM/query builder only.
• Credential isolation — Migration credentials are separate from application credentials with elevated DDL permissions. Application database user cannot ALTER TABLE or DROP.
• Audit logging — Every migration execution is logged with: who approved it, when it ran, how long it took, and the exact SQL executed.

What Breaks First

• Lock contention on large tables — ALTER TABLE ADD COLUMN acquires an ACCESS EXCLUSIVE lock in PostgreSQL. Fix: use ALTER TABLE ... ADD COLUMN ... DEFAULT NULL (lock-free in PostgreSQL 11+) and backfill in batches.
• Migration ordering conflict between branches — Two branches create migration V5, but with different content. Flyway rejects the checksum mismatch. Fix: use timestamp-based versioning (V20260401120000) instead of sequential integers.
• ORM schema drift after migration — The migration adds a column, but the ORM entity was not updated. The application ignores the new column, and data written to it is lost. Fix: generate the ORM schema from the database after migration and diff against the committed schema.