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

6-Dimensional Architecture Breakdown

⚙️1. Underlying Mechanism

Execution

▸
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
▸
1. ▸Dual-Write: Deploy application version N that writes to both phone and mobile, but reads from phone
▸
1. ▸Backfill: Run a throttled background job updating historical rows (`UPDATE users SET mobile = phone WHERE mobile IS NULL
▸
`)
▸
1. ▸Switch Reads: Deploy application version N+1 reading from mobile and writing to mobile
▸
1. ▸Contract: Drop the old phone column and enforce NOT NULL constraints

🎯2. Appropriate Use Context

Scope

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

⚠️3. Production Failure Modes

P0 Risk

✓
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
✓
1. ▸Rapid Backfill CPU Starvation: Running an unthrottled `UPDATE table SET new_col = old_col
✓
` transaction locking millions of rows and exhausting disk IOPS
✓
1. ▸Premature Contract: Dropping the old column while a canary server instance is still running old code

📡4. Diagnostic Signals & Telemetry

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

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

Trade-off

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)

REAL-WORLD TELEMETRY

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.