postgres-migration-review

Review a PostgreSQL schema migration before it runs and say whether it is safe on a live database, with a safer sequence when it is not. It changes nothing.

Arguments

• A migration (required; ask for it when missing): a file, several files, or the pending changes of the project's migration tool.

Flow

1. Read the migration and its tool. Find the project's migration tool, whether it wraps each migration in a transaction, and the PostgreSQL major version in use (lock behaviour differs between versions). Read the tables' real sizes and indexes through the dbhub MCP server when it is available; otherwise say that sizes are unknown.
2. Check every statement for:
   • Locks that block reads or writes for long: ALTER TABLE that rewrites the table (a type change, a volatile default), CREATE INDEX without CONCURRENTLY, adding a foreign key or a CHECK without NOT VALID, SET NOT NULL on a large table without a validated CHECK first, VACUUM FULL, CLUSTER, and any ALTER TABLE that can queue behind a long transaction and then block everyone behind it (no lock_timeout).
   • Statements that cannot run in a transaction in a tool that opens one, such as CREATE INDEX CONCURRENTLY.
   • Data loss: DROP COLUMN, DROP TABLE, a narrowing type change, TRUNCATE, a DELETE or UPDATE without a WHERE.
   • Large data changes in one statement: a backfill of a big table that should run in batches outside the schema migration.
   • Incompatibility with the running application: during a rolling deploy the old code runs against the new schema; a rename, a drop or a new NOT NULL column without a default breaks it.
3. Check the way back: whether the migration can be reverted and what a revert loses.
4. Check the project's own rules for migrations (naming, forward-only, one change per file) in its contributing guide.
5. For each problem, write the safer sequence: for example add the column as nullable, backfill in batches, add a CHECK ... NOT VALID, validate it, then set NOT NULL; or expand, migrate the code, then contract across separate deploys.

Report

• Verdict: safe to run, safe with changes, or unsafe, in one sentence.
• Findings, most severe first: the statement, the lock or risk, why it matters at this table's size, and the safer sequence as SQL.
• Deploy order: which steps go before, with and after the code change.
• Revert: how, and what is lost.
• What could not be checked (table sizes, the PostgreSQL version, long-running transactions).