DevOps

Zero-Downtime Database Schema Migration: Flyway, Liquibase & Expand-Contract Pattern in Production

Database migrations are the most dangerous operation in a production deployment. A single ALTER TABLE on the wrong table at the wrong time can take down your entire application for hours. This guide gives you a complete playbook for zero-downtime schema changes using Flyway, Liquibase, and the battle-tested expand-contract pattern.

Md Sanwar Hossain April 11, 2026 19 min read DevOps

1. Why Database Migrations Break Production Deployments

Most production outages caused by database migrations are entirely preventable. The root cause is almost always one of six classic failure modes that engineering teams repeat because they don't understand the locking semantics of their database engine.

The Six Classic Failure Modes

2. The Expand-Contract Pattern: Step-by-Step

The expand-contract pattern (also called parallel change) solves the root cause of migration outages: code and schema are deployed together. The pattern decouples them into three phases, each backward-compatible with all running service versions.

-- Safe: Add new column as nullable (no lock on PostgreSQL 11+)
ALTER TABLE users ADD COLUMN full_name VARCHAR(500);

-- Safe: Create new table
CREATE TABLE user_profiles (
    user_id BIGINT PRIMARY KEY REFERENCES users(id),
    full_name VARCHAR(500),
    bio TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Safe: Create index concurrently (PostgreSQL)
CREATE INDEX CONCURRENTLY idx_users_email_lower
ON users (LOWER(email));

-- Batch backfill to avoid long transactions
UPDATE users
SET full_name = first_name || ' ' || last_name
WHERE id BETWEEN 1 AND 100000
  AND full_name IS NULL;
-- Run in batches until all rows backfilled
-- Application: write to BOTH old columns AND new column

-- Only run after ALL services deployed with new code
ALTER TABLE users DROP COLUMN first_name;
ALTER TABLE users DROP COLUMN last_name;

-- Add NOT NULL now that all rows have values
ALTER TABLE users ALTER COLUMN full_name SET NOT NULL;

3. Flyway with Spring Boot: Configuration & Best Practices

Flyway is the most widely used database migration tool in the Java ecosystem. Its SQL-first, version-ordered approach is simple, predictable, and integrates natively with Spring Boot's auto-configuration.

Maven Dependency

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>

Application Properties Configuration

spring.flyway.enabled=true
spring.flyway.locations=classpath:db/migration
spring.flyway.baseline-on-migrate=true
spring.flyway.validate-on-migrate=true
spring.flyway.out-of-order=false
spring.flyway.table=flyway_schema_history
spring.flyway.clean-disabled=true

File Naming Convention

• V1__init_schema.sql — versioned migration (runs once, in order)
• V2__add_user_profiles.sql — versioned migration
• R__seed_reference_data.sql — repeatable migration (runs when checksum changes)
• U2__undo_add_user_profiles.sql — undo migration (Flyway Teams only)

The double underscore (__) separates version from description. Never modify an already-applied versioned migration — Flyway validates checksums on startup and will refuse to start if a mismatch is detected.

Java-Based Migration Example

For complex data migrations that require Java logic (e.g., hashing, encryption, conditional logic), Flyway supports Java-based migrations:

@Component
public class V5__DataMigration extends BaseJavaMigration {
    @Override
    public void migrate(Context context) throws Exception {
        new JdbcTemplate(new SingleConnectionDataSource(
                context.getConnection(), true))
            .execute("UPDATE users SET status = 'ACTIVE' WHERE status IS NULL");
    }
}

Flyway Repair Command

When a migration fails partway through, Flyway marks it as failed in flyway_schema_history and refuses to run further migrations. After fixing the underlying issue, use repair to allow Flyway to continue:

flyway repair  # marks failed migration as resolved

4. Liquibase with Spring Boot: When and How

Liquibase takes a more abstracted approach than Flyway. Migrations are defined as changesets in XML, YAML, JSON, or SQL. The key advantage is native rollback support and database-agnostic migrations that can target PostgreSQL, MySQL, Oracle, and others from the same changelog.

Maven Dependency

<dependency>
    <groupId>org.liquibase</groupId>
    <artifactId>liquibase-core</artifactId>
</dependency>

Master Changelog File (XML format)

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.20.xsd">

    <include file="changelogs/v1-init.xml"/>
    <include file="changelogs/v2-user-profiles.xml"/>
</databaseChangeLog>

Changeset with Rollback

<changeSet id="add-full-name-column" author="sanwar">
    <addColumn tableName="users">
        <column name="full_name" type="VARCHAR(500)"/>
    </addColumn>
    <rollback>
        <dropColumn tableName="users" columnName="full_name"/>
    </rollback>
</changeSet>

YAML Format Alternative

databaseChangeLog:
  - changeSet:
      id: add-user-bio
      author: sanwar
      changes:
        - addColumn:
            tableName: users
            columns:
              - column:
                  name: bio
                  type: TEXT

Liquibase Tags for Rollback

liquibase tag VERSION_2_3_0      # tags current database state
liquibase rollback VERSION_2_3_0 # rolls back to tagged state

5. Flyway vs Liquibase: Decision Guide

Both tools integrate identically with Spring Boot auto-configuration. The choice comes down to your team's workflow preferences and enterprise requirements.

Recommendations

• ✅ Use Flyway if your team writes SQL fluently and you want simplicity with predictable version ordering
• ✅ Use Liquibase if you need reliable rollback, multi-database support, or enterprise compliance features
• ✅ Both integrate identically with Spring Boot auto-configuration — switching is straightforward
• ✅ Whichever you choose, apply the expand-contract pattern — the tool doesn't make a migration safe, the pattern does

6. Online Index Builds Without Downtime

Adding indexes to large tables is one of the most common migration tasks — and one of the most dangerous. Each database engine has a different mechanism for non-blocking index creation.

PostgreSQL — CREATE INDEX CONCURRENTLY

-- Non-blocking: uses background worker, allows reads & writes during build
CREATE INDEX CONCURRENTLY idx_orders_user_id
ON orders (user_id);

-- Caveat: takes 2-3x longer than regular CREATE INDEX
-- Caveat: cannot run inside a transaction
-- Check progress in pg_stat_progress_create_index

MySQL — pt-online-schema-change

Percona's pt-online-schema-change works by creating a shadow table, copying data in chunks, and using triggers to keep the shadow table in sync while traffic continues on the original:

pt-online-schema-change \
  --alter "ADD INDEX idx_orders_user_id (user_id)" \
  --host=localhost \
  --user=root \
  --password=secret \
  --execute \
  D=mydb,t=orders

gh-ost (GitHub's Online Schema Migration for MySQL)

gh-ost \
  --user="root" --password="secret" \
  --host=replica-host \
  --database="mydb" \
  --table="orders" \
  --alter="ADD INDEX idx_orders_user_id (user_id)" \
  --execute

• gh-ost works via binary log replication, does not use triggers (safer than pt-osc for high-write tables)
• Supports postpone, throttle, and cut-over controls — you can pause the migration if load spikes
• Used at GitHub on 1-billion+ row tables in continuous production deployments

7. Shadow Columns & Data Backfilling at Scale

The shadow column technique is the expand-contract pattern applied specifically to column renaming or type changes. It's the safest way to rename a column without downtime or data loss.

Shadow Column Workflow

1. Phase 1 (Expand): Add full_name alongside existing first_name + last_name columns
2. Deploy new code: Write to all three columns — first_name, last_name, and full_name
3. Phase 2 (Backfill): Run a batch job to populate full_name for all existing rows
4. Verify: SELECT COUNT(*) FROM users WHERE full_name IS NULL — must return 0
5. Switch reads: Update code to read from full_name, stop writing to old columns
6. Phase 3 (Contract): Drop first_name and last_name after 100% rollout confirmed

Spring Batch Backfill Job

@Configuration
public class BackfillJobConfig {

    @Bean
    public Job backfillFullNameJob(JobBuilderFactory jobs, Step backfillStep) {
        return jobs.get("backfillFullNameJob")
            .start(backfillStep)
            .build();
    }

    @Bean
    public Step backfillStep(StepBuilderFactory steps,
                              JdbcCursorItemReader<User> reader,
                              ItemProcessor<User, User> processor,
                              JdbcBatchItemWriter<User> writer) {
        return steps.get("backfillStep")
            .<User, User>chunk(1000)
            .reader(reader)
            .processor(processor)
            .writer(writer)
            .build();
    }
}

Batch Size Recommendations

• Chunk size: 1,000–5,000 rows per transaction — balances memory and transaction overhead
• Rate limiting: Add a configurable sleep between chunks (50–200ms) to avoid overwhelming the primary
• Replication lag monitoring: Watch replica lag during backfills on primary-replica setups
• Use Spring Batch for any backfill involving more than 1 million rows — it provides restart, skip, and retry semantics out of the box

8. Rollback Strategies and Safety Nets

No migration strategy is complete without a tested rollback plan. "Tested" is the key word — rollback scripts that have never been run in staging are not safety nets, they are false confidence.

Flyway Repair

Mark a failed migration as resolved to allow Flyway to continue after the underlying issue is fixed manually. Use flyway repair to delete the failed entry from flyway_schema_history.

Undo Migrations (Flyway Teams)

-- U2__undo_add_user_profiles.sql
DROP TABLE IF EXISTS user_profiles;
ALTER TABLE users DROP COLUMN IF EXISTS full_name;

Liquibase Rollback Tags

liquibase --changeLogFile=changelog.xml tag BEFORE_V2_MIGRATION
# ... run migration ...
# If rollback needed:
liquibase --changeLogFile=changelog.xml rollback BEFORE_V2_MIGRATION

Additional Safety Nets

• Blue-green database strategy: Maintain two schemas (blue/green), swap traffic after migration verified on the inactive side
• Read replicas as rollback points: Promote a replica before running risky migrations — gives an instant point-in-time rollback target
• Application-level feature flags: Deploy new code behind a flag; keep old code path active as fallback for instant application-level rollback
• AWS RDS automated snapshots: Trigger a manual snapshot before major migrations — RDS supports point-in-time recovery with <5 minute granularity
• GCP Cloud SQL point-in-time recovery: Enabled by binary logging; can restore to any second within the retention window

9. CI/CD Integration & Migration Testing

Migrations must be validated in CI before reaching production. A migration that passes checksum validation but fails against real data volumes is a ticking time bomb. Use Testcontainers to test against a real database engine, not an in-memory H2 substitute.

GitHub Actions Pipeline

- name: Run Flyway Migrations (dry-run)
  run: |
    ./gradlew flywayInfo
    ./gradlew flywayValidate

- name: Run integration tests with Testcontainers
  run: ./gradlew test

Testcontainers Migration Test

@Testcontainers
@SpringBootTest
class FlywayMigrationTest {

    @Container
    static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:16")
        .withDatabaseName("testdb")
        .withUsername("test")
        .withPassword("test");

    @DynamicPropertySource
    static void configureProperties(DynamicPropertyRegistry registry) {
        registry.add("spring.datasource.url", postgres::getJdbcUrl);
    }

    @Autowired
    private Flyway flyway;

    @Test
    void shouldRunAllMigrations() {
        MigrationInfoService info = flyway.info();
        assertThat(info.pending()).isEmpty();
        assertThat(info.applied()).isNotEmpty();
    }

    @Test
    void shouldValidateMigrationChecksum() {
        assertThatCode(() -> flyway.validate()).doesNotThrowAnyException();
    }
}

Pre-Deployment Checklist in CI

• ✅ flyway validate — checksum verification against applied migrations
• ✅ flyway info — show all pending migrations before they run
• ✅ Test against production-like data volume in staging (not just schema)
• ✅ Dry-run with --dry-run flag (Flyway Teams feature)
• ✅ Verify rollback script executes successfully in staging environment

10. Production Checklist

Use this checklist before every production migration. Treating database migrations as "just another code change" is the fastest way to a 3 AM incident call.

Md Sanwar Hossain

Software Engineer · Java · Spring Boot · Microservices · AI/LLM Systems

All Posts