Zero-Downtime Database Migrations (Expand & Contract)

Q: How does gh-ost (GitHub Online Schema Transformations) perform zero-downtime migrations in MySQL?

gh-ost creates a ghost table with the new schema, reads the binary log (binlog) to stream ongoing writes asynchronously, backfills historical data in chunks, and swaps the tables using an atomic rename.

Q: How do you add a `NOT NULL` constraint to a huge table without locking it?

Add a `CHECK (column IS NOT NULL) NOT VALID;` constraint (instant, no lock), then execute `ALTER TABLE ... VALIDATE CONSTRAINT;` which verifies rows in the background without exclusive locks.

Q: Why should you never execute large data backfills inside the main migration transaction?

Long-running transactions hold open row locks, prevent database autovacuuming, bloat write-ahead logs (WAL), and dramatically increase failover recovery time if interrupted.

Stack: SOFTWARE ARCHITECTURE STACK →Senior (L5-L6)pattern

THE SHORT ANSWER

By adopting the multi-phase Expand and Contract (Parallel Run) pattern: adding new nullable columns (Expand), dual-writing to both columns in application code, asynchronously backfilling legacy rows in small batches, switching reads to the new column, and safely dropping the old column in a final release (Contract).

Engineering Handbook & Failure Dynamics

1. Underlying Mechanism

Directly renaming a column (`ALTER TABLE users RENAME COLUMN phone TO mobile;`) instantly breaks running application instances that still expect the old column name. The Expand and Contract pattern executes migrations across distinct deployment stages: 1) Expand: Add new column `mobile` as nullable; 2) Dual-Write: Deploy application version N that writes to both `phone` and `mobile`, but reads from `phone`; 3) Backfill: Run a throttled background job updating historical rows (`UPDATE users SET mobile = phone WHERE mobile IS NULL;`); 4) Switch Reads: Deploy application version N+1 reading from `mobile` and writing to `mobile`; 5) Contract: Drop the old `phone` column and enforce `NOT NULL` constraints.

2. Appropriate Use Context

High-traffic production web applications and mission-critical databases where scheduled maintenance downtime windows are forbidden.

3. Production Failure Modes

1) AccessExclusiveLock Outage: Running `ALTER TABLE ADD COLUMN ... DEFAULT 'foo'` on legacy Postgres/MySQL versions, locking the table for 45 minutes and killing active connections; 2) Rapid Backfill CPU Starvation: Running an unthrottled `UPDATE table SET new_col = old_col;` transaction locking millions of rows and exhausting disk IOPS; 3) Premature Contract: Dropping the old column while a canary server instance is still running old code.

4. Diagnostic Signals & Telemetry

Monitoring PostgreSQL `pg_locks` for waiting queries, lock duration spikes during deployments, database replication lag spikes during backfill batches, and application 500 error rates during migration rollouts.

5. Prevention & Safeguards

Set strict lock timeouts (`SET lock_timeout = '2s';`) before running DDL migrations; always use `CREATE INDEX CONCURRENTLY` in PostgreSQL; run backfills in small throttled batches (<1,000 rows with 50ms sleep); and use Online Schema Change tools (gh-ost / pt-online-schema-change) for large MySQL tables.

6. Architectural Trade-offs

Guarantees 100% continuous availability and zero table locking at the expense of requiring 3 to 4 sequential application deployments to complete a single schema change.

Case Study (TinyCTO In-Field Example)

TinyCTO Incident 038: A junior engineer ran `ALTER TABLE transactions ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);` on a 400M-row table. Postgres placed an Exclusive Lock on both tables to validate every row, halting checkout for 28 minutes. Re-executing with `NOT VALID` followed by `VALIDATE CONSTRAINT` validated the constraint asynchronously with zero downtime.

Interactive Concept Drills

3 Cards

What are the 5 discrete steps of the Expand and Contract database migration pattern?

1) Expand (add new nullable column), 2) Dual-Write (write to both, read old), 3) Backfill (populate historical rows), 4) Switch Reads (read and write new), 5) Contract (delete old column).

Why is `CREATE INDEX CONCURRENTLY` required in PostgreSQL for production tables?

Standard `CREATE INDEX` acquires a `SHARE` lock that blocks all incoming INSERT, UPDATE, and DELETE queries until indexing finishes; `CONCURRENTLY` builds the index without blocking writes.

What should you always set at the beginning of a migration transaction to prevent catastrophic lock queues?

A short `lock_timeout` (e.g. `SET lock_timeout = '2s';`), which aborts the migration immediately if it cannot acquire the lock within 2 seconds instead of queuing up behind other transactions.