A Dry-Run Harness for Agent Migrations in CI

A dry run is only meaningful if the data looks like production. Three properties matter: row counts per table, the distribution of values in indexed columns, and the presence of the messy cases (nulls, unicode, legacy formats) that synthetic generators miss. Research summarized by Xata found that PostgreSQL cardinality estimates were off by 10x or more in 16% of single-join queries and 52% of three-join queries, because real data is correlated and non-uniform in ways benchmarks are not (Xata on anonymization). A snapshot that keeps the shape but not the scale will pass the dry run and lie to you.

The practical way to build the snapshot is a monthly or weekly job, not a per-pull-request one, because dumping and restoring a large database is slow. Follow the pattern most teams already understand:

# 1. Dump from a read replica, schema plus data
pg_dump --format=custom --file=/snapshots/prod.dump "$REPLICA_URL"

# 2. Restore into the dry-run database
pg_restore --dbname="$DRYRUN_URL" --no-owner /snapshots/prod.dump

# 3. Apply deterministic masking
psql "$DRYRUN_URL" -v ON_ERROR_STOP=1 -f mask.sql

The masking script must be deterministic, so a given input always produces the same output. Hash-based masking with a secret salt preserves referential integrity across tables, and context-preserving formats (keeping the domain on an email, keeping the last four digits of a card number) keep validation logic working (Xata on anonymization). A script that rewrites every name to Test User destroys the distributions you built the snapshot to test.

Two traps ruin most masking scripts. First, they drift: a new column lands and nobody updates the script, so every "anonymized" refresh is a quiet fiction (Seedfast on staging data). Second, they leave free-text fields untouched, and support tickets or agent conversation logs are where real names and account numbers hide. Run a check in the snapshot job that scans every text column for a known canary string seeded into production, and fail the snapshot build if the canary appears after masking. Vendors in this space make the same point: masking has to be automated and auditable, or you cannot prove to an auditor that it ran (Redgate on database provisioning).

Structure the job as four steps: restore, run, measure, compare. The restore step pulls the snapshot artifact, or better, restores from a template database the runner keeps warm. The run step executes the migration with a hard timeout so a pathological lock wait cannot burn a runner for an hour. The measure step queries the database for what actually happened. The compare step reads the baseline file and decides pass or fail.

dry-run-migration:
  needs: [build-snapshot]
  timeout-minutes: 45
  steps:
    - name: restore snapshot
      run: ./ci/restore-snapshot.sh "$SNAPSHOT_URI"
    - name: note start
      run: date +%s > /tmp/start
    - name: run migration
      run: |
        timeout 1800 ./migrate up --no-prompt || echo "EXIT=$?" >> "$CI_ENV"
        date +%s > /tmp/end
    - name: collect metrics
      run: ./ci/collect-metrics.py --out metrics.json
    - name: compare to baseline
      run: ./ci/compare-budget.py metrics.json migrations/agents/0042/index.json

The hard timeout is not a substitute for budgets; it is the backstop. If the migration cannot complete in 1,800 seconds on a snapshot the size of production, the budget comparison will fail long before the timeout fires. Set the timeout from the baseline: roughly 3x the baseline wall-clock time, floor of 15 minutes.

Isolation matters for the numbers to mean anything. Run each migration in its own database, on a runner with a fixed CPU and memory allocation, with no other migrations running concurrently. GitLab's migration style guide draws the same line, separating regular schema migrations, post-deployment migrations and batched background migrations based on how long each takes and whether it blocks deploy (GitLab migration style guide). Your dry run should know which class each migration belongs to, because a post-deploy migration measured with the same budget as a regular one will fail for the wrong reason.

Total wall-clock time is the number people track and the least useful one on its own, because it combines waiting and working. Capture these six, each with a source query.

Wall-clock time per phase. Wrap each phase of the migration (pre-check, schema change, backfill, index build, verification) in its own timer. A migration that got slower because the index build doubled is a different fix than one that got slower because lock wait grew.

Lock wait time. On PostgreSQL, pg_blocking_pids() returns the sessions blocking a given backend, and pg_locks with granted = false shows pending requests; pg_stat_activity.wait_event_type = 'Lock' identifies sessions waiting on locks (Bytebase on showing Postgres locks, Crunchy Data on finding the blocker). Sample every 500 ms during the run and record the maximum and the 99th percentile:

SELECT pid,
       wait_event_type,
       wait_event,
       pg_blocking_pids(pid) AS blocked_by,
       now() - query_start  AS waiting_for
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
  AND pid <> pg_backend_pid();

Note that pg_blocking_pids() shows immediate blockers only. A reader queued behind a waiting ALTER reports the ALTER as its blocker, not the long report that started the chain, so walk the graph if you want the root cause (DbGorilla on reading pg_locks). For a budget, immediate blockers are enough: if anything is waiting, the migration is holding a queue.

