Skip to content

CONCEPT Cited by 3 sources

Online DDL

Definition

Online DDL is the family of techniques for applying schema changes (ALTER TABLE, CREATE INDEX, column adds, type changes, charset changes) to a production database without blocking concurrent reads and writes and without a service outage. At the MySQL altitude, Online DDL spans MySQL's native ALGORITHM=INSTANT / INPLACE options (where the engine can apply the change without a full table rebuild), third-party tools like pt-online-schema-change and gh-ost (which build a shadow table via replication-driven copy + swap), and higher-level orchestration in systems like Vitess's VReplication-driven schema-change workflow. The operator-facing discipline is to analyse each DDL statement against the engine's capability matrix (what can be INSTANT? INPLACE? what requires a shadow-copy rewrite?) and pick the cheapest mechanism that satisfies correctness. Pure performance wins come from extending the INSTANT envelope (no rewrite) and from avoiding engine primitives that are slow inside a rewrite (e.g. CONVERT(... USING utf8mb4) when a programmatic text transform is available).

Seen in

  • sources/2026-04-21-planetscale-the-state-of-online-schema-migrations-in-mysql — Shlomi Noach (PlanetScale, 2024-07-23). Canonical three-mechanism-class taxonomy disclosure of the 2024 state of Online DDL. Frames the space as a choice across (i) native ALGORITHM=INPLACE — technically non-blocking on the primary but causes replica-lag-equal-to-operation-duration, making it unusable in primary-replica topologies; (ii) native ALGORITHM=INSTANT — metadata-only, instant on replicas, zero resource cost, but narrow eligibility and non-revertible with metadata-level loss on DROP COLUMN; and (iii) third-party shadow-table tools (pt-osc, gh-ost, spirit, Vitess) — the six-property default for "the (still vast) majority of changes". Canonical new framing: even when INSTANT is eligible, mixing the two execution models in production doubles operational surface — "maintaining two different techniques in your flow/automation creates more complexity. If you already have to use one of the 3rd party solutions, you may as well use it all the time." Reconciliation pattern canonicalised as patterns/auto-detect-instant-ddl-eligibility — Vitess and spirit auto-detect INSTANT eligibility inside the shadow-table tool's submission surface so the operator never has to choose. Vitess goes further, preserving first-class revertibility via [[patterns/instant-schema-revert-via-inverse- replication|inverse-replication-kept-alive]]. The load-bearing 2024 Noach synthesis: 3rd-party shadow-table tools are the default mechanism; INSTANT is a special-case short-circuit inside that default, not a replacement for it.

  • — Lucy Burns, PlanetScale, 2021-05-20. Canonical launch-era framing of Online DDL as the engine under a managed deploy-request workflow. Positions pt-online-schema-change and gh-ost as the two established Online DDL mechanisms ("online or non-blocking schema changes that don't lock tables while being deployed" — both using the shadow- table approach) but notes they are "often run manually and require the support of additional infrastructure." PlanetScale's 2021 contribution was wrapping gh-ost (inside Vitess) behind the branch- based schema-change workflow — Online DDL as the execution engine; branching + deploy-request + pre-flight conflict check + traffic-aware throttling as the management layer. Establishes the Online-DDL- plus-workflow framing that 2024 posts (Coutermarsh, Noach) later deepen with instant-deploy and schema- revert.

  • sources/2026-04-21-planetscale-instant-deploy-requests — canonical fast-path-vs-safe-path disclosure on the wiki. Shlomi Noach (PlanetScale, 2024-09-04) canonicalises that PlanetScale's deploy-request architecture now has two execution paths: the safe-default Online DDL path (shadow-table + cut-over, hours on a large table, revertible via 30-minute inverse-replication window) and the fast-path instant deployment (native MySQL ALGORITHM=INSTANT, seconds on a large table, not revertible, possible multi-second metadata lock). Eligibility is pre-evaluated per deploy request: the fast path applies iff every statement qualifies — see concepts/instant-deploy-eligibility for the three- part composition rule. Online DDL remains the default strategy because its properties (no app-visible lock, guaranteed revertibility) are well-understood; instant is the operator-visible opt-in when performance matters more — canonicalised as patterns/instant-deploy-opt-in. Canonical wiki framing: the fast path is a specialisation of Online DDL for the subset of changes MySQL can fulfil without a rewrite, not a replacement. Ties back to

