Writing a Rollback Plan for a Schema Change

A rollback plan that a team can actually rehearse has eight sections. Write them in this order; each one depends on the one before.

1. Header: migration name, ticket, the exact commit hashes of the migration and of the application version that expects it.
2. Forward steps: numbered, each with the SQL or command.
3. Reverse steps: numbered to match the forward steps, each with the SQL or command.
4. Irreversible data: a table of what gets destroyed, when, and where the recovery copy is.
5. Point of no return: the numbered step after which rollback is not possible, and why.
6. Validation queries: the statements that prove success or failure at each checkpoint.
7. Owners and decision authority: who runs each command, who calls the stop, who approves a restore.
8. Communication: who is told what, on which channel, at which timestamp.

The eighth item is easy to skip and the most useful under pressure. A rollback is a business event as much as a technical one, and the difference between a rehearsed recovery and an improvised one can be measured in downtime (AWS Migration blog).

Reverse steps with the exact command

Do not write "remove the column." Write the statement. For an expand step that adds a nullable column:

-- forward
ALTER TABLE users ADD COLUMN email_address text;

-- reverse
ALTER TABLE users DROP COLUMN IF EXISTS email_address;

Then write the check that the reverse actually took effect:

SELECT count(*) FROM information_schema.columns
WHERE table_name = 'users' AND column_name = 'email_address';
-- expect 0

For a backfill, the reverse is not a statement but a stop condition. The plan should say how the backfill job is identified and terminated, for example by job ID in the scheduler, and what happens to rows already copied. Usually the answer is "leave them": a nullable column with extra data hurts nothing, and dropping it is the later reverse. The expand and contract write-up is explicit that during transition both structures coexist and data may be duplicated, often with triggers, so the plan needs an explicit statement on what happens to that duplicate data on the way back (Jasmin Fluri).

For a constraint change, the reverse is another constraint statement. If the forward step sets a column NOT NULL, the reverse is ALTER TABLE users ALTER COLUMN email_address DROP NOT NULL, which is cheap. The expensive part is getting to NOT NULL safely in the first place: adding the constraint directly takes an ACCESS EXCLUSIVE lock, which blocks reads as well as writes, while the database scans every row (Atlas). The safe forward path is backfill, add a NOT VALID check constraint, validate it, then promote, and the plan should spell out which of those sub-steps is reversible and which is not (RunxBuild).

For index changes, the reversibility depends on how the index was built. A plain CREATE INDEX takes a SHARE lock, which allows concurrent reads but blocks all writes for the duration of the build, while CREATE INDEX CONCURRENTLY takes a weaker lock and lets writes continue at the cost of a longer build (Bytebase). Both are reversed by DROP INDEX, but if a concurrent build fails it leaves an invalid index behind that must be dropped by hand, so the plan should include the query that finds invalid indexes in that case (Stack Overflow). The CONCURRENTLY keyword is a build instruction, not a stored property of the index, which is why a completed index looks identical either way (PostgreSQL docs).

The data that cannot be un-dropped

This is the section people read when something has gone wrong, so make it brutal. For every destructive action, record four things: what is destroyed, the step number that destroys it, whether a copy exists outside the affected table, and how long that copy survives. A column drop, a table rename followed by a drop, a type change that truncates, and a backfill that overwrites a column in place all belong here.

A worked example makes the cost concrete. Suppose a backfill copies users.name into new first_name and last_name columns by splitting on the first space. Then a later migration drops users.name. If the split is wrong for compound surnames, the original is gone from the table, and the reverse is not "run the backfill backwards" because the split cannot be inverted. The only recovery is a backup, and its value decays by the minute. An article on rollback strategy puts it plainly: a backup from fifteen minutes ago is far more useful than one from twenty-four hours ago, and restoring it sacrifices everything written since (Octopus).

So the plan states, in the irreversible data table, something like: "users.name dropped at step 9. Full table snapshot taken at step 3 into users_name_backup. Snapshot retained seven days. Retention confirmed by backup job owner." That sentence tells a future reader exactly what to ask for and whose answer to trust.

Data migration rollback planning goes further and separates the four kinds of state that must recover together: schema, data, configuration and application version, plus a check that the recovered versions actually work together (softwaremodernizationservices.com). A backup proves you have a recovery point. It does not prove the old application can read the restored schema, so include the application version in the plan and rehearse the pair.

Marking the point of no return

