When a Coding Agent Writes the Migration: Gates and Dry Runs

Change the definition of "done" for an agent-authored migration. The agent is not finished when the file exists. It is finished when it produces four artifacts you can review:

The lock annotation is where the agent earns its keep. Ask it to list, for every statement, the lock the statement requests and the lock times measured in staging. It can also flag the classic traps in its own output. A column added with a constant default is catalogue-only since PostgreSQL 11, but a volatile default such as now() or gen_random_uuid() forces a full table rewrite under ACCESS EXCLUSIVE (RunxBuild on adding columns safely). A plain CREATE INDEX takes a SHARE lock that blocks INSERT, UPDATE and DELETE for the duration, while CREATE INDEX CONCURRENTLY takes SHARE UPDATE EXCLUSIVE and lets writes continue (Bytebase on CREATE INDEX CONCURRENTLY).

If your team runs the agent from a coding harness, a skill that encodes these rules saves you from restating them in every prompt. Quanta Skills is the library of those skills, installable into a coding agent at https://quantaintelligence.ai/skills.

A staging run against a table with a thousand rows tells you the migration works. It does not tell you how long a rewrite takes, because the answer scales with row count and with index count. Size the copy deliberately.

Worked example. Suppose orders holds 40 million rows and carries five indexes. You plan to add status text DEFAULT 'pending' NOT NULL and an index on (status, created_at). The default is constant, so the column addition itself is catalogue-only and fast regardless of table size (RunxBuild). The index is the expensive part. If a full index build reads the table at roughly 200 MB/s in your environment and the heap plus existing indexes come to 60 GB, expect around five minutes of scanning, plus sort and write time, on top of the 40 million row copy. A staging copy that has the same row count and the same indexes, but maybe fewer partitions, gets you a number in the right order of magnitude. A thousand-row copy gets you a lie.

Practically:

1. Restore a recent production dump into a staging instance with matching instance class and storage. Row counts and index definitions matter more than data freshness.
2. Run the migration exactly as the agent wrote it, with no manual edits, so you are measuring the artifact under review.
3. Time each statement separately, not the whole file. \timing in psql or driver-side timing per statement works.
4. While the migration's strong-lock statement runs, fire a trivial write (INSERT INTO orders ... or an update of one row) from a second session every second and log latency. That series is your user-facing impact, not the migration's own duration.
5. Record the peak number of sessions waiting on the table during the window.

The last two steps matter because the migration's own runtime and the blocking it causes are different numbers. A CREATE INDEX CONCURRENTLY can run for ten minutes while writes continue, and a non-concurrent build can run for ten minutes while every write to that table queues. Same duration, very different outcome.

For MySQL and MariaDB shops the equivalent numbers come from online schema change tooling. pt-online-schema-change copies the table into a shadow table, records changes with triggers, and swaps at the end, and by default it refuses to run without --execute, applies lock_wait_timeout=60 and innodb_lock_wait_timeout=1, and pauses when replicas fall behind (Percona Toolkit docs). Vitess documents the same shape of problem, direct ALTER TABLE blocking reads and writes, with managed online DDL as the recommended path (Vitess schema change docs). MariaDB's own online schema change copies rows while buffering concurrent changes and acquires a short EXCLUSIVE lock at the end to synchronize (MariaDB docs). The staging question is the same in every case: how long is the window in which writes are blocked or buffered, and how large is the buffer.

A lock timeout is the single highest-value line in any migration against a busy table. Set to a few seconds, it makes the migration fail and roll back rather than sit at the head of the queue (Citus on lock timeouts, Xata).

BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN note text;
COMMIT;

The preview is a staging exercise that answers three questions with numbers. How often would this migration have failed with a 3 second timeout? What is the oldest transaction on the target table at the times you would deploy? How long does the lock actually get held when it succeeds?

A concrete way to run it. On the staging copy, open a session and hold a transaction with a deliberately slow query (SELECT count(*) FROM orders, pg_sleep(30) or similar), then run the migration with the timeout from a second session. It should fail after 3 seconds, and you should record which statement was blocked and for how long it had been running. Loop this at a few timeout values, say 1, 3 and 10 seconds, and at a couple of workload intensities. The result is a decision table:

The point is not the exact numbers, which depend on your hardware and workload. The point is that "this migration is safe" becomes a statement about a measured success rate and a measured worst-case hold, and a reviewer can disagree with it on evidence.

