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

Start by classifying the statement. Most ALTER TABLE forms take an ACCESS EXCLUSIVE lock, which conflicts with every other lock mode including the ACCESS SHARE lock a plain SELECT takes. What differs is how long the lock is held: metadata-only changes release it almost immediately, while table rewrites hold it for the entire operation per this lock catalog.

The practical rule for the reviewer is a table of allowed and forbidden forms.

The volatile-default trap deserves spelling out because agents reach for now() and gen_random_uuid() constantly. A constant default is stored as metadata and applied on read, so it is fast. A volatile default must produce a different value per row, so PostgreSQL writes every row while holding the lock per this migration writeup. The safe path is to add the column nullable, deploy code that writes it, backfill in batches, add a NOT VALID check, then validate, which scans under a weaker lock as documented in the same guide. Validation under SHARE UPDATE EXCLUSIVE allows reads and writes to continue per this comparison.

For indexes, the difference between the two forms is entirely about locking. A regular build acquires SHARE and blocks row changes. The concurrent build acquires SHARE UPDATE EXCLUSIVE, allows reads and writes, and takes longer because it scans the table more than once per the lock mode comparison. The cost of CONCURRENTLY is that it cannot run inside a transaction block, and a failure leaves an invalid index that must be dropped and rebuilt per the PostgreSQL manual as summarized here. Migration frameworks that wrap every change in a transaction will therefore break the concurrent form, so check the wrapper before you check the SQL.

One detail reviewers miss: CONCURRENTLY is an instruction to the builder, not a property of the index. Once the index exists, pg_indexes shows the same definition either way, so you cannot audit after the fact whether the concurrent form was used per the same discussion. If that audit matters, log the migration script alongside the change record.

A lock timeout is the single change that turns a migration-induced outage into a retried job. The mechanism is the queue: a long-running query holds a lock, the ALTER waits, and every query that arrives later queues behind the ALTER even though it does not conflict with the original query per this walkthrough. Setting a short lock timeout makes the ALTER fail fast instead of blocking the world, and you retry until a quiet moment gives you the lock.

The migration the agent writes should look like this, and the reviewer should reject anything that does not set both timeouts explicitly:

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

Two details matter. SET LOCAL applies only inside the transaction, so it does not leak into other sessions. And the two settings guard different waits: lock_timeout bounds the wait for the lock, statement_timeout bounds total execution. On MySQL the analogous settings are lock_wait_timeout and innodb_lock_wait_timeout, and Percona's online schema change tool sets them low deliberately so that it becomes the victim of lock contention rather than other transactions per the Percona Toolkit documentation.

Decision rule for the reviewer: if the agent's migration does not set a lock timeout, or sets one longer than your application's query timeout, reject it without reading further. Three to five seconds is a common starting point. Anything above the time a user will wait for a page is too long.

Expect a specific error when the timeout fires. PostgreSQL raises an error with the code 55P03, the message that it could not obtain a lock on the relation, a detail line naming the wait duration, and a hint to close the transactions occupying the relation per this error reference. That error is a success of the control, not a failure of the migration. The retry layer should treat it as retryable with backoff, and the reviewer should confirm the retry exists as shown here. MySQL raises lock wait timeout errors instead, and the same logic applies.

Timeout values are ordinary run-time settings, so you can inspect what is actually in effect rather than trusting the migration file. PostgreSQL exposes them through pg_settings, and listing every timeout name and value is a one-line query per this configuration guide. Run it in the clone and paste the output into the review.

A dry run on an empty local database proves nothing. The risks you are checking are all about size and concurrency: how long the rewrite takes, whether the lock wait exceeds your budget, and whether any statement fails outright. The clone needs your production row counts and, ideally, realistic read and write traffic while the migration runs.

Concurrency is a property of the data, which is why the clone matters. An index build on a table with many duplicate values can fail during the concurrent build's uniqueness check if duplicates appear while it runs, which is a documented limitation of the concurrent build in this guide. A table that is empty locally never shows this.

A dry-run procedure a reviewer can follow in under a minute:

# 1. Restore a clone from the latest production snapshot
# 2. Replay representative read and write load
# 3. Apply the migration with timeouts and capture timings
psql "$CLONE_URL" -v ON_ERROR_STOP=1 \
  -c "SET lock_timeout = '3s'" \
  -c "SET statement_timeout = '30s'" \
  -f migration.sql
