Zero-Downtime Database Migrations: The Expand/Contract Pattern with PostgreSQL

In the early stages of a web application, database schema migrations are trivial. A developer runs `prisma migrate dev` or `knex migrate:latest`, an exclusive table lock is acquired for a few milliseconds, and the deployment succeeds without anyone noticing. But as an application scales to thousands of concurrent users, active background workers, and multi-gigabyte tables, running naive DDL statements directly against production is one of the fastest ways to trigger an unexpected outage.

Every senior backend engineer has witnessed the catastrophe: an ORM auto-generates an `ALTER TABLE orders ADD COLUMN status_code VARCHAR(32) NOT NULL DEFAULT 'pending'` migration. On a table with 20 million rows, PostgreSQL must rewrite the table or validate constraints under an `ACCESS EXCLUSIVE` lock. Even if the operation takes only 4 seconds, all incoming read and write transactions queue up behind the DDL lock. Within seconds, backend connection pools saturate, web servers exhaust memory, and users face cascading 504 Gateway Timeouts.

The Expand/Contract (Parallel Run) Architectural Pattern#

To eliminate maintenance windows and release code continuously, enterprise software architectures rely on the Expand/Contract pattern (also known as the Parallel Run pattern). Instead of attempting an instantaneous, breaking schema transformation, the migration is decomposed into five backward-compatible phases spanning multiple zero-downtime application deployments.

• Phase 1 (Expand): Introduce the new column, table, or relation in the database as nullable or unconstrained. Existing application versions continue functioning normally.
• Phase 2 (Dual-Writing): Deploy application code that reads from the old column but writes simultaneously to both the old and new schema fields. New records are immediately synchronized.
• Phase 3 (Backfill): Run an idempotent, throttled background worker to backfill historical records from the old column to the new column in small, bounded batches.
• Phase 4 (Read Cutover): Verify data consistency, then deploy application code that shifts all read queries to the new schema while continuing to write to both (or stopping writes to the old column).
• Phase 5 (Contract): Remove all references to the old column in application code, and in a final release, safely drop the deprecated column and its legacy triggers.

Safe DDL Execution: Lock Timeouts and Statement Timeouts#

When executing schema changes on PostgreSQL, you must never allow a migration runner to wait indefinitely for a lock. If a long-running reporting query is scanning the target table, your DDL will hang waiting for an exclusive lock, blocking all incoming web traffic.

Always wrap migration scripts with explicit session-level lock timeouts. If the migration cannot acquire the required lock within two seconds, it aborts immediately, releases its lock request, and lets production traffic continue uninterrupted.

-- Always enforce strict lock and statement timeouts before DDL
SET lock_timeout = '2s';
SET statement_timeout = '5s';

-- Expand Phase: Add new column as NULLABLE without table rewrites
ALTER TABLE users ADD COLUMN phone_number_e164 VARCHAR(20);

-- Reset timeouts back to cluster defaults
RESET lock_timeout;
RESET statement_timeout;

Adding Indexes Without Freezing Writes#

A standard `CREATE INDEX` command acquires a `SHARE` lock on the target table, blocking all concurrent `INSERT`, `UPDATE`, and `DELETE` operations until the entire index is built. On high-volume tables, this locks out write traffic for minutes or hours.

In production PostgreSQL environments, indexes must always be created concurrently using `CREATE INDEX CONCURRENTLY`. This builds the index across two separate table scans without blocking writes. Note that `CONCURRENTLY` cannot run inside a multi-statement transaction block.

-- Non-blocking index creation outside a transaction block
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created 
ON orders (customer_id, created_at DESC);

-- If the concurrent build fails due to a deadlock or timeout, 
-- clean up the invalid index stub before retrying:
-- DROP INDEX CONCURRENTLY IF EXISTS idx_orders_customer_created;

Throttled Batch Backfilling in Production#

Once the new schema exists and dual-writing is enabled, millions of historical rows must be backfilled. Running a single `UPDATE users SET phone_number_e164 = normalize(phone);` will lock hundreds of thousands of row pages, generate massive write-ahead log (WAL) replication lag, and potentially bloat the table.

The correct approach is a chunked, keyset-paginated backfill worker that processes a bounded number of rows per iteration with a mandatory sleep pause between batches:

// TypeScript/Node.js resilient batch backfill runner
async function backfillUserPhoneNumbers(pool: Pool, batchSize = 1000) {
  let lastProcessedId = 0;
  let hasMore = true;

  while (hasMore) {
    const result = await pool.query(`
      WITH batch AS (
        SELECT id, raw_phone 
        FROM users 
        WHERE id > $1 AND phone_number_e164 IS NULL 
        ORDER BY id ASC 
        LIMIT $2
      )
      UPDATE users u
      SET phone_number_e164 = format_e164(batch.raw_phone)
      FROM batch
      WHERE u.id = batch.id
      RETURNING u.id;
    `, [lastProcessedId, batchSize]);

    if (result.rows.length === 0) {
      hasMore = false;
      break;
    }

    lastProcessedId = result.rows[result.rows.length - 1].id;
    console.log(`Processed batch up to user ID ${lastProcessedId}`);

    // Yield CPU and allow replication replicas to drain WAL lag
    await new Promise((resolve) => setTimeout(resolve, 100));
  }
}

Safely Adding NOT NULL Constraints#

Adding a `NOT NULL` constraint to an existing column requires PostgreSQL to verify that every row satisfies the condition. Under standard syntax (`ALTER TABLE ... ALTER COLUMN ... SET NOT NULL`), this holds an `ACCESS EXCLUSIVE` lock for the duration of the table scan.

A much safer zero-downtime technique is to attach a `CHECK (column IS NOT NULL) NOT VALID` constraint first, and then validate it concurrently in a second non-blocking step:

-- Step 1: Add constraint without scanning historical data (fast instant lock)
ALTER TABLE users 
ADD CONSTRAINT check_users_phone_not_null 
CHECK (phone_number_e164 IS NOT NULL) NOT VALID;

-- Step 2: Validate existing data with a SHARE UPDATE EXCLUSIVE lock (allows concurrent reads and writes!)
ALTER TABLE users 
VALIDATE CONSTRAINT check_users_phone_not_null;

Conclusion: Reliability is a Disciplined Release Lifecycle#

Zero-downtime schema migrations require teams to view database changes as multi-phase deployment programs rather than one-line script executions. By coupling the Expand/Contract pattern with lock timeouts, concurrent index creation, and throttled batch workers, your engineering organization can deploy software continuously without risking downtime or corrupting customer data.

If your organization is planning a major database refactor, struggling with migration locking in production, or modernizing backend systems, explore my Software Architecture Services and Full-Stack Engineering offerings. You can also benchmark your database hygiene using the Production Readiness Grader or book a consultation to review your migration strategy directly.