Why migration timing changes with your data

A schema change can be valid SQL, pass review, and work on staging while still holding a costly lock in production. The difference often lies in the data the statement has to read or rewrite.

A small test database helps check correctness. To understand migration cost, you also need realistic row counts, data distributions, and observations from the database while the statement runs.

Lock mode and lock duration answer different questions

A migration linter can identify statements that request strong locks. The time those locks remain held depends on the work Postgres performs, the available resources, and other activity in the database.

That distinction matters for a type change that rewrites a table. The SQL may be a single line, but Postgres has to process the existing rows while holding the required lock.

What to carry into a rehearsal

• Row counts. A rewrite on a large table can take much longer than the same change on a fixture database.
• Data distribution. Skew, nulls, and large accounts affect planner choices. Uniform seed data may produce different query plans.
• Concurrent activity. Other sessions can wait for a migration or delay its lock acquisition. A quiet branch alone does not reproduce production contention.
• Environment differences. Hardware, indexes, and storage conditions affect timing. Treat rehearsal measurements as observations of that environment.

What Antifailure measures

Antifailure applies pending migrations to a disposable branch of a masked database. It records statement durations, samples locks from another connection, observes table rewrites, and compares query plans.

Lock sampling records the strongest mode held per table, a lower bound on its duration, and whether another session was observed waiting. The report also includes migration lint findings and suggested changes.

In a recorded orders-app demo, changing total_cents to bigint rewrote the orders table. The report recorded an AccessExclusiveLock on that table for at least 10.5 seconds. That is a measurement from the demo branch, not a forecast for another database.

Plan the path back

Reverting application code does not undo every schema or data change. Dropping a column removes data; a backfill may create values an older release does not expect.

For a column type change, consider an expand-and-contract sequence: add a new column, backfill in batches, move reads and writes, and remove the old column in a later migration. Rehearse the sequence and check compatibility with the previous release.

Put the findings in the review

A useful report names the statement, the affected table, the observed lock, and the conditions of the test. Your team can then decide whether to change the migration, adjust its timeout, or gather more evidence before deployment.

Read the migration rehearsal guide to set up the checks for your repository.