- Home
- Skills
- Data & Databases
- Zero-Downtime Migrations
Zero-Downtime Migrations
Expand-contract schema changes — add, backfill, switch, then drop — safe across several deploys.
Free
Works with the AI Agents you already use
Zero-Downtime Migrations
Example session with this skill installed
I need to rename the 'username' column to 'handle' in our production 'profiles' table. It has 5 million rows. Give me the migration plan.
- Read your context and instructions
- Compiled the zero-downtime migrations
I have analyzed the request to rename username to handle on the profiles table. This is a breaking change that requires a 4-step expand-contract cycle to avoid production downtime.
Database Migration Plan: Rename username to handle
| Step | Action | Type | Notes |
|---|---|---|---|
| 1 | Add handle column as NULL | Additive | Safe for existing code. |
| 2 | Backfill handle from username | Data | Use chunked updates for 5M rows. |
| 3 | Deploy code: Dual-write + Fallback-read | Code | Write to both; read handle (fallback to username). |
| 4 | Tighten handle and drop username | Contract | Run only after code is fully deployed and verified. |
Migration 1: Add New Column
-- 20240520_add_handle_to_profiles.sql
-- Step 1: Add the new column as nullable.
ALTER TABLE profiles ADD COLUMN handle TEXT NULL;
Backfill Script (Application Level)
-- Run in batches of 10,000 to avoid long-held locks
UPDATE profiles
SET handle = username
WHERE handle IS NULL
AND id BETWEEN $start AND $end;
Migration 2: Tighten and Drop
-- 20240522_tighten_handle_in_profiles.sql
-- Step 2: Only run after code writes to both columns and backfill is 100% verified.
ALTER TABLE profiles ALTER COLUMN handle SET NOT NULL;
-- 20240523_drop_username_from_profiles.sql
-- Step 3: Only run after code no longer references the 'username' column.
ALTER TABLE profiles DROP COLUMN username;
Next steps
- Deploy the additive migration and verify the new column exists.
- Run the chunked backfill script during off-peak hours.
- Update the User model to handle dual-writes to both fields.
Connects securely to your tools. The creator never sees your data.
About this skill
Database schema changes often cause production downtime due to table locks or application crashes during rolling deploys. A single PR that adds a NOT NULL column or renames a field frequently breaks the running version of the app. This skill enforces the expand-contract pattern to ensure zero-downtime migrations.
What it does
- Enforces expand-contract by splitting breaking changes into safe, multi-stage deployments.
- Validates nullability to ensure new columns are added as
NULLfirst to prevent write failures. - Audit index creation for large tables, ensuring Postgres
CONCURRENTLYusage to avoidACCESS EXCLUSIVElocks. - Plans backfills by designing idempotent, chunked data updates for multi-million row tables.
- Refines rename strategies by transitioning through dual-writing and fallback reading phases.
How it works
- Classify the change based on the proposed SQL to determine if it requires 1, 2, or 3+ migration files.
- Draft the additive migration ensuring it is safe to deploy while the old application code is still running.
- Design the backfill and contraction steps, including the necessary application code logic for dual-writing or defensive reading.
Frameworks & tools
This skill works with any SQL-based migration system including Prisma, Alembic, Django Migrations, Sequelize, and raw SQL scripts. It includes specific optimizations for Postgres production environments.
Why this beats prompting it yourself
Generic AI prompts often suggest one-shot migrations that cause locks or 500 errors during deploys. This skill encodes strict industry-standard safety rules that prioritize uptime over brevity, catching "single-migration smells" before they reach CI.
Use cases
- Adding a
NOT NULLconstraint to a table with existing production data. - Renaming a high-traffic column without breaking the current app version.
- Converting a column data type on a table with millions of rows.
- Adding unique or secondary indexes to hot tables without blocking writes.
Known limitations
Does not provide performance tuning for specific database engine internals beyond locking behavior. Focused on the migration lifecycle rather than query optimization.
Focuses on schema migration logic and safety patterns. Does not perform query optimization or engine-level performance tuning.
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
1 use
Times used to date
Free forever
No account required to browse
Trust & safety
Security scan
Security scan passed 28 days ago
- Free with an Agensi account