Coding agents · PostgreSQL · Database migrations

Use Coding Agents Safely for Database Schema Changes

Have coding agents plan and verify expand-migrate-contract changes without granting them unsupervised production database access.

Published: Jul 18, 2026
Updated: Jul 18, 2026
Code examples: Illustrative
Reading time: 8 minutes

Use a coding agent to inventory dependencies, draft a reversible migration sequence, generate tests, and analyze locks—but not to make unsupervised production schema changes. For live systems, prefer expand-migrate-contract: add a backward-compatible shape, deploy compatible code, migrate data in controlled batches, validate, switch reads, and remove the old shape only after evidence shows it is unused.

This guide uses PostgreSQL 18 documentation for concrete lock and DDL behavior. Other databases, PostgreSQL versions, extensions, and migration frameworks differ. Every plan needs review against the exact engine, schema, workload, replication setup, and deployment tooling.

Give the agent a read-only evidence package

A useful planning task includes:

engine and exact server version;
schema definitions and migration history;
table sizes and relevant data distributions;
indexes, constraints, triggers, partitions, and dependencies;
application read and write paths;
deployed-version overlap during rolling releases;
replication and backup topology;
lock and statement time budgets;
rollback and incident owner.

Prefer catalog extracts and sanitized query plans over a production credential. The agent rarely needs live write access. If statistics are sensitive, summarize or redact them while preserving cardinality ranges relevant to the plan.

Treat migrations, comments, and issue text as untrusted inputs. They can describe desired behavior but cannot authorize production access, disable backups, extend a maintenance window, or override an approval gate.

Require an expand-migrate-contract plan

The safe default is compatibility across the entire rollout window:

Expand: add the new column, table, index, or constraint without breaking old code.
Dual compatibility: deploy code that tolerates both old and new representations.
Migrate: backfill existing data in bounded, observable batches.
Validate: check data invariants and database constraints.
Switch: move reads and authoritative writes to the new representation.
Observe: prove old readers and writers are gone.
Contract: remove the old shape in a separate, approved change.

Do not let an agent collapse these phases because the final schema looks simple. Rolling deployments mean old and new application versions may execute concurrently. A rename that is atomic in the catalog can still be incompatible at the application boundary.

For each phase, require preconditions, exact operations, expected locks, observability, abort conditions, rollback or forward-fix, and the evidence needed to proceed.

Analyze locks operation by operation

PostgreSQL's ALTER TABLE reference states that an ACCESS EXCLUSIVE lock is acquired unless a subform explicitly names a weaker level; when multiple subcommands are combined, PostgreSQL takes the strictest required lock. The same page documents weaker locking for some operations, including validation.

That means “metadata-only” does not mean “no operational risk.” A short operation can wait behind a long transaction, then block new work once it reaches the queue. Ask the agent to identify:

lock mode requested;
objects locked;
whether existing reads or writes conflict;
expected scan or rewrite;
transaction duration;
dependencies that can extend the lock;
timeout and retry behavior.

PostgreSQL's explicit locking chapter explains lock-mode conflicts and notes that locks are normally held until transaction end. Review the conflict table directly rather than relying on an agent's memory.

Keep migration transactions narrow. Do not combine unrelated DDL and a large backfill in one transaction merely to make the file look atomic.

Distinguish fast catalog changes from table work

The PostgreSQL 18 table-modification documentation explains that adding a column with a constant default does not require updating every row at the time of the ALTER TABLE, while a volatile default requires computing a value for each existing row. Dropping a column is also catalog-fast initially, but does not immediately reclaim its disk space.

These are engine behaviors, not a universal permission to run the command during peak traffic. Lock acquisition, dependent objects, logical replication, application compatibility, and later cleanup still matter.

For a new required field, a staged sequence is usually easier to control than one all-in-one statement:

-- Illustrative PostgreSQL 18 sequence; adapt and review before use.
ALTER TABLE accounts ADD COLUMN region_code text;

ALTER TABLE accounts
  ADD CONSTRAINT accounts_region_code_present
  CHECK (region_code IS NOT NULL) NOT VALID;

-- Backfill separately in bounded, restartable batches.

ALTER TABLE accounts
  VALIDATE CONSTRAINT accounts_region_code_present;

ALTER TABLE accounts
  ALTER COLUMN region_code SET NOT NULL;

