Zero-Downtime Schema Migrations: The Expand/Contract Pattern, Online DDL, and Concurrency Locks

In early-stage startups and low-traffic prototypes, modifying a database schema is effortless: a developer writes an ALTER TABLE script, executes it via an ORM migration tool, and the schema updates in milliseconds. But when an enterprise application scales to hundreds of millions of rows and thousands of concurrent transactions per second, that exact same migration script transforms into an existential operational risk capable of taking down an entire production cluster in seconds.

A naive command such as adding a column with a default value, changing a data type, adding a non-null constraint, or building a foreign key acquires an exclusive table lock in PostgreSQL or a metadata lock in MySQL. Because incoming read and write transactions must wait behind the pending lock, database connection pools exhaust in under fifteen seconds, HTTP 500 error rates spike to 100%, and client applications experience an immediate blackout.

Top engineering organizations—including Stripe, GitHub, Shopify, and GitLab—operate on a non-negotiable principle: all database schema mutations must incur zero downtime and zero user-facing latency penalties. In this deep systems guide, we will examine the mechanical realities of relational lock managers, explore the 4-phase Expand/Contract pattern, configure bounded lock timeouts, implement an automated backfill engine with replication-aware throttling, and contrast native online DDL with triggerless ghost-table replicators.

1. The Mechanics of Database Locks and the Head-of-Line Catastrophe

To understand why production migrations cause outages, one must look past the SQL syntax and observe how the database lock manager adjudicates concurrent access to relations. In PostgreSQL, every statement acquires a lock on the table's relation object (a relation lock). These locks range in severity from AccessShareLock (acquired by ordinary SELECT queries) to AccessExclusiveLock (acquired by altering or dropping tables).

The Lock Conflict Matrix

Two lock modes conflict if they cannot be held concurrently on the same relation by different transactions. While multiple transactions can concurrently hold AccessShareLock without friction, an AccessExclusiveLock conflicts with all other lock modes, including innocuous SELECT statements.

The Head-of-Line Blocking Trap

The fatal mistake most teams make is assuming that because an ALTER TABLE ADD COLUMN in modern PostgreSQL takes only 2 milliseconds of execution time, it cannot cause an outage. The danger is not the time it takes to execute; it is the time it takes to acquire the lock.

Consider the following sequence of events on a busy table processing 2,000 queries per second:

1. Time 00:00.000: A slow background analytical query or unindexed search begins executing: SELECT * FROM orders WHERE .... This transaction holds an AccessShareLock and runs for 6 seconds.
2. Time 00:01.000: Your CI/CD deployment pipeline executes an automated migration: ALTER TABLE orders ADD COLUMN loyalty_tier VARCHAR(32);. This statement requests an AccessExclusiveLock. Because Query 1 is still holding AccessShareLock, the DDL cannot acquire the lock and enters the database lock wait queue.
3. Time 00:01.001: 50 new customer HTTP requests arrive, executing routine fast reads: SELECT id, total FROM orders WHERE id = 49201;. In relational lock queues, to prevent writer starvation, lock requests are granted in FIFO order. Because the DDL is waiting for an exclusive lock, the lock manager forces all subsequent SELECT queries to wait behind the DDL!
4. Time 00:04.000: Within 3 seconds, 600 web application requests are waiting in the PostgreSQL process table. The application's database connection pool (e.g., HikariCP or Npgsql) reaches maximum capacity (100/100 active connections).
5. Time 00:05.000: Web servers run out of available threads. Reverse proxies (Nginx, Cloudflare) begin issuing HTTP 504 Gateway Timeouts. Health checks fail, orchestrators restart containers, and a full catastrophic cascading failure ensues.

2. The Universal Golden Rule: Bounded Lock Timeouts

Every single automated database migration script executed in production must be wrapped with a strictly bounded lock timeout. In PostgreSQL, this is configured via the lock_timeout session parameter:

-- Enforce a 2-second lock acquisition ceiling
SET lock_timeout = '2s';

-- Enforce a 10-second statement execution ceiling
SET statement_timeout = '10s';

-- Execute the schema alteration safely
ALTER TABLE orders ADD COLUMN loyalty_tier VARCHAR(32);

When lock_timeout is set, if the DDL cannot acquire the necessary table lock within 2,000 milliseconds, PostgreSQL immediately aborts the transaction and releases all waiting locks with error code 55P03 (lock_not_available). The migration fails fast, the web traffic waiting in line is never frozen, and the application experiences zero downtime.

Your deployment orchestrator can then catch the lock failure, sleep for 5 seconds with exponential backoff and jitter, and retry the migration when transaction density drops.

