Online DDL in Vitess 16+: choosing between vitess, gh-ost, and pt-osc strategies for large sharded tables

Answer to the Question

For a Vitess 16+ cluster with large, write‑heavy sharded tables, the following repeatable policy works best:

1. Default to the built‑in vitess strategy when:
   • The migration must be fully revertible (REVERT works on all shards).
   • Per‑shard progress visibility is required (use vtctldclient OnlineDDL show).
   • Application teams want a single, internally managed workflow without external binaries.
2. Choose gh-ost when:
   • The team already uses gh‑ost for non‑Vitess migrations and wants its interactive controls (pause, throttle, postpone cut‑over).
   • Replication lag is a concern and the cluster is configured for row‑based binlogs.
3. Choose pt-osc only when:
   • There is an existing Percona‑toolchain that the team prefers.
   • The table write load is moderate; heavy write tables suffer from trigger‑based amplification.
   • The cluster can tolerate the extra write amplification and the tool’s trigger‑based copy mode.

These criteria are driven by table size, write throughput, MySQL binlog format, and the need for revertibility. All strategies require roughly 2× the table size on disk during migration, and a brief cut‑over that stalls new connections for <1–3 s.

Measuring and Bounding Cut‑over Impact

1. On staging, run the same ALTER TABLE with the chosen strategy under production‑like write load.
2. Record:
   • Copy duration (seconds).
   • Peak disk usage (bytes).
   • Connection stall: capture the 99th percentile of connection latency during cut‑over using a lightweight load‑generator.
3. Set a policy threshold: if the p99 stall exceeds 2 s, schedule cut‑over in a low‑traffic window or switch strategy.

Cluster‑Wide Governance

• Enforce a default strategy by setting the @@ddl_strategy variable or the ApplySchema ddl_strategy flag in a CI pipeline that all migrations must pass through.
• Audit after each migration with:

  vtctldclient OnlineDDL show --keyspace ks --shard s1
  

  This shows strategy_used and state for every shard.
• Reject or flag any migration that reports a strategy other than the cluster default.

Version‑Sensitive Notes

• Revert support and throttler integration differ between Vitess 16, 17, and later; pin the policy to the exact deployed release and re‑validate on each major upgrade.
• All three strategies require row‑based binlogs; ensure binlog_format=row and binlog_row_image=full are set.
• External tools (gh‑ost, pt‑osc) must be the same major version as the Vitess release; mismatches can cause silent failures.

Missing Diagnostic Detail?

If your cluster uses a non‑standard binlog format or has custom replication lag thresholds, let us know so the recommendation can be adjusted.