- Home
- Skills
- Data & Databases
- Zero-Downtime Migrations for PostgreSQL and MySQL
Works with the AI Agents you already use
Zero-Downtime Migrations for PostgreSQL and MySQL
**The problem.** Coding agents write migrations that pass every test and then take production down.
$12
Zero-Downtime Migrations for PostgreSQL and MySQL
Example session with this skill installed
Add a required tenant_id column to our orders table (about 180 million rows, PostgreSQL 15, Rails 7) with a foreign key to tenants and an index, and backfill it from customers.tenant_id.
- Read your context and instructions
- Compiled the zero-downtime migrations
Example session (illustrative).
Facts gathered: PostgreSQL 15.6; orders is 212 GB, ~180M rows, ~900 writes/s; Rails 7.1 migrations run in a transaction by default; deploys are rolling.
Plan (6 steps, each its own deploy or job; nothing applied):
| # | Ships as | SQL / change | Lock | Compatible with |
|---|---|---|---|---|
| 1 | migration | ADD COLUMN tenant_id bigint (nullable, no default) | ACCESS EXCLUSIVE, metadata only, lock_timeout 3s | old code |
| 2 | migration, disable_ddl_transaction! | add_index :orders, :tenant_id, algorithm: :concurrently | SHARE UPDATE EXCLUSIVE, ~25 min est. | old code |
| 3 | migration | ADD CONSTRAINT orders_tenant_fk FOREIGN KEY ... NOT VALID | brief SHARE ROW EXCLUSIVE on both tables | old code |
| 4 | code deploy | model sets tenant_id on create/update (dual-write) | none | both |
| 5 | job | batched backfill WHERE id >= :lo AND id < :lo + 5000 AND tenant_id IS NULL, pause if replay_lag > 5 s | row locks per batch | both |
| 6 | 4 migrations, one transaction each | (a) VALIDATE CONSTRAINT orders_tenant_fk; (b) add CHECK (tenant_id IS NOT NULL) NOT VALID; (c) validate it; (d) SET NOT NULL (no scan on PG 12+) and drop the CHECK | SHARE UPDATE EXCLUSIVE for the scans; brief ACCESS EXCLUSIVE for (b) and (d), lock_timeout 3s | new code |
Flagged in review: the first draft used null: false, default: ... with a subquery default, which is not allowed and would have needed a rewrite anyway; add_index without algorithm: :concurrently, which would have blocked writes for the whole build; and the CHECK added and validated in one transaction, which would have held ACCESS EXCLUSIVE for the whole validation scan.
Rollback: steps 1-3 have down migrations (DROP INDEX CONCURRENTLY, drop constraint, drop column). Step 5 is re-runnable; stop the job to roll back. Step 6 is reversible (DROP NOT NULL). If step 2 fails, check pg_index.indisvalid and drop the INVALID index before retrying.
Handed to you: 7 migration files, the backfill job, and this plan. Nothing was run against any database.
Connects securely to your tools. The creator never sees your data.
About this skill
The problem. Coding agents write migrations that pass every test and then take production down. CREATE INDEX without CONCURRENTLY blocks writes for minutes. ALTER TABLE ... SET NOT NULL scans a 200 GB table under an exclusive lock. A one-line RENAME COLUMN breaks every app server still running the old code. Even an "instant" ALTER can stall all traffic while it waits in the lock queue behind one long-running query. None of this shows up on a laptop database with 50 rows.
What the agent does with this skill.
- Checks the database version, table size and migration tool first, because the safe answer changes at PostgreSQL 11 and 12 and MySQL 8.0.12 and 8.0.29.
- Splits risky changes into expand / dual-write / backfill / switch reads / contract steps, each compatible with the old and the new app version, and says which deploy each step ships in.
- Replaces locking operations with safe equivalents:
CREATE INDEX CONCURRENTLY(and cleanup of INVALID indexes),NOT VALID+VALIDATE CONSTRAINT, the PG 12+ CHECK trick forSET NOT NULL,UNIQUE USING INDEX, new-column-plus-trigger for type changes and renames,ALGORITHM=INSTANT/INPLACE, LOCK=NONEon MySQL. - Adds
lock_timeout/statement_timeout(or MySQLlock_wait_timeout) so a blocked migration fails fast instead of queuing the whole site behind it. - Writes backfills as idempotent, resumable, primary-key batches that watch replica lag, outside the migration transaction.
- Knows how Rails, Django, Alembic and Prisma wrap migrations in transactions and how to opt out for concurrent index builds.
- Runs a pre-merge checklist and writes a per-step rollback plan, marking one-way steps explicitly.
- Never runs anything against production. It produces the files and the plan; a human applies them.
Who it's for. Teams that let Claude Code, Cursor, Codex CLI or similar agents touch application code that ships database migrations, and reviewers who want agent-written migrations to arrive already safe.
Requirements. Any agent that supports SKILL.md skills. A PostgreSQL (primary focus) or MySQL/InnoDB project. No extra software, database access optional (read-only inspection only, when you grant it). Works with or without AgentHarness.
Find more skills like this on the Claude Skills Marketplace, the Codex Skills Marketplace and the Gemini Skills Marketplace, or browse Claude Skills for Data & Databases.
Supports only PostgreSQL and MySQL. Does not execute SQL directly. Requires manual entry of table stats for accurate lock duration estimates. Multi-step migrations require multiple deployments.
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 buying a skill.
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 17 days ago
- Software
- PostgreSQLorMySQLMigration tool
Use your own migration tool: Rails, Django, Alembic, Prisma or plain SQL.
Permissions
No special permissions declared or detected