Vitess Native Online DDL vs External Tools: Choosing an Execution Engine for Sharded Schema Change

Every schema change on a sharded keyspace forces one early decision that determines the operational cost of everything downstream: which engine actually rebuilds the table on each shard. Vitess ships a managed Online DDL subsystem (the vitess and online strategies) that drives a VReplication-based row copy under its own scheduler; the alternative is to bolt an external tool — gh-ost or pt-online-schema-change — onto the same fleet. This page resolves that choice for a specific operational context: a horizontally partitioned MySQL topology where a single ALTER TABLE fans out to dozens or hundreds of primaries, and where routing, throttling, and cutover must stay coordinated across all of them. It is written for MySQL SREs and Python orchestration builders who need a defensible engine selection, not a feature checklist. The broader coordination model these engines plug into is defined in Online DDL Orchestration & Migration Coordination; this page is the engine-selection layer beneath it.

Prerequisites #

Before the comparison is actionable, confirm the following about your environment:

• Vitess 14+ for the vitess strategy (the VReplication-based managed engine). Vitess 19+ tightens --postpone-completion semantics and the OnlineDDL command surface; the examples here assume 19 or newer. The older online alias maps to gh-ost running inside Vitess.
• MySQL 8.0 primaries with ROW-based binary logging and GTIDs enabled on every shard. Both native VReplication and gh-ost tail the binary log, so binlog_row_image=FULL (or at least MINIMAL with matching tooling config) is required.
• A working control-plane path via vtctldclient and, for dashboards, vtadmin. You should already be able to run vtctldclient GetKeyspaces and reach every VTTablet.
• Assumed knowledge: how queries reach a shard through the stateless VTGate routing layer, and how the VSchema routing contract maps logical tables to physical shards. If a column you are altering is referenced by a routing rule or a lookup vindex, that dependency changes the safe cutover order.
• A staging keyspace with representative row counts — chunk-size and throttling behaviour on a 200-row table tells you nothing about a 400-million-row table.

Two Execution Paths, Side by Side #

The two engines differ less in what they produce — both end with an atomically swapped, rebuilt table — than in who owns the copy loop, the throttle signal, and the cutover barrier. Native Online DDL keeps all three inside the Vitess control plane; external tools own the copy loop and throttle themselves, and must be taught about the topology so the orchestrator can observe them.

The load-bearing distinction: with the native path, the same VReplication primitive that powers resharding also performs the schema change, so lag throttling, progress reporting, and the coordinated cutover are first-class control-plane operations. With an external tool, you gain fine-grained, tool-specific tuning (gh-ost’s dynamic throttle-control-replicas, pt-osc’s --max-load/--critical-load gates) at the cost of running an out-of-band process that Vitess does not natively schedule, throttle, or observe.

Core Mechanism: How Each Engine Rebuilds a Table #

Native vitess strategy (VReplication) #

When you submit a migration with --ddl-strategy=vitess, Vitess assigns it a migration_uuid and fans one job out to every shard in the keyspace. On each shard, VTTablet creates a shadow table with the target schema, then starts a VReplication stream that copies existing rows in primary-key-ordered chunks while simultaneously tailing the binary log to apply concurrent writes. The Vitess lag throttler watches replica lag on each shard and pauses the copy whenever lag exceeds the threshold, resuming automatically — this is the same backpressure mechanism used for resharding, so it is topology-aware by construction. When the copy is caught up, the migration reaches complete (or complete (postponed) if you passed --postpone-completion), and an atomic RENAME swaps the shadow table in during a brief write-lock window bounded by --cutover-threshold.

Because the state lives in _vt.schema_migrations and is exposed through SHOW VITESS_MIGRATIONS, the migration is an observable, resumable entity — a VTTablet restart mid-copy resumes from the persisted VReplication position rather than restarting the copy. The per-shard phase model and how controllers survive restarts are covered in tracking migration progress and state machines.

gh-ost #

gh-ost is trigger-less: it connects to a shard’s primary (or, ideally, a replica), reads the binary log stream, and copies rows into a _gho-suffixed ghost table while applying binlog events for concurrent changes. It throttles against a configurable set of replicas and a set of load metrics, and performs an atomic table swap at cutover. On a sharded fleet, gh-ost has no concept of a keyspace — you invoke one process per shard against that shard’s primary, and your orchestrator is responsible for discovering shard endpoints from the topology, sequencing the runs, and scraping each process’s progress (its hooks and status socket) back into a unified view. The classic operational hazard here is metadata-lock contention at the swap, dissected in resolving gh-ost lock contention in sharded MySQL.

pt-online-schema-change #

pt-online-schema-change (pt-osc) takes the older trigger-based approach: it creates a _new table, installs AFTER INSERT/UPDATE/DELETE triggers on the original to mirror live writes, copies existing rows in chunks, then atomically renames. Triggers make it broadly compatible with almost any MySQL version, but they add write amplification to every transaction on the source table for the duration of the copy, and they conflict with pre-existing triggers. Like gh-ost, pt-osc is topology-blind and must be driven per shard by your orchestrator.

The Decision: A Topology-Aware Comparison #

For a Vitess-managed sharded keyspace the default answer is the native vitess strategy, because it is the only option where the cutover barrier, throttling, and progress are the control plane’s own responsibility rather than something your orchestrator must reconstruct per shard. External tools earn their place in three situations: you are migrating MySQL instances that Vitess does not (yet) manage; you need a throttling policy more granular than the lag throttler exposes; or you are on a MySQL version or storage-engine configuration where the trigger-based path is the only compatible one. Choosing an external engine does not exempt you from coordination — you still owe the fleet the same global barrier described in coordinating multi-shard schema migrations; you just have to implement it yourself around out-of-band processes.

Step-by-Step: Submitting a Migration Each Way #

1. Submit via the native vitess strategy #

Set the strategy on the session and issue ordinary DDL through VTGate. Vitess fans it out to every shard and returns the migration_uuid to the client:

-- Connected to VTGate on the commerce keyspace
SET @@ddl_strategy = 'vitess --postpone-completion --singleton';
ALTER TABLE orders ADD COLUMN fulfilment_center_id BIGINT UNSIGNED NULL;

--postpone-completion holds every shard at the caught-up-but-not-cut-over state so you can fire a coordinated cutover later; --singleton rejects a second concurrent migration on the same table. Equivalently, submit through the control plane so the invocation is scriptable and auditable:

vtctldclient ApplySchema \
  --ddl-strategy "vitess --postpone-completion --singleton" \
  --sql "ALTER TABLE orders ADD COLUMN fulfilment_center_id BIGINT UNSIGNED NULL" \
  commerce

Verify the migration was accepted on every shard before proceeding:

vtctldclient OnlineDDL show commerce <migration_uuid>

2. Fire the coordinated cutover once every shard is caught up #

With postponement, no shard swaps until you say so. Wait for the global barrier — every shard reporting complete — then complete the cutover fleet-wide:

vtctldclient OnlineDDL complete commerce <migration_uuid>

3. Run an external tool per shard (when native is not the fit) #

External tools need a shard endpoint, not a keyspace. Resolve each shard’s primary from the topology, then invoke the tool against it. A gh-ost run against one shard’s primary:

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

The --postpone-cut-over-flag-file is the external-tool analogue of --postpone-completion: gh-ost copies and stays in sync but will not swap until the flag file is removed. Your orchestrator removes the file on every shard’s process only after all shards report caught-up, reconstructing the barrier by hand.

4. Drive and observe the whole loop from Python #

VTGate speaks the MySQL protocol, so a plain DB-API driver polls native migration state; external-tool runs are subprocesses whose status you scrape. A minimal barrier-aware controller for the native path:

import time
import pymysql

TERMINAL_FAIL = {"failed", "cancelled"}

def shard_states(conn, uuid):
    """Return {shard: migration_status} for one migration across every shard."""
    with conn.cursor(pymysql.cursors.DictCursor) as cur:
        cur.execute("SHOW VITESS_MIGRATIONS LIKE %s", (uuid,))
        return {r["shard"]: r["migration_status"] for r in cur.fetchall()}

def await_barrier(conn, uuid, ready="complete", poll=15):
    """Block until every shard is caught up and postponed at the cutover gate."""
    while True:
        states = shard_states(conn, uuid)
        if any(s in TERMINAL_FAIL for s in states.values()):
            raise RuntimeError(f"migration {uuid} failed on a shard: {states}")
        if states and all(s == ready for s in states.values()):
            return states                      # safe to fire the coordinated cutover
        time.sleep(poll)

conn = pymysql.connect(host="vtgate.internal", port=15306, db="commerce")

Reading persisted state before every action is what keeps a restarted controller from double-submitting — the same idempotency invariant that governs the state machine in the parent orchestration model.

Configuration Reference #

The flags below dominate the behaviour and duration of a migration. Defaults favour safety over speed; the recommended column is production-oriented for a large sharded fleet.

The misconfigurations that cause the most pain are predictable. Submitting with the native direct strategy on a sharded keyspace bypasses managed Online DDL and runs a blocking ALTER on every primary — a fleet-wide stall. Omitting --postpone-completion (or the gh-ost postpone flag file) forfeits the global barrier, so each shard cuts over the instant its copy finishes and the keyspace spends an unbounded window serving mixed schemas. Setting the retention too low lets the table garbage collector drop the shadow/ghost table before a same-day rollback can reuse it, turning a reversible change into a full re-copy.

Failure Modes Specific to Engine Choice #

Stalled native copy from replica lag. Symptom: migration stuck in running, throttler metric mysql_lag above threshold, copy throughput near zero. Root cause: the lag throttler is doing its job — replicas cannot absorb the copy write volume. Mitigation: let the throttler self-heal; if lag never recovers, reduce copy concurrency or move the heavy copy to an off-peak window per region, as covered in scheduling DDL windows across multiple timezones.

External-tool run invisible to the control plane. Symptom: SHOW VITESS_MIGRATIONS returns nothing, yet a _gho or _new table is growing on a primary. Root cause: an external tool was run out-of-band; Vitess neither scheduled nor tracks it. Mitigation: register every external run in your own orchestration store keyed by shard and target schema hash, and alert on _gho/_ghc/_new tables that have no matching orchestrator record — an orphaned external run will otherwise silently hold triggers or a binlog reader.

Trigger conflict with pt-osc. Symptom: pt-osc aborts with “table already has triggers.” Root cause: the target table already carries application triggers, which pt-osc’s trigger-based mirror cannot coexist with. Mitigation: switch that table to gh-ost or the native strategy (both trigger-less), or reconcile the existing triggers first.

Partial cutover across shards. Symptom: some shards on the new schema, others still on the old; queries spanning them see two table shapes. Root cause: a shard-local failure fired after the barrier released and the cutover fan-out began. This is most dangerous for cross-shard transactions, which can observe both shapes at once. Mitigation: treat the cutover fan-out as all-or-nothing — on any shard’s swap failure, immediately re-swap the shards that already cut over back to their retained original table (possible only because retention kept it alive), then re-queue the failed shard.

Metadata-lock contention at swap. Symptom: the final RENAME times out; metadata-lock waits spike in performance_schema. Root cause: a long-running transaction or open cursor holds the table’s metadata lock. Mitigation: kill or wait out the blocking transaction, then retry the cutover — the detailed gh-ost variant is worked through in the lock-contention page linked above.

Verification #

Confirm the outcome, do not assume it. For the native path, every shard should report a terminal complete and no leftover artifacts:

# Every shard should show migration_status = complete
vtctldclient OnlineDDL show commerce <migration_uuid>

# The keyspace should be free of orphaned _vt artifact tables
vtctldclient OnlineDDL cleanup commerce <migration_uuid>

Confirm the new column is actually visible through the router, not just present on disk — query through VTGate and check it resolves to the shards you expect:

-- Through VTGate: the new column must exist on every shard's routed view
SELECT fulfilment_center_id FROM orders WHERE order_id = 1082337;

Then watch the post-cutover signals for a few minutes: query-plan p99 latency (optimizers recompile against the new definition and the buffer pool for the rebuilt table is cold), the _vt/_gho table count (should return to baseline), and the throttler metric (should sit clear of its threshold). Only after p99 settles back to baseline should the change be declared truly done — skipping that window converts a clean migration into a visible latency incident.

Related #

• Coordinating Multi-Shard Schema Migrations — serialization, phasing, and the global cutover barrier your chosen engine must respect.
• Tracking Migration Progress and State Machines — the per-shard state model and how progress differs between native and external engines.
• Resolving gh-ost Lock Contention in Sharded MySQL — diagnosing and clearing metadata-lock stalls at the external-tool cutover.
• Scheduling DDL Windows Across Multiple Timezones — timing the heavy row copy against per-region traffic troughs.
• Configuring Lookup Vindexes for Cross-Shard Joins — why a column referenced by a vindex constrains the safe cutover order.

← Back to Online DDL Orchestration & Migration Coordination · Related area: Vitess Sharding Architecture & Topology Design