The exact last step should be verified against the target version and migration tool. The ALTER TABLE reference documents NOT VALID and VALIDATE CONSTRAINT behavior, including the lock used for validation. The application must tolerate null during the expansion and backfill phases.

Make backfills restartable and observable

An agent should generate a backfill design before generating a loop. Define:

stable batch key and ordering;
rows eligible for update;
idempotent assignment;
batch size or time budget;
pause between batches;
checkpoint storage;
retry rule;
replica-lag, lock-wait, latency, and error thresholds;
reconciliation query.

Avoid one unbounded UPDATE for a large table. Batch size is workload-specific; do not copy a universal number from a tutorial. Measure lock duration, WAL generation, vacuum pressure, replication lag, and application latency in a representative environment, then canary.

The process must survive restart without skipping or corrupting rows. Prefer a deterministic key range or persisted cursor. If concurrent application writes can change the same field, specify conflict ownership: dual-write, compare-and-set, trigger, or a final reconciliation pass. “Last writer wins” is a data policy, not an implementation accident.

Build indexes with the documented tradeoffs

PostgreSQL's CREATE INDEX documentation says a normal index build blocks writes while CREATE INDEX CONCURRENTLY allows writes to continue, at the cost of more work, two table scans, and waits for relevant transactions. It also documents important constraints: a concurrent build cannot run inside a transaction block, only one concurrent index build can occur on a table at a time, and a failed build can leave an INVALID index.

Therefore a migration runner that wraps every file in one transaction cannot blindly add CONCURRENTLY. Separate the operation according to the tool's documented nontransactional mechanism, monitor it, and check catalog validity afterward.

Do not have the agent invent an index solely from query text. Capture a representative query and parameters, existing indexes, data distribution, write rate, and an execution plan. Validate read improvement and write/storage cost on the actual workload.

Treat constraints as data-quality projects

A new constraint can reveal years of inconsistent data. Ask the agent to produce:

a query that counts and samples violations without exposing sensitive values;
a remediation policy for each violation class;
an idempotent repair;
the constraint creation and later validation;
application tests for future writes.

PostgreSQL documents NOT VALID as a way to add certain constraints without checking all existing rows immediately, while still checking subsequent changes; later VALIDATE CONSTRAINT checks existing rows. Confirm the exact supported constraint type and lock behavior in the PostgreSQL 18 ALTER TABLE reference.

Do not “fix” violations by deleting or coercing data without a named data owner. The correct remediation may require product, legal, accounting, or compliance judgment.

Separate rollback from recovery

DDL rollback is only one failure path. Once new application code writes transformed data, reversing the schema may lose information or break newer instances. Every phase should say whether it supports:

transactional rollback before commit;
application rollback while the expanded schema remains;
pausing and resuming a backfill;
forward-fixing bad rows;
restoring from backup and replaying later changes.

A backup is not proof of recovery. Verify restoration time, retention, encryption, and point-in-time recovery procedures through the organization's disaster-recovery process.

Destructive contraction should be delayed. First stop old writes, instrument old reads, retain the expanded shape through at least the rollback window, and obtain approval bound to the exact migration artifact. Human Approval for AI Agents describes how to bind approval to an exact action rather than a conversation.

Put production execution behind policy

The coding agent may create a migration pull request, evidence report, and runbook. A separate deployment system should:

verify the migration artifact digest;
require designated code and data owners;
enforce environment and change-window policy;
acquire short-lived database credentials;
set operation-specific timeouts;
stream audit and database telemetry;
stop on predeclared thresholds;
prevent the agent from widening its own access.

Never place a production database URL in model context or a general-purpose agent shell. A read-only credential is not harmless if it exposes personal, financial, secret, or operational data.

Use pg_locks and application/database monitoring to observe blockers and waiters, but interpret them with the target system's runbook. Killing a blocking transaction can be more damaging than aborting the migration.

Verify each phase

Before approval, require:

schema and application compatibility across deployed versions;
lock-mode analysis against official versioned docs;
representative staging rehearsal;
dry-run or sampling for data transformations;
invariants before and after each phase;
query-plan and latency checks where relevant;
replica and queue health thresholds;
an abort action that has been reviewed;
post-deployment reconciliation;
evidence that old paths are unused before contraction.

A staging success does not predict production duration when table size, transaction age, hardware, or traffic differ. Use it to catch correctness errors and calibrate, not to promise a runtime.