PATTERN Cited by 1 source

Auto-detect INSTANT-DDL eligibility¶

Problem¶

MySQL 8.0+ offers two roughly-non-blocking schema-change paths that differ by two orders of magnitude in execution time and by one order of magnitude in revert story:

ALGORITHM=INSTANT: seconds on a large table, metadata-only, no shadow- table, not revertible at the mechanism altitude.
Shadow- table online schema change (pt-osc / gh-ost / spirit / Vitess): hours on a large table, physical- copy rewrite, [[patterns/instant-schema-revert-via- inverse-replication|revertible]] via the kept-alive shadow-table substrate.

INSTANT eligibility depends on which MySQL version the target server runs and which specific change shape the ALTER TABLE expresses — adding a column is fine on 8.0; adding arbitrary columns in arbitrary positions is only fine on 8.0.29+; dropping a column that participates in an index is never eligible, and so on. Operators writing a DDL statement are expected to know the per-version eligibility matrix in order to exploit the fast path — a knowledge tax at submission time.

Solution¶

Let the migration tool auto-detect INSTANT eligibility when the DDL is submitted. The operator submits a single DDL through the shadow-table tool's normal submission surface. The tool inspects the statement against MySQL's eligibility rules and:

Invokes ALGORITHM=INSTANT directly if every statement qualifies — seconds, no copy, no shadow- table, no binlog bloat.
Falls back to the shadow-table path otherwise — hours on a large table, with the full revertibility substrate the shadow-table path provides.

One submission surface; engine-internal fast-path decision; operator sees "migration in progress" and doesn't care which path the engine took. The per-version eligibility matrix becomes the tool's problem, not the operator's.

Canonical framing from Noach (2024):

"Both vitess and spirit go an extra mile and can auto detect when a migration can be fulfilled using INSTANT DDL, which means you don't need to think about it or be aware of which particular version supports which changes." (Source: sources/2026-04-21-planetscale-the-state-of-online-schema-migrations-in-mysql)

Mechanism¶

At submission time, the tool:

Parses the incoming DDL into an AST.
Inspects each statement against the target server's INSTANT eligibility matrix (MySQL version-aware).
If every statement qualifies, executes via ALGORITHM=INSTANT — metadata-only, server-internal.
Otherwise executes via the shadow-table path — build shadow, backfill, catch-up, cut-over.

Additional bookkeeping:

Revertibility implications are logged / surfaced to the operator — taking the fast path forfeits the [[patterns/instant-schema-revert-via-inverse- replication|inverse-replication revert window]] that the shadow-table path provides as an emergent property.
Composition rules: for multi-statement deploys, the tool may require every statement to qualify for INSTANT before taking the fast path (the all-or-nothing eligibility composition rule).

Instantiations¶

Vitess: the schemadiff library evaluates each DDL against a per-version eligibility model. Vitess 21 shipped expanded INSTANT scenario analysis. Relates to the larger instant-deploy eligibility framework canonicalised on the wiki via Noach's 2024-09-04 Instant Deploy Requests post.
spirit (CashApp): implements the same detection. Positioned by Noach as the "recent newcomer" third-party tool that co-pioneers this pattern alongside Vitess.
gh-ost and pt-online-schema- change do not auto-detect INSTANT eligibility — they always execute the shadow-table path. Operators running these tools who want the INSTANT fast path must invoke ALTER TABLE …, ALGORITHM=INSTANT outside the tool's workflow.

Trade-offs¶

Opaque-path risk. The operator submits a DDL and gets "migration complete" in either seconds or hours; without explicit logging, which path was taken isn't obvious. Tools in this class should surface which path was taken and, for the fast path, warn that the change is non-revertible.
Revertibility asymmetry at the product-UI altitude. Canonicalised as concepts/schema-change-revertibility-asymmetry — reverting an ADD is cheap; reverting a DROP is expensive even if both took the INSTANT fast path. Auto-detection doesn't reshape the asymmetry, only the discovery surface. Platform-level opt-in (see patterns/instant-deploy-opt-in) is the answer at product altitude; auto-detection is the answer at tool altitude.
Cross-version replica skew. If primaries and replicas are on different MySQL versions (during a rolling upgrade), a statement INSTANT-eligible on the primary may not be on a replica. Tools need a "least-common-denominator" eligibility model across the cluster.
Adds a dependency on MySQL internals tracking. The tool must keep its eligibility matrix current with each MySQL release — operational burden that pt-osc / gh-ost deliberately sidestep by always running the safe path.

Composes with¶

patterns/shadow-table-online-schema-change — the fallback path when eligibility fails. Auto-detect sits on top of it, not beside it.
patterns/instant-deploy-opt-in — PlanetScale's product-layer version of this pattern. Where the tool-layer pattern is "tool picks optimal path", the product-layer pattern is "operator opts into fast path knowing the revert trade-off". The two patterns can coexist — the tool detects eligibility; the product exposes the choice to the operator.

Seen in¶

sources/2026-04-21-planetscale-the-state-of-online-schema-migrations-in-mysql — Shlomi Noach (PlanetScale, 2024-07-23). Canonical wiki disclosure of the pattern and its two instantiations (systems/vitess and systems/spirit). Positioned as the reconciliation that makes INSTANT/shadow-table coexistence operationally tractable at the tool altitude. Noach's load-bearing observation: "if you already have to use one of the 3rd party solutions, you may as well use it all the time" — which is exactly what auto-detection achieves without forfeiting INSTANT- speed on eligible shapes.