Zero-Downtime Database Migrations: Patterns and Strategies

The Zero-Downtime Migration Challenge

Traditional migrations lock tables. At scale, even a 1-second lock on a high-traffic table causes cascading timeouts.

The Expand/Contract Pattern

Never do breaking changes in a single migration. Use three phases:

Phase 1: Expand (backward-compatible)

Add new column/table without removing old one.

Phase 2: Migrate

Backfill data, update application code.

Phase 3: Contract (cleanup)

Remove old column after all code is deployed.

Renaming a Column Safely

-- NEVER do this (breaks running app):
-- ALTER TABLE users RENAME COLUMN username TO user_name;

-- Phase 1: Expand — add new column
ALTER TABLE users ADD COLUMN user_name VARCHAR(255);

-- Copy data (can run in background)
UPDATE users SET user_name = username WHERE user_name IS NULL;

-- Phase 2: Make both work in application code
-- Write to BOTH columns, read from new column with fallback
-- Deploy app that handles both

-- Phase 3: Contract — after all app instances are updated
ALTER TABLE users DROP COLUMN username;

Adding a NOT NULL Column

-- NEVER:
-- ALTER TABLE orders ADD COLUMN status VARCHAR(50) NOT NULL DEFAULT 'pending';
-- This rewrites the entire table!

-- Phase 1: Add nullable column (instant)
ALTER TABLE orders ADD COLUMN status VARCHAR(50);

-- Phase 2: Backfill in batches (no lock)
DO $
DECLARE
  batch_size INT := 1000;
  last_id BIGINT := 0;
  max_id BIGINT;
BEGIN
  SELECT MAX(id) INTO max_id FROM orders;
  WHILE last_id < max_id LOOP
    UPDATE orders
    SET status = 'completed'
    WHERE id > last_id AND id <= last_id + batch_size
      AND status IS NULL;
    last_id := last_id + batch_size;
    PERFORM pg_sleep(0.01);  -- Throttle to avoid overload
  END LOOP;
END $;

-- Phase 3: Add constraint (validates, no rewrite in PG)
ALTER TABLE orders ADD CONSTRAINT orders_status_not_null
  CHECK (status IS NOT NULL) NOT VALID;

ALTER TABLE orders VALIDATE CONSTRAINT orders_status_not_null;
-- NOT VALID adds instantly; VALIDATE runs in background

-- Phase 4: Make it a real NOT NULL (fast, already validated)
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_status_not_null;

Creating Indexes Without Blocking

-- Bad: blocks all writes during index build
CREATE INDEX idx_orders_user ON orders (user_id);

-- Good: CONCURRENTLY builds without blocking
CREATE INDEX CONCURRENTLY idx_orders_user ON orders (user_id);
-- Takes longer but doesn't block reads or writes

-- MySQL equivalent
ALTER TABLE orders ADD INDEX idx_user (user_id), ALGORITHM=INPLACE, LOCK=NONE;

Large Table Data Backfill

// Batch backfill without locking
async function backfillInBatches(db: Pool) {
  const BATCH_SIZE = 500
  let lastId = 0
  let processed = 0

  while (true) {
    const result = await db.query(
      `UPDATE orders
       SET normalized_status = LOWER(status)
       WHERE id > $1 AND id <= $1 + $2
         AND normalized_status IS NULL
       RETURNING id`,
      [lastId, BATCH_SIZE]
    )

    if (result.rowCount === 0) break

    processed += result.rowCount
    lastId += BATCH_SIZE
    console.log(`Processed: ${processed}`)

    await sleep(10) // Throttle — give DB room to breathe
  }
}

Feature Flags for Schema Changes

// Use feature flags to gradually roll out schema changes
async function getUser(id: string) {
  const useNewSchema = await featureFlags.isEnabled('new_user_schema', { userId: id })

  if (useNewSchema) {
    return db.query('SELECT id, full_name, email FROM users_v2 WHERE id = $1', [id])
  }
  return db.query('SELECT id, first_name || ' ' || last_name AS full_name, email FROM users WHERE id = $1', [id])
}

Migration Checklist

• Test on a copy of production data first
• Have a rollback plan for every migration
• Monitor DB metrics during migration
• Use CONCURRENTLY for index creation
• Batch large data updates
• Never drop columns in the same deploy as the code change
• Set lock_timeout to prevent long waits
• Run during low-traffic periods for risky changes

Lock Timeout Safety

-- Set lock timeout to fail fast rather than queue
SET lock_timeout = '2s';

BEGIN;
  ALTER TABLE users ADD COLUMN score INT DEFAULT 0;
  -- If lock not obtained in 2s, fails cleanly
COMMIT;