This post is maintained by Bytebase, an open-source database DevSecOps tool that runs MySQL online schema migrations through gh-ost.
gh-ost and pt-online-schema-change are the two tools the MySQL community reaches for when Online DDL can't keep an ALTER online. They solve the same problem with opposite mechanisms — and they fail at the same problem in different ways.
1. Architecture
gh-ost (GitHub Online Schema Transmogrifier)
- Triggerless. Asynchronous.
- Tails the binlog to capture writes. Requires Row-Based Replication; typically reads from a replica.
- Builds a ghost table, copies the source in chunks, replays binlog events into the ghost.
- Cut-over is a brief metadata lock you trigger explicitly.
pt-online-schema-change (Percona Toolkit)
- Trigger-based. Synchronous.
INSERT,UPDATE,DELETEon the source fire triggers that write to the ghost table in the same transaction.- Cut-over is an atomic two-table
RENAME. - Runs on MySQL 5.5+ and supports foreign keys directly.
The two designs trade the same cost in opposite directions.
pt-osc keeps the ghost strictly consistent with the source — no replication lag, no asynchronous gap. Every write on the source pays for that with trigger overhead. The overhead compounds at cut-over: the metadata lock has to coordinate with the trigger-driven writes, and under heavy write traffic that contention is a known operational concern. pt-osc itself ships with innodb_lock_wait_timeout=1 and lock_wait_timeout=60 to position itself as the lock-contention victim rather than the disruptor.
gh-ost keeps writes on the source cheap and pushes consistency work onto the binlog tail. The ghost table trails the source by however long the binlog stream takes — eventual consistency between source and ghost, not per-transaction.
2. Feature Comparison
| Feature | gh-ost | pt-online-schema-change |
|---|---|---|
| Mechanism | Binlog tail (no triggers) | DML triggers |
| Sync model | Asynchronous | Synchronous |
| Source load | Lower (reads on replica) | Higher (trigger on every write) |
| Foreign keys | No — drop and recreate | Yes (--alter-foreign-keys-method) |
| Replication | Requires RBR | Works with SBR or RBR |
| Throttling | Replication lag, hooks, custom queries | Replication lag, copy speed |
| Runtime control | Unix socket — throttle, postpone-cut-over-flag-file, retune chunk size, panic to abort | Restart-only |
| Cut-over | Manual trigger; brief metadata lock | Atomic RENAME; metadata lock coordinates with concurrent writes |
| Cut-over scheduling | --postpone-cut-over-flag-file for an off-hours window | Cuts over as soon as the copy completes |
| Unique key | Shared NOT NULL unique key with the exact same columns between before/after | A PRIMARY KEY or UNIQUE INDEX on the table |
| MySQL versions | 5.7+ | 5.0.2+ |
| Resumable | No — process death loses the migration | Yes — --resume, with --history, --nodrop-new-table, --nodrop-triggers on the prior run |
| Managed lifecycle | No — wrap your own retries, alerting, scheduling | No — same |
Two operational properties matter regardless of which you pick. Resumability is uneven — pt-osc supports --resume if the prior run kept the new table and triggers around (--nodrop-new-table, --nodrop-triggers, --history). gh-ost has no equivalent: if the process dies, the migration is lost. Neither tool is managed — monitoring, retries, alerting, and scheduling are yours to wire up around it.
3. Pros and Cons
| Tool | Pros | Cons |
|---|---|---|
| gh-ost | Lower load on the source. No triggers. Live control via Unix socket. Cut-over you can postpone. | No foreign-key support. Requires RBR. MySQL 5.7+. No resume after process death. |
| pt-online-schema-change | Foreign keys preserved. Wide MySQL version support. Resumable with --resume. Conceptually simple. | Triggers add load on every write. Lock contention at cut-over under heavy workload. No runtime control. |
4. When to Use
Choose gh-ost when:
- The workload is write-heavy and the source can't afford trigger overhead.
- You run MySQL 5.7+ with Row-Based Replication.
- You need throttling, replica testing, or a cut-over window you control.
- Foreign keys aren't present, or you can drop and recreate them.
Choose pt-online-schema-change when:
- Tables have foreign keys you can't drop.
- You're on MySQL 5.5 or 5.6.
- The workload is light to moderate; trigger overhead won't dominate.
- You want a simpler setup with no replica dependency.
- You need resumable migrations (
--resume, with--historyand--nodrop-*).
5. Bottom Line
Both tools solve the same operational problem: ALTER a large MySQL table while it serves traffic. They diverge on what to do with concurrent writes. gh-ost keeps the source cheap and accepts replication lag plus runtime complexity. pt-osc keeps the ghost strictly consistent and accepts trigger load plus cut-over contention.
The deciding factors are usually concrete:
- Foreign keys you can't drop → pt-osc.
- MySQL 5.5 or 5.6 → pt-osc.
- Heavy write traffic → gh-ost.
Either way, wrap the tool in your own scheduling, retry, and monitoring. Both expect to be operated by a system that handles the lifecycle around them.