Database

Schema Migration Safety: Testing Zero-Downtime Database Changes

HelpMeTest

HelpMeTest

— 7 min read
Schema Migration Safety: Testing Zero-Downtime Database Changes
  • Use the expand/contract pattern: never rename or drop in a single migration
  • Test backward compatibility by running old app code against the new schema before cutover
  • Shadow tables let you verify data integrity without touching production reads/writes
  • Index migrations must be tested for lock acquisition time, not just correctness
  • CI gates should run migration + rollback on a production-sized dataset clone
  • Blue-green DB migrations require both schema versions to serve live traffic simultaneously

Database migrations are where deployments go to die. The migration script works perfectly in staging, you push it to production, and within seconds you have a cascade of column "email_address" does not exist errors because the old app pods are still running. Zero-downtime schema changes require a testing discipline most teams skip until they're reading a post-mortem.

Why Schema Migrations Fail in Production

The core problem is time. Between the moment your migration runs and the moment all app instances are updated, two versions of your application share one database. Any migration that changes or removes something the old code expects will cause failures during that window.

Three root causes cover most production incidents:

  1. Column renames done in one step — old code writes to email, new code expects email_address, migration renamed it. Old pods fail immediately.
  2. Non-concurrent index creation — CREATE INDEX on PostgreSQL locks the table. CREATE INDEX CONCURRENTLY doesn't, but takes longer and can fail partway through.
  3. NOT NULL constraints added without defaults — old code inserts a row without the new column, constraint rejects it.

All three are preventable with the right migration pattern and the right tests.

The Expand/Contract Pattern

Expand/contract (also called parallel change) solves backward compatibility by splitting every breaking change into three phases:

Phase 1: Expand — add the new thing without removing the old thing. Phase 2: Migrate — backfill data, update app code to write to both old and new. Phase 3: Contract — remove the old thing once no code references it.

For a column rename from email to email_address:

-- Phase 1: Expand
ALTER TABLE users ADD COLUMN email_address VARCHAR(255);
UPDATE users SET email_address = email;

-- App now writes to BOTH columns
-- Trigger keeps them in sync:
CREATE OR REPLACE FUNCTION sync_email_columns()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.email IS DISTINCT FROM OLD.email THEN
    NEW.email_address := NEW.email;
  END IF;
  IF NEW.email_address IS DISTINCT FROM OLD.email_address THEN
    NEW.email := NEW.email_address;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER sync_email
BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION sync_email_columns();
-- Phase 3: Contract (separate deployment, weeks later)
DROP TRIGGER sync_email ON users;
ALTER TABLE users DROP COLUMN email;

Testing Each Phase

Each phase needs its own test suite:

# test_phase1_expand.py
def test_old_app_code_still_works_after_expand(db):
    """Old code only writes to 'email' — both columns must be populated."""
    db.execute("INSERT INTO users (email) VALUES ('test@example.com')")
    row = db.fetchone("SELECT email, email_address FROM users WHERE email = 'test@example.com'")
    assert row['email'] == 'test@example.com'
    assert row['email_address'] == 'test@example.com'  # sync trigger fired

def test_new_app_code_works_after_expand(db):
    """New code writes to 'email_address' — old column must also be populated."""
    db.execute("INSERT INTO users (email_address) VALUES ('new@example.com')")
    row = db.fetchone("SELECT email, email_address FROM users WHERE email_address = 'new@example.com'")
    assert row['email'] == 'new@example.com'
    assert row['email_address'] == 'new@example.com'
# test_phase3_contract.py
def test_old_column_gone(db):
    """After contract, old column must not exist."""
    columns = db.fetchall(
        "SELECT column_name FROM information_schema.columns "
        "WHERE table_name = 'users'"
    )
    column_names = [c['column_name'] for c in columns]
    assert 'email' not in column_names
    assert 'email_address' in column_names

Shadow Tables for Data Integrity Testing

A shadow table is a parallel copy of your production table that receives the same writes during migration. It lets you verify that your migration produces correct data without touching production reads.

-- Create shadow table with new schema
CREATE TABLE users_v2 (
    id BIGINT PRIMARY KEY,
    email_address VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Dual-write trigger
CREATE OR REPLACE FUNCTION dual_write_users()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO users_v2 (id, email_address, created_at)
  VALUES (NEW.id, NEW.email, NEW.created_at)
  ON CONFLICT (id) DO UPDATE
    SET email_address = EXCLUDED.email_address;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_dual_write
AFTER INSERT OR UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION dual_write_users();

Run your shadow table validation as a scheduled job during the migration window:

def validate_shadow_table(db):
    """Verify shadow table matches production for all rows written since migration start."""
    result = db.fetchone("""
        SELECT COUNT(*) as mismatches
        FROM users u
        LEFT JOIN users_v2 u2 ON u.id = u2.id
        WHERE u2.id IS NULL
           OR u.email != u2.email_address
    """)
    assert result['mismatches'] == 0, f"{result['mismatches']} rows diverged between shadow and production"

Testing Index Migrations

Index creation is one of the most dangerous migration operations. A non-concurrent index on a 500M-row table can lock writes for minutes.

What to Test

Lock acquisition: Does the index creation acquire the right lock type? CREATE INDEX CONCURRENTLY takes only a ShareUpdateExclusiveLock, which doesn't block reads or writes.

-- Test in a transaction (will show lock type, then rollback)
BEGIN;
EXPLAIN (ANALYZE, BUFFERS) CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
ROLLBACK;

Estimated duration on production data size: Test on a row count that matches production. A 10K-row staging table tells you nothing about a 100M-row production table.

# Script to estimate index build time from pg_stat_progress_create_index
psql -c "
SELECT
  phase,
  blocks_done,
  blocks_total,
  ROUND(blocks_done::numeric / NULLIF(blocks_total, 0) * 100, 2) AS pct_complete,
  EXTRACT(EPOCH FROM (NOW() - start_time)) AS elapsed_seconds
FROM pg_stat_progress_create_index;
"

Validity after concurrent build: CREATE INDEX CONCURRENTLY can finish in an INVALID state if a transaction was open during the build.

def test_index_is_valid(db):
    index_name = 'idx_users_email'
    row = db.fetchone("""
        SELECT indisvalid
        FROM pg_index pi
        JOIN pg_class pc ON pc.oid = pi.indexrelid
        WHERE pc.relname = %s
    """, [index_name])
    assert row is not None, f"Index {index_name} does not exist"
    assert row['indisvalid'], f"Index {index_name} exists but is INVALID"

CI Gate for Index Migrations

Your CI pipeline should refuse to proceed if an index build leaves an invalid index:

# .github/workflows/migrate.yml
- name: Run migrations
  run: flyway migrate

- name: Validate indexes
  run: |
    python scripts/validate_indexes.py
  env:
    DATABASE_URL: ${{ secrets.STAGING_DB_URL }}
# scripts/validate_indexes.py
import sys
import psycopg2

conn = psycopg2.connect(os.environ['DATABASE_URL'])
cur = conn.cursor()
cur.execute("""
    SELECT pc.relname AS index_name, pt.relname AS table_name
    FROM pg_index pi
    JOIN pg_class pc ON pc.oid = pi.indexrelid
    JOIN pg_class pt ON pt.oid = pi.indrelid
    WHERE NOT pi.indisvalid
      AND pt.relnamespace = 'public'::regnamespace
""")
invalid = cur.fetchall()
if invalid:
    for idx, tbl in invalid:
        print(f"INVALID INDEX: {idx} on {tbl}")
    sys.exit(1)
print("All indexes valid.")

Testing Blue-Green Database Migrations

Blue-green deployments for databases are harder than for stateless services. You have two app stacks (blue and green) but typically one database. The goal: migrate the schema so both stacks can run simultaneously, then cut over traffic.

The Test Matrix

For a schema change, you need to verify four combinations:

App Version Schema Version Expected Result
Old (blue) Old Works (baseline)
Old (blue) New Must still work
New (green) Old Must still work
New (green) New Works (target) 
# Parameterized test covering all four combinations
import pytest

@pytest.mark.parametrize("app_version,schema_version", [
    ("old", "old"),
    ("old", "new"),
    ("new", "old"),
    ("new", "new"),
])
def test_app_schema_compatibility(app_version, schema_version, db_factory, app_factory):
    db = db_factory(schema_version=schema_version)
    app = app_factory(version=app_version, db=db)

    # Exercise the critical path
    user_id = app.create_user(email="test@example.com")
    user = app.get_user(user_id)
    assert user['email'] == "test@example.com"
    app.update_user(user_id, email="updated@example.com")
    updated = app.get_user(user_id)
    assert updated['email'] == "updated@example.com"

Maintaining Compatibility During Cutover

Use database feature flags to control which schema version is "active":

CREATE TABLE schema_flags (
    flag_name VARCHAR(100) PRIMARY KEY,
    enabled BOOLEAN DEFAULT FALSE,
    enabled_at TIMESTAMPTZ
);

INSERT INTO schema_flags (flag_name, enabled) VALUES ('use_email_address_column', FALSE);
def get_user_email_column(db):
    flag = db.fetchone(
        "SELECT enabled FROM schema_flags WHERE flag_name = 'use_email_address_column'"
    )
    return 'email_address' if flag and flag['enabled'] else 'email'

This lets you flip the column preference independently of the deployment.

Column Rename Safety Checklist

Before executing a column rename in any environment:

  • Expand phase deployed and verified in staging
  • Sync trigger tested: write to old column → new column populated, and vice versa
  • All application queries updated to write to both columns
  • Backfill script executed and row count verified (backfill count == total rows)
  • New column has same NOT NULL constraint as old (or explicit nullable decision made)
  • Contract phase scheduled for minimum one full deployment cycle after expand
  • Contract phase tested in staging with old code undeployed

CI Pipeline Design for Migration Testing

A complete migration CI pipeline has four stages:

migration-ci:
  stages:
    - restore        # Restore production snapshot to ephemeral DB
    - migrate        # Run pending migrations
    - validate       # Schema assertions + index validity
    - regression     # Run app integration tests against migrated DB
    - rollback-test  # Run rollback script, verify schema restored

Stage 1: Restore from production snapshot. Testing against a 10-row seed database is useless. Production snapshots reveal lock contention, constraint violations on real data, and performance regressions. Anonymize PII before restoring:

pg_restore --no-privileges --no-owner -d ci_migration_db production_snapshot.dump
python scripts/anonymize_pii.py --db ci_migration_db

Stage 2: Run migrations with timing. Record how long each migration takes. If a migration that took 200ms now takes 45 seconds on production-sized data, that's a CI failure.

time flyway migrate 2>&1 | tee migration.log
python scripts/check_migration_timing.py migration.log --max-seconds 30

Stage 3: Schema assertions. Verify the final schema matches your expected state, not just that the migration ran without errors:

def test_expected_schema_state(db):
    # Verify columns
    assert column_exists(db, 'users', 'email_address')
    assert not column_exists(db, 'users', 'email')

    # Verify constraints
    assert constraint_exists(db, 'users', 'users_email_address_not_null')

    # Verify indexes
    assert index_exists(db, 'idx_users_email_address')
    assert index_is_valid(db, 'idx_users_email_address')

Stage 4: Rollback test. Run the down migration. Verify the schema returns to its prior state. This is covered in depth in the rollback testing post, but at minimum: run it, check no errors, verify old column is back.

What "Done" Looks Like

A migration is tested and safe when:

  1. Expand phase runs clean on a production-sized dataset clone
  2. Old app + new schema integration tests pass (no errors during the cutover window)
  3. New app + new schema integration tests pass (target state works)
  4. Index validity check passes post-migration
  5. Migration timing is within acceptable bounds for your maintenance window (or no window needed for concurrent operations)
  6. Rollback executes cleanly and restores the prior schema state

Skip any of these and you're gambling with production. The expand/contract pattern is slower than a rename-in-one-step, but the slowness is the safety. The testing overhead is real, but it's an order of magnitude cheaper than a production incident during peak traffic.

Read more

Start now free