Zero-Downtime Database Schema Migrations

As digital applications achieve continuous delivery, performing scheduled maintenance windows to run SQL migrations is no longer acceptable. Executing a naive ALTER TABLE statement on a table with tens of millions of rows acquires an Exclusive Access Lock (AccessExclusiveLock), blocking all concurrent SELECT, INSERT, UPDATE, and DELETE operations.

If the migration takes 30 seconds to rewrite table pages, incoming application queries stack up, connection pools exhaust, and user-facing HTTP 500 errors surge. In this engineering guide, we explore the industry-standard Expand-Contract Pattern (Parallel Writing), safe DDL guidelines, and Postgres lock timeout strategies for executing zero-downtime migrations.

1. The Expand-Contract (Parallel Write) Migration Pattern

When restructuring a database schema (such as splitting a column, renaming a field, or changing data types), the migration must be broken down into phased deployments rather than executing a single atomic script:

2. Step-by-Step Implementation Example

Suppose we need to rename a column phone_number to phone_e164 and reformat data into E.164 international format without taking down production services.

Step 1: Add New Nullable Column & Sync Triggers (Expand)

First, execute DDL to add the new column without a default constraint, and install a temporary synchronization trigger:

-- Set tight lock timeouts to prevent queue blocking if locks cannot be acquired immediately
SET lock_timeout = '3s';

-- Step 1.1: Add new column (instant operation in PostgreSQL 11+)
ALTER TABLE user_profiles ADD COLUMN phone_e164 VARCHAR(32);

-- Step 1.2: Create trigger function to maintain dual-write parity
CREATE OR REPLACE FUNCTION sync_user_profiles_phone()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.phone_e164 IS NULL AND NEW.phone_number IS NOT NULL THEN
        NEW.phone_e164 := NEW.phone_number;
    ELSIF NEW.phone_number IS NULL AND NEW.phone_e164 IS NOT NULL THEN
        NEW.phone_number := NEW.phone_e164;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_sync_user_profiles_phone
BEFORE INSERT OR UPDATE ON user_profiles
FOR EACH ROW EXECUTE FUNCTION sync_user_profiles_phone();

Step 2: Backfill Historical Data in Batches

Updating millions of rows in a single UPDATE user_profiles SET phone_e164 = phone_number statement locks the entire table and fills WAL logs. Instead, backfill data in micro-batches (e.g. 5,000 rows per batch) with short sleep intervals:

-- Python/SQL script for batched background migration
DO $$
DECLARE
    rows_updated INT := 0;
    batch_size INT := 5000;
BEGIN
    LOOP
        WITH target_rows AS (
            SELECT id FROM user_profiles
            WHERE phone_e164 IS NULL AND phone_number IS NOT NULL
            LIMIT batch_size
            FOR UPDATE SKIP LOCKED
        )
        UPDATE user_profiles u
        SET phone_e164 = u.phone_number
        FROM target_rows t
        WHERE u.id = t.id;

        GET DIAGNOSTICS rows_updated = ROW_COUNT;
        EXIT WHEN rows_updated = 0;
        
        -- Pause to allow application queries priority access
        PERFORM pg_sleep(0.05);
    END LOOP;
END $$;

Step 3: Enforce NOT NULL Constraints Safely

Adding a standard ALTER TABLE user_profiles ALTER COLUMN phone_e164 SET NOT NULL; causes PostgreSQL to scan every row while holding a full table lock. To avoid this, validate the constraint in two steps:

-- Step 3.1: Add CHECK constraint as NOT VALID (instant lock release)
ALTER TABLE user_profiles 
ADD CONSTRAINT chk_phone_e164_not_null 
CHECK (phone_e164 IS NOT NULL) NOT VALID;

-- Step 3.2: Validate existing rows concurrently without blocking writes
ALTER TABLE user_profiles VALIDATE CONSTRAINT chk_phone_e164_not_null;

Step 4: Drop Triggers and Legacy Column (Contract)

Once all application servers are deployed and reading from phone_e164, remove the legacy sync trigger and drop the old column:

DROP TRIGGER IF EXISTS trg_sync_user_profiles_phone ON user_profiles;
DROP FUNCTION IF EXISTS sync_user_profiles_phone();
ALTER TABLE user_profiles DROP COLUMN phone_number;

3. Adding Indexes Without Blocking Writes

Executing CREATE INDEX blocks all write operations until the entire index is built. In production databases, always use CREATE INDEX CONCURRENTLY. PostgreSQL builds the index by performing two passes over the table without acquiring write-blocking locks.

-- Safe concurrent index creation for live production tables
CREATE INDEX CONCURRENTLY idx_user_profiles_phone_e164 
ON user_profiles (phone_e164);

Conclusion

Zero-downtime database migrations require shifting from monolithic DDL scripts to decoupled Expand-Contract workflows. By combining batched updates, non-valid constraints, CONCURRENTLY index generation, and strict lock timeouts, development teams can evolve database architectures while maintaining 99.99% system availability.