Automating Database Migrations with Flyway and Spring Boot in a CI/CD Pipeline

The 02:45 AM Alter Table Deadlock

The incident began not with a crash, but with a silent stall. At 02:44 UTC, a routine deployment to the production cluster initiated. The CI pipeline reported a successful build, the Docker image was pushed to the registry, and the rolling update on the Kubernetes cluster commenced. One minute later, the p99 latency for the user-service spiked from 45ms to 30,000ms. By 02:47 UTC, the connection pool was exhausted, and every health check across the fleet was failing with 503 Service Unavailable.

The culprit was a seemingly innocent Flyway migration script: V20231012_04__add_index_to_large_audit_table.sql.

-- The "Death" Script
CREATE INDEX idx_audit_logs_user_id ON audit_logs (user_id);

In PostgreSQL, a standard CREATE INDEX acquires a SHARE lock on the table. This lock doesn't block SELECT statements, but it prevents any concurrent INSERT, UPDATE, or DELETE operations. On a table with 45 million rows, this index creation was estimated to take four minutes. Because the Spring Boot application was configured to run migrations on startup (spring.flyway.enabled=true), the first pod to start grabbed the lock. Every other pod in the deployment remained in a ContainerCreating or CrashLoopBackOff state because they couldn't acquire the Flyway schema history lock, while the existing production pods were blocked by the index creation on every write operation.

The Anatomy of the Migration Failure

When Flyway is integrated into a Spring Boot lifecycle without guardrails, the application's availability is directly tied to the database's schema-locking behavior. In this incident, several factors converged to create a total system eclipse.

1. Implicit Migration Execution: By allowing the application to execute migrations on startup, we tied the deployment of application code to the long-running database maintenance task. 2. Lack of Concurrent Indexing: Standard PostgreSQL indexes block writes. In a high-throughput environment, any index on a table larger than 1GB should use CREATE INDEX CONCURRENTLY. 3. Statement Timeout Absence: The migration runner had no lock_timeout. It was willing to wait forever to acquire the lock, effectively queueing up behind other long-running transactions and blocking subsequent ones. 4. Transaction Wrapping: Flyway wraps migrations in a transaction by default. For operations like CREATE INDEX CONCURRENTLY, which cannot run inside a transaction blocks, this creates a configuration mismatch that often leads engineers to fall back to blocking indexes.

[Deploy Trigger] -> [Pod A Starts] -> [Flyway grabs Schema Lock]
                                     |
                                     V
[Pod A executes ALTER/CREATE INDEX] <- [Exclusive/Share Lock acquired]
                                     |
      [Active Traffic] ------------> [Blocked on Writes]
                                     |
[Pod B, C, D Start] ---------------> [Blocked on Flyway Schema Lock]
                                     |
[Result: Cascade Failure across all nodes]

Decoupling Migration from Application Startup

The first remediation step was moving away from "auto-migration on startup" for production environments. While convenient for local development, it is a liability in CI/CD. We shifted to a "Migrate-then-Deploy" pattern. The migration is now a discrete step in the GitHub Actions pipeline, executed by a short-lived runner that has the necessary credentials to modify the schema but isn't part of the application's runtime footprint.

This requires configuring Spring Boot to validate the schema rather than modify it at runtime. In application-prod.yaml:

spring:
  flyway:
    enabled: false # Do not run migrations on startup
    check-location: true
  jpa:
    hibernate:
      ddl-auto: validate # Ensure the schema matches the entities

The CI/CD pipeline now executes the Flyway CLI or a dedicated Flyway Gradle/Maven task before the Kubernetes rollout begins. If the migration fails, the deployment of the new code never starts, leaving the current (stable) version running.

Engineering Safe Migrations for PostgreSQL

To prevent the SHARE lock bottleneck, we standardized our migration scripts to include specific PostgreSQL session settings. Every migration now follows a strict template that enforces timeouts and non-blocking operations.

Common DDL operations like adding a column with a default value used to be expensive in older Postgres versions (pre-v11), but even in modern versions, adding a NOT NULL column without a default requires a full table scan. Our standard migration header now looks like this:

