Rolling Back a Failed Spatial Migration

Problem Statement

A migration failed at 03:12. The deploy is halted, the application is running the previous version, and somebody has to decide within a few minutes whether to re-run, roll back or escalate. This page is the decision procedure — what a failed spatial migration can actually leave behind, how to tell which case you are in, and what to do about each. It complements the zero-downtime migration patterns that aim to prevent the situation.

Why the Naive Approach Fails

The instinctive response is to re-run the migration, and it is right about half the time. The other half it turns a recoverable situation into a confusing one: a second CREATE INDEX CONCURRENTLY fails on the duplicate name left by the first, a re-run backfill re-processes rows it already handled, and an ALTER TABLE that partially applied leaves a schema that matches neither the old nor the new model.

The opposite instinct — restore from backup — is equally reflexive and far more expensive. On a spatial database of any size, a restore is hours, and most failed migrations do not need one.

What a failure can leave behind Three categories. Transactional DDL rolls back completely and leaves nothing. Concurrent DDL leaves an invalid index or a detached partition. A batched data migration leaves committed batches up to its watermark. Only the last two need any action. Three states, and only two of them need work transactional DDL ADD COLUMN, ADD CONSTRAINT, CREATE INDEX without CONCURRENTLY — rolled back entirely nothing to do concurrent DDL CREATE/DROP INDEX CONCURRENTLY, DETACH CONCURRENTLY — artefacts survive the failure clean up, then retry batched data migration committed up to the watermark; the rest untouched — resumable if it was written to be resume or revert

Production-Ready Implementation

The triage query — run this first, before deciding anything:

sql
-- 1. anything still running?
SELECT pid, state, now() - xact_start AS running, left(query, 80) AS query
FROM pg_stat_activity
WHERE application_name LIKE '%migration%' OR query ILIKE '%CONCURRENTLY%';

-- 2. invalid indexes: the classic concurrent-DDL leftover
SELECT indexrelid::regclass AS invalid_index, indrelid::regclass AS on_table
FROM pg_index WHERE NOT indisvalid;

-- 3. constraints added but never validated
SELECT conrelid::regclass AS table_name, conname
FROM pg_constraint WHERE NOT convalidated AND contype = 'c';

-- 4. partitions detached but not re-attached or dropped
SELECT c.relname
FROM pg_class c
WHERE c.relkind = 'r'
  AND c.relname LIKE 'vehicle_positions_%'
  AND NOT EXISTS (SELECT 1 FROM pg_inherits i WHERE i.inhrelid = c.oid);

-- 5. how far did the backfill get?
SELECT count(*) FILTER (WHERE geom IS NULL)     AS remaining,
       count(*) FILTER (WHERE geom IS NOT NULL) AS done
FROM listings;

Five queries, thirty seconds, and the state is no longer a mystery. Each maps to a specific action:

python
# recovery.py — one function per leftover, all idempotent
import psycopg


def drop_invalid_indexes(dsn: str) -> list[str]:
    """Remove indexes left INVALID by an interrupted concurrent build."""
    dropped = []
    with psycopg.connect(dsn, autocommit=True) as conn, conn.cursor() as cur:
        cur.execute("""
            SELECT indexrelid::regclass::text
            FROM pg_index WHERE NOT indisvalid
        """)
        for (name,) in cur.fetchall():
            cur.execute(f"DROP INDEX CONCURRENTLY IF EXISTS {name}")
            dropped.append(name)
    return dropped


def validate_pending_constraints(dsn: str) -> list[str]:
    """Finish constraints added NOT VALID whose validation did not run."""
    done = []
    with psycopg.connect(dsn) as conn, conn.cursor() as cur:
        cur.execute("""
            SELECT conrelid::regclass::text, conname
            FROM pg_constraint WHERE NOT convalidated AND contype = 'c'
        """)
        for table, name in cur.fetchall():
            cur.execute(f"ALTER TABLE {table} VALIDATE CONSTRAINT {name}")
            conn.commit()
            done.append(f"{table}.{name}")
    return done

Both functions are safe to run when there is nothing to do, which is what makes them usable at three in the morning by someone who has not read this page.

