Before AI Writes a PostgreSQL Schema Migration, Verify Locks, Staging, and Recovery

Review AI-written PostgreSQL 18 schema migrations for lock impact, staged compatibility, backfill safety, observability, rollback, and tested recovery.

A Correct Statement Can Still Be an Unsafe Migration

An AI assistant can produce valid-looking data definition language in seconds. That does not establish that the change is safe for a live database. A statement that succeeds on an empty development table may wait behind an old transaction, block application traffic, rewrite a large relation, fill storage, amplify replication lag, or remove data that an older application version still needs. The migration file is only one artifact in a release system.

This workflow is deliberately scoped to PostgreSQL 18 and the current PostgreSQL 18 documentation. It is not a universal recipe for another major version, a managed-service variant, an extension, an ORM, or a particular deployment platform. It does not promise zero downtime. Actual behavior depends on the exact PostgreSQL version and command, table and index shape, data volume, workload, dependencies, transaction state, replicas, storage, and application rollout. A qualified human DBA or SRE must own the plan, set environment-specific limits, watch execution, and make the final release decision.

AI may help organize a draft plan, enumerate questions, or compare a proposed change with cited documentation. It should not receive production credentials, private data, unapproved schema details, or unrestricted production access. NIST’s AI RMF Core calls for defined human-AI roles, documented oversight, testing, and monitoring. Apply that discipline here: the model proposes; accountable operators verify.

1. Freeze the Target Before Reviewing the SQL

Create a migration identity card. Record the repository commit, migration identifier and checksum, application versions that may be live during the change, exact PostgreSQL server version, hosting model, primary and replica topology, relevant extensions, target schemas and relations, proposed release window, and the public or internal documentation cutoff used for review. Name the DBA or SRE who owns execution and the application owner who owns compatibility.

Also record recovery objectives, maintenance-window constraints, current backup evidence, and the approved observability dashboard. Do not let “PostgreSQL-compatible” stand in for a version. Do not let an AI answer based on an unspecified release become evidence for PostgreSQL 18. If any identity field is unknown, the review starts at HOLD.

2. Break the Migration Into Independently Reviewable Operations

Rewrite the proposal as an operation ledger rather than one opaque migration. Give each schema change, index build, constraint action, data backfill, application switch, cleanup, and verification step its own identifier. For every item, capture the intended invariant, affected objects, expected lock class, possible scan or rewrite, estimated work, transaction boundary, retry behavior, stop condition, and evidence required before the next item.

This matters because PostgreSQL can choose the strictest lock required when several ALTER TABLE subcommands are combined. Combining work may reduce repeated scans in some cases, but it can also hide the riskiest operation inside a convenient bundle. The human reviewer should decide whether operations belong together after reading the exact PostgreSQL 18 behavior, not because an AI combined them syntactically.

3. Verify the Exact Lock for Every DDL Form

Use the PostgreSQL 18 ALTER TABLE reference and explicit-locking chapter for the exact subform. The reference says ALTER TABLE acquires ACCESS EXCLUSIVE unless a lower level is explicitly documented. An ACCESS EXCLUSIVE lock conflicts with every table-level lock mode, including the ACCESS SHARE lock used by ordinary reads.

Translate that into an operational question: how long might acquisition wait, what sessions can block it, and what traffic could queue behind it after acquisition? Inspect long-running and idle-in-transaction sessions as part of the preflight. A fast catalog change can still cause a customer-visible queue if it waits in the wrong place. “The statement ran in milliseconds on staging” is not a lock proof unless staging reproduced the relevant concurrency.

4. Determine Whether PostgreSQL Will Scan, Rewrite, or Rebuild

Label each operation metadata-only, validation scan, table rewrite, index build, data backfill, or destructive cleanup, with a citation and a DBA sign-off. PostgreSQL 18 documents important distinctions. For example, adding a column with a nonvolatile default can avoid a table rewrite, while a volatile default, stored generated column, identity column, or some constrained domain cases can rewrite the table and its indexes. Changing a column type normally rewrites them, subject to documented exceptions.

Never generalize one favorable case into “adding columns is safe.” Rewrites can take significant time and temporarily require substantial extra disk space. PostgreSQL also warns that rewriting forms of ALTER TABLE are not MVCC-safe: after a rewrite, a concurrent transaction using a snapshot taken before the rewrite can see the table as empty. The plan must estimate headroom and identify the actual expression, type, collation, indexes, partitions, and descendants involved. Unknown rewrite behavior is a release blocker.

5. Map Dependencies and the Compatibility Window

List views, indexes, constraints, triggers, functions, publications, subscriptions, jobs, reports, permissions, ORM mappings, caches, and every application version that reads or writes the affected shape. PostgreSQL’s dependency-tracking documentation explains that dependent objects can restrict a drop and that CASCADE can remove dependent objects. That mechanism is not permission to accept an AI-generated cascade without a reviewed object list.

Define the mixed-version interval explicitly. During a rolling deployment, old and new application instances may coexist. The expansion must remain valid for both, and the cleanup must wait until telemetry proves that incompatible code paths are gone. ORM-generated migrations deserve the same review as handwritten DDL. A framework’s “reversible” label does not prove data preservation, lock safety, or compatibility.

