Database Migrations in Continuous Delivery

Every CI/CD guide covers application deployment. Very few cover the harder problem: database schema changes in a system that can never be offline.

The challenge is that application and database deployments are not atomic. During a rolling or blue-green deployment, old and new application versions run simultaneously against the same database. A migration that’s incompatible with the old version causes the old version to fail — precisely during the deployment window when you can least afford failures.

The Compatibility Constraint

During any deployment that isn’t a hard cutover (and hard cutovers require downtime), two versions of your application will simultaneously be accessing the same database. Your migration strategy must satisfy:

v2 must be able to run against the old schema: before the migration runs, v2 is deployed to some instances while v1 is still running. If v2 requires the new schema to function, you have a deployment ordering problem.

v1 must be able to run against the new schema: after the migration runs, v1 may still be handling requests while v2 is being deployed. If the migration breaks v1 compatibility, you have production failures during deployment.

Both constraints together mean: all migrations must be backward-compatible with the application version that will be running before the migration and the version running after it.

This is a significant constraint. It eliminates a class of “simple” migrations: renaming a column, changing a column type, adding a NOT NULL constraint without a default.

Safe Migration Operations

Some operations are inherently backward-compatible:

-- SAFE: Adding a nullable column (old code ignores it, new code uses it)
ALTER TABLE orders ADD COLUMN customer_reference VARCHAR(255);

-- SAFE: Adding a table (old code ignores it)
CREATE TABLE order_tags (
    order_id UUID NOT NULL REFERENCES orders(id),
    tag VARCHAR(100) NOT NULL
);

-- SAFE: Adding an index (doesn't affect data or column structure)
CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders(customer_id);

-- SAFE: Adding a NOT NULL column with a default (old code ignores, new code uses)
ALTER TABLE orders ADD COLUMN priority INTEGER NOT NULL DEFAULT 0;

-- SAFE: Widening a column type (VARCHAR(50) → VARCHAR(100))
ALTER TABLE orders ALTER COLUMN notes TYPE VARCHAR(1000);

Operations that require the expand/contract pattern (cannot be done in one step):

-- UNSAFE: Renaming a column (v1 code uses old name, fails after migration)
-- UNSAFE: Dropping a column (v1 code reads/writes it, fails after migration)
-- UNSAFE: Adding NOT NULL constraint without a default (v1 inserts without the column)
-- UNSAFE: Changing a column's type in a breaking way (VARCHAR → INTEGER)

The Expand/Contract Pattern

Breaking changes are handled in three deployment stages:

Stage 1: Expand — add the new structure alongside the old

-- Renaming "notes" to "customer_notes": Stage 1
ALTER TABLE orders ADD COLUMN customer_notes TEXT;

-- Backfill existing data
UPDATE orders SET customer_notes = notes WHERE customer_notes IS NULL;

-- Application v1.1: writes to BOTH columns, reads from old column

Stage 2: Migrate — update the application to use the new structure

Deploy v2.0: reads from new column, writes to both columns (or new only if v1 is gone)

Stage 3: Contract — remove the old structure (only after v1 is fully decommissioned)

-- After v1 is completely gone from all instances
ALTER TABLE orders DROP COLUMN notes;

Each stage is a separate deployment. The timeline might be:

• Day 1: Stage 1 migration + v1.1 that writes to both columns
• Day 3: Stage 2 — v2.0 reads new column (after confirming v1 is gone)
• Day 10: Stage 3 — drop old column (after confirming v2 is stable)

This feels slow. It is. Zero-downtime schema changes on live databases have this cost.

Flyway: Migration Management

Flyway is the standard Java migration tool. It applies versioned SQL scripts in order and records which have been applied in a flyway_schema_history table.

db/migration/
├── V1__initial_schema.sql
├── V2__add_orders_table.sql
├── V3__add_customer_reference_column.sql
├── V4__add_customer_notes_column.sql       ← Expand stage
├── V5__backfill_customer_notes.sql          ← Backfill (idempotent)
└── V6__drop_notes_column.sql               ← Contract stage (later)

Flyway configuration in Spring Boot:

spring:
  flyway:
    enabled: true
    baseline-on-migrate: false  # Don't baseline on existing schema
    validate-on-migrate: true   # Fail if checksums don't match
    locations: classpath:db/migration

Flyway runs migrations at application startup before the application accepts requests. This means the migration runs before any requests hit the new code — but it also means startup blocks until the migration completes.

For long-running migrations (backfilling millions of rows), blocking startup is not acceptable. Handle this with:

1. Run the backfill as a separate process/job, not in a Flyway migration
2. Use a background migration that runs incrementally after startup
3. Accept longer startup time if your orchestration can handle it

Large Table Migrations

Backfilling a column on a table with millions of rows takes time and locks. Doing it in a single UPDATE statement is dangerous in production.

Batch the backfill:

-- Bad: Locks the entire table for the duration
UPDATE orders SET customer_notes = notes WHERE customer_notes IS NULL;

-- Better: Batch in smaller chunks
DO $$
DECLARE
    batch_size INT := 10000;
    rows_updated INT;
BEGIN
    LOOP
        UPDATE orders SET customer_notes = notes
        WHERE customer_notes IS NULL AND id IN (
            SELECT id FROM orders WHERE customer_notes IS NULL LIMIT batch_size
        );
        GET DIAGNOSTICS rows_updated = ROW_COUNT;
        EXIT WHEN rows_updated = 0;
        PERFORM pg_sleep(0.1); -- Brief pause between batches
    END LOOP;
END;
$$;

Or better: run the backfill as a background job from the application, tracking progress in a separate table.

Adding indexes to large tables also requires special handling:

-- WRONG: Locks the entire table, blocks all reads and writes
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- RIGHT: Creates index concurrently without blocking
CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders(customer_id);

CREATE INDEX CONCURRENTLY takes longer but doesn’t acquire a lock that blocks queries. It should be used for any index on a table receiving production traffic.

Blue-Green Deployments and Migrations

Blue-green deployment with database migrations is particularly tricky: you can’t switch traffic atomically from v1 to v2 if the migration runs before the switch (v1 is still running against the new schema) or after (v2 starts without the migration).

The solution is expand/contract applied at the deployment level:

1. Run the expand migration (backward-compatible change)
2. Deploy v2 to green environment (works against both old and new schema)
3. Switch traffic to green
4. Verify green is healthy
5. Run contract migration (remove old structures) — only after v1 (blue) is confirmed decommissioned

This means the contract migration happens at a later deployment, not during the v2 deployment. It’s a three-deployment process for any breaking schema change.

Versioning Strategies

Some teams use a dual-write period where the application writes to both old and new columns/tables, allowing the migration to proceed while maintaining compatibility. This works for simple transformations but is complex for more significant structural changes.

For multi-tenant systems or systems with separate databases per customer, coordinate migration runs across all databases. Flyway supports running migrations against a database at startup, but running against hundreds of databases requires a separate migration runner.

The Non-Negotiable Test

Every migration should be:

1. Tested against a production data snapshot — schema and data that matches production as closely as possible
2. Verified for estimated runtime — how long does it take against realistic data volumes?
3. Verified for lock behavior — does it acquire locks that block queries?
4. Reversible — can you roll back if something goes wrong?

A migration that works fine against the empty test database and corrupts production data is a real failure mode. Test migrations against realistic data before deploying them.

The discipline of zero-downtime database migrations is one of the most underrated continuous delivery skills. It’s also one of the most valuable — it’s what allows teams to deploy multiple times per day without risking downtime.