- Home
- Skills
- Data & Databases
- migration safety checker
migration safety checker
Audit Postgres migrations for locking risks, duration bottlenecks, and data-loss scenarios.
$9
Works with the AI Agents you already use
migration safety checker
Example session with this skill installed
Review this migration for a table with 50M rows (40GB) on Postgres 15. The runner is Rails.
-- UP
ALTER TABLE users ADD COLUMN bio text DEFAULT 'N/A' NOT NULL;
CREATE INDEX idx_users_bio ON users(bio);
- Read your context and instructions
- Compiled the migration safety checker
No migration runner configuration supplied: assuming default Rails behavior (wrapped in a transaction).
do-not-deploy
Adding a column with a volatile default and then creating a standard index inside a Rails transaction will hold an ACCESS EXCLUSIVE lock on the users table for the entire duration of the index build.
2. Operation-by-operation risk table
| # | Operation | Lock level | Blocks reads? | Blocks writes? | Duration class | Notes |
|---|---|---|---|---|---|---|
| 1 | ALTER TABLE users ADD COLUMN bio text DEFAULT 'N/A' NOT NULL | ACCESS EXCLUSIVE | Yes | Yes | ms | Metadata only on PG11+; lock held until transaction COMMIT. |
| 2 | CREATE INDEX idx_users_bio ON users(bio) | SHARE | No | Yes | min (50M rows) | Blocks all writes. Cannot run CONCURRENTLY inside a transaction. |
RF-12: Strong lock held across slow work. The ACCESS EXCLUSIVE lock from step 1 is held while step 2 builds the index.
- RF-09: Missing DOWN migration. No rollback provided for audit.
Total duration of the longest-held lock: ~5-10 minutes (estimated index build time for 50M rows).
3. Rewritten migration
-- Stage 1: Add the column (Online, PG 11+)
-- Run in a standard migration
SET lock_timeout = '3s';
ALTER TABLE users ADD COLUMN bio text DEFAULT 'N/A' NOT NULL;
-- Stage 2: Create the index (Online)
-- Must run with disable_ddl_transaction!
SET lock_timeout = '3s';
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_bio ON users(bio);
4. Rollback plan
-- Rollback for Stage 2
DROP INDEX CONCURRENTLY IF EXISTS idx_users_bio;
-- Rollback for Stage 1
ALTER TABLE users DROP COLUMN bio;
- Data loss on rollback: Dropping the
biocolumn destroys all data stored in that column since deployment. - Point of no return: Stage 1 (column creation).
- Rollback locking:
DROP COLUMNtakesACCESS EXCLUSIVE.
5. Deploy window recommendation
- When: Off-peak. While Stage 1 is fast, the lock queue can still block traffic if long queries are running.
- Preconditions: Run the long-running transaction check. Ensure at least 10GB free disk for the new index.
- Abort criteria: If
lock_timeouttriggers, abort and retry during lower traffic.
Next steps
- Run the
pg_stat_activityquery to check for long-running transactions. - Update the Rails migration to use
disable_ddl_transaction!for the index creation. - Verify disk space on the DB instance for the new index pages.
Connects securely to your tools. The creator never sees your data.
About this skill
Migrations that work in staging often crash production by triggering ACCESS EXCLUSIVE locks on large tables. This skill audits Postgres DDL to identify locking risks, estimate duration based on table size, and catch data-loss scenarios before they reach your main branch.
What it does
- Locking analysis identifies which statements block reads or writes and for how long.
- Online rewrites transforms unsafe
ALTER TABLEcommands into zero-downtime staged plans. - Rollback auditing verifies if
DOWNmigrations are present, correct, or cause irreversible data loss. - Transaction safety flags operations like
CREATE INDEX CONCURRENTLYthat fail inside transaction blocks. - Duration estimation calculates time classes (ms to hours) when provided with row counts and table sizes.
How it works
- Paste your migration along with optional table statistics and Postgres version.
- Review the risk table which breaks down every statement by lock level and blocking behavior.
- Apply the staged plan which provides independent, revertible SQL steps and application-level wait conditions.
- Execute pre-flight checks using provided queries to detect long-running transactions that would stall the lock queue.
Frameworks & tools
Postgres 11 through 18, including managed services like RDS, Aurora, and Cloud SQL. Supports analysis for migrations generated by Rails, Django, Alembic, Flyway, Goose, and Atlas.
Why this beats prompting it yourself
Postgres locking logic is nuanced, especially regarding the lock queue where a single metadata change can block all reads. This skill uses a strict lock matrix and regex-based parsing to ensure no ACCESS EXCLUSIVE lock goes unflagged, preventing the "it worked on my machine" production outage.
Use cases
- Reviewing a
CREATE INDEXon a table with 100M+ rows to ensure it stays online. - Rewriting a column rename or type change into an expand/contract staged rollout.
- Auditing third-party migration files for missing
lock_timeoutguards. - Verifying if a rollback migration will destroy data written since the last deploy.
Known limitations
Postgres only. Cannot connect to your database, so estimates rely entirely on provided row counts. Does not verify business logic correctness, only locking and structural safety.
Postgres only. Requires row counts for duration estimates. Cannot connect to live DB. Inferred lock levels may be conservative for exotic DDL. Skip business logic verification.
How to use
- 1
Connect your agent to the Agensi MCP
Copy the prompt and paste it into Claude, ChatGPT, Cursor or any agent that supports MCP. It sets up the connection for you. Manual setup
- 2
Sign in to Agensi
Your agent shows a sign-in link. Sign in and click Allow.
- 3
Ask for what you need
Your agent loads this skill and uses it on your task. It always asks before any payment.
Reviews
No reviews yet
Be one of the first to try it. Every listed skill goes through the trust checks below.
Security scanned
Checked by our 8-point scan before listing
Fresh listing
Recently published to Agensi
Money-back guarantee
Doesn't do what its page promises? You get your money back.
Trust & safety
Security scan
Security scan passed 9 days ago