DB Zero Downtime Migration

Zero-Downtime Migration

Online schema changes that avoid table locks and keep the application serving traffic throughout the migration.

When to Use

• Adding, removing, or modifying columns on tables with millions of rows
• Creating indexes on large production tables
• Changing column types or constraints without downtime
• Planning DDL operations during high-traffic periods
• Evaluating whether a migration requires a maintenance window

Instructions

Key Concepts

1. The Lock Problem

Most DDL statements in PostgreSQL acquire an ACCESS EXCLUSIVE lock, which blocks all reads and writes on the table. On a table with active queries, just waiting to acquire this lock can create a queue that cascades into a full outage.

-- This blocks ALL reads and writes until it completes:
ALTER TABLE orders ADD COLUMN shipped_at TIMESTAMPTZ;

-- On a table with 100M rows and active queries, this can take
-- minutes to acquire the lock, during which new queries queue up

2. Safe vs Unsafe Operations

Safe (no rewrite, brief lock):

• ADD COLUMN with no default (PostgreSQL 11+ with volatile default is also safe)
• DROP COLUMN (marks column as dropped, no rewrite)
• CREATE INDEX CONCURRENTLY (does not block writes)
• ADD CONSTRAINT ... NOT VALID (skips existing row validation)

Unsafe (full table rewrite or long lock):

• ALTER COLUMN TYPE (rewrites every row)
• ADD COLUMN ... DEFAULT <value> (pre-PostgreSQL 11)
• CREATE INDEX without CONCURRENTLY
• ADD CONSTRAINT without NOT VALID

3. Lock Timeout Strategy

Set a short lock timeout so DDL fails fast instead of queueing:

SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN shipped_at TIMESTAMPTZ;
-- If the lock is not acquired within 3 seconds, the statement
-- fails instead of creating a cascading queue
-- Retry during a quieter moment

4. Concurrent Index Creation

-- WRONG: blocks all writes until index is built
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- RIGHT: builds index without blocking writes
CREATE INDEX CONCURRENTLY idx_orders_customer ON orders (customer_id);
-- Takes longer but allows normal operations to continue
-- IMPORTANT: if this fails, it leaves an INVALID index that must be dropped

5. pg_repack

Rebuilds tables online without holding ACCESS EXCLUSIVE for the duration. Uses triggers to capture changes during the rebuild.

# Rebuild a table to reclaim bloat without downtime
pg_repack --no-superuser-check -t orders mydb

# Rebuild and reorder by a specific column for better locality
pg_repack --no-superuser-check -t orders -o created_at mydb

Worked Example

Scenario: add a NOT NULL column with a default to a 50M-row orders table.

-- Step 1: Add the column as nullable (instant, no rewrite)
SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN priority INT;

-- Step 2: Set the default for new rows
ALTER TABLE orders ALTER COLUMN priority SET DEFAULT 0;

-- Step 3: Backfill existing rows in batches
DO $$
DECLARE
  batch_size INT := 10000;
  max_id BIGINT;
  current_id BIGINT := 0;
BEGIN
  SELECT MAX(id) INTO max_id FROM orders;
  WHILE current_id < max_id LOOP
    UPDATE orders
    SET priority = 0
    WHERE id > current_id AND id <= current_id + batch_size
      AND priority IS NULL;
    current_id := current_id + batch_size;
    COMMIT;
  END LOOP;
END $$;

-- Step 4: Add NOT NULL constraint without validating existing rows
ALTER TABLE orders ADD CONSTRAINT orders_priority_not_null
  CHECK (priority IS NOT NULL) NOT VALID;

-- Step 5: Validate the constraint (does not hold ACCESS EXCLUSIVE)
ALTER TABLE orders VALIDATE CONSTRAINT orders_priority_not_null;

This approach completes each step in seconds of lock time, regardless of table size.

Anti-Patterns

1. Running ALTER COLUMN TYPE directly on large tables. This rewrites every row while holding an ACCESS EXCLUSIVE lock. Instead, use the expand-contract pattern: add a new column, backfill, swap in application code, drop old column.

2. Forgetting lock_timeout on DDL statements. Without a timeout, a DDL statement can wait indefinitely for a lock while queueing all subsequent queries. Always set lock_timeout before DDL.

