Zero-Downtime Database Migrations

Database migrations are among the most anxiety-inducing operations in production engineering. A single misstep can lock tables, corrupt data, or bring an entire application to its knees. Traditional migration approaches that rely on maintenance windows are increasingly unacceptable as users expect 24/7 availability. The cost of downtime is measured not just in lost revenue but in eroded trust.

Zero-downtime migrations solve this by restructuring how we think about schema changes. Instead of treating a migration as a single atomic event, we decompose it into a series of backward-compatible steps that can be applied while the application continues serving traffic. This guide covers the foundational patterns, tooling, and real-world strategies for executing database migrations without any service interruption.

The Expand/Contract Pattern

The expand/contract pattern is the cornerstone of zero-downtime migrations. Every destructive schema change, whether it is renaming a column, changing a data type, or splitting a table, can be decomposed into two phases that preserve backward compatibility at every step.

During the expand phase, you add new structures alongside existing ones. No existing columns are removed, no constraints are tightened, and no data types are narrowed. The application is updated to write to both the old and new structures simultaneously, ensuring that the new schema is populated with fresh data.

The contract phase happens only after every running application instance has been updated to use the new schema. At this point, the old columns, tables, or constraints are removed. Since no running code references them, the removal is safe.

-- EXPAND PHASE: Add new column alongside old one
ALTER TABLE users ADD COLUMN display_name VARCHAR(255);

-- Backfill existing data
UPDATE users SET display_name = full_name WHERE display_name IS NULL;

-- Application code writes to BOTH columns during transition
-- INSERT INTO users (full_name, display_name, ...) VALUES ($1, $1, ...);

-- CONTRACT PHASE (after all app instances updated):
ALTER TABLE users DROP COLUMN full_name;

The critical insight is that between the expand and contract phases, the database schema must be compatible with both the old and new application code. This compatibility window is what enables rolling deployments without coordination between the migration and the deployment.

A common mistake is trying to collapse both phases into a single deployment. This creates a race condition: if the migration runs before all application pods are updated, old code will fail trying to access removed columns. If the migration runs after, new code will fail trying to access columns that do not exist yet.

Backward-Compatible Migration Strategies

Not every schema change fits neatly into a simple add-then-remove pattern. Complex operations require specific strategies to maintain backward compatibility. Here are the most common scenarios and how to handle each one.

Adding a NOT NULL column requires special care. You cannot add a NOT NULL column without a default value to a table with existing rows, as it will fail immediately. Instead, add the column as nullable first, backfill existing rows, then add the NOT NULL constraint.

-- Step 1: Add nullable column with a default
ALTER TABLE orders ADD COLUMN currency VARCHAR(3) DEFAULT 'USD';

-- Step 2: Backfill in batches to avoid long locks
DO $$
DECLARE
    batch_size INT := 5000;
    affected INT := 1;
BEGIN
    WHILE affected > 0 LOOP
        UPDATE orders
        SET currency = 'USD'
        WHERE id IN (
            SELECT id FROM orders
            WHERE currency IS NULL
            LIMIT batch_size
        );
        GET DIAGNOSTICS affected = ROW_COUNT;
        PERFORM pg_sleep(0.1); -- Brief pause to reduce contention
    END LOOP;
END $$;

-- Step 3: Add the NOT NULL constraint
ALTER TABLE orders ALTER COLUMN currency SET NOT NULL;

Changing a column's data type follows the expand/contract pattern exactly. Create a new column with the desired type, set up a trigger or application-level dual-write to keep both columns synchronized, migrate all reads to the new column, then drop the old one.

Splitting a table is more involved. Create the new table, set up dual-writes at the application level, backfill historical data, migrate reads to the new table, and then stop writing to the old columns. If you are working with a system that uses type-safe APIs like tRPC, the type system will catch any read/write mismatches during the transition.

Renaming a table should never be done with a direct RENAME statement. Instead, create the new table, set up replication between old and new, migrate application reads, migrate application writes, and finally drop the old table.

Blue-Green Schema Changes

Blue-green deployments are well-understood for stateless application servers, but applying the same concept to databases introduces unique challenges. The database is shared state. You cannot simply spin up a second database and switch traffic because both environments need consistent data.

The practical approach to blue-green schema changes uses schema versioning rather than separate databases. Each migration is tagged with a version number, and the application code is aware of which schema versions it supports.

// Migration version tracking in application config
const SCHEMA_COMPATIBILITY = {
  minVersion: 42,  // Oldest schema version this code works with
  maxVersion: 44,  // Newest schema version this code works with
  preferredVersion: 44  // Schema version this code was built for
};

