Staging cannot tell you how long a lock is held

Almost every migration incident has the same shape. The change was reviewed. It ran on staging in under a second. It ran in CI. Then it reached production and something held a lock long enough that requests queued behind it, connections filled, and the application stopped answering.

Nothing about the review was careless. The problem is that the property that matters is not visible in any environment that lacks production data, and the artifact everyone inspects, the SQL, does not contain it.

The property that does not fit in a diff

A lock has two independent characteristics. Which lock mode a statement takes is a static fact about the statement, and a linter can tell you. How long it is held is a function of how much data it has to touch, and nothing static can tell you.

Those two are frequently confused because on a seeded table they look identical. An ACCESS EXCLUSIVE lock held for four milliseconds against ten thousand rows and the same lock held for twenty-seven seconds against ninety million are the same line of SQL. Only one of them is an outage.

This is why static migration linters, which are useful, are not sufficient. They correctly tell you that a statement takes a strong lock. They cannot tell you that it will hold it past your connection pool's patience, because that answer depends on the table.

Why the staging copy does not stand in

Four things are usually different, and each one hides a distinct failure.

• Row count. Rewrite and scan times scale with it. Staging is typically several orders of magnitude smaller, so every duration measured there is meaningless.
• Distribution. Planner choices depend on statistics. Uniform seeded data produces different plans from real data with its skew, its nulls and its handful of enormous accounts.
• Concurrency. A lock is only a problem when something else wants the table. A quiet staging box has no queue to form behind it, so the same lock is invisible.
• Index and bloat state. A table that has been written to for two years does not behave like one created by a fixture script this morning.

The uncomfortable consequence is that a green staging run on a schema change is close to no evidence at all. It proves the SQL parses and the application still boots. It does not address the question anybody actually has, which is whether this is safe to run at 2pm on a Tuesday.

What has to be measured instead

The question is not “is this migration valid” but “what does this migration do to a database shaped like ours, while it is being used.” That requires executing it against production-shaped data and watching, which is what Antifailure rehearses on a fresh branch. Four measurements come out of it:

• Per-statement timing. Not the total, which averages away the one statement that matters, but each statement separately.
• The strongest lock held per table. Per table, because a migration touching six tables has six different blast radii and reporting the maximum tells you nothing about which one to fix.
• Query plan comparison. Plans before and after, compared, which is how you catch the index you stopped using rather than the one you forgot to add.
• A pg_stat_statements diff between main and the branch, which is how an N+1 introduced by an ORM change shows up as a number instead of as a support ticket next week.

Rollback is a separate question, and it expires

“We can roll back” is usually said about the deployment rather than the schema, and the two come apart quickly. Once a column is dropped the data is gone. Once a backfill has run, reverting the code leaves rows the old version never expected. Once a type has changed in place, going back is another rewrite holding another lock.

So rollback feasibility is a property to check while rehearsing, not a reassurance to offer during an incident. It is frequently true at the moment of deploy and false twenty minutes later, and knowing which of those you are in changes what you do next.

What this looks like in practice

The output that is worth having is not a pass or a fail. It is a statement specific enough to argue with: this statement holds ACCESS EXCLUSIVE on orders for twenty-seven seconds at your row count, eighty-four statements queue behind it, and the table is rewritten in full.

That is a decision somebody can make. “It passed on staging” is not, and it never was. It only looked like one because the thing it failed to measure is invisible until the day it is not.