Zero-Downtime DB Migrations: Flyway, Liquibase, Expand–Contract

Safe production DB migrations require two things: a versioned migration tool managed in Git and CI, and a pattern that keeps old and new schemas working simultaneously during rollout — expand-contract.

Flyway vs Liquibase

Flyway — pragmatic default

• SQL-first: V1__init.sql, V2__add_email.sql
• Plain DDL you can copy-paste
• Simpler; lower ceremony; fast to learn
• flyway_schema_history tracks applied migrations

Liquibase

• XML, YAML, JSON, or SQL changelogs
• Rich DSL: preconditions, rollback tags, contexts
• Stronger built-in rollback
• More verbose; steeper learning curve
• Better for enterprise with complex governance

Decision

• Small/medium teams, Postgres/MySQL, fast iteration: Flyway
• Enterprise, multi-DB support, strict rollback/preconditions: Liquibase
• Discipline around them matters more than tool choice

Production guardrails

• Never auto-run migrations in prod via app startup at scale
• Apply migrations explicitly from CI/CD before starting app instances
• Lock prod DB user to DDL-capable only during migration window
• CI runs migrations against clean DB plus prod schema snapshot
• Immutable migrations: never edit merged; add new

Expand-Contract pattern — zero-downtime golden rule

Never deploy a code change that's incompatible with currently-running code's schema.

Phase 1: Expand

• Add new column alongside old, nullable
• Create new index CONCURRENTLY
• Deploy code that dual-writes both columns and reads from old

Phase 2: Backfill

• Populate new column for existing rows
• Run async in batches (10k at a time, sleep between)
• Monitor replication lag

Phase 3: Switch reads

• Deploy code that reads new column, still writes both
• Use feature flag for gradual shift

Phase 4: Stop writing old

• Deploy code that writes only new column
• Wait for all instances to roll

Phase 5: Contract

• Drop old column
• Usually wait days or weeks between Phase 4 and 5 to preserve rollback

Adding NOT NULL safely

• Postgres 11+: ALTER ADD COLUMN with DEFAULT is now fast (metadata-only)
• Older: add nullable with default, backfill, SET NOT NULL via CHECK NOT VALID then VALIDATE trick

Changing column type or renaming

• Can't in place at scale
• Add new column, dual-write, backfill, switch, drop (expand-contract)

Foreign keys at scale

• Adding FK validates existing rows; locks
• Postgres: NOT VALID then VALIDATE CONSTRAINT (online)

Online DDL features

Postgres

• CREATE INDEX CONCURRENTLY
• ALTER TABLE ADD COLUMN with default (11+)
• DROP INDEX CONCURRENTLY
• REINDEX CONCURRENTLY (12+)
• pg_squeeze or pg_repack for online bloat cleanup
• lock_timeout plus retry pattern

MySQL

• ALGORITHM=INPLACE LOCK=NONE
• ALGORITHM=INSTANT (8.0.12+) for metadata-only
• gh-ost (GitHub), pt-online-schema-change (Percona) for large tables

CockroachDB

• Schema changes online by design; background jobs

Planetscale

• Branching model: branch, apply schema, merge with zero downtime via Vitess

Large table strategy (hundreds of millions of rows)

• Test on clone of production (Aurora fast clone)
• Estimate runtime; if over 10 min, use tool or break up
• Set lock_timeout or statement_timeout
• Run during low-traffic window
• Monitor replication lag; pause if lag grows
• Backfill in chunks with progress tracking; idempotent
• Verify integrity with shadow reads or checksum

Rollback strategy

• Small change: backout migration file
• Expand-contract: keep old column until safe; revert code works
• Destructive change (dropped column): full DB restore from backup
• Liquibase rollback tags auto-generate (best-effort)
• Test rollback in CI for critical migrations

Common failure modes

• Long ALTER locks table for minutes; requests time out
• Breaking schema change shipped with incompatible code: replicas crash
• Unreviewed migration runs in prod with data loss
• No rollback path; schema harder to revert than code
• Missing backfill; new column empty; reads fail

Gotchas

• Auto-migrate on Spring Boot startup dangerous at scale (crash loop applies partial)
• V versioning collisions: use timestamp or rebase before merge
• Long migrations block deploy pipelines: run out-of-band
• Seed data should not be in migrations
• Environment drift from ad-hoc DDL; block in prod
• Read-replica lag during DDL; throttle
• Cross-service schema coordination painful (antipattern); use DB-per-service
• Destructive changes: review + wait period + confirm no references

CDC as migration tool

• Debezium streaming to new system during migration
• Enables dual-running old and new backends
• Powerful for DB moves (Postgres to Aurora, MySQL to CockroachDB)

Testcontainers for CI

• Spin up real Postgres/MySQL in CI
• Run migrations and tests
• Don't test against H2 or fake DB

Policy on day 1

• All DDL via migrations in Git
• Reviewed PRs; DBA-aware reviewer
• CI runs on clean and prod schema snapshot
• Prod migrations from CI, not app startup
• Online DDL by default
• Expand-contract for column changes
• Backfill in chunks with progress
• Rollback plan always
• Team comm on schema changes

The strategic answer

• Tool is easy part; discipline is hard part
• Expand-contract always
• Online DDL always
• Test on production-like data
• Automate from CI
• Never couple deploy and schema atomically on big tables
• Feature flags gate new code paths
• Zero-downtime is a practice, not a feature