// At startup, verify schema compatibility
async function checkSchemaVersion(db) {
  const result = await db.query(
    'SELECT version FROM schema_migrations ORDER BY version DESC LIMIT 1'
  );
  const currentVersion = result.rows[0].version;

  if (currentVersion < SCHEMA_COMPATIBILITY.minVersion) {
    throw new Error(
      `Schema version ${currentVersion} is too old. ` +
      `Minimum supported: ${SCHEMA_COMPATIBILITY.minVersion}`
    );
  }

  if (currentVersion > SCHEMA_COMPATIBILITY.maxVersion) {
    throw new Error(
      `Schema version ${currentVersion} is too new. ` +
      `Maximum supported: ${SCHEMA_COMPATIBILITY.maxVersion}`
    );
  }

  return currentVersion;
}

This approach enables safe rollbacks. If a new application version is deployed and encounters issues, you can roll back to the previous application version without rolling back the schema, as long as the schema version falls within the old application's compatibility range.

The key constraint is that your compatibility window must span at least two schema versions. The current production schema and the next migration must both be supported by the currently deployed application code. This is exactly the expand/contract pattern applied at the version level.

Online Schema Migration Tools

For large tables with millions or billions of rows, even an ALTER TABLE that adds a nullable column can take hours and hold locks that block writes. Online schema migration tools solve this by performing the change on a shadow copy of the table while the original continues serving traffic.

gh-ost (GitHub Online Schema Transmogrifier) for MySQL works by creating a ghost table with the desired schema, capturing changes to the original table via the binary log, and replaying them on the ghost table. Once the ghost table is caught up, it atomically swaps the tables.

# gh-ost migration example: add an index without locking the table
gh-ost \
  --host=db-primary.internal \
  --database=myapp \
  --table=events \
  --alter="ADD INDEX idx_events_created (created_at, event_type)" \
  --allow-on-master \
  --chunk-size=1000 \
  --max-load=Threads_running=25 \
  --critical-load=Threads_running=100 \
  --throttle-control-replicas=db-replica-1.internal \
  --postpone-cut-over-flag-file=/tmp/gh-ost-postpone.flag \
  --execute

pg_repack for PostgreSQL achieves similar results by rebuilding tables and indexes without holding exclusive locks. It uses triggers to capture concurrent changes during the rebuild.

For teams using distributed tracing with OpenTelemetry, instrumenting your migration tooling is essential. Add trace spans around each batch operation so you can monitor migration progress and identify bottlenecks in your observability platform.

These tools are not magic. They still consume I/O and CPU resources, and they can fall behind on very write-heavy tables. Always run them during lower-traffic periods when possible and configure appropriate throttling limits.

Data Backfill Strategies

Backfilling data is the most operationally sensitive part of any zero-downtime migration. A naive UPDATE statement on a table with 100 million rows will hold a lock for minutes or hours, blocking all other writes. Batched backfills are essential.

The fundamental approach is to process rows in small chunks, releasing locks between batches to allow normal traffic to proceed. The chunk size depends on your database's lock granularity, the complexity of the update, and how much write throughput you can sacrifice.

import time
import psycopg2

def batched_backfill(conn, batch_size=5000, sleep_seconds=0.05):
    """Backfill a new column in batches with throttling."""
    cursor = conn.cursor()
    total_updated = 0

    while True:
        cursor.execute("""
            WITH batch AS (
                SELECT id FROM orders
                WHERE shipping_zone IS NULL
                LIMIT %s
                FOR UPDATE SKIP LOCKED
            )
            UPDATE orders
            SET shipping_zone = calculate_zone(shipping_address)
            FROM batch
            WHERE orders.id = batch.id
            RETURNING orders.id
        """, (batch_size,))

        affected = cursor.rowcount
        conn.commit()
        total_updated += affected

        if affected == 0:
            break

        print(f"Updated {total_updated} rows so far...")
        time.sleep(sleep_seconds)  # Throttle to reduce contention

    print(f"Backfill complete: {total_updated} rows updated")
    return total_updated

The FOR UPDATE SKIP LOCKED clause is critical. It prevents the backfill query from waiting on rows that are currently locked by other transactions. Instead, it skips those rows and picks them up in a subsequent batch.

For extremely large tables, consider running multiple backfill workers in parallel, each operating on a different range of primary key values. Partition the key space evenly and assign each worker a non-overlapping range. This can reduce total backfill time from hours to minutes.

Monitor your database's replication lag during backfills. If replicas fall behind, increase the sleep interval between batches. Many teams set an automatic threshold: if replication lag exceeds five seconds, the backfill pauses until it drops below two seconds.

Migration Orchestration and Rollbacks

Individual migration steps are only part of the challenge. Orchestrating the entire lifecycle of a zero-downtime migration, from the initial expand through the final contract, requires careful coordination across multiple deployments.

A migration lifecycle typically spans three or more deployments:

