Gating Migrations on Estimated Lock Duration

A migration passes every dry-run, the checksums match, the diff is additive — and it still takes production down for ninety seconds because ALTER TABLE orders ALTER COLUMN total TYPE numeric(14,2) rewrote 400 million rows under an ACCESS EXCLUSIVE lock while every INSERT piled up behind it. The DDL was correct. It was also catastrophically expensive, and nothing in the pipeline knew that, because cost is not visible in the SQL text — it lives in the size of the table the SQL touches. If you have watched a “safe, reviewed” schema change stall a checkout flow, the missing gate is one that reads live table statistics, multiplies them by the known cost of each operation, and refuses to let a deploy through when the estimate blows past a written lock budget. This page shows how to build that estimator in CI, where PostgreSQL and MySQL diverge on what actually rewrites a table, and how to wire the number to a hard pass/fail before the deploy is ever scheduled.

Symptom / Error Signatures

The failure has two faces: the outage you get without the gate, and the block message you get once it exists.

Without the gate, the table you touched freezes and its waiters surface in the logs. On PostgreSQL, application connections start returning:

ERROR:  canceling statement due to lock timeout
ERROR:  canceling statement due to statement timeout

and a lock query shows a long-lived AccessExclusiveLock on the target relation with a queue of ExclusiveLock/RowExclusiveLock waiters stacked behind it. On MySQL 8.0 the equivalent is a metadata-lock pileup:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Waiting for table metadata lock

visible as State: Waiting for table metadata lock across many rows in SHOW PROCESSLIST while a single ALTER TABLE holds them all.

With the gate in place, the same change never deploys — instead the CI job fails loudly with a message you write yourself, for example:

lock-budget: FAIL — estimated ACCESS EXCLUSIVE hold 74000ms on "orders" (rows=412,338,901, op=rewrite) exceeds budget 500ms

That second signature is the one you want: a red pipeline with a number attached, not a red dashboard with a customer attached.

Root Cause Analysis

The estimator’s whole job is to turn a statement into a predicted lock class and a predicted duration, and both predictions depend on the engine. A gate that scans SQL text alone — the destructive-DDL detection described in the parent gating overview — can tell you ALTER COLUMN ... TYPE is risky, but it cannot tell you whether this table has ten thousand rows or ten billion. Duration is row_count × per-row cost, and row_count comes from statistics, not syntax.

The two engines disagree sharply on which operations force a full-table rewrite under a blocking lock, which is why a single hard-coded rule set is wrong on one of them:

The estimator therefore needs three inputs per statement: the operation class, the live row count and average row width for the target table, and the engine’s rewrite rules. The row count must come from the production catalog — pg_class.reltuples and pg_relation_size() on PostgreSQL, information_schema.tables.table_rows and data_length on MySQL — read through the same read-only role the other gates use. A number pulled from an empty CI database is worthless: the table is representative only in production, which is exactly the mistake that makes a green table-lock regression test stall on real data.

Immediate Mitigation

If a heavyweight migration is queued for the next deploy and you have no estimator yet, you can compute the estimate by hand and stop the deploy before wiring anything permanent.

1. Pull the live size of every table the migration touches. Run this as the read-only CI role against production, never against the empty pipeline database.

-- PostgreSQL · run as the read-only ci_diff role against PRODUCTION · read-only, safe anytime
-- reltuples is an estimate maintained by ANALYZE; run against a recently-analyzed catalog.
SELECT c.relname AS table_name,
       c.reltuples::bigint AS est_rows,
       pg_total_relation_size(c.oid) AS total_bytes,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS size_pretty
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relname = ANY(ARRAY['orders','order_items']);

-- MySQL 8.0 · run as a read-only role against PRODUCTION · read-only, safe anytime
-- table_rows is an InnoDB estimate; treat it as order-of-magnitude, not exact.
SELECT table_name, table_rows AS est_rows,
       data_length + index_length AS total_bytes
FROM information_schema.tables
WHERE table_schema = DATABASE()
  AND table_name IN ('orders','order_items');

2. Classify each statement against the rewrite rules. For the change in front of you, decide from the table above whether it is metadata-only, an online build, or a full rewrite under an exclusive lock. Only the last class spends the lock budget.

3. Estimate the hold and compare it to the budget. Use a conservative per-row rewrite rate for your hardware — a common starting point is 3–8 million rows per second for a sequential rewrite on SSD-backed storage; measure yours once and pin it. est_rows / rewrite_rate gives seconds of exclusive hold. If that exceeds your budget (say 500 ms of ACCESS EXCLUSIVE), the migration must not ship as written.

4. Stop the deploy and reshape the change. Convert the offending statement to a non-blocking form before it goes anywhere: a naive index becomes a concurrent build (see building indexes with CREATE INDEX CONCURRENTLY), and a giant column rewrite is split into add-column-plus-backfill rather than one ALTER. For MySQL rewrites that cannot be made online in place, route them through an online schema change tool so the copy happens without holding the table.

Permanent Fix / Long-Term Pattern

The durable version turns those four manual steps into one CI check that runs on every pull request touching a migration and fails closed. The estimator is a small script: it parses the migration into statements, classifies each against an engine-specific rewrite table, fetches live row counts through the read-only role, computes the predicted exclusive-lock hold, and exits non-zero when any statement exceeds the budget. Wire it as a required status check exactly as the migration pipeline gating overview describes for the other gates, so a red estimate blocks the merge queue and cannot be waved through.

# .github/workflows/lock-budget.yml — runs on every PR that touches migrations/
# Context: reads PROD via a read-only role for stats only; never applies DDL; non-zero exit blocks merge.
lock_budget:
  runs-on: ubuntu-latest
  steps:
    - uses: actions/checkout@v4
    - name: Estimate lock duration
      env:
        PROD_READONLY_URL: $
      run: |
        set -euo pipefail
        # --budget-ms is the agreed ACCESS EXCLUSIVE ceiling; --rewrite-rate is measured once for your hardware
        ./bin/estimate-lock \
          --dir migrations/ \
          --stats-url "$PROD_READONLY_URL" \
          --budget-ms 500 \
          --rewrite-rate 4000000

The lock budget itself must be a written number, agreed with whoever owns the availability SLO, not a value each engineer guesses. Pin it in the repository next to the workflow so the gate has a single source of truth and every change is measured against the same ceiling. Keep the estimator conservative: when a statement’s class is ambiguous — a SET NOT NULL that may or may not full-scan on your version — round up to the worse case so the gate errs toward blocking. Because table statistics can be stale, the estimate is a fast, cheap upper-bound filter, not a proof; a migration that passes the budget but sits near the edge should still be promoted to the apply-and-measure proof in catching table-lock regressions in migration tests, which runs the DDL against a production-size snapshot and measures the lock it actually held. The estimator stops the obvious disasters in seconds; the snapshot test confirms the marginal ones. Whether a partially-applied rewrite can even be rolled back cleanly depends on your engine’s transactional DDL support, which is why blocking before apply matters most where DDL is non-transactional.

Verification Checklist

• The estimator reads row counts from the live production catalog through a read-only role, not from the empty CI database.
• Engine-specific rewrite rules are encoded separately for PostgreSQL and MySQL, so a metadata-only add is not flagged and a full rewrite is.
• The lock budget is a written number committed to the repository, agreed with the availability-SLO owner.
• A test migration with a large-table ALTER COLUMN ... TYPE fails the gate with the estimated milliseconds printed in the job log.
• A large additive change that is metadata-only (nullable column, no default) passes the gate regardless of table size.
• The check is marked required in branch protection so an over-budget estimate blocks the merge and cannot be overridden silently.

Frequently Asked Questions

How accurate does the row-count estimate need to be? Only accurate to an order of magnitude, because you are comparing against a budget with wide headroom, not measuring an SLA. reltuples and information_schema.table_rows are both approximations maintained by the engine, and that is fine: a table with 400 million rows is unambiguously over a 500 ms budget whether the true count is 380 or 420 million. Where the estimate lands near the threshold, treat that as a signal to promote the change to a real snapshot measurement rather than to chase a more precise count.

Won’t this gate block legitimate large migrations that genuinely need to run? It blocks them in their blocking form, which is the point — a legitimately large change should run in a non-blocking form. A column rewrite becomes add-column-plus-backfill, a naive index becomes a concurrent build, and an unavoidable table copy runs through an online schema change tool. The gate does not forbid touching big tables; it forbids holding an exclusive lock on them past the budget, forcing the change into a shape that does not.

Why estimate from statistics at all instead of just running the migration in CI and timing it? Because the CI database is empty and the production table is not, so a timed run against CI reports milliseconds for a change that will take minutes in production. Statistics-based estimation is the only way to reason about production-scale cost without a production-scale copy in the pipeline. The apply-and-time approach is still valuable, but it belongs to a snapshot-backed test stage restored from real data, not to the fast per-PR gate.

Return to the parent topic, Migration Pipeline Gating, for how this budget check sits alongside backward-compatibility diffing, checksum verification, and destructive-DDL detection as one required set of blocking gates.

Related

• Blocking Deploys on Failed Migration Dry-Runs
• Catching Table-Lock Regressions in Migration Tests
• Building Indexes with CREATE INDEX CONCURRENTLY