How do tools like gh-ost perform a zero-downtime schema migration on a multi-billion-row MySQL table without a direct ALTER TABLE ever locking it, and what's the actual failure mode if the migration falls behind production write volume?
Quick Answer
gh-ost creates a shadow copy of the table, backfills existing rows in throttled batches, and captures ongoing production changes by tailing the binary log (rather than using triggers like older tools), then does a brief atomic rename to swap the shadow table in — all without holding a long-lived lock on the original table. If the migration's binlog-tailing falls behind the rate of production writes, the cutover keeps getting deferred indefinitely, and in the worst case the shadow table grows more stale relative to production the longer the migration runs, risking a cutover that briefly applies changes out of order if not handled carefully.
Detailed Answer
Imagine renovating a busy retail store's checkout counter without ever closing the store. Instead of shutting the doors, you build an entire new counter in the back room, copy over the existing till and inventory records at your own pace, and meanwhile keep a running log of every single sale still happening at the old counter out front. Only at the very last moment — once the back-room counter has caught up completely — do you do a fast, five-second swap of the signage so customers now walk to the new counter, which by then already reflects every sale that happened while you were building it.
A direct ALTER TABLE in MySQL, for most schema changes, requires rebuilding the table and until relatively recent versions and instant-DDL-eligible changes, holds a metadata lock that blocks reads and writes for the duration — on a multi-billion-row table this can mean hours of full unavailability, which is unacceptable for a production system. gh-ost (and its predecessor pt-online-schema-change) were built specifically to avoid this by never touching the original table's structure directly: they create a new table with the desired schema, copy rows across in small batches to avoid overloading the source, and separately capture every write happening to the original table during the copy so nothing is lost.
gh-ost's key internal difference from pt-online-schema-change is how it captures ongoing changes: pt-osc uses triggers on the original table (extra code that fires on every INSERT/UPDATE/DELETE to also apply the change to the shadow table), which adds write overhead and risk directly on the production table's hot path. gh-ost instead connects to the source as if it were a replica and tails the binary log asynchronously, parsing row-based binlog events and applying them to the shadow table — this means zero triggers and zero added overhead on the actual production write path, since the binlog stream would be produced regardless of whether gh-ost is watching it. Once the shadow table's backfill is complete and its binlog-derived state is caught up to nearly real-time, gh-ost performs the cutover: a brief table rename swap, typically locking for well under a second.
In production, the critical operational parameter is throttling: gh-ost continuously monitors the source's replication lag and load, and deliberately slows or pauses its own batch copying if it detects the source struggling, specifically to avoid the migration itself becoming the cause of a production incident. Engineers monitor the migration's own progress percentage, its current copy throughput, and the replica lag it's inducing, and set explicit throttle flags (max load thresholds on metrics like Threads_running) so the tool self-regulates rather than needing constant manual babysitting during a migration that might run for many hours on a huge table.
The actual failure mode when a migration falls behind production write volume isn't data loss — gh-ost's binlog-tailing approach means it will eventually catch up as long as the binlog retention window is long enough — but if production write volume sustained during the migration exceeds gh-ost's replay throughput indefinitely, the cutover point simply never arrives, and the migration can run for days without completing, all while consuming binlog disk space on the source and leaving the schema change in permanent limbo. The gotcha experienced engineers watch for is exactly this scenario during a traffic spike or a concurrent bulk-import job: a migration that was on track to finish in 3 hours can effectively stall indefinitely if a separate high-volume batch job starts writing to the same table mid-migration, and the correct response is to pause the competing batch job or the migration itself, not to disable gh-ost's throttling to force it through, which would risk exactly the production impact the whole tool exists to avoid.