gh-ost vs pt-osc for Vitess DDL

When you have decided an external schema-change tool is the right call on a Vitess fleet, the next choice is which one — and gh-ost and pt-online-schema-change (pt-osc) differ in ways that matter more on a sharded topology than on a single MySQL host.

Context #

This comparison sits under Throttling and Cutover Control for Online DDL, because throttling and cutover are exactly where these two tools diverge most. It assumes you have already ruled the native engine out for a specific table or fleet; if you have not, that is the first decision, and it is made in Vitess Native Online DDL vs External Tools — for a Vitess-managed sharded keyspace the native vitess strategy is almost always preferable, because it makes throttling, progress, and the cutover barrier control-plane operations instead of things your orchestrator must reconstruct per shard. Everything below applies only once you are outside that happy path: migrating MySQL instances Vitess does not yet manage, needing a throttling policy more granular than the lag throttler exposes, or running on a configuration where one tool’s copy mechanism is the only compatible one.

The comparison #

The table is the decision. The prose below explains the three rows that carry the most operational weight on a sharded fleet.

Copy mechanism: binlog-tail vs trigger copy #

gh-ost is trigger-less. It connects to a shard — ideally a replica, to keep read load off the primary — reads the binary log, and streams existing rows into a _gho-suffixed ghost table while applying concurrent changes from the binlog. Nothing is installed on the source table; the only footprint is a binlog reader and the ghost table. Because it never touches the write path, it adds no per-transaction overhead, which is what makes it safe on hot tables.

pt-osc takes the older trigger-based route. It creates a _new table, installs AFTER INSERT, AFTER UPDATE, and AFTER DELETE triggers on the original so every live write is mirrored into the new table, copies existing rows in chunks, then renames. The triggers make it compatible with almost any MySQL version and — crucially — let it follow foreign-key relationships, which gh-ost cannot. The cost is that every write on the source table now does extra work for the entire duration of the copy, and the triggers cannot coexist with any application triggers already on the table.

On a sharded fleet both tools are topology-blind: neither knows what a keyspace is. You run one process per shard against that shard’s endpoint, and your orchestrator is responsible for discovering shard primaries from the topology, sequencing the runs, and scraping each process’s progress back into a unified view. That is the same coordination burden described in Coordinating Multi-Shard Schema Migrations — the external tool does not lift it.

Throttling knobs #

This is where the tools differ most, and why the choice belongs under a throttling page.

gh-ost throttles on replica lag directly. You name the replicas whose lag matters with --throttle-control-replicas and set the ceiling with --max-lag-millis; gh-ost polls those replicas and pauses the copy the moment lag crosses the line, resuming when it recovers. This is a close analogue of the native Vitess lag throttler and is the right model when your risk is read replicas falling behind SLO:

gh-ost \
  --host=shard-80-primary.internal --port=3306 \
  --database=commerce --table=orders \
  --alter="ADD COLUMN region_id BIGINT UNSIGNED NULL" \
  --throttle-control-replicas="shard-80-replica-a.internal:3306,shard-80-replica-b.internal:3306" \
  --max-lag-millis=1500 \
  --critical-load="Threads_running=80" \
  --allow-on-master --postpone-cut-over-flag-file=/var/run/ddl/orders-80.postpone \
  --execute

pt-osc throttles on primary load, not replica lag. --max-load pauses the copy when a server status variable (by default Threads_running) exceeds a ceiling, and --critical-load aborts entirely if load blows past a hard limit. Replica lag is not a first-class input — pt-osc protects the primary from saturation, but a replica quietly falling behind will not, by itself, throttle it. On a fleet where replica lag is your binding constraint, that difference is the whole argument for gh-ost.

pt-online-schema-change \
  --alter="ADD COLUMN region_id BIGINT UNSIGNED NULL" \
  --max-load="Threads_running=25" \
  --critical-load="Threads_running=50" \
  --chunk-time=0.5 \
  --execute D=commerce,t=orders,h=shard-80-primary.internal,P=3306

Both tools give you a hard abort gate (--critical-load), but only gh-ost gives you a lag-shaped throttle out of the box. If you need lag-driven backpressure with pt-osc you have to build it yourself around --max-load, which is exactly the kind of reconstruction the native strategy would have saved you.

Cutover and trigger conflicts #

Both perform an atomic rename at cutover and both hold a brief lock to do it, so both are vulnerable to metadata-lock contention from long-running transactions — the failure worked through in Resolving gh-ost Lock Contention in Sharded MySQL. The difference is coordination. gh-ost supports a postpone-cut-over flag file: it copies and stays in sync but will not swap until the file is removed, giving your orchestrator a per-shard barrier it can release in unison — the manual equivalent of native --postpone-completion. pt-osc has no comparable hold; it swaps when the copy finishes, so a fleet-wide barrier around pt-osc means gating submission timing rather than cutover timing, which is a weaker guarantee.

