Reverting a Completed Online DDL Migration

A migration that has already cut over is not necessarily permanent. Vitess keeps the pre-migration table on disk for a bounded window after the swap, and within that window a single command re-swaps it back — but only if you understand exactly what “revert” restores and what it cannot.

Context

Revert is the recovery half of the cutover story told in Throttling and Cutover Control for Online DDL: the same atomic-swap machinery that put the new table in service can put the old one back. It matters most when a cutover technically succeeds but the result is wrong — a regressed query plan, an application incompatibility, a bad column type — and you need to undo the schema change without a full re-copy in the opposite direction. Revert is only possible because the completion path deliberately did not destroy the original; that retention window is the whole subject here. The migration-state vocabulary this page leans on is defined in Decoding Online DDL Migration States.

How the retained table makes revert possible

When a migration completes, Vitess does not drop the original table — it renames it aside into an internal, retained artifact and renames the finished shadow table into its place. For the length of the --retain-online-ddl-tables window (default 24 hours) that retained original sits untouched on every shard. vtctldclient OnlineDDL revert <uuid> exploits it directly: it performs another atomic swap, this time renaming the retained original back into service and the now-current table aside. No rows are re-copied. The revert is near-instant because it is pure table renaming — the data was never thrown away, only set aside.

Revert is itself a first-class Online DDL migration. It is submitted, it gets its own migration_uuid, and it fans out per shard exactly like the migration it undoes. That means it obeys the same cutover discipline: it takes a brief write lock per shard to do the swap, and it can be coordinated across the fleet.

Revert against the retention window Completed holds the new table serving with the original retained. A revert command within the window swaps back to Reverted with the original serving. When retention expires the original is garbage-collected and revert is no longer possible. complete new table serving, original retained reverted original swapped back into service non-revertible retained original garbage-collected revert cmd (window open) retention window expires → revert no longer possible

Reverting, step by step

First confirm the migration is still within its retention window and the artifact still exists — a revert after garbage collection fails outright. Then submit the revert against the original migration’s UUID:

# Confirm the completed migration and its retained artifact still exist
vtctldclient OnlineDDL show commerce a1b2c3d4_...

# Revert it — swaps the retained original back in, per shard
vtctldclient OnlineDDL revert commerce a1b2c3d4_...

The revert returns its own UUID. Track that UUID to confirm the reversal reached complete on every shard, exactly as you tracked the original cutover:

vtctldclient OnlineDDL show commerce <revert_uuid>

Because the revert is a per-shard migration, coordinate it the same way you coordinate any fleet-wide swap. If the original migration was cut over as a coordinated barrier, the revert must be too — you do not want half the fleet reverted and half still on the new schema. The barrier mechanics are the ones from Coordinating Multi-Shard Schema Migrations.

What revert does and does not restore

This is the part that catches people. Revert restores the table structure. It does not un-write data.

Consider an ALTER that added a nullable column. While the new schema was live, the application wrote values into that column. Revert swaps the original (column-less) table back into service — but the original table was set aside at cutover and has been kept in sync with writes only for structural compatibility, not for the new column that does not exist in it. The practical rule is direction-dependent:

  • Additive, structure-only changes (adding a nullable column, adding an index) revert cleanly. Reverting drops the added column or index; the base rows are intact because the retained table tracked ongoing writes to the columns it shares.
  • Changes that destroy or transform information are not safely revertible. If the migration narrowed a column type, dropped a column, or rewrote values, the information removed by the forward migration is gone from the new table, and reverting to the old structure cannot resurrect data that the forward change discarded. Revert restores the shape, not the lost bytes.
  • Data written under the new schema into new columns does not survive a revert. Reverting to a structure without that column necessarily loses what was written there. If that data matters, dump it before reverting.

So revert is a true “undo” only for additive structural changes. For destructive or transformative changes, treat revert as “restore the old shape and accept the data consequences” — and if that is not acceptable, your rollback is a forward migration, not a revert.

A useful way to plan is to classify every migration before you run it by how it can be rolled back. Additive-only changes (add nullable column, add index) are revert-safe: keep the retention window open and revert is a genuine undo. Changes that add a column the application will immediately populate are revert-costly: the structure reverts cleanly but the newly written data is lost, so plan a dump of that column as part of the rollback runbook. Destructive changes (drop column, narrow type) are revert-ineffective for data purposes: revert restores the old shape but nothing restores the discarded information, so the real rollback is a forward re-migration and you should treat the original change as one-way. Writing that classification into the migration ticket turns “can we roll this back?” from a mid-incident guess into a decision already made.

Why revert beats a hand-written reverse migration

When revert applies, prefer it over submitting a fresh ALTER that undoes the change by hand. Revert re-swaps a table that already exists and is already in sync, so it is a rename, not a re-copy — it completes in seconds and puts no copy load on the fleet. A hand-written reverse migration re-runs the whole Online DDL machinery: a full row copy, another pass through the throttler, and another cutover barrier to coordinate, taking as long as the original change and re-imposing all its replica-lag risk. The only time a forward reverse migration is the right tool is when revert cannot apply — the retention window has closed, or the change was destructive and you are rebuilding the old shape from scratch. Inside the window, for a revertible change, revert is strictly cheaper and safer.

The retention window is the deadline

--retain-online-ddl-tables (default 24h) is the hard clock on revertibility. Until it expires the retained original is protected; once it does, Vitess’s table garbage collector is free to drop it, and the migration becomes permanently non-revertible. Two consequences follow. First, set the retention long enough to cover your realistic rollback horizon — if a regression might not surface until the next business day, 24h is marginal and 48h–72h is safer. Second, vtctldclient OnlineDDL cleanup forces the garbage collection early; running it immediately after a cutover to tidy up artifacts throws away your revert path on purpose. Do not cleanup until you are certain you will not revert.

Edge cases and gotchas

  • Retention expired. Past the --retain-online-ddl-tables window the retained original is gone and revert fails. Recovery is now a forward migration that re-creates the old structure — and any data lost to the original forward change stays lost.
  • cleanup run too early. OnlineDDL cleanup forcibly drops retained artifacts before the window ends, deleting the revert path. Never run it on a migration you might still roll back.
  • Non-revertible change types. Dropping a column, narrowing a type, or transforming values cannot be undone by revert in a way that restores the removed information. Revert only guarantees the old shape returns.
  • New-column data loss on revert. Anything the application wrote into a column added by the forward migration is discarded when you revert to a structure without it. Export it first if it matters.
  • Partial revert after partial cutover. If the original cutover only reached some shards, reverting the whole UUID acts per shard — reverted shards return to the old shape while never-cut-over shards were already there. Reconcile the actual per-shard state before assuming the fleet is uniform.
  • Reverting a revert. A revert is itself a completed migration with its own retention window, so you can revert the revert to reapply the change — within its window. Track UUIDs carefully or you will lose the thread of which shape is live.

Verification

Confirm the reversal actually landed on every shard and the old structure is what the router now serves — not just that the command returned. Check the revert UUID reached complete fleet-wide, then confirm the reverted-away column is gone through VTGate:

# The revert migration should be complete on every shard
vtctldclient OnlineDDL show commerce <revert_uuid>

-- Through VTGate: a column removed by the revert must no longer resolve
SELECT region_id FROM orders WHERE order_id = 4820117;  -- expect: Unknown column

The query erroring with “Unknown column” on the reverted column is the positive signal that the old structure is live on the routed shards. Cross-check that no orphaned artifact tables remain and that replica lag and query-plan p99 have settled, exactly as after any cutover.

← Back to Throttling and Cutover Control for Online DDL