Zero-Downtime Schema Migration Lab (Interactive)
Add a column to a 50-million-row table: raw ALTER locks everything, gh-ost shadows it in with binlog replay. Race a ghost-table backfill against live writes with chunking and throttling, then compare total lock time against a 2ms rename.
ALTER TABLE vs gh-ost Ghost Migration
Add a column to a 50-million-row table under live traffic: compare table locks against triggerless binlog copy.
Binlog delta backlog
0
Throttled seconds
0
auto-pause when backlog > 150k
Elapsed
0s
Cutover
pending sync
gh-ost never locks the source: it copies throttled chunks, tails the binlog as a replication client (no synchronous triggers), pauses when the backlog spikes, and finishes with an atomic RENAME that costs 2 milliseconds. Raise live writes above 60k/s to watch throttling protect production latency.
How It Works Under the Hood
A raw ALTER TABLE on a hundred-million-row production table rewrites the whole table under an exclusive metadata lock, queueing every write behind hours of copying until connection pools saturate and the system cascades into outage. gh-ost instead builds a shadow table with the new schema, backfills in throttled chunks, and tails the binary log as a replication client to replay live mutations without triggers. When checksums match, an atomic RENAME swaps schemas in two milliseconds.
Core Architectural Principles
- Triggerless binlog streaming avoids write amplification on the source table during migration.
- Automatic throttling pauses the backfill when the delta backlog or replication lag spikes.
- Expand/Contract pattern makes column renames safe: add, dual-write, backfill, switch reads, then drop.
For any "how do you change a huge table" question, answer with the ghost pipeline: shadow table, chunked copy, binlog replay, throttling against lag thresholds, atomic rename. Then broaden to application-level Expand/Contract, add column, dual write, backfill, switch reads, contract, because schema changes and code deploys must move in backward-compatible steps.
Lock-free evolution of massive tables versus doubled disk usage during the copy and multi-sprint migration choreography.