# 4. Sample lock activity while it runs
psql "$CLONE_URL" -c "SELECT pid, wait_event_type, state, query FROM pg_stat_activity WHERE state <> 'idle'"

For long index builds, monitoring progress mid-run is the difference between evidence and guesswork. The pg_stat_progress_create_index view reports the current phase, the blocks and tuples done so far, and the PID of any locker holding up the build per this monitoring guide. That output is what a reviewer needs: it shows whether the build is making progress or stuck behind a lock.

Write down the numbers from the clone. "The ALTER completed in 1.2 seconds with no lock wait" and "the concurrent index build ran for 14 minutes while writes continued" are reviewable statements. "The migration looks safe" is not. For online schema change tools on MySQL, the clone run should also confirm the tool's safety checks, which refuse to operate without a primary key or unique index and pause the copy when replicas lag per the Percona Toolkit documentation.

Every agent-written migration needs one of two written statements. Either it has a tested rollback, or it is forward-only and the reviewer knows what that means for data.

A rollback plan is not "run the down migration." It must contain:

• The exact statement or tool that reverses the change, and whether running it is also lock-safe.
• The effect on data written after the migration. Dropping a column that the new code has been writing is not reversible for rows created since.
• The recovery procedure for a failed concurrent index build, which is DROP INDEX CONCURRENTLY followed by a rebuild, not a simple rerun per this guide.
• The timeout budget for the rollback itself, because reversing during an incident with a long lock wait makes the incident worse.

Some changes are genuinely forward-only, and saying so is a valid plan. A backfill that converted rows from one representation to another now has no original values to restore. The honest review note says the data is not recoverable from the database alone and points to the backup. If the reviewer cannot find that note, the sign-off is a guess.

Online schema change tools handle reversibility differently, and it is worth knowing which model you are on. Tools that build a shadow table, copy rows into it and swap names leave data recoverable up to the length of the process per this walkthrough of the shadow table approach. Percona's tool creates an empty copy, copies rows in small chunks, captures concurrent changes with triggers and swaps with an atomic rename, then drops the original table by default per the Percona Toolkit documentation. Some systems make the trade explicit: Vitess documents revertible migrations as a first-class state per the Vitess schema change guide, and MariaDB's online schema change notes that changes are buffered to a temporary file whose growth is bounded only by free disk space, so heavy concurrent writes during a long ALTER can fill the disk in the MariaDB docs.

The point of the gate is that a named person owns the evidence. Use a matrix so the same change does not get approved by whoever happens to be online.

Two evidence rules keep the matrix honest. The approver must see raw output, not a summary: the dry-run log, the pg_stat_activity sample, the progress view. And the approval is recorded in the change or pull request with the migration commit hash, so there is no ambiguity about which version of the SQL was approved.

If the agent is part of a larger build, for example a service backed by a new schema, the gate applies unchanged but someone has to own the overall shape. Quanta can take a described site, app or plan and have a specialist build it with you, which helps when the schema and the application arrive together: Quanta.

Reviewers should recognize the common error strings so they can tell a controlled failure from a real one.

Lock timeout on PostgreSQL. The error code is 55P03, the message says the lock could not be obtained, the detail line gives the wait duration and the hint tells you to close the transactions occupying the relation per this error reference. Healthy: the timeout worked, retry with backoff. Unhealthy: it fires on every attempt, which means a long transaction is permanently holding the table, often an idle-in-transaction connection from a connection pool.

Concurrent index inside a transaction. The server refuses with a message that CREATE INDEX CONCURRENTLY cannot run inside a transaction block per this guide. This is almost always caused by the migration framework, not the agent. Fix the framework, not the SQL.

Invalid index left behind. The concurrent build fails partway and the index stays marked not valid. This is expected behavior of the concurrent form per this explanation, and the recovery is DROP INDEX CONCURRENTLY then rebuild.

Lock wait timeout on MySQL. The equivalent of the PostgreSQL lock timeout, and the same retry logic applies. Online schema change tools lower it deliberately so they lose the contention fight instead of blocking your application per the Percona Toolkit documentation.

To find the blocker during an incident, query the lock graph. The blocking-PID functions and a recursive query over pg_stat_activity produce a tree of which session blocks which with the query shown here. Terminating the blocker is a last resort. The point of the gate is that you should not be in this position.

When the agent is producing the migrations and you want the surrounding build or review tooling handled by someone with database experience, the natural next step is the Quanta skill library: install a skill into your coding agent, or put an expert on the job at Quanta Skills.