1. Deploy 1: Run the expand migration. The application code is unchanged and does not yet use the new schema elements.
2. Deploy 2: Deploy application code that writes to both old and new columns, reads from the new column (with fallback to old), and runs the data backfill.
3. Deploy 3: Deploy application code that only reads and writes the new column. The old column is now unused.
4. Deploy 4: Run the contract migration, removing the old column.

Each deployment can be rolled back independently. If Deploy 2 has issues, you roll back to Deploy 1, and the database is still in the expanded state with the old columns intact. The data written to new columns by Deploy 2 is simply ignored by Deploy 1.

// Migration state machine for tracking multi-step migrations
const MigrationState = {
  PENDING: 'pending',
  EXPANDING: 'expanding',
  EXPANDED: 'expanded',
  DUAL_WRITING: 'dual_writing',
  BACKFILLING: 'backfilling',
  CUTOVER_READY: 'cutover_ready',
  CONTRACTING: 'contracting',
  COMPLETED: 'completed',
  ROLLED_BACK: 'rolled_back'
};

class MigrationOrchestrator {
  constructor(db, migrationId) {
    this.db = db;
    this.migrationId = migrationId;
  }

  async transition(fromState, toState) {
    const result = await this.db.query(
      `UPDATE migration_tracking
       SET state = $1, updated_at = NOW()
       WHERE id = $2 AND state = $3
       RETURNING *`,
      [toState, this.migrationId, fromState]
    );

    if (result.rowCount === 0) {
      throw new Error(
        `Cannot transition from ${fromState} to ${toState}`
      );
    }

    console.log(`Migration ${this.migrationId}: ${fromState} -> ${toState}`);
    return result.rows[0];
  }
}

Tracking migration state explicitly prevents a common failure mode: running the contract phase before all application instances have been updated. By requiring an explicit state transition from cutover_ready to contracting, you create a checkpoint where an engineer must verify that all old-code instances have been drained.

If your production runtime uses Bun or another modern JavaScript runtime, ensure your migration scripts are tested against the same runtime version running in production. Database driver compatibility varies across runtimes, and a migration that works in Node.js might behave differently under Bun's SQLite driver.

Testing and Validation

Zero-downtime migrations must be tested more rigorously than traditional migrations because they run against live data under concurrent load. A migration that works perfectly on a quiet staging database might fail under production traffic patterns.

Build a migration test harness that simulates concurrent traffic during the migration:

import asyncio
import asyncpg
import random

async def migration_load_test():
    """Simulate concurrent reads and writes during a migration."""
    pool = await asyncpg.create_pool(
        'postgresql://localhost/testdb',
        min_size=10,
        max_size=50
    )

    errors = []
    operations = 0

    async def simulate_traffic(pool, duration_seconds=60):
        nonlocal operations
        end_time = asyncio.get_event_loop().time() + duration_seconds

        while asyncio.get_event_loop().time() < end_time:
            async with pool.acquire() as conn:
                try:
                    op = random.choice(['read', 'write', 'update'])
                    if op == 'read':
                        await conn.fetch(
                            'SELECT * FROM orders WHERE id = $1',
                            random.randint(1, 1000000)
                        )
                    elif op == 'write':
                        await conn.execute(
                            'INSERT INTO orders (customer_id, total) '
                            'VALUES ($1, $2)',
                            random.randint(1, 10000),
                            random.uniform(10.0, 500.0)
                        )
                    else:
                        await conn.execute(
                            'UPDATE orders SET total = $1 WHERE id = $2',
                            random.uniform(10.0, 500.0),
                            random.randint(1, 1000000)
                        )
                    operations += 1
                except Exception as e:
                    errors.append(str(e))

    # Run 20 concurrent workers
    workers = [simulate_traffic(pool) for _ in range(20)]
    await asyncio.gather(*workers)

    print(f"Operations: {operations}, Errors: {len(errors)}")
    if errors:
        print(f"Sample errors: {errors[:5]}")

    await pool.close()

asyncio.run(migration_load_test())

Run this test harness in one terminal while executing your migration in another. The test should complete with zero errors. If you see lock timeouts, deadlocks, or constraint violations, the migration is not truly zero-downtime.

Additionally, validate data integrity after every migration step. Write assertions that verify row counts, check for null values in columns that should be populated, and confirm that foreign key relationships are intact. These checks should run automatically as part of your CI/CD pipeline.

Version your migration tests alongside the migrations themselves. When a migration spans multiple deployments, each deployment should include tests that verify the current state of the schema and the data. Pair this with distributed tracing to get full visibility into migration performance across your database cluster.

Zero-downtime migrations are not just a technique but a discipline. They require thinking about every schema change as a multi-step process, maintaining backward compatibility at every intermediate state, and investing in tooling that makes the process repeatable and safe. The upfront cost is higher than a simple ALTER TABLE, but the payoff is a production system that never goes dark for maintenance.