Two more preview checks belong in the same run. First, confirm what is waiting and who blocks it, using pg_blocking_pids() rather than reading pg_locks by hand, because that function reports soft blocks (sessions queued behind a waiting session) as well as hard ones (the pg_locks walkthrough). Second, check for prepared transactions with no PID, which can hold a conflicting lock and show up as a zero process ID, so a naive blocker query returns nothing useful (same source).

During the staging run, poll this and keep the output with the migration:

SELECT blocked.pid,
       left(blocked.query, 60) AS blocked_query,
       blocker.pid             AS blocking_pid,
       blocker.application_name,
       now() - blocker.xact_start AS blocker_age
FROM pg_stat_activity AS blocked
CROSS JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid)
JOIN pg_stat_activity AS blocker ON blocker.pid = b.pid
WHERE blocked.wait_event_type = 'Lock';

For index builds specifically, pg_stat_progress_create_index reports the current phase (initializing, building index, waiting for writers, validating) along with blocks and tuples done, which gives you a progress signal on a long build instead of a silent wait (Bytebase on CREATE INDEX CONCURRENTLY). One behavioural detail to remember when reviewing an agent's plan: CONCURRENTLY is an instruction at build time, not a property the index keeps, and the result is an ordinary index once it is valid (Stack Overflow answer on CONCURRENTLY). If a build fails, it can leave an invalid index that needs dropping before a retry, which is a rollback step the agent should spell out.

An approval gate is only useful if it approves the right thing. Most migration reviews approve the text. Change that.

Make the sign-off item a measurement table, and require one named person to accept it. The gate passes when all of these are true:

• The staging run was executed against a copy whose row counts are within 20 percent of production and whose indexes match.
• Every statement that takes a read-blocking lock on a table above a stated row threshold is either rewritten to a non-blocking form or explicitly accepted with a measured worst-case hold.
• The skip-locked forms, CREATE INDEX CONCURRENTLY, the NOT VALID plus VALIDATE CONSTRAINT sequence and batch backfills, are present wherever the agent could have used them. The multi-step path for adding a NOT NULL column safely is a nullable add, a code deploy that writes the column, a batched backfill, a NOT VALID check, then VALIDATE CONSTRAINT and a cheap promotion (RunxBuild).
• The measured lock_timeout success rate from staging is recorded, and the deploy script applies the same timeout.
• Rollback is written down and tested on the copy, including the invalid-index case.

The named approver should be the person who owns the table's availability, not the person who prompted the agent. That single rule changes behavior: when you know you will be asked for the measured hold time, you run the measurement before opening the request.

Two more gate items that catch agent-specific mistakes. First, split the migration so each transaction touches one table. A migration that alters two tables in one transaction can deadlock against ordinary application traffic that reads them in the opposite order, and the standard fix is to split it or to avoid holding strong locks across more than one table (pganalyze on migration deadlocks). Second, if you use a review tool, point it at the staging logs as well as the SQL. Classic lock-monitoring queries using pg_locks joins can miss or misattribute blockers, and the built-in pg_blocking_pids() plus pg_stat_activity is the reliable pair (PostgreSQL wiki on lock monitoring, Crunchy Data on finding the source of the lock).

If you are adopting this process across several teams, the fastest route is often to hand the reviewer role to someone who does this for a living; Quanta Intelligence keeps AI experts for a line of business, and a migration review expert can run the gate while your engineers keep shipping.

Give the agent this checklist as its definition of done, and give the reviewer the last three lines.

For the retry loop around a short timeout, the pattern is simple: try, catch the lock-timeout error, back off, try again, and give up after N attempts with a clear error. Failing is cheap when the statement rolls back and leaves traffic untouched; failing silently is not, so make the migration runner exit non-zero and page someone if it exhausts attempts.

Once the gate exists, a coding agent becomes genuinely useful for migrations rather than a source of risk. It can generate the split statements, propose the batch size, write the staging scripts, and fill in the lock annotation. It still should not be the one to decide that eight seconds of blocked writes is acceptable on a revenue table. That is a judgment about your business, and the approval gate exists to put it in front of a person.

Quanta Inference gives you one API for text, image and other model types through an OpenAI-compatible endpoint priced in US dollars, which is convenient if your migration bot also drafts release notes, reads staging logs and classifies errors in one pipeline. The gate does the rest.