Database Migration Patterns

Safe, reversible database schema changes for production systems.

When to Activate

• Creating or altering database tables
• Adding/removing columns or indexes
• Running data migrations (backfill, transform)
• Planning zero-downtime schema changes
• Setting up migration tooling for a new project

Core Principles

1. Every change is a migration — never alter production databases manually
2. Migrations are forward-only in production — rollbacks use new forward migrations
3. Schema and data migrations are separate — never mix DDL and DML in one migration
4. Test migrations against production-sized data — a migration that works on 100 rows may lock on 10M
5. Migrations are immutable once deployed — never edit a migration that has run in production

Migration Safety Checklist

Before applying any migration:

• Migration has both UP and DOWN (or is explicitly marked irreversible)
• No full table locks on large tables (use concurrent operations)
• New columns have defaults or are nullable (never add NOT NULL without default)
• Indexes created concurrently (not inline with CREATE TABLE for existing tables)
• Data backfill is a separate migration from schema change
• Tested against a copy of production data
• Rollback plan documented

PostgreSQL Patterns

Adding a Column Safely

-- GOOD: Nullable column, no lock
ALTER TABLE users ADD COLUMN avatar_url TEXT;

-- GOOD: Column with default (Postgres 11+ is instant, no rewrite)
ALTER TABLE users ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT true;

-- BAD: NOT NULL without default on existing table (requires full rewrite)
ALTER TABLE users ADD COLUMN role TEXT NOT NULL;
-- This locks the table and rewrites every row

Adding an Index Without Downtime

-- BAD: Blocks writes on large tables
CREATE INDEX idx_users_email ON users (email);

-- GOOD: Non-blocking, allows concurrent writes
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

-- Note: CONCURRENTLY cannot run inside a transaction block
-- Most migration tools need special handling for this

Renaming a Column (Zero-Downtime)

Never rename directly in production. Use the expand-contract pattern:

-- Step 1: Add new column (migration 001)
ALTER TABLE users ADD COLUMN display_name TEXT;

-- Step 2: Backfill data (migration 002, data migration)
UPDATE users SET display_name = username WHERE display_name IS NULL;

-- Step 3: Update application code to read/write both columns
-- Deploy application changes

-- Step 4: Stop writing to old column, drop it (migration 003)
ALTER TABLE users DROP COLUMN username;

Removing a Column Safely

-- Step 1: Remove all application references to the column
-- Step 2: Deploy application without the column reference
-- Step 3: Drop column in next migration
ALTER TABLE orders DROP COLUMN legacy_status;

Large Data Migrations

-- BAD: Updates all rows in one transaction (locks table)
UPDATE users SET normalized_email = LOWER(email);

-- GOOD: Batch update with progress
DO $$
DECLARE
  batch_size INT := 10000;
  rows_updated INT;
BEGIN
  LOOP
    UPDATE users
    SET normalized_email = LOWER(email)
    WHERE id IN (
      SELECT id FROM users
      WHERE normalized_email IS NULL
      LIMIT batch_size
      FOR UPDATE SKIP LOCKED
    );
    GET DIAGNOSTICS rows_updated = ROW_COUNT;
    RAISE NOTICE 'Updated % rows', rows_updated;
    EXIT WHEN rows_updated = 0;
    COMMIT;
  END LOOP;
END $$;

Flyway (Java / Spring Boot)

Setup (build.gradle)

dependencies {
    implementation 'org.flywaydb:flyway-core'
    // For MySQL: also add flyway-mysql
    implementation 'org.flywaydb:flyway-mysql'
}

# application.yml
spring:
  flyway:
    enabled: true
    locations: classpath:db/migration
    baseline-on-migrate: false

Naming Convention

V{version}__{description}.sql      ← versioned migration (runs once)
R__{description}.sql               ← repeatable migration (runs when changed)
U{version}__{description}.sql      ← undo migration (Flyway Teams only)

Examples:

V1__create_users_table.sql
V2__add_avatar_url_to_users.sql
V3__backfill_display_name.sql
R__create_views.sql

Workflow Commands

# Apply pending migrations (also runs automatically on app startup)
./gradlew flywayMigrate

# Check migration status
./gradlew flywayInfo

# Validate migrations match checksums
./gradlew flywayValidate

# Repair failed migration (fix checksum mismatch or mark as failed)
./gradlew flywayRepair

# Clean all objects (dev only — NEVER in production)
./gradlew flywayClean

Migration File Example

-- src/main/resources/db/migration/V2__add_avatar_url_to_users.sql
ALTER TABLE users ADD COLUMN avatar_url VARCHAR(500);

-- src/main/resources/db/migration/V3__add_email_index.sql
-- For MySQL, use standard CREATE INDEX (no CONCURRENTLY)
CREATE INDEX idx_users_email ON users (email);

Flyway Rules

• Never edit a deployed migration file — Flyway validates checksums; any edit causes FlywayValidationException
• Use flywayRepair only when a migration failed mid-run and left a dirty state
• flywayClean is destructive — disable in production with spring.flyway.clean-disabled=true
• Separate schema and data — DDL changes and data backfills must be separate migration files

Zero-Downtime Migration Strategy

For critical production changes, follow the expand-contract pattern:

Phase 1: EXPAND
  - Add new column/table (nullable or with default)
  - Deploy: app writes to BOTH old and new
  - Backfill existing data

Phase 2: MIGRATE
  - Deploy: app reads from NEW, writes to BOTH
  - Verify data consistency

Phase 3: CONTRACT
  - Deploy: app only uses NEW
  - Drop old column/table in separate migration

Timeline Example

Day 1: Migration adds new_status column (nullable)
Day 1: Deploy app v2 — writes to both status and new_status
Day 2: Run backfill migration for existing rows
Day 3: Deploy app v3 — reads from new_status only
Day 7: Migration drops old status column

Anti-Patterns