The point of no return is the first step after which the reverse steps no longer restore the system. It is almost never the first destructive statement; it is the commit, deploy or backfill that makes the destruction visible to users. Common markers:

• The merge of the contract migration that drops the old column, once it has run in production.
• The cutover of reads from the old column to the new one, once the old column stops receiving writes and the backfill has stopped.
• The first write to a new table by the live application, after which the old table is stale.
• The promotion of a NOT NULL constraint, after which old application code that omits the column fails.

Write the marker as a numbered step, with a one-line reason, like "Step 7, first application write to orders_v2. Before this, rollback is a code deploy plus a drop. After it, rollback requires replaying orders from the v2 table back into v1." That sentence is what the on-call engineer reads at 02:00 when deciding whether to roll back or fix forward. It is also the sentence that should have been argued about in design review, because the point of no return is a design choice, not an accident.

The cutover literature recommends that the decision to roll back or fix forward be assigned to a named person and recorded alongside the boundary itself (softwaremodernizationservices.com). The Octopus article makes the same point from the deployment side: sometimes rolling forward is less risky than rolling back, and someone must weigh which risk is larger (Octopus). Put that person's name and backup name in the document.

The review checklist you already use for a migration can become the skeleton of the rollback document. Each item becomes a line with a checkbox, an owner and an evidence link. Run it as an argumentative review, not a tick-box exercise: the goal is to find the step where the plan quietly assumes something.

The lock-risk line deserves special attention because it is where "instant" statements turn into outages. A harmless-looking ALTER TABLE can queue behind a long-running query and, because lock requests are ordered, block every subsequent read behind it; a lock timeout set inside the transaction makes the statement fail fast instead of blocking the table (RunxBuild). Write that setting into the plan for every step that touches a busy table, with the value you chose and the reason.

The ownership line also deserves care beyond the obvious. Name a primary and a secondary for each command that can destroy data, and for any step that requires a restore. A plan that says "the DBA" is not a plan.

A rollback plan that has not been executed is a hypothesis. Rehearse it on a copy of production data, with production-sized tables and the real migration scripts, and time each phase. The rehearsal should follow the same sequence the document gives, in the same order, including the boring steps like taking the snapshot and posting the status message.

Record four numbers and a narrative:

• Time from "stop called" to "traffic drained or writes fenced."
• Time to run the reverse steps.
• Time to restore from backup if the rehearsal includes it, measured to the point where the old application serves requests.
• Total time from first symptom to verified recovery.

Recovery targets are normally expressed as an acceptable data-loss window and an acceptable restore time, and they are only meaningful if measured; define where the clock starts and ends, including fencing writes, transferring data, checking the application and reopening service (softwaremodernizationservices.com). Time the whole thing, not just the SQL. In practice the reverse statements are rarely the slow part.

Rehearsing the rollback itself is called out as a distinct practice in rollback strategy guides, which recommend testing rollbacks in staging rather than assuming they work because the forward path did (Flagsmith). Run the plan once, fix what broke, run it again, and attach the second run's log to the change record.

If your staging database is small, the rehearsal will mislead you on lock timing. A statement that scans a few thousand rows in milliseconds can hold a lock for minutes on a table with millions, and tools that lint migrations for blocking behavior exist precisely because static rules miss the state of the database (Hacker News discussion). Where a tool manages online changes for you, such as an online schema change utility that copies to a shadow table and renames atomically, the plan should say which steps the tool owns and which it does not, because the tool drops the original table when it finishes (Percona).

Take a small change that still has a real point of no return: adding email_address to users, backfilling from email, indexing it, and planning to drop email later.

Forward steps, with reverses and a no-return marker:

1. Snapshot users to a backup table. Reverse: none needed. Data at risk: none yet.
2. ALTER TABLE users ADD COLUMN email_address text; Reverse: DROP COLUMN. Reversible.
3. Deploy code that writes both columns. Reverse: redeploy previous version, which ignores the new column. Reversible.
4. Backfill in batches from email to email_address. Reverse: stop the job; duplicated values are harmless until step 6.
5. CREATE INDEX CONCURRENTLY on the new column. Reverse: DROP INDEX. Reversible; check for invalid index first.
6. Switch reads to email_address. Reverse: flip the flag back. Reversible while dual writes are still on.

The point of no return is the moment the dual write is removed and email stops being updated. After that, going back means reconciling values written only to email_address. That step should not appear in the same migration as the expand. Keep it in a later change with its own plan, or the recovery cost of a five-minute outage becomes a data reconciliation project.