CONCEPT Cited by 1 source

Schema-change revertibility asymmetry¶

Definition¶

Schema-change revertibility asymmetry is the structural observation that the compensating DDL for an ADD COLUMN and the compensating DDL for a DROP COLUMN are not symmetric operations, even when both run under MySQL's ALGORITHM=INSTANT fast path. ADD COLUMN → DROP COLUMN is a safe, reversible pair at the mechanism altitude. DROP COLUMN → ADD COLUMN is fundamentally destructive: both the data and the metadata are lost at the drop, and the ADD cannot recover either.

Canonical framing from Noach (2024):

"The anti-change for ADD COLUMN is a DROP COLUMN, and since both are supported by INSTANT DDL, chances are you'll be able to recover quickly and relatively safely."

"What if your change was a DROP COLUMN? Lost data aside, what is the anti-change you'd apply to restore the previous schema? Not only data was lost, but also metadata. What was the column type? Length? Was it nullable? That information cannot be inferred unless you have the previous schema. In all likelihood, you use version control to manage your schema and are thus able to extract the previous definition. It is worth pointing out, though, that crafting the anti-change of a schema migration is nontrivial." (Source: sources/2026-04-21-planetscale-the-state-of-online-schema-migrations-in-mysql)

Two losses, not one¶

A DROP COLUMN destroys two things:

Data — every value in every row for that column is gone. Recovery requires backup-sourced reconstruction or upstream re-derivation.
Metadata — the column's type, length, nullability, default, character set, collation, and participation in indexes and constraints are gone from the data dictionary. The reverse DDL needs all of these to even be authored.

The metadata loss is the less-obvious half of the asymmetry and is the harder half to mitigate. Data loss has a well-understood recovery mechanism (backups, PITR). Metadata loss requires the operator to author the anti-change from some non-database source of truth — typically a version-controlled schema file.

Why this matters for INSTANT DDL specifically¶

ALGORITHM=INSTANT removes every operational barrier that would make a destructive DDL feel risky on a production database:

No multi-hour wait that gives the operator time to realise the mistake.
No disk-space pressure that forces a pre-flight check.
No cancellation cost that would reward hesitation.
No replica lag to surface the change to the broader team.

Noach's human-factors framing:

"The answer is with the human behavior of always choosing INSTANT where possible. You're an instant away from destroying your data, and with no barriers to hold you back, nor a mechanism (short of backups and delayed replicas) to take you back to safety." (Source: sources/2026-04-21-planetscale-the-state-of-online-schema-migrations-in-mysql)

The revertibility asymmetry is therefore an INSTANT-specific risk amplifier rather than a DROP COLUMN risk in general — on a shadow-table migration the hours-long copy phase is itself a cooling-off period, and on a Vitess-managed migration the inverse-replication revert window preserves both data and the old-schema metadata structurally.

Breaking queries, not just losing data¶

A secondary consequence of DROP COLUMN that the asymmetry amplifies: callers with explicit references to the dropped column break immediately. Noach:

"If dropping the column did cause queries to break, you will then need to either fix all the queries, or attempt to re-introduce the column."

Re-introducing the column restores the schema (modulo the metadata-authoring friction) but does not restore the data — the callers' SELECT dropped_col queries now return NULL (or the new default) for every row rather than the values they expect. The observable- behaviour break is not reversible by reversing the DDL.

Partial mitigation via invisible columns¶

MySQL invisible columns (8.0+) are the sanctioned pre-drop discovery primitive: mark the column invisible first, let traffic run, see what breaks on SELECT *-shaped callers. But as Noach notes, this only catches the SELECT * subset — explicit-column callers retain access and are not surfaced by the invisibility flip.

The architectural contract¶

The asymmetry implies an architectural contract for any platform that offers INSTANT-speed schema changes:

Drops should default to non-instant (shadow-table path with revert window) even when INSTANT eligibility is met, unless the operator explicitly opts in to the fast path.
Adds can default to instant safely, because the compensating DROP is also instant-eligible and non-destructive modulo the window of data written to the new column.
Platform revert UI should distinguish adds from drops — "undo this ADD" is a no-op operation; "undo this DROP" is an engineering project.

PlanetScale's instant-deploy eligibility + opt-in design (patterns/instant-deploy-opt-in) implements this contract: the default is Online DDL (revertible); the operator opts into INSTANT when they've decided the speed is worth the asymmetry.

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 asymmetry. Frames ADD/DROP as superficially-symmetric metadata operations that diverge at the revert altitude — one reverts as the inverse instant-DDL; the other reverts as a craft operation requiring out-of-band schema history. Canonical quote: "Not only data was lost, but also metadata." The load-bearing insight behind why PlanetScale makes INSTANT an operator-visible opt-in rather than an automatic optimisation.