Choosing the recovery action Starting from a failed migration: if the failure was environmental and the revision is idempotent, clean up artefacts and re-run. If the migration itself is wrong and the revision is reversible, downgrade. If it is wrong and irreversible, repair by hand. Restore from backup only when data was destroyed. Four outcomes, in order of how often they apply 1 · clean up and re-run environmental failure, idempotent revision — the common case 2 · downgrade the migration is wrong and its docstring says reversible 3 · repair forward by hand irreversible but the data is intact — usually a corrective migration 4 · restore — only when data was destroyed

Configuration and Tuning Knobs

lock_timeout and statement_timeout should differ. A short lock timeout makes a blocked DDL fail fast and harmlessly. A long statement timeout lets a legitimate index build finish. Setting one value for both guarantees one of the two behaves wrongly.

log_min_duration_statement = 0 for the migration session produces a complete record of what ran and how long it took. On a migration that fails, that log is the difference between knowing where it stopped and guessing.

A dedicated application_name makes the triage query above actually work. Set it in the migration runner and every subsequent investigation gets easier.

How thirty failed migrations were actually resolved Across thirty recorded migration failures: twenty-one were cleaned up and re-run, six were rolled back with a downgrade, two were repaired forward with a corrective migration, and one required a restore. Thirty failures, and what each one needed cleaned up and re-run 21 — environmental, idempotent revision downgraded 6 — the migration itself was wrong repaired forward 2 — irreversible but the data was intact restored from backup 1 — a drop that should not have run Reaching for a restore first would have turned twenty-one short incidents into twenty-one long ones.

Verification Steps

After any recovery, verify the schema matches the model rather than assuming:

bash
alembic current           # which revision does the database claim to be at?
alembic check             # does the model agree with the database?
sql
-- and the spatial specifics
SELECT count(*) FROM pg_index WHERE NOT indisvalid;              -- expect 0
SELECT count(*) FROM pg_constraint WHERE NOT convalidated;       -- expect 0
SELECT count(*) FROM listings WHERE geom IS NULL;                -- expect the known number

The third query needs a known expected value, which is the argument for recording one before the migration starts. “How many rows should legitimately be null?” is a question with a definite answer beforehand and a very uncertain one afterwards.

Gotchas Checklist

  • Alembic’s version table may disagree with reality. A revision that failed after its DDL but before the version insert leaves the schema changed and the version unchanged. alembic stamp is how you reconcile, once you have verified which state the schema is actually in.
  • Do not re-run a non-idempotent backfill. Without a watermark, a second run re-processes rows, which is harmless for a pure transform and destructive for anything cumulative.
  • DROP INDEX CONCURRENTLY also cannot run inside a transaction. The cleanup script needs autocommit, exactly like the build did.
  • A detached partition is invisible to the parent but still holds data. Re-attaching it is usually the right recovery; dropping it is not, and the difference is a lot of rows.
  • Write down what you did. A recovery performed at 3 a.m. and not recorded becomes an unexplained schema difference three months later.

Rollback or Fix Forward: Deciding Under Pressure

The decision is not a matter of taste, and it is easier if the criteria are agreed before the incident rather than during it.

Roll back when the migration has not yet written data in a format only the new code understands. A migration that added a column, created an index, or installed a constraint is trivially reversible, and reversing it restores a state that is known to work. Roll back also when the failure is not understood: an unexplained error during a schema change is a reason to return to the last known-good state and investigate from there, not to add a second change on top of the first.

Fix forward when reversing would itself destroy data. A completed reprojection that overwrote geometries in place cannot be undone by dropping a column, because the original coordinates are gone; the recovery path is a corrected transform applied to the current values, or a restore from backup. Fix forward also when the migration is partially applied across a partitioned table and the rollback would have to walk the same partitions the failure just interrupted — that is the same amount of risk in the less-tested direction.

The time budget is the tiebreaker. If the rollback takes ten minutes and the fix is not yet written, roll back. If the rollback takes three hours because it rebuilds an index and the fix is a one-line correction that has already been reviewed, fix forward. Estimating both durations honestly, in advance, is what makes the choice fast when it has to be.

Whichever path is taken, the application must survive both states. A deploy that requires the new column to exist turns a schema rollback into an outage, which is why the column-add and the code that reads it belong in separate releases.