The trigger conflict is a hard blocker for pt-osc, not a tuning matter. If the target table already carries application triggers, pt-osc aborts with “table already has triggers” and there is no flag to work around it — the trigger-based mirror simply cannot coexist. On tables with existing triggers, gh-ost (or the native strategy) is the only option.

Driving either tool across a sharded fleet #

Neither tool understands a keyspace, so the fleet-level work is identical regardless of which you pick, and it is work the native strategy would have done for you. For each shard you resolve the primary (and a throttle replica) from the topology, launch one process, and stream its progress back into a single view. gh-ost exposes a Unix status socket you can interrogate live — echo status | nc -U /tmp/gh-ost.commerce.orders.sock — and reconfigure mid-run, which makes it the friendlier tool to supervise from an orchestrator. pt-osc reports progress to its log, so you scrape stdout instead. In both cases you own the fan-out order, the concurrency limit across shards, and the barrier: launch every shard, wait for each to report caught-up, then release the cutover in unison (gh-ost’s flag file removed on all shards; pt-osc’s runs allowed to finish together). Getting that barrier right by hand around out-of-band processes is precisely the reconstruction the native --postpone-completion gives you for free — a recurring reason the external route costs more operationally than it looks.

A second fleet-level cost is failure blast radius. Because neither tool resumes, a shard whose process dies restarts from zero while its siblings keep going, so a single flaky primary can leave the fleet in a long-lived split state where some shards are cut over and others are mid-copy. Bound that by capping how many shards you run concurrently and by treating any shard that has restarted more than once as a stop-the-line event rather than letting the fan-out drift.

Fitting each tool to a table #

The choice is often per table, not per fleet. Walk three questions in order. Does the table already have application triggers, or need foreign-key handling? If triggers exist, pt-osc is out and gh-ost (or native) is in; if foreign keys must be followed and native is unavailable, pt-osc is the only external tool that manages them. Is the binding risk replica-lag SLO on a hot, read-heavy table? Then gh-ost’s direct lag throttle (--max-lag-millis on named replicas) is the right control, and pt-osc’s primary-load throttle protects the wrong thing. Is the instance a legacy or non-ROW-binlog configuration where gh-ost cannot tail the log? Then pt-osc’s trigger copy is the compatibility fallback. Only after those three does raw throughput matter, and on that axis the two are close enough that it rarely decides anything.

Edge cases and gotchas #

• pt-osc and existing triggers. Any application trigger on the target table stops pt-osc cold. Audit for triggers before choosing it; switching to gh-ost mid-incident wastes the copy time already spent.
• gh-ost and foreign keys. gh-ost does not handle foreign-key relationships by default and its FK workarounds are fragile. FK-heavy schemas are pt-osc’s clearest win — its trigger model follows the relationships.
• Throttle scope mismatch. Choosing pt-osc on a fleet whose binding constraint is replica lag means you are protecting the wrong thing: --max-load guards the primary while replicas drift past SLO. Match the tool’s throttle model to your actual risk.
• Orphaned artifacts. A killed gh-ost leaves a _gho/_ghc table and a binlog reader; a killed pt-osc can leave _new tables and live triggers on the source. Alert on _gho/_ghc/_new tables with no matching orchestrator record — an orphaned pt-osc trigger keeps amplifying every write silently.
• No resume. Neither tool resumes after a crash; both restart the whole copy. On a 400-million-row shard that is hours of lost work — a strong nudge back toward the native strategy, which resumes from its persisted VReplication position.
• Binlog format. gh-ost needs binlog_format=ROW on the source it tails; pt-osc’s triggers work regardless of binlog format, which is occasionally the deciding factor on a legacy instance.

Verification #

Whichever tool you run, confirm the swap actually happened and left nothing behind on the shard. Check for orphaned artifacts and — for pt-osc — leftover triggers, since a stray trigger is the silent tax:

# On each shard's primary: no leftover copy tables, and (pt-osc) no leftover triggers
mysql -h shard-80-primary.internal -e "
  SELECT table_name FROM information_schema.tables
   WHERE table_schema='commerce' AND table_name RLIKE '_(gho|ghc|new|old)$';
  SELECT trigger_name FROM information_schema.triggers
   WHERE trigger_schema='commerce' AND event_object_table='orders';"

Then confirm the altered column resolves through the router on every shard, exactly as you would for a native migration — present on one shard’s disk is not proof the fleet is consistent.

Related #

• Throttling and Cutover Control for Online DDL — the native throttle and cutover barrier these external knobs are compared against.
• Vitess Native Online DDL vs External Tools — the prior decision of whether to use an external tool at all, and why native usually wins.
• Resolving gh-ost Lock Contention in Sharded MySQL — diagnosing the metadata-lock stall both tools hit at cutover.

← Back to Throttling and Cutover Control for Online DDL