How GitHub Made MySQL Schema Changes Boring Again

It's 2 PM on a Tuesday. Traffic is healthy. Then a perfectly reasonable request arrives: add one column to orders.

On a small table, this is a two-minute change. On an 800 GB table carrying a live product, it can be an incident waiting to happen. The dangerous part is not the SQL syntax. It is the operational question behind it: how do you change the shape of a table without turning the table into a traffic jam?

GitHub's answer, gh-ost, is one of my favourite pieces of pragmatic database engineering. It does not discover a new source of truth. It notices that MySQL already has one: the binary log.

Many schema changes force MySQL to rebuild a table or take metadata locks that block application traffic. Newer MySQL releases have made native online DDL much better, and it should always be your first thing to evaluate. But “online” is not a universal promise: the exact locking and copy behaviour depends on the MySQL version, storage engine, and the alteration itself.

For a large, hot table, an unsafe ALTER TABLE is a bad bet. You can accumulate replication lag, saturate disk I/O, hold a lock longer than expected, and discover that the rollback path is worse than the forward path. The migration plan needs to be as deliberate as the application deploy.

The classic answer is pt-online-schema-change. Its shape is sensible:

1. Create a shadow table with the desired schema.
2. Add triggers to mirror writes from the original table.
3. Copy historical rows in small chunks.
4. Swap the tables when both sides match.

The sharp edge is step two. A trigger runs in the same transaction as the application write that caused it. Every INSERT, UPDATE, and DELETE on the original table now performs extra work against the shadow table, while competing for locks in both places.

That coupling is precisely what hurts under pressure. You can pause the row copy, but you cannot remove the triggers without losing changes. In other words: the migration appears paused while the write overhead keeps following every production transaction.

MySQL's binary log already records committed changes for replication and recovery. GitHub's insight was wonderfully direct: if the binlog already describes every row change, why put triggers on the busy table just to discover the same changes again?

gh-ost tails row-based binlog events, builds a ghost table, copies existing rows into it, and asynchronously replays new changes. No application transaction needs to call into migration code.

The result is a cleaner separation of concerns:

Copy
Application writes ──→ original table ──→ MySQL binlog
                                           │
                                           │  gh-ost tails row events
                                           ▼
                                ghost table (new schema)
                                           │
                                           ▼
                                  atomic RENAME TABLE cutover

The original write path stays the original write path. The migration becomes a controlled consumer of the database's own change stream.

In its default, least-intrusive mode, gh-ost connects through a replica to discover the topology. It reads metadata and binlog events from that replica while performing the row copy, ghost-table writes, and eventual cutover on the primary. This also lets a primary using statement-based replication be supported when the hooked replica logs row-based events.

The migration has four moving parts:

1. Prepare. Clone the table definition, apply your --alter statement, and create a ghost table.
2. Backfill. Read the original table in key-range chunks and insert those rows into the ghost table.
3. Catch up. Tail binlog row events and apply inserts, updates, and deletes to the ghost table.
4. Cut over. Once the ghost table is caught up, use an atomic RENAME TABLE swap.

One important nuance: “atomic” does not mean “no lock exists anywhere.” The rename needs metadata locks, so a long-running transaction can still delay cutover. Good operators monitor for that and use gh-ost's cutover controls rather than treating the final seconds as magic.

gh-ost does write to the primary — it has to populate and maintain the ghost table — but it serializes that migration work through its own connection. It is decoupled from the concurrency of application writes on the original table.

That distinction matters. With trigger-based migration, every application writer inherits the migration's overhead. With gh-ost, the migration is a separately controlled workload that consumes the binlog and writes to the ghost table at a rate you choose.

This does not make a migration free. It still consumes I/O, CPU, buffer-pool space, binlog bandwidth, and replica capacity. It does make the load observable, rate-limited, and removable from the application's critical transaction path.

This is the operational detail that changes how safe the tool feels.

When gh-ost throttles, it stops copying rows and stops applying row changes to the ghost table. It keeps only negligible internal heartbeat activity so it can measure lag and know when to resume. There are no mirroring triggers left attached to production writes.

That makes an ordinary business-hours workflow possible: start the migration, watch the database, and back off immediately when the system needs headroom. You can throttle on replica lag, server load, an arbitrary query, or a flag file — and tune several of those controls while the process is still running.

--test-on-replica is the confidence-building feature more teams should borrow. gh-ost can run the migration on a replica, briefly stop replication at the end, swap the tables, swap them back, and leave both versions available for comparison.

GitHub used dedicated production replicas to continuously exercise migrations on its tables and validate that the ghost and original data matched. That is the right posture: a production schema change should not be the first time your organization discovers whether a tool, table shape, or workload pattern is compatible.

Start with a noop run, then validate the exact ALTER on a representative replica. Check row counts and checksums, inspect the generated table definition, and rehearse the cutover controls before you are looking at a real alert.

For a 500 GB finance.transactions table, adding a status column might look like this. Treat credentials and thresholds as examples; choose them from measurements of your own fleet.

bash
Copy
1gh-ost \
2  --database="finance" \
3  --table="transactions" \
4  --alter="ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'pending'" \
5  --host="replica.db.example.com" \
6  --port=3306 \
7  --user="ghost_user" \
8  --password="${DB_PASSWORD}" \
9  --chunk-size=1000 \
10  --max-lag-millis=1500 \
11  --max-load="Threads_running=25" \
12  --throttle-control-replicas="replica1.db.example.com,replica2.db.example.com" \
13  --throttle-flag-file=/tmp/gh-ost.throttle \
14  --verbose \
15  --execute

For a direct primary connection, use --allow-on-master only when you have deliberately chosen that mode and the primary emits row-based binlog events. The replica-driven default is normally the better starting point.

gh-ost is powerful because it has a narrow model. Do not force a table into that model without checking the constraints.

• Foreign keys: gh-ost does not support migrating tables that use foreign-key relationships in the normal way.
• Existing triggers: it does not support migrating a table that already has triggers.
• Key requirements: it needs a primary key or another suitable unique key to copy and apply changes safely.
• Binlog requirements: the source it tails needs row-based binlog events. A replica can be configured to log those even if the primary uses statement-based replication.
• Generated columns and edge-case DDL: verify the current compatibility matrix and test the precise schema before committing to the approach.

Also, do not reach for an online-schema-change tool by reflex. If native online DDL for your MySQL version and exact alteration gives you the locking behaviour you need, the native path is simpler. gh-ost earns its complexity when the table is large, hot, and too important to gamble on.

The enduring idea here is not “always use gh-ost.” It is: look for the system that already observes the thing you are about to instrument.

Trigger-based migration instruments every write to reconstruct a change stream. The binlog is already that stream. By reusing it, GitHub removed work from the hottest path, gained real throttling, and made a risky operation something an operator could reason about.

That is the kind of simplification worth looking for: not fewer moving parts on a diagram, but fewer moving parts attached to the request that matters most.

Further reading: GitHub's original gh-ost engineering post and the gh-ost documentation.