3. The 4-Phase Expand/Contract (Parallel Run) Architecture

The Expand/Contract pattern (also known as the Parallel Run pattern) decouples database schema changes from application deployments. Instead of attempting to alter, backfill, and switch an active database column in a single monolithic transaction, the migration is decomposed into four distinct, backward-compatible phases.

Phase 1: Expand (Additive Schema Mutation & Dual-Writing)

In the Expand phase, the database schema is augmented with the new column or table. The modification must be strictly additive and backward-compatible with the currently running application code.

Crucially, the column must be created as nullable (or with a constant default in PostgreSQL 11+ which stores defaults in the system catalog without rewriting rows):

-- Phase 1 Migration Script
SET lock_timeout = '2s';

-- Safe in PostgreSQL 11+ (stored in pg_attribute, 0 row rewrites)
ALTER TABLE users ADD COLUMN phone_e164 VARCHAR(20) DEFAULT NULL;

Immediately following the schema update, Application Version 1 is deployed. This code version continues reading from the old column (e.g., raw_phone), but begins dual-writing to both the old column and the new column (phone_e164) on all new INSERT and UPDATE operations. This guarantees that all incoming data is pristine from this timestamp onward.

Phase 2: Asynchronous Batched Backfill with Replication Throttling

With dual-writing established, historical records must be migrated. Attempting to backfill 50 million rows with a single query like UPDATE users SET phone_e164 = normalize(raw_phone); will lock thousands of disk pages, generate gigabytes of write-ahead log (WAL) records, saturate disk I/O, and cause replica databases to fall minutes behind the primary (replication lag).

The solution is an asynchronous batch worker that processes records in bounded, cursor-based chunks using primary key ranges:

-- Bounded cursor backfill query
UPDATE users 
SET phone_e164 = normalize_phone(raw_phone)
WHERE id > @last_processed_id AND id <= @batch_end_id
  AND phone_e164 IS NULL;

The worker must actively sample replication lag before each batch. If replica lag exceeds a safe threshold (e.g., 1,500 milliseconds), the worker halts and sleeps until the replicas catch up.

Phase 3: Read Switching, Dual-Writes, and Safe Constraints

Once 100% of historical records have been backfilled, you deploy Application Version 2. This version switches all read queries to use the new column (phone_e164) while maintaining dual-writes to the old column as an operational safety valve. If an unforeseen bug emerges in production, you can roll back to Version 1 immediately without data loss.

If the new column requires a NOT NULL constraint, adding it directly via ALTER TABLE users ALTER COLUMN phone_e164 SET NOT NULL; in PostgreSQL would require scanning the entire table while holding an exclusive lock. Instead, use a two-step Check Constraint approach:

-- Step 1: Add check constraint without validating historical rows (sub-millisecond lock)
SET lock_timeout = '2s';
ALTER TABLE users ADD CONSTRAINT check_phone_not_null 
  CHECK (phone_e164 IS NOT NULL) NOT VALID;

-- Step 2: Validate constraint concurrently (only acquires ShareUpdateExclusiveLock, writes proceed!)
ALTER TABLE users VALIDATE CONSTRAINT check_phone_not_null;

Phase 4: Contract (Deprecation Cleanup and Column Dropping)

After Application Version 2 has operated stably under peak production traffic for several days, you deploy Application Version 3. This version stops writing to the old column completely.

Once all older container instances have fully terminated and no active transactions reference the old column, you execute the final contract script:

-- Phase 4 Contract Script
SET lock_timeout = '2s';
ALTER TABLE users DROP COLUMN raw_phone;

4. Native Online DDL vs. Triggerless Ghost Tables (gh-ost)

While PostgreSQL supports rich online operations (such as CREATE INDEX CONCURRENTLY and VALIDATE CONSTRAINT), MySQL and MariaDB have historically suffered from severe InnoDB table-copy lockouts during structural alterations.

To solve this, large-scale engineering platforms utilize Ghost Table Engines. The two industry standards are Percona's pt-online-schema-change and GitHub's open-source gh-ost.

Why gh-ost outperforms trigger-based approaches: Trigger-based tools hook into every INSERT, UPDATE, and DELETE executed by web applications. Under high concurrency, these synchronous triggers create distributed lock contention and can cause lock timeouts on the main application. gh-ost bypasses application transactions entirely by reading MySQL's binary replication log (binlog) asynchronously as an external replica, converting changes into idempotent statements on the ghost table.

5. Production-Ready Batch Backfill Worker Implementation

Below is a production-grade C# worker service utilizing modern asynchronous streams, cursor pagination, and database metrics verification to safely backfill millions of records:

using System;
using System.Data;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;

public sealed class DatabaseBackfillWorker : BackgroundService
{
    private readonly string _connectionString;
    private readonly ILogger<DatabaseBackfillWorker> _logger;
    private const int BatchSize = 2500;
    private const int ThrottleDelayMs = 150;
    private const double MaxReplicaLagSeconds = 1.2;

    public DatabaseBackfillWorker(string connectionString, ILogger<DatabaseBackfillWorker> logger)
    {
        _connectionString = connectionString;
        _logger = logger;
    }

    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        long lastProcessedId = 0;
        long totalMigrated = 0;

        _logger.LogInformation("Starting zero-downtime backfill worker. BatchSize: {Size}", BatchSize);

        while (!stoppingToken.IsCancellationRequested)
        {
            // 1. Throttle if replica replication lag exceeds safety threshold
            await WaitForReplicaHealthAsync(stoppingToken);

            using var conn = new SqlConnection(_connectionString);
            await conn.OpenAsync(stoppingToken);

            using var cmd = conn.CreateCommand();
            cmd.CommandTimeout = 30; // Strict statement timeout
            cmd.CommandText = @"
                WITH TargetRows AS (
                    SELECT TOP (@BatchSize) Id, LegacyPhone
                    FROM Users WITH (NOLOCK)
                    WHERE Id > @LastId AND NormalizedPhone IS NULL
                    ORDER BY Id ASC
                )
                UPDATE u
                SET u.NormalizedPhone = dbo.NormalizeE164(t.LegacyPhone),
                    u.UpdatedAt = SYSUTCDATETIME()
                OUTPUT inserted.Id
                FROM Users u
                INNER JOIN TargetRows t ON u.Id = t.Id;";

            cmd.Parameters.Add("@BatchSize", SqlDbType.Int).Value = BatchSize;
            cmd.Parameters.Add("@LastId", SqlDbType.BigInt).Value = lastProcessedId;

            long highestBatchId = lastProcessedId;
            int rowsUpdated = 0;

            using (var reader = await cmd.ExecuteReaderAsync(stoppingToken))
            {
                while (await reader.ReadAsync(stoppingToken))
                {
                    long currentId = reader.GetInt64(0);
                    if (currentId > highestBatchId) highestBatchId = currentId;
                    rowsUpdated++;
                }
            }

            if (rowsUpdated == 0)
            {
                _logger.LogInformation("Backfill complete! Total rows migrated: {Total}", totalMigrated);
                break;
            }

            totalMigrated += rowsUpdated;
            lastProcessedId = highestBatchId;

            _logger.LogInformation("Migrated batch of {Count} rows. LastId: {LastId}, Total: {Total}", 
                rowsUpdated, lastProcessedId, totalMigrated);

            // Yield CPU and allow database disk I/O buffers to flush
            await Task.Delay(ThrottleDelayMs, stoppingToken);
        }
    }

    private async Task WaitForReplicaHealthAsync(CancellationToken token)
    {
        while (!token.IsCancellationRequested)
        {
            double lag = await CheckReplicationLagSecondsAsync(token);
            if (lag < MaxReplicaLagSeconds) return;

            _logger.LogWarning("Replication lag is {Lag}s (exceeds {Max}s). Throttling backfill...", 
                lag, MaxReplicaLagSeconds);
            await Task.Delay(2000, token);
        }
    }

    private async Task<double> CheckReplicationLagSecondsAsync(CancellationToken token)
    {
        // Query sys.dm_hadr_database_replica_states or PostgreSQL pg_stat_replication
        return 0.2; // Nominal healthy replica lag
    }
}

6. The Zero-Downtime Migration Readiness Rubric

Before any schema migration script is approved in your deployment pipeline, verify it against this operational rubric:

1. Is lock_timeout explicitly set? Every DDL transaction must fail after 2 seconds rather than queueing up and blocking read traffic.
2. Are new columns nullable or using non-rewrite defaults? Never add a column with an unindexed dynamic default expression on tables with more than 100,000 rows.
3. Are indexes built concurrently? In PostgreSQL, always use CREATE INDEX CONCURRENTLY; in MySQL, ensure ALGORITHM=INPLACE, LOCK=NONE is supported.
4. Is backfill volume separated from schema DDL? Never update rows inside the DDL migration transaction. Schema changes alter definitions; background workers backfill data.
5. Are check constraints validated in two steps? Add constraints with NOT VALID first, then validate them asynchronously with VALIDATE CONSTRAINT.
6. Is the code dual-writing before backfill begins? If code does not write to both columns, concurrent user updates will overwrite backfilled data, causing data divergence.