Database-Migration Agents

Know which half the agent is good at, because the verifier only covers that half.

Coding agents work when a cheap check catches their mistakes (the generator–verifier gap). Migrations are a case where the available check is strong on syntax and blind on consequence:

• What CI does verify: the migration parses, applies to a fresh database, the ORM models match afterwards, the down-migration reverses it, the test suite passes on the new schema. An agent clears all of this reliably.
• What CI does not verify: how long the statement holds a lock on a table with 400 million rows; whether the currently-deployed application code can still read the schema between the migration landing and the next deploy finishing; whether the backfill saturates replication; whether the rewrite needs disk you do not have. None of these are visible on a test database with 200 rows and one connection.

So the design principle for the whole playbook: stop trying to make the agent smarter about the second list, and instead move items from the second list into the first — a shadow apply that reports lock duration, a linter that rejects a class of statement outright, a rule that a migration must be compatible with the previous release. Every one of those turns a judgement call into a check.

Teach it expand–contract as three changes, and refuse the one-shot version.

The single most valuable thing you can put in the agent's instructions is that a schema change is not a change, it is a sequence in which the old and new application code are both correct at every intermediate point. Renaming a column is the canonical example: ALTER TABLE ... RENAME COLUMN is one statement and one outage, because the code deployed thirty seconds ago is still selecting the old name.

• Expand. Add the new column, nullable, no default that rewrites the table. Add the new index concurrently. Add the constraint as NOT VALID. Nothing existing breaks because nothing existing changed.
• Migrate the code. Write to both, read from the old, then flip reads behind a flag (feature flags, rollout and versioning). This is the step that spans releases, and it is the step agents skip.
• Contract. Drop the old column — in a separate change, in a later release, after the retention window you chose in advance.

Have the agent emit all three artefacts in one pull request as separate, individually deployable migrations with explicit ordering and a stated minimum release gap, and make "which application versions run correctly against the intermediate schema" a required field in the PR body. If the tooling supports it, prefer a system that enforces the pattern rather than documenting it — pgroll keeps both schema versions live behind versioned views for exactly this reason; on MySQL, gh-ost and pt-online-schema-change exist because the equivalent ALTER is not survivable at size.

The lock queue is the outage, not the statement.

The failure that surprises teams is not a slow ALTER. It is this: your DDL asks for an ACCESS EXCLUSIVE lock, a long-running SELECT is holding a conflicting lock, and because Postgres grants locks in a FIFO queue, every subsequent query on that table now queues behind your migration — which is itself waiting. A statement that would have taken two milliseconds takes the site down for the duration of somebody's analytics query.

The mitigation is mechanical, which means the agent can be made to emit it every time:

-- Fail fast instead of queueing the whole table behind us.
SET lock_timeout = '3s';
SET statement_timeout = '30s';

ALTER TABLE orders ADD COLUMN fulfilled_at timestamptz;   -- metadata only

-- Long-running work never holds a table lock:
CREATE INDEX CONCURRENTLY idx_orders_fulfilled_at ON orders (fulfilled_at);

-- Two-phase constraint: cheap lock now, full scan without blocking writes.
ALTER TABLE orders ADD CONSTRAINT orders_fulfilled_ck
  CHECK (fulfilled_at IS NULL OR fulfilled_at >= created_at) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_fulfilled_ck;

Three rules follow, and they belong in the agent's system prompt and in the linter, not in a wiki page nobody re-reads. Set a short lock_timeout on every migration session so a contended lock fails the migration instead of the application — and pair it with a retry, because failing fast is only safe if something tries again. Never run CREATE INDEX without CONCURRENTLY on a live table, and know that it cannot run inside the transaction most migration frameworks wrap around your file. And treat any statement that rewrites the table — most type changes, a volatile default on older engines — as a migration that needs an online-change tool rather than a bigger maintenance window.

Give it a real oracle: a shadow apply on production-shaped data.

This is the step that converts the playbook from advice into engineering. Before a human reads the diff, the migration runs automatically against a clone with production-scale row counts and a synthetic workload, and the pipeline reports four numbers back onto the pull request: maximum lock duration per statement, total runtime, queries blocked, and bytes written. Those are the facts the reviewer cannot get from the diff and the agent cannot get from reasoning.

• Restore, do not generate. A clone from a recent backup or a branch of production has the row counts, the index bloat and the data distribution that make a migration slow. Seeded fixtures do not, and a green run against fixtures is the false accept you were trying to avoid.
• Run a workload against it. Lock contention needs contention. Replaying even a thin slice of read traffic while the migration applies is what surfaces the queue problem, and it is the difference between "took 40 ms" and "blocked 900 queries for 40 ms".
• Lint before the human, not after. A rules engine over the SQL — squawk, Atlas's destructive-change analysis, Bytebase's review rules, or your own — is a sound checker in the sense that matters: it rejects a superset of the dangerous statements, cheaply and identically every time. Put it in the required checks and let the agent iterate against it before a person is involved (code-review agents).
• Publish the thresholds. "Auto-mergeable if lock time is under 100 ms, no statement rewrites the table, and the change is additive" is a policy. Anything outside it routes to a DBA with the numbers attached. The reviewer's job becomes classifying a measured plan rather than imagining one — the same move that makes infrastructure-as-code agents workable.

Backfills are jobs the agent writes, not migrations it runs.

The most common way a migration agent causes a real incident is not DDL at all. It is UPDATE orders SET fulfilled_at = created_at across 400 million rows, inside the migration transaction, holding row locks and generating a replication backlog that takes the read replicas out for an hour. The statement is correct. It is also the wrong artefact.

Require the agent to emit data changes as a separate, operable job with five properties, and give it a template so it does not have to invent them:

• Batched by key range, with a bounded batch size and a sleep between batches, so the write rate is a dial rather than a surprise.
• Checkpointed, writing its progress somewhere durable, so an interrupted backfill resumes instead of restarting.
• Idempotent, because it will be re-run — the same discipline as idempotency and retries, and the reason the update should be conditional rather than unconditional.
• Throttled on a real signal, replication lag being the usual one: above a threshold, the job pauses itself.
• Observable and stoppable — rows processed, rows remaining, current position — because "is it nearly done" will be asked, and because someone needs to be able to halt it without a deploy.

The agent authors; a controlled runner applies. And measure the right failure.

Separate authorship from execution and most of the residual risk goes away. The agent should have read access to the schema and to query statistics, and no DDL privilege anywhere near production; the migration is applied by the same pipeline that applies human-authored ones, with the same approvals, the same audit record and the same runner identity (scoped credentials for agents). An agent that can both write and execute a schema change is a single component whose worst day is your worst day.

Then measure the thing that actually hurts, not throughput:

• Incidents attributable to a migration, and for each one, which check would have caught it. That list is your backlog — every entry is a candidate rule for the linter or the shadow apply.
• p99 latency during migration windows, compared with the same window a week earlier. Lock contention shows up here before it shows up in an incident channel.
• Contract debt: how many expand steps are shipped and awaiting their drop. This grows silently under an additive-only rule and is the honest cost of it — an old column nobody removed is cheap; forty of them is a schema nobody can reason about.
• Rate of migrations reverted or hot-fixed, which is the closest available proxy for how often the sequence, not the statement, was wrong.

Related: large-scale migration agents for the code-side counterpart, repairing agent side effects for the cleanup when a backfill wrote the wrong values, and staging environments for agents for building the clone this all depends on.