6. Turn Expand-and-Contract Into a Gated Rollout

For changes that cannot be safely atomic, design an environment-specific sequence: introduce a compatible new structure; deploy code that can tolerate old and new states; populate or transform data in controlled work; verify parity; switch reads or writes; observe; and remove the old structure only in a later release. This is a team rollout pattern, not a PostgreSQL guarantee and not a universal prescription.

Document how each application version behaves at every stage, including retries and partial failure. Avoid assuming that dual writes are automatically consistent; define how discrepancies are detected and repaired. Destructive actions such as dropping a column or narrowing a type belong after the compatibility window and explicit retention decision. PostgreSQL notes that dropping a column is logically quick but does not immediately reclaim its on-disk space, so cleanup expectations must also be accurate.

7. Separate Backfill Work From Schema DDL

A backfill is production data work, not a footnote. Give it a bounded rate, resumable cursor or other approved progress marker, idempotency rule, pause switch, validation query owned by the team, and limits for load, replica lag, error rate, and storage growth. Decide how concurrent application writes are reconciled. Test interruption and resumption with representative data.

Do not paste a model-generated bulk update into production simply because it is inside a transaction. A single large transaction can hold locks, retain row versions, generate substantial WAL, delay replicas, and make failure recovery harder. The article intentionally provides no runnable backfill statement: batching keys, cadence, ordering, and conflict handling are application-specific decisions for the DBA/SRE and data owner.

8. Treat Concurrent Index Builds as Their Own Release Event

Read the PostgreSQL 18 CREATE INDEX documentation before choosing CONCURRENTLY. It permits normal writes to continue, but it performs more work, waits for relevant transactions, and cannot run inside a transaction block. PostgreSQL documents two table scans for a concurrent build and notes that failure can leave an invalid index. A concurrent unique index can begin enforcing uniqueness before the index is available for use. If that build fails during its second scan, the invalid index can continue enforcing the uniqueness constraint.

Therefore “use concurrently” is not a universal safety switch. Give the index build separate scheduling, load and disk budgets, an invalid-index detection and disposition plan, and an application query-plan verification step. Account for long transactions, partitioned-table restrictions, and the rule that only one concurrent index build can run on a table at a time. The human operator must know what will happen if the build stalls or is canceled.

9. Stage Constraint Enforcement and Validation Deliberately

For supported PostgreSQL 18 foreign-key, check, or not-null cases, the DBA may evaluate introducing a constraint as NOT VALID and validating it later. The official reference explains that this can skip the initial scan while new inserts or updates are checked. VALIDATE CONSTRAINT later scans existing rows and takes a SHARE UPDATE EXCLUSIVE lock on the altered table; foreign-key validation also requires a ROW SHARE lock on the referenced table.

Do not turn that option into boilerplate. Confirm that it applies to the exact constraint and partition layout. Define how existing violations will be found and resolved, how validation duration will be observed, and what happens if validation fails. An unvalidated constraint is not evidence that historical data conforms. The release record should distinguish “enforced for new changes,” “historical rows validated,” and “application assumption enabled.”

10. Rehearse the Failure Modes, Not Just the Happy Path

Use an isolated, production-like environment with the same PostgreSQL major version, relevant extensions, representative schema and data distribution, realistic table and index scale where feasible, and concurrent traffic that includes long transactions. Measure lock acquisition, execution time, WAL volume, storage growth, application latency, replica lag, and validation time. Record the limitations of the rehearsal instead of calling it production-equivalent.

Inject safe failures: a lock that cannot be acquired within the approved window, an interrupted backfill, an index build that does not complete, a failed validation, a replica that falls behind, and an application instance still using the old shape. Confirm that operators can pause before destructive work. A test that only proves the SQL parser accepts the migration does not establish operational readiness.

11. Set Time Budgets and Stop Conditions Per Migration Session

Choose lock-wait, statement, transaction, and idle-transaction controls with the DBA for this workload and driver. PostgreSQL 18 documents that lock_timeout aborts a statement after a lock wait exceeds its limit, while statement_timeout covers statement execution more broadly. The documentation cautions against setting these globally in postgresql.conf because that affects every session.

Do not publish magic timeout numbers. Record approved session scope, how the migration tool applies settings, which timeout should fire first, and how a timeout is surfaced. Define objective stop conditions for latency, blocked sessions, error rate, storage, WAL archiving, replication lag, and elapsed phase time. Cancellation itself must be rehearsed; an operator needs to know whether it leaves a transaction aborted, an invalid index, a partial backfill, or a committed earlier phase.

12. Observe Locks, Progress, Replicas, and the Application Together

Build a release dashboard before the window. PostgreSQL’s cumulative statistics views expose database activity and replication information, while its progress-reporting views cover selected commands such as CREATE INDEX, VACUUM, ANALYZE, CLUSTER, COPY, and base backup. Progress reporting is not available for every DDL form, so absence from a progress view is not proof that nothing is happening.

Correlate database evidence with request latency, connection-pool saturation, job queues, error codes, and business invariants. Identify blockers and waiters, not merely CPU. Watch replicas and downstream consumers before advancing. Save timestamps and phase outcomes in the migration record so the next decision is based on evidence, not an AI estimate or a calm-looking average.

13. Prove Rollback and Recovery as Different Capabilities

PostgreSQL ROLLBACK discards updates made by the current transaction. That does not make every release reversible. Some operations cannot share one transaction boundary; CREATE INDEX CONCURRENTLY is one documented example. PostgreSQL also documents that sequence counter advances such as nextval are immediately visible to other transactions and are not rolled back if the transaction aborts. External effects, committed phases, destructive DDL, and lossy data transformations are not restored by rolling back a later application deployment.

For each phase, specify one of three paths: abort before commit; forward-fix while preserving compatibility; or recover from protected data. Then prove the chosen recovery mechanism. PostgreSQL 18 explains that point-in-time recovery combines a base backup with a continuous sequence of archived WAL and that logical pg_dump output is not a substitute for that chain. Its PITR chapter explicitly advises setting up and testing WAL archiving and inspecting the restored database before reopening access.

A dashboard showing “backup succeeded” is not a restore drill. Record the last isolated restore, recovered target, achieved recovery time and recovery point, required earlier backups for any incremental chain, configuration and extension dependencies, and the person authorized to invoke recovery. If the required restore path has not been exercised within the organization’s policy, mark the destructive phase HOLD.

Fictional Worked Example: Adding a Normalized Customer State

Consider a fictional subscription service whose customer_profile table has 180 million rows. The team wants a normalized state field for routing, plus an index and a not-null guarantee. An AI draft proposes one migration that adds the field with a derived default, fills every row, marks it not null, creates an index, and drops the legacy field. It labels the file “safe and reversible.”

The review rejects that bundle. The version card confirms PostgreSQL 18.6, two read replicas, a rolling application deployment, and reporting jobs that can hold old snapshots. The ledger identifies a potentially rewriting default, a large backfill, constraint validation, an index build, and destructive cleanup. No one accepts the model’s runtime estimate.

The human owners redesign the release as gated phases. First they introduce a compatible nullable field without relying on a volatile derived default. Next they deploy application code that tolerates both representations and records parity. A separately controlled backfill runs within team-defined load and lag budgets and can pause and resume. After parity and violation checks pass, the DBA evaluates the PostgreSQL 18 constraint-staging path and validates historical rows. The index is scheduled and monitored as its own event, with invalid-index handling prepared. Reads switch only after query behavior and replica health pass. The legacy field remains through an observation window and a full older-version retirement check.

During rehearsal, a long reporting transaction delays the index phase and replica lag crosses the test threshold. That is useful evidence: the runbook gains a preflight for old transactions and a specific stop condition. The production review remains HOLD until a fresh restore drill demonstrates the required recovery point and time. Nothing about the final plan is “zero downtime by default”; it is a risk-bounded rollout owned by named humans.

The RELEASE or HOLD Gate

The accountable DBA/SRE and application owner should record RELEASE only when all of the following are true: the exact artifact and PostgreSQL 18 target are frozen; every operation has documented lock, scan, rewrite, transaction, and dependency behavior; mixed-version compatibility is proven; backfill, index, and constraint phases have bounded stop conditions; representative rehearsal evidence is attached; monitoring and staffing are ready; destructive work is delayed until the compatibility window closes; and the applicable abort, forward-fix, or tested recovery path meets the organization’s objectives.

Record HOLD for any unknown lock or rewrite, unreviewed CASCADE, unexplained long transaction, insufficient disk or WAL capacity, unacceptable replica lag, failed or incomplete validation, invalid index without disposition, partial backfill without a safe resume path, active old application version, missing observer, untested recovery chain, or disagreement between responsible owners. A HOLD is a successful control, not a failed prompt.

Concise Pre-Release Checklist

• Exact migration checksum, PostgreSQL 18 version, topology, objects, and owners are recorded.
• Each operation has a verified lock class and scan, rewrite, index, or backfill classification.
• Dependencies, partitions, replicas, extensions, ORMs, and mixed application versions are mapped.
• Expand, backfill, validate, switch, observe, and contract phases have separate gates where needed.
• Timeout scope, stop thresholds, monitoring, cancellation, and invalid-index handling are rehearsed.
• Rollback is not confused with forward repair or backup/PITR recovery.
• A recent isolated restore proves the required recovery target and timing before destructive work.
• Named human DBA/SRE and application owners sign RELEASE; otherwise the decision is HOLD.

Source Notes

This guide uses the PostgreSQL 18 documentation for ALTER TABLE behavior, table-level locks, concurrent index-build caveats, dependency tracking, client-session timeouts, activity and replication statistics, command progress reporting, transaction rollback, transaction-isolation caveats, and continuous archiving and point-in-time recovery. The human-oversight framing follows the voluntary NIST AI RMF 1.0 and its Core outcomes. Recovery testing and backup-validation framing uses NIST SP 800-34 Rev. 1 by analogy; that publication is federal information-system contingency-planning guidance, not PostgreSQL-specific or universally mandatory. Verify all details against the deployed version and the organization’s approved database, security, and recovery procedures before acting.