```sql
-- V20231015_01__add_status_to_orders.sql
SET statement_timeout = '30s';
SET lock_timeout = '5s';

-- For index creation, we use a separate script with: -- SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- CREATE INDEX CONCURRENTLY ...

ALTER TABLE orders ADD COLUMN IF NOT EXISTS status VARCHAR(20); ```

By setting lock_timeout, we ensure that if the migration cannot acquire the necessary lock within 5 seconds (perhaps due to a long-running report query), it fails gracefully. This prevents the migration from sitting in the lock queue and blocking all other traffic.

Implementing the CI/CD Guardrails with GitHub Actions

The pipeline must be the enforcer of these rules. We implemented a dedicated migration job that runs after the build but before the deployment. A critical part of this is the "Migration Dry Run" or "Check." Since Flyway Pro/Enterprise is often outside the budget for smaller teams, we use a custom script to validate that migrations are idempotent and follow naming conventions.

Here is the hardened GitHub Actions workflow configuration:

```yaml
jobs:
  database-migration:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      
      - name: Setup Java
        uses: actions/setup-java@v3
        with:
          java-version: '17'
          distribution: 'temurin'

- name: Flyway Check and Clean run: | ./gradlew flywayInfo -Pflyway.url=${{ secrets.DB_URL }} \ -Pflyway.user=${{ secrets.DB_USER }} \ -Pflyway.password=${{ secrets.DB_PASS }}

- name: Execute Migrations run: | # Use the flyway-commandline or gradle task ./gradlew flywayMigrate -Pflyway.url=${{ secrets.DB_URL }} \ -Pflyway.user=${{ secrets.DB_USER }} \ -Pflyway.password=${{ secrets.DB_PASS }} \ -Pflyway.outOfOrder=false ```

One specific technical hurdle was handling the flyway_schema_history table in a multi-region setup. We found that by setting spring.flyway.group=true, Flyway wraps the entire migration run in a single transaction. This is dangerous for large migrations but essential for ensuring the schema history table doesn't get corrupted if the CI runner loses its connection halfway through.

The 72-Hour Cooldown and Backward Compatibility

The most difficult lesson learned was that rolling back a database migration is almost never the right answer. If you deploy a migration that drops a column, and the code deployment fails, you cannot simply "roll back" the code because the data is gone.

We enforced a "Two-Phase Schema Change" policy. Every destructive change must be split into three deployments: 1. Phase 1 (Addition): Add the new column/table. Code writes to both old and new, but reads from old. 2. Phase 2 (Migration): A background job syncs historical data from the old column to the new column. Code now reads from the new column. 3. Phase 3 (Cleanup): Drop the old column/table only after Phase 2 has been stable in production for at least 72 hours.

This strategy ensures that the application code at version $N$ is always compatible with the database schema at version $N+1$. We verified this by running two versions of the application simultaneously during the rolling update.

Hard-Won Performance Metrics

During our post-incident optimization, we looked at the flyway_schema_history table's performance. On a high-transaction system, querying this table on every application startup (even for validation) added overhead. By moving migration execution to the CI/CD pipeline and switching the application to hibernate.ddl-auto: validate, we saw a significant improvement in startup times.

Specifically, our Spring Boot "Time to Ready" metric dropped from 28 seconds to 12 seconds because Hibernate no longer had to query the information_schema to compare against the local entity state while Flyway was also holding its own locks. Furthermore, by enforcing index concurrently, our P99 during deployments dropped from spikes of 30,000ms back to the baseline of 45ms.

The ultimate takeaway: The database is not an extension of your application; it is a shared global state. Managing it through the same lifecycle as a stateless container is an architectural smell that eventually leads to deadlocks. Treat migrations as a first-class infrastructure-as-code deployment, separate from the application binary, with its own timeouts, retry logic, and safety checks. If a migration takes more than 10 seconds on a staging-sized dataset, it is a bug that must be refactored into a background task or a concurrent operation.