Zero-Downtime Database Evolution: Flyway + Spring Boot Expand/Migrate/Contract Pattern

Why Online DDL Causes Outages

DDL in MySQL is not just metadata changes; InnoDB rebuilds physical pages. The behavior depends on ALGORITHM and LOCK options: COPY (legacy default) rebuilds the whole table with an exclusive write lock, blocking reads and writes. INPLACE modifies in place for most operations, but changing column types or character sets still triggers an implicit rebuild. INSTANT (MySQL 8.0+) changes only metadata in milliseconds, but cannot modify NULL attributes or add primary keys.

Two root causes bring down releases:

Metadata lock contention : Long-running transactions or idle connections hold metadata locks. A simple ALTER TABLE waits for Waiting for table metadata lock, queuing subsequent requests until HikariCP exhausts.

Replication lag amplification : Large-table changes generate massive row-based binlog events or a single long transaction. Replicas fall behind from seconds to minutes; routing reads to replicas then yields Unknown column errors.

The core principle: zero-downtime evolution is not about making DDL faster, but ensuring the application and database remain compatible during the structural inconsistency window.

Why Teams Choose Flyway

Flyway and Liquibase are the main contenders. Liquibase's XML/YAML abstraction enables cross-database portability, but DBAs dislike the indirection and developers find debugging cumbersome. Flyway uses plain SQL scripts in classpath:db/migration, ordered by version in the filename, and records a checksum after execution. Any tampering with executed scripts causes startup failure — a discipline that fits production rigor.

Production-Grade Spring Boot Flyway Configuration

spring:
  flyway:
    enabled: true
    locations: classpath:db/migration
    baseline-on-migrate: true
    baseline-version: "1.0.0"
    validate-on-migrate: true
    clean-disabled: true
    out-of-order: false

Key details:

Baseline anchoring : For legacy systems, do not start from V1. Use baseline to pin the current schema state; subsequent migrations only apply after that anchor.

Environment isolation : Separate common migrations ( db/migration/common/) from environment-specific ones ( db/migration/env/{profile}/) and load dynamically via Spring Profiles.

Version naming : Use V{timestamp}__{description}.sql with strictly monotonic timestamps; sequential numbers cause Git merge conflicts in team settings.

Smooth Change Pattern: Expand, Migrate, Contract

Parallel Change (Expand/Migrate/Contract) is the industry-hardened rule — never complete all steps in one release window.

Step 1: Expand (Add Only)

Before deployment, create new columns and indexes. New columns must allow NULL or have a harmless default. Declare indexes explicitly with ALGORITHM=INPLACE, LOCK=NONE to prevent the engine from choosing a blocking strategy. Application code does not need changes yet, or only adds a fallback to old logic. The database schema must always stay half a step ahead of the application.

Step 2: Migrate (Dual-Write & Traffic Switch)

Implement dual-write: writes update both old and new columns; reads switch to the new column via a feature flag. To avoid inconsistency, use eventual consistency — after the old write succeeds, emit an MQ message to backfill the new column, or run a scheduled reconciliation job. Monitor the data delta; allow minor drift during the window but prevent snowballing.

Step 3: Contract (Clean Up)

After the new structure has fully taken over and run through at least one complete release cycle, disable dual-write, retire old code, then execute DROP COLUMN or DROP INDEX via Flyway. Never delete columns the same night you cut traffic — you lose the rollback handle.

Iron rule : Application upgrades and destructive DDL must never share a release window. Database changes are backward-compatible; applications upgrade first.

Script Writing, Rollback, and Pre-Release Checks

Flyway does not provide automatic rollback — this is by design. DDL in MySQL is an implicit commit, so physical ROLLBACK is impossible. Prepare compensating scripts prefixed with U__ that mirror the forward script (e.g., DROP or corrective DML). On failure, assess manually and run the compensation script or restore from a snapshot.

Migration Script Habits

Explicit existence checks : MySQL 8.0.19+ supports ADD COLUMN IF NOT EXISTS; add it for safety during manual debugging. PostgreSQL supports transactional DDL, so wrap in BEGIN/COMMIT.

Separate DDL and DML : Do not mix schema changes and historical data backfills in one script. Run structure changes first; then use a separate batch script for data — failure affects only data, not metadata.

Large indexes in isolation : For tables over ten million rows, place index creation in its own script to avoid coupling with column changes and to keep lock duration predictable.

CI/CD Pipeline Gates

DryRun syntax check : Run Flyway CLI info with dryRunOutput to validate SQL syntax and permission dependencies without executing.

Environment health confirmation : Verify disk space (at least 2x target table size), replica lag, and active connection count. Query information_schema — avoid full-table scans in production.

Least-privilege execution account : Grant only ALTER, CREATE, INDEX, DROP; never SUPER or ALL PRIVILEGES. Pipeline runs SHOW GRANTS and blocks on mismatch.

Kubernetes Rolling Deployment DB Coordination Pitfalls

Embedding Flyway in the Spring Boot startup causes chaos under K8s rolling updates: each new Pod races to run migrations, leading to checksum conflicts or connection storms.

Correct approach : Decouple Flyway from the application startup. Run it as a separate CI/CD step or a K8s Job. The release sequence becomes:

Pipeline triggers Flyway Job to execute the Expand-phase scripts; wait for schema_version table to record success.

Confirm replica sync completion; lag returns to baseline.

Trigger application rolling update. New Pods connect to the already-expanded schema; old Pods continue working against the same schema.

During the transition:

Old code reading new columns: use IFNULL or default-value fallbacks.

Dual-write must be idempotent — deduplicate via business unique keys or distributed locks to prevent compensation tasks from overwhelming the table.

Temporarily route read traffic to the primary to avoid replica DDL replay lag causing missing columns. Absorbing extra read load is safer than data corruption.

Monitoring, Circuit Breaking, and Fallback Plans

Do not rely on manual log watching. Feed key metrics into Prometheus and alerting:

Migration duration : Alert if a large-table change exceeds 5 minutes with no progress.

InnoDB lock waits : Query performance_schema.data_locks or SHOW ENGINE INNODB STATUS; investigate waits over 10 seconds.

Replication lag : Pause traffic switch if lag exceeds 30 seconds; prioritize data consistency.

Business 5xx error rate : Trigger circuit breaker at 1% spike; halt further deployment.

Circuit-break logic must be codified in the pipeline: script timeout, unreleased locks, error-rate surge → CI/CD hook sends termination signal → K8s executes rollout undo to roll back the application. Do not tough it out.

If a migration stalls halfway (schema half-old, half-new): immediately route all traffic back to old-version instances, set the database read-only. Once the window stabilizes, run a compensation job with idempotent UPSERT to fill gaps. Degrade non-core features first; protect the core transaction path.

Post-Mortems and Landing Checklist

Incident 1: Large-Table Index Addition Exhausts Connection Pool

An 80-million-row ledger table received a direct ALTER TABLE ADD INDEX without specifying an algorithm. InnoDB performed a full rebuild, running 14 minutes. HikariCP threw ConnectionPoolTimeoutException.

Lesson : For MySQL < 8.0, use gh-ost or pt-online-schema-change. On 8.0+, always declare ALGORITHM=INPLACE and apply rate limiting beforehand. Missing pipeline-integrated monitoring with auto-timeout is a fatal gap.

Incident 2: Rolling Deploy Sees Missing Column on Replica

DDL had not finished replicating when new Pods started; queries on the new column returned Unknown column.

Lesson : Sequence was reversed. Must be: DDL lands → wait for replica lag to hit zero → then upgrade application. Enhance K8s readinessProbe with a custom script that verifies sync status before marking Ready.

Production Checklist (Print and Post)

[ ] Staging runs full migrate + dual-write validation; data reconciliation passes.

[ ] Pre-production: confirm disk headroom > 2x target table size, replica lag < 10s.

[ ] Flyway scripts decoupled from app; executed via independent Job/CI step.

[ ] Phase-1 scripts only add, never drop; verify schema_version shows SUCCESS.

[ ] Canary 1-2 nodes, enable read verification switch, observe 15 minutes anomaly-free.

[ ] After dual-write enable, run data diff script; discrepancy rate < 0.01% before proceeding.

[ ] Sustain current state for 3 days: no slow-query spikes, no business complaints.

[ ] Execute cleanup scripts to remove redundant columns; verify execution plans revert to baseline.

Zero-downtime is not a silver-bullet tool; it is the result of release discipline, code-level safeguards, and monitoring circuit breakers working in concert. Flyway merely versions the schema; what decides survival is whether the team can bite down on the rhythm: expand the database first, upgrade the application next, pay down the debt last. Cloud-native databases and online DDL tools grow stronger, but the decoupling principle between business logic and data structure remains invariant. Master this flow, and midnight releases finally let you sleep through the night.