Safe AI-Assisted Database Migrations: An Expand-and-Contract Plan

A generated database migration can look tidy while making deployment unsafe. Renaming a column may break the old application version still serving requests. Adding a constraint may reject historical records. A supposedly reversible change may lose information that no rollback script can recreate.

Safe AI-assisted database migrations begin with deployment compatibility, not SQL formatting. Treat the proposed migration as a draft. Your job is to establish what it changes, how long it may take, and whether old and new application versions can coexist while the change rolls out.

Define the change and its consequences

Write down the business goal in one sentence. Then list the tables, columns, indexes, constraints, and application paths affected. Distinguish a schema change from a data transformation. A new nullable column is a different risk from rewriting every row or deleting an old field.

Review PostgreSQL's table-alteration documentation for the operation involved. Exact locking and rewrite behaviour depend on the statement and server version. Don't rely on an assistant's generic claim that a change is “online” or “instant.” Confirm it against your database and workload.

Use SQL Formatter on a sanitised migration to make its structure easier to review. The formatter does not execute SQL, check lock behaviour, prove reversibility, or validate business logic. Keep credentials and production data out of shared examples.

Expand before you remove anything

Suppose a fictional application wants to replace display_name with separate name fields. A direct rename isn't enough, and splitting names automatically may be inappropriate. A safer design can add new fields, update the application to understand both representations, and only later retire the old field.

An illustrative first step might be:

ALTER TABLE customers ADD COLUMN preferred_name text;

This example deliberately avoids deleting data. The next application version can read the new field when present and fall back to the old one. Decide explicitly whether writes update both fields, only the new field, or a compatibility layer. Otherwise old and new workers can disagree about the authoritative value.

Expand-and-contract isn't a promise that every change is easy. It is a way to separate compatibility work from destructive cleanup. The contract stage should happen only after you know that older application versions, background jobs, and exports no longer depend on the old structure.

Rehearse with representative data

Run the migration in an isolated environment with production-like volume and distribution, using appropriately protected or anonymised data. Ten tidy rows won't reveal a long backfill, a skewed index, or legacy values that violate the proposed constraint.

Measure execution time, lock waits, disk growth, and application latency during rehearsal. Record the server version and settings. A successful empty-database test proves much less than a realistic rehearsal with concurrent traffic.

Check historical edge cases before adding constraints. Count nulls, duplicate keys, unexpected formats, and references to missing rows. If the backfill makes assumptions, capture them as queries and review their results. Don't let generated code silently invent a meaning for ambiguous customer data.

Separate backfills from deployment

A large data backfill often belongs in a resumable job rather than inside the deployment transaction. Process bounded batches, record progress, and make the transformation safe to restart. Choose batch size from observed database behaviour, not from a convenient round number in a prompt.

Watch for writes arriving while the backfill runs. You may need dual-write logic, a version marker, or a final reconciliation step. Compare counts and selected invariants before switching reads to the new representation. Matching row counts alone won't prove that values are correct.

For repeatable job-state design, see durable workflow checkpoints. The migration's success condition should be a verified data state, not merely the absence of an exception in a worker log.

Understand locks and deployment order

Read the relevant ALTER TABLE reference for your exact operation. Set an appropriate lock-wait policy and decide what happens if the migration cannot acquire its lock promptly. A deployment should fail clearly rather than hold application traffic indefinitely.

Document the order: deploy compatible reads and writes, apply expansion, backfill, validate, switch traffic, observe, and finally remove obsolete structures. The sequence may differ by application, but it must be explicit. Include scheduled jobs and administrative scripts, not only the main web service.

Compare sanitised before-and-after configuration with Text Diff. Review feature flags and field names alongside SQL. A correct migration can still fail because one worker starts using the new field before it exists.

Make recovery realistic

Ask what rollback actually restores. Reverting application code may be enough after an additive change. It won't recover a dropped column or undo an ambiguous data conversion. Test restore procedures and estimate how long recovery would interrupt the service.

Define stop conditions before launch: excessive lock waits, rising error rates, failed invariants, or unexpected disk pressure. Assign a person who can stop the rollout. Keep a change log with evidence from rehearsal and production checks, not just a generated “down” migration.

Conclusion

Let AI help draft SQL and identify questions, but don't let it decide production safety. Expand-and-contract changes, representative rehearsal, bounded backfills, and a believable recovery plan make a migration reviewable. Delete old structures only when compatibility evidence says you're ready.