Database Migration Patterns for Zero-Downtime Deployments

Database migrations are the riskiest part of most deployments. A careless ALTER TABLE can lock a table for minutes, a column rename can break every running instance, and a failed migration can leave your schema in an inconsistent state. This guide covers patterns that let you change your database schema without downtime.

The fundamental problem

In a zero-downtime deployment, old and new versions of your application run simultaneously during the rollout. If the new version expects a column that does not exist yet, or the old version writes to a column that has been removed, you get errors.

The rule is simple: your database schema must be compatible with both the old and new application versions at all times during the rollout.

The expand-contract pattern

This is the core pattern for zero-downtime migrations. Every breaking schema change is split into three phases:

1. Expand: Add the new structure alongside the old one. Both versions work.
2. Migrate: Move data from the old structure to the new one. Both versions work.
3. Contract: Remove the old structure. Only the new version is running.

Example: renaming a column

You cannot simply run ALTER TABLE users RENAME COLUMN name TO full_name. The old application code references name and would break.

Step 1 - Expand (deploy 1):

-- add the new column
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);

-- backfill existing data
UPDATE users SET full_name = name WHERE full_name IS NULL;

-- add a trigger to keep them in sync during transition
CREATE OR REPLACE FUNCTION sync_user_name() RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
        IF NEW.full_name IS NULL AND NEW.name IS NOT NULL THEN
            NEW.full_name := NEW.name;
        ELSIF NEW.name IS NULL AND NEW.full_name IS NOT NULL THEN
            NEW.name := NEW.full_name;
        END IF;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_sync_user_name
    BEFORE INSERT OR UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION sync_user_name();

Step 2 - Migrate (deploy 2):

Update application code to read from and write to full_name. The old name column is still populated by the trigger, so any old instances still running are fine.

Step 3 - Contract (deploy 3):

Once all instances are on the new code and you have verified everything works:

DROP TRIGGER trg_sync_user_name ON users;
DROP FUNCTION sync_user_name();
ALTER TABLE users DROP COLUMN name;

Safe vs unsafe schema changes

Safe changes (single-step)

These can be applied directly without multi-step rollouts:

Change	Why it is safe
Add a nullable column	Old code ignores it
Add a new table	Old code does not reference it
Add an index (CONCURRENTLY)	Does not lock the table
Add a new enum value	Old code does not use it
Increase a VARCHAR length	No data loss, backward compatible

Unsafe changes (require expand-contract)

Change	Why it is dangerous
Rename a column	Old code references the old name
Remove a column	Old code writes to it
Change a column type	May cause cast errors
Add a NOT NULL constraint	Old code may insert NULLs
Rename a table	Old code references the old name

Adding a NOT NULL constraint safely

You cannot add NOT NULL directly if old code inserts rows without the new column. Here is the safe approach:

-- Step 1: add the column as nullable with a default
ALTER TABLE orders ADD COLUMN status VARCHAR(20) DEFAULT 'pending';

-- Step 2 (after deploying code that always sets status):
-- add a CHECK constraint first (non-blocking in Postgres)
ALTER TABLE orders ADD CONSTRAINT orders_status_not_null
    CHECK (status IS NOT NULL) NOT VALID;

-- Step 3: validate the constraint (scans the table but does not lock writes)
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_not_null;

-- Step 4: now safely add the NOT NULL constraint
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_status_not_null;

Backfilling data without locking

Large backfills should run in batches to avoid holding locks and overwhelming the database:

import time

def backfill_full_name(connection, batch_size=1000):
    """Backfill full_name from name column in batches."""
    while True:
        cursor = connection.cursor()
        cursor.execute("""
            UPDATE users
            SET full_name = name
            WHERE id IN (
                SELECT id FROM users
                WHERE full_name IS NULL AND name IS NOT NULL
                LIMIT %s
                FOR UPDATE SKIP LOCKED
            )
            RETURNING id
        """, (batch_size,))

        updated = cursor.rowcount
        connection.commit()

        if updated == 0:
            break

        print(f"Updated {updated} rows")
        time.sleep(0.1)  # brief pause to reduce load

Key points:

• LIMIT controls the batch size.
• FOR UPDATE SKIP LOCKED avoids blocking other writers.
• The sleep between batches prevents the backfill from starving normal traffic.

Index creation

Creating an index locks the table for writes in most databases. Postgres supports concurrent index creation:

-- BAD: locks the table until the index is built
CREATE INDEX idx_users_email ON users(email);

-- GOOD: does not lock the table (takes longer but no downtime)
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

If CREATE INDEX CONCURRENTLY fails, it leaves an invalid index. Check and clean up:

-- check for invalid indexes
SELECT indexrelid::regclass, indisvalid
FROM pg_index WHERE NOT indisvalid;

-- drop the invalid index and retry
DROP INDEX CONCURRENTLY idx_users_email;
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

Migration tooling

Use a migration tool to track and apply changes. Popular options:

# Flyway (Java ecosystem)
flyway migrate

# Alembic (Python/SQLAlchemy)
alembic upgrade head

# Knex (Node.js)
npx knex migrate:latest

# golang-migrate
migrate -database postgres://... -path ./migrations up

Migration file structure

migrations/
  V001__create_users_table.sql
  V002__add_email_to_users.sql
  V003__add_full_name_column.sql      # expand
  V004__backfill_full_name.sql         # migrate
  V005__drop_name_column.sql           # contract (deployed separately)

Rollback strategies

Forward-only migrations

Some teams never roll back migrations. Instead, they fix issues with new migrations. This is simpler and avoids the complexity of writing rollback scripts for every change.

Reversible migrations

If you need rollbacks, write explicit down migrations:

-- V003_up: add column
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);

-- V003_down: remove column
ALTER TABLE users DROP COLUMN IF EXISTS full_name;

The problem is that down migrations for data changes (backfills, type conversions) are often lossy. You cannot un-backfill data.

Recommended approach

1. Keep expand and contract steps in separate deployments.
2. Deploy the expand step first. If something goes wrong, the new column is harmless.
3. Deploy the application change. If it fails, roll back the application. The schema still works with the old code.
4. Deploy the contract step only after the application change is stable.

Summary

Zero-downtime migrations come down to one principle: never make a schema change that is incompatible with the currently running code. Use the expand-contract pattern for breaking changes, batch your backfills, create indexes concurrently, and keep expand and contract steps in separate deployments. The extra steps slow you down slightly, but they eliminate the 3 AM pages caused by a locked table during a production migration.