migration safety checker

    1

    Audit Postgres migrations for locking risks, duration bottlenecks, and data-loss scenarios.

    $9

    Secure checkout via Stripe

    Money-back guarantee

    Converts to your local currency at checkout

    Security scanned

    Works with the AI Agents you already use

    Claude CodeClaude CodeCursorCursorCodex CLICodex CLIMuseMuseDotsDots+21 more

    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

    #OperationLock levelBlocks reads?Blocks writes?Duration classNotes
    1ALTER TABLE users ADD COLUMN bio text DEFAULT 'N/A' NOT NULLACCESS EXCLUSIVEYesYesmsMetadata only on PG11+; lock held until transaction COMMIT.
    2CREATE INDEX idx_users_bio ON users(bio)SHARENoYesmin (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 bio column destroys all data stored in that column since deployment.
    • Point of no return: Stage 1 (column creation).
    • Rollback locking: DROP COLUMN takes ACCESS 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_timeout triggers, abort and retry during lower traffic.

    Next steps

    1. Run the pg_stat_activity query to check for long-running transactions.
    2. Update the Rails migration to use disable_ddl_transaction! for the index creation.
    3. 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 TABLE commands into zero-downtime staged plans.
    • Rollback auditing verifies if DOWN migrations are present, correct, or cause irreversible data loss.
    • Transaction safety flags operations like CREATE INDEX CONCURRENTLY that fail inside transaction blocks.
    • Duration estimation calculates time classes (ms to hours) when provided with row counts and table sizes.

    How it works

    1. Paste your migration along with optional table statistics and Postgres version.
    2. Review the risk table which breaks down every statement by lock level and blocking behavior.
    3. Apply the staged plan which provides independent, revertible SQL steps and application-level wait conditions.
    4. 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 INDEX on 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_timeout guards.
    • 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. 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. 2

      Sign in to Agensi

      Your agent shows a sign-in link. Sign in and click Allow.

    3. 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

    Permissions

    Terminal / Shell
    Read Files
    Write Files
    Listed9 days ago

    What's inside

    Frequently Asked Questions

    This skill is designed for Postgres 11 through 18, including managed services like AWS RDS, Aurora, and Google Cloud SQL. It supports raw SQL migrations as well as those generated by frameworks like Rails, Django, Alembic, Flyway, and Atlas.

    While the skill cannot connect to your database for security reasons, it provides highly accurate duration classes (e.g., milliseconds vs. hours) if you provide approximate row counts and table sizes during the audit process.

    Standard LLM prompts often miss subtle lock-queue behaviors where a metadata lock blocks subsequent reads; this skill uses a specialized lock matrix and regex-based parsing to ensure every `ACCESS EXCLUSIVE` risk is flagged with zero false negatives.

    When a high-risk migration is detected, the skill generates a 'Staged Plan'—a series of independent SQL steps and application-level wait conditions designed to transform a blocking operation into a zero-downtime deployment.

    The skill audits 'DOWN' migrations to ensure they are logically sound and warns you specifically if a rollback would result in irreversible data loss for records created after the migration was applied.

    New in Data & Databases