(Vitess 21 ships "more INSTANT DDL scenario analysis" in schemadiff, widening the fast-path envelope).

  • — canonical new declarative-tool altitude on the wiki. Brian Morrison II's 2022 PlanetScale tutorial uses Atlas CLI to issue MySQL Online DDL declaratively — write schema.hcl, atlas schema apply computes the diff and emits ALTER TABLE … ADD COLUMN … as the engine-level DDL. The canonical framing is that declarative tools sit on top of Online DDL: Atlas doesn't re-implement Online DDL semantics, it invokes the engine's native ALTER TABLE which is subject to the usual INSTANT / INPLACE / rewrite classification. The tutorial is thin on which Online DDL algorithm Atlas requests (or whether it leaves that up to the engine default) — a production-voice deep-dive on Atlas + MySQL 8 Online DDL is still missing from the corpus. Canonical new [[patterns/declarative-schema- management]] pattern + concepts/schema-as-code concept now sit alongside this page; declarative tools are complementary to, not a replacement for, Online DDL — the tools determine when to apply and what the diff is; the engine determines how the apply executes.

  • — Vitess 21 ships multiple Online DDL improvements: ALTER VITESS_MIGRATION CLEANUP ALL command; more INSTANT DDL scenario analysis beyond the documented limitations; charset-change handling now uses programmatic text conversion rather than MySQL's CONVERT(... USING utf8mb4) for performance in primary-key / iteration-key columns; more analysis delegated to the schemadiff library for programmatic power + testability. Reintroduced atomic distributed transactions now integrate "with core Vitess components and workflows, such as Online DDL and VReplication (including operations like MoveTables and Reshard)".

  • sources/2026-04-21-planetscale-behind-the-scenes-how-schema-reverts-work — canonical wiki walk of the online-DDL shape in the VReplication-driven flavour. Guevara + Noach frame online DDL as a four-step pattern — build shadow with new schema, apply DDL to shadow, backfill + track changes, cut over — shared by pt-online-schema-change, gh-ost, and Vitess, canonicalised as patterns/shadow-table-online-schema-change. The post then differentiates Vitess on five design properties (copy + changelog progress both tracked; per- transaction GTID mapping; GTID-driven interleaving; transactional sidecar-state coupling; non-termination after cut-over) — the last of which enables instant schema revert: VReplication is kept alive past cut-over and re-primed in the inverse direction, so the old-schema table stays in sync with every post- cut-over write. Canonical new concepts/shadow-table, concepts/cutover-freeze-point, and concepts/pre-staged-inverse-replication concepts and patterns/instant-schema-revert-via-inverse-replication pattern all originate from this post. Positions PlanetScale as the only production MySQL platform that turns a destructive DDL into a reversible, data-preserving operation — strictly stronger than traditional restore- from-backup rollback (no row loss) but weaker than time travel (rows added post-cut-over reappear without values for columns the old schema had that the new one didn't).

  • systems/vitess
  • systems/mysql
  • systems/vitess-vreplication
  • concepts/shadow-table
  • concepts/cutover-freeze-point
  • concepts/pre-staged-inverse-replication
  • concepts/online-database-import
  • concepts/instant-ddl-mysql
  • concepts/instant-deploy-eligibility
  • concepts/non-revertible-schema-change
  • patterns/snapshot-plus-catchup-replication
  • patterns/shadow-table-online-schema-change
  • patterns/instant-schema-revert-via-inverse-replication
  • patterns/instant-deploy-opt-in

  • companies/planetscale

  • — composition altitude. Coutermarsh (2024) canonicalises how online-DDL fits into a complete application-tier workflow: Vitess online migration is the production-safety substrate; a pre-execution linter catches "any common mistakes" before any DDL runs; a per-target schema-change queue serialises concurrent submissions from multiple engineers and runs combined-schema lint; a 30-minute revert window follows cutover. The canonical framing is that "most large scale companies you can think of are using some variety of online schema change tools for their migrations" — positioning online DDL as table-stakes for large-team production databases, not as a specialist-only concern.

  • — Shlomi Noach, PlanetScale, 2022-05-09. Canonical axiom-layer disclosure of non-blocking migration as tenet 1 of the operational relational schema paradigm. Verbatim: "Some relational databases, and for some types of schema migrations, place a write lock on the migrated table, effectively rendering it inaccessible to the app. This in turn commonly manifests as an outage scenario. An ALTER TABLE migration for large tables can be measured in hours or even days. These blocking migrations are unacceptable to modern development flows and modern apps, and databases must offer non-blocking migrations that allow full access to the migrated table throughout the operation." This is the principles post beneath all other online-DDL mechanism disclosures — Burns 2021 (launch narrative), Noach 2024 (instant deploys), Guevara + Noach 2022-10 (schema reverts), Coutermarsh 2024 (workflow) all instantiate this tenet at the mechanism altitude. Principles post / manifesto format — no new online-DDL mechanism here, but the foundational philosophical charter.

  • — Shlomi Noach, PlanetScale, 2021-07-13. Canonical operational-friction disclosure predating Noach's better-known 2022 paradigm essay by 10 months. The post names online DDL tools (gh-ost, pt-online-schema-change) by name and frames the core problem: "these require access to your production system. The developer needs to understand how to invoke these tools; how to configure throttling; how to observe and monitor their progress; how to clean up their artifacts." Online DDL mechanism-as-library is necessary but not sufficient for developer-owned schema change — the 2021 post canonicalises six additional operational skills (metadata-locking, failure-mode literacy, production topology, tool invocation, throttling, cleanup) plus error handling + coordination as the ambient tax online-DDL-as-a-library does not retire. concepts/schema-change-operational-friction is the new concept page; this Online DDL page is canonicalised as "the mechanism at the bottom of the stack that the deploy-request + schema-change-queue + pre-flight-conflict-check managed layer wraps so the developer doesn't have to touch gh-ost directly." The load-bearing 2021 framing: "A RDBMS schema change is an alien operation for many developers. It feels nothing like a code deployment." Online DDL retires the "alien" property of the mechanism; the managed layer retires the "alien" property of the workflow.

  • — canonical wiki disclosure of Online DDL's foreign-key incompatibility problem and its fix. The standard shadow-table Online DDL mechanism fails on FK-carrying tables for three structural reasons: (1) the shadow table's FKs reference the same parent as the production table, so parent-side writes see double the blocking surface; (2) RENAME TABLE parent TO parent_old at swap time follows the parent-table pointer by default, leaving children's FKs pinned to parent_old — producing a broken schema; (3) FK validation + cascades are applied by InnoDB internally but don't reach the binlog, breaking the VReplication catch-up phase. PlanetScale's fix composes three pieces: internal operations tables + rename_table_preserve_foreign_key (both in the MySQL fork) + application-level cascade orchestration in Vitess. Canonical framing: Online DDL on FK tables requires coordinated changes at the storage engine, the server, the CDC substrate, and the proxy — no single layer can make it work alone. FK constraint names also change on every deployment under shadow-table Online DDL (ANSI SQL requires names unique to the schema; the shadow's synthesized name collides with the production name).

Last updated · 542 distilled / 1,571 read