Flyway Migration Best Practices for Spring Boot: What Can Go Wrong (2026)

Flyway migrations look simple: add a SQL file, deploy, done. But production databases have millions of rows, live traffic, and zero tolerance for mistakes. A migration that adds a NOT NULL column without a default, or locks a large table, causes downtime. This guide covers the patterns that keep migrations safe.

---

Flyway Basics in Spring Boot

Flyway auto-runs migrations on startup. Naming convention is strict:

src/main/resources/db/migration/
  V1__create_users_table.sql
  V2__add_email_index.sql
  V3__add_user_preferences.sql

# application.properties
spring.flyway.enabled=true
spring.flyway.locations=classpath:db/migration
spring.flyway.baseline-on-migrate=false  # Don't baseline existing schemas silently
spring.flyway.validate-on-migrate=true

---

Anti-Pattern 1: Adding NOT NULL Columns Without Defaults

The most common migration mistake that causes production failures:

-- DANGEROUS on a live table with existing rows
ALTER TABLE orders ADD COLUMN shipping_address VARCHAR(500) NOT NULL;
-- ERROR: column "shipping_address" contains null values

Safe pattern - three-phase migration:

-- V10__add_shipping_address_phase1.sql
-- Phase 1: Add nullable column (no lock, instant)
ALTER TABLE orders ADD COLUMN shipping_address VARCHAR(500);

-- V11__add_shipping_address_phase2.sql (deploy + backfill in app code first)
-- Phase 2: Backfill existing rows (run in batches to avoid long lock)
UPDATE orders
SET shipping_address = COALESCE(billing_address, 'Unknown')
WHERE shipping_address IS NULL;

-- V12__add_shipping_address_phase3.sql (after all rows have values)
-- Phase 3: Add NOT NULL constraint
ALTER TABLE orders ALTER COLUMN shipping_address SET NOT NULL;

---

Anti-Pattern 2: Table Locks on Large Tables

DDL operations like ALTER TABLE take an exclusive lock. On a 100M-row table, this can lock for minutes:

-- DANGEROUS - locks entire table during index creation
ALTER TABLE orders ADD INDEX idx_customer_id (customer_id);

-- DANGEROUS - full table rewrite
ALTER TABLE orders MODIFY COLUMN amount DECIMAL(12,4); -- was DECIMAL(10,2)

Safe pattern - concurrent index creation (PostgreSQL):

-- V15__add_customer_index.sql
-- CONCURRENTLY avoids table lock (PostgreSQL)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_id
    ON orders(customer_id);
-- Note: cannot run inside a transaction block

For Flyway to support CONCURRENTLY, disable transaction wrapping for this migration:

// Java-based migration for special cases
public class V15__add_customer_index extends BaseJavaMigration {

    @Override
    public void migrate(Context context) throws Exception {
        try (Statement stmt = context.getConnection().createStatement()) {
            // Flyway wraps in transaction by default - set autocommit for CONCURRENTLY
            context.getConnection().setAutoCommit(true);
            stmt.execute(
                "CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_id " +
                "ON orders(customer_id)"
            );
        }
    }

    @Override
    public boolean canExecuteInTransaction() {
        return false; // Required for CONCURRENT index creation
    }
}

---

Anti-Pattern 3: Deleting Columns While Old Code Reads Them

In a rolling deployment, old pods still run while new pods are deployed. If new code drops a column that old code reads, old pods crash:

-- V20__drop_legacy_column.sql
ALTER TABLE users DROP COLUMN legacy_token; -- Old pods still SELECT this!

Safe pattern - expand/contract:

Deploy 1: Stop writing to legacy_token (but keep reading)
Deploy 2: Stop reading legacy_token (but keep the column)
Deploy 3: DROP COLUMN legacy_token (safe - no code references it)

---

Anti-Pattern 4: Modifying Existing Migration Files

Flyway checksums every migration file. Modifying an applied migration breaks your deployment:

ERROR: Validate failed: Migration checksum mismatch for migration version 5
  -> Applied to database : 1234567890
  -> Resolved locally    : 0987654321

Rule: Never modify applied migration files. If you made a mistake:

-- Create a new migration to fix the previous one
V6__fix_v5_wrong_column_type.sql

For development only, use spring.flyway.repair to fix checksum mismatches:

# NEVER run this in production without understanding consequences
flyway repair

---

Production-Safe Flyway Configuration

# application.properties - production settings
spring.flyway.enabled=true
spring.flyway.validate-on-migrate=true
spring.flyway.out-of-order=false          # Reject out-of-order migrations
spring.flyway.clean-disabled=true         # NEVER allow flyway clean in production
spring.flyway.connect-retries=5           # Retry on DB not ready (Kubernetes)
spring.flyway.connect-retries-interval=10 # 10s between retries

Critical: always disable flyway:clean in production. It drops your entire schema.

@Configuration
public class FlywayConfig {

    @Bean
    public FlywayMigrationStrategy migrationStrategy() {
        return flyway -> {
            // Log migration plan before executing
            MigrationInfo[] pending = flyway.info().pending();
            if (pending.length > 0) {
                log.info("Applying {} Flyway migration(s)", pending.length);
                Arrays.stream(pending).forEach(m ->
                    log.info("  -> V{}  {}", m.getVersion(), m.getDescription()));
            }
            flyway.migrate();
        };
    }
}

---

Common Mistakes to Avoid

• Running Flyway with spring.flyway.clean-on-validation-error=true - this drops and recreates your schema on any checksum mismatch; catastrophic in production
• Large data migrations in the same file as schema changes - schema changes take locks; backfilling data while holding the lock causes long outages; always separate them
• No IF NOT EXISTS / IF EXISTS guards - idempotent migrations survive accidental replays; CREATE INDEX IF NOT EXISTS never fails on re-run
• Using Flyway repair in production without a backup - repair marks failed migrations as success and updates checksums; only use after confirming the DB state is correct

---

Summary

Safe Flyway migrations follow the expand/contract pattern: add nullable columns first, backfill data in application code, then enforce constraints in a separate deploy. Use CREATE INDEX CONCURRENTLY to avoid table locks, never modify applied migration files, and always disable flyway:clean in production. Three-phase migrations and rolling-deploy awareness are what separates incident-free deployments from 2am rollbacks.

---

Find Migration Anti-Patterns in Your Project

JOptimize analyzes your Flyway migration files for NOT NULL additions without defaults, missing IF NOT EXISTS guards, and data migrations combined with schema changes - ranked by production risk.

• IntelliJ Plugin - flags risky migration patterns inline as you write SQL: Install JOptimize for IntelliJ
• Web Dashboard - scan your full migration history for zero-downtime compliance: Analyze your project free ?

Catch dangerous migration patterns before they cause a production outage - free scan, no configuration required.