Rows and documents touched. Read pg_stat_user_tables before and after, and diff n_tup_ins, n_tup_upd and n_tup_del. For document stores, count operations from the migration's own instrumented client. A backfill that touches 40 million rows when the plan said 4 million is a design error worth failing on.

Retries. Count retry attempts from the migration runner and from the database (deadlock cancellations, lock timeout errors). A migration that succeeds only after 30 retries will succeed differently, or not at all, under a busier production load.

Peak memory. Sample the migration process RSS every second and record the maximum, plus the peak of any temporary files the database wrote.

Lock duration on the primary table. The maximum time any single lock was held on the target table, timed from pg_locks start if instrumented, otherwise inferred from the phase timing around the ALTER.

Store the baseline as a JSON file next to the migration, in the same pull request directory. Baselines committed in the repository get reviewed, get diffed, and get the same "why did this change" question as any other code change.

{
  "migration": "0042_add_agent_trace_index",
  "snapshot_tag": "prod-2026-09-01",
  "budgets": {
    "wall_clock_ms": 420000,
    "lock_wait_p99_ms": 1500,
    "rows_touched": 12000000,
    "retries": 0,
    "peak_memory_mb": 1800,
    "lock_hold_ms": 200
  }
}

Setting the initial numbers is the part most teams get wrong. Do not use the first run's values as the budget; the first run is also the measurement of a snapshot that may be cold, may have missing statistics, or may have run while another job competed for IO. Instead:

1. Run the migration three times on the same snapshot with ANALYZE called before each, and take the median as the baseline.
2. Add the tolerance from the table above, based on the variance you observed.
3. Set the lock_hold_ms budget from your application's latency target, not from the measurement. If your pool can absorb a 200 ms queue without errors, the budget is 200 ms, even if the baseline came in at 400. A migration over budget is then a real incident candidate, not a statistical fluctuation.
4. When a migration legitimately changes a budget, put the reason in the pull request: "backfill moved from 4M to 12M rows because we now repair legacy nulls." A budget change without a reason is a review rejection.

For MySQL and Vitess, the same file structure applies. Capture the chunk-copy rate and the replication lag that pt-online-schema-change reports, since its safety checks pause or abort on replica lag and server load (Percona Toolkit). Set a budget on lag of zero or a few seconds; any sustained lag means the migration will throttle in production.

One honest limit: convergence to a single baseline works when the migration's cost is dominated by data volume, which most schema and backfill migrations are. For migrations whose cost depends on the concurrency pattern in the snapshot, record the snapshot tag in the budget file and fail the comparison if the tag does not match the snapshot the job restored. A comparison across two different snapshots is noise dressed up as a gate. When a migration is one of several competing for the same dry-run runner, it is often cleaner to let an agent manage the queue: Quanta Skills (https://quantaintelligence.ai/skills) packages migration-and-metrics work that a coding agent can run, so the budget comparison is a skill invocation rather than a bespoke script.

Suppose an agent migration adds a composite index and backfills a nullable column on an events table with 40 million rows. The baseline file says 400,000 ms wall-clock, 12 million rows touched, zero retries, 1,600 MB peak memory, and a 200 ms lock-hold budget.

Run one comes in at 380,000 ms wall-clock but 1,900 MB peak, because the backfill batches were larger than the snapshot's memory headroom. The job exits non-zero on memory alone. Run two reduces the batch size and passes memory while wall-clock moves to 430,000 ms, a 7.5% increase, inside the 20% tolerance. Run three shows retries of 6, from deadlock cancellations caused by two tables locked in opposite order, which is a known failure mode when a migration touches related tables in one transaction (pganalyze on migration deadlocks). The job fails on retries, the migration is split into one change per table, and the fourth run passes cleanly. Production never sees any of these four states.

The comparison script should exit non-zero on the first metric over budget and print all failures, not just the first. Engineers chase a failing build once; they should not chase it six times.

# ci/compare-budget.py (essentials)
import json, sys
metrics = json.load(open(sys.argv[1]))["metrics"]
budgets = json.load(open(sys.argv[2]))["budgets"]
failures = []
for key, cap in budgets.items():
    value = metrics.get(key)
    if value is None:
        failures.append(f"{key}: MISSING")
    elif value > cap:
        failures.append(f"{key}: {value} > budget {cap}")
if failures:
    print("\n".join(failures))
    sys.exit(1)

Two more failure modes deserve specific handling. First, the lock queue soft-block: in a measured PostgreSQL 18.6 container, a reader that started after a waiting ALTER reported the ALTER as its blocker even though the ALTER was itself waiting on a report, while an unrelated query on another table returned immediately (DbGorilla on reading pg_locks). Your sampler should record the depth of the queue, not just the presence of one waiter. Second, MariaDB's online change buffer is written to the filesystem with no explicit size limit, so a heavy concurrent DML load during a long change can fill the disk hosting the temporary directory (MariaDB online schema change). Add a disk-free metric to the job for MariaDB targets, and fail when free space drops below 20% during the run.