3. Leaving invalid indexes after failed CREATE INDEX CONCURRENTLY. A failed concurrent index creation leaves an INVALID index. It consumes space and slows writes. Check with SELECT * FROM pg_indexes WHERE indexdef LIKE '%INVALID%' and drop invalid indexes.

4. Backfilling millions of rows in a single transaction. This holds locks on all modified rows and generates massive WAL. Batch the backfill into chunks of 1,000-10,000 rows with commits between batches.

5. Using NOT NULL constraint directly via ALTER COLUMN SET NOT NULL. On PostgreSQL versions before 12, this scans the entire table. Use a CHECK constraint with NOT VALID plus VALIDATE CONSTRAINT instead.

PostgreSQL Specifics

• PostgreSQL 11+: ADD COLUMN ... DEFAULT <value> no longer rewrites the table for most types. The default is stored in the catalog and applied on read.
• PostgreSQL 12+: ALTER COLUMN SET NOT NULL can use an existing valid CHECK (col IS NOT NULL) constraint to skip the table scan.
• pg_stat_progress_create_index shows progress of CREATE INDEX CONCURRENTLY.
• pg_repack requires the pg_repack extension and briefly acquires ACCESS EXCLUSIVE at the start and end of a rebuild.

Details

Advanced Topics

gh-ost for MySQL: GitHub's online schema change tool avoids MySQL's trigger-based limitations. It creates a shadow table, copies data via binlog streaming, and swaps atomically. Key flags:

gh-ost --alter "ADD COLUMN shipped_at DATETIME" \
  --database mydb --table orders \
  --host replica.db --allow-on-master \
  --chunk-size 1000 --max-load Threads_running=25 \
  --execute

pt-online-schema-change: Percona's tool uses triggers to capture changes during the copy. Simpler than gh-ost but adds trigger overhead. Choose gh-ost for high-write tables, pt-osc for simpler setups.

Statement Timeout as Safety Net:

SET statement_timeout = '30s';
-- Any migration step that takes longer than 30s is killed
-- Prevents runaway operations from monopolizing the database

Engine Differences

PostgreSQL provides transactional DDL -- ALTER TABLE inside a transaction can be rolled back. MySQL commits DDL implicitly; there is no rollback. This makes PostgreSQL significantly safer for multi-step schema changes, as a failed step does not leave the schema in an inconsistent state.

MySQL's ALGORITHM=INPLACE and ALGORITHM=INSTANT hint at the DDL strategy. INSTANT operations (available in MySQL 8.0+) are similar to PostgreSQL 11+ catalog-only changes. However, MySQL INSTANT is limited to adding columns at the end of the table.

Real-World Case Studies

A SaaS platform needed to add a tenant_id column to a 200M-row events table. Direct ALTER TABLE estimated 45 minutes of downtime. The team used the zero-downtime approach: added the nullable column (instant), set a default for new rows, backfilled in 10K-row batches over 4 hours during off-peak, then added a NOT VALID check constraint and validated it. Total lock time: under 10 seconds cumulative. The application served traffic throughout the entire migration.

Source

• PostgreSQL ALTER TABLE
• pg_repack
• gh-ost
• Sadalage, P. & Fowler, M. "Refactoring Databases" (2006)

Process

1. Classify the DDL operation as safe or unsafe using the lock reference above.
2. Set lock_timeout before every DDL statement; fail fast if the lock is not available.
3. For unsafe operations, decompose into safe steps: add column, backfill in batches, add constraint with NOT VALID, validate.
4. Monitor with pg_stat_activity and pg_locks during each migration step.

Harness Integration

• Type: knowledge -- this skill is a reference document, not a procedural workflow.
• No tools or state -- consumed as context by other skills and agents.
• related_skills: db-expand-contract, db-migration-rollback, db-acid-properties, db-pessimistic-locking, db-deadlock-prevention

Success Criteria

• Every DDL statement is preceded by SET lock_timeout to fail fast instead of queueing.
• Unsafe operations (type changes, adding constraints) are decomposed into safe steps using the expand-contract pattern.
• Indexes are created with CONCURRENTLY to avoid blocking writes.
• Data backfills run in batches with commits between batches, not in a single transaction.