Use this skill for planning, testing, and safely executing PostgreSQL schema migrations — especially when working with production data or shared databases. **Trigger when user asks to:** - Test a schema migration before applying it to production - Add, remove, or rename columns
Installs just this skill. Get the whole plugin for auto-invocation.
⚡ How it fires
How this skill gets triggered: by you, by Claude, or both.
Fires itselfClaude auto-loads it when your prompt matches the work.
You can call itInvoke it directly when you want it.
Slash command/postgres-database-migration
👁️ Context preview
The summary Claude sees to decide when to auto-load this skill.
Use this skill for planning, testing, and safely executing PostgreSQL schema migrations — especially when working with production data or shared databases. **Trigger when user asks to:** - Test a schema migration before applying it to production - Add, remove, or rename columns
📊 Stats
Stars1,793
Forks99
LanguagePython
LicenseApache-2.0
📦 Ships with pg-aiguide
</> SKILL.md
postgres-database-migration.SKILL.md
---name: postgres-database-migration
description: |
Use this skill for planning, testing, and safely executing PostgreSQL schema migrations — especially when working with production data or shared databases.
**Trigger when user asks to:**
- Test a schema migration before applying it to production
- Add, remove, or rename columns safely on a live table
- Change a column's data type without downtime
- Add or drop indexes, constraints, or foreign keys on large tables
- Understand which ALTER TABLE operations lock the table
- Roll back a failed migration
- Plan a zero-downtime migration strategy
- Fork a database to test a migration safely
**Keywords:** migration, schema change, ALTER TABLE, add column, drop column, rename column, change type, zero downtime, lock, AccessExclusiveLock, concurrent index, forking, ghost, rollback, backfill, deploy
Covers: lock-level reference for every common DDL operation, safe migration patterns, fork-based testing, zero-downtime column changes, index creation, constraint addition, backfill strategies, pre/post-migration validation, and rollback planning.
---# PostgreSQL Database Migrations
A schema migration that works on an empty dev database can fail, lock, or corrupt data on a production table with millions of rows. This guide covers how to assess risk, test against real data, and execute migrations safely.
## DDL Lock Reference
| `DROP COLUMN` | `AccessExclusiveLock` (brief) | **Fast.** Column marked invisible; space reclaimed by VACUUM over time. |
| `SET DEFAULT` / `DROP DEFAULT` | `AccessExclusiveLock` (brief) | Metadata change only. Does not touch existing rows. |
| `CREATE INDEX CONCURRENTLY` | `ShareUpdateExclusiveLock` | **Non-blocking.** Allows reads and writes during build. Slower than regular index creation. |
| `DROP INDEX CONCURRENTLY` | `ShareUpdateExclusiveLock` | **Non-blocking.** Waits for queries using the index to finish, then drops. No table-level exclusive lock. |
**Key insight:** `AccessExclusiveLock` blocks everything — reads and writes. Even if the operation itself is fast (milliseconds), it must wait for all in-flight transactions to finish before acquiring the lock. A long-running query or idle transaction can cause an `ALTER TABLE` to hang and queue up all subsequent queries behind it.
## Safe Migration Patterns
### Add a Column
```sql
-- SAFE: nullable column, no default — instant
ALTER TABLE orders ADD COLUMN tracking_number TEXT;
-- SAFE (PG 11+): column with non-volatile default — instant
ALTER TABLE orders ADD COLUMN priority INTEGER NOT NULL DEFAULT 0;
-- UNSAFE: column with volatile default — full table rewrite
-- DO: add nullable, then backfill, then set default + NOT NULL
ALTER TABLE orders ADD COLUMN created_at TIMESTAMPTZ;
-- Backfill in batches (see Backfill section)
ALTER TABLE orders ALTER COLUMN created_at SET DEFAULT now();
ALTER TABLE orders ALTER COLUMN created_at SET NOT NULL; -- only if PG12+ or CHECK exists
```
### Drop a Column
```sql
-- SAFE: instant (column marked invisible, space reclaimed by VACUUM)
ALTER TABLE orders DROP COLUMN old_status;
```
**Application coordination:** Ensure your application no longer references the column before dropping it. For zero-downtime deploys, this requires two steps:
1. Deploy code that doesn't read/write the column
2. Then drop the column in a separate migration
**Security caveat:** `DROP COLUMN` does not physically delete the data. The column is marked as dropped in `pg_attribute` but the values remain on disk until `VACUUM` reclaims the space — and even then, a superuser could recover them. If the column contains sensitive data, run `VACUUM FULL` on the table after dropping, or use dump/restore to ensure the data is truly gone.
### Rename a Column
```sql
-- SAFE: instant metadata change
ALTER TABLE orders RENAME COLUMN status TO order_status;
```
**Warning:** This breaks any application code, views, or functions that reference the old column name. For zero-downtime deploys, use the column-swap pattern instead:
1. Add the new column
2. Deploy code that writes to both columns
3. Backfill old rows
4. Deploy code that reads from the new column
5. Drop the old column
### Change a Column Type
Most type changes rewrite the entire table. Safe alternatives:
```sql
-- UNSAFE: full table rewrite, blocks everything
-- DON'T: ALTER TABLE orders ALTER COLUMN amount TYPE NUMERIC(12,2);
-- SAFE: use a new column + backfill
ALTER TABLE orders ADD COLUMN amount_new NUMERIC(12,2);
-- Backfill in batches (see Backfill section below)
UPDATE orders SET amount_new = amount WHERE id BETWEEN 1 AND 10000;
-- ... continue in batches ...
-- Swap columns
ALTER TABLE orders DROP COLUMN amount;
ALTER TABLE orders RENAME COLUMN amount_new TO amount;
```
**Exception:** Some casts don't require a rewrite and are fast:
| From | To | Rewrite? |
|------|----|----------|
| `VARCHAR(n)` → `VARCHAR(m)` where m > n | No | Metadata only |
| `VARCHAR(n)` → `TEXT` | No | Metadata only |
| `NUMERIC(p,s)` → `NUMERIC(p2,s)` where p2 > p (same scale) | No | Metadata only |
| `INTEGER` → `BIGINT` | **Yes** | Full rewrite |
| `TIMESTAMP` → `TIMESTAMPTZ` | **Yes** | Full rewrite |
### Add a NOT NULL Constraint
```sql
-- PG 18+: simplified two-step pattern
ALTER TABLE orders ALTER COLUMN order_status SET NOT NULL NOT VALID;
ALTER TABLE orders VALIDATE NOT NULL ON order_status;
-- PG 12–17: fast if a valid CHECK constraint already exists
-- Step 1: add CHECK (non-blocking scan)
ALTER TABLE orders ADD CONSTRAINT orders_status_nn CHECK (order_status IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_nn;
-- Step 2: add NOT NULL (PG12+ recognizes the CHECK and skips the scan)
ALTER TABLE orders ALTER COLUMN order_status SET NOT NULL;
-- Step 3: drop the now-redundant CHECK
ALTER TABLE orders DROP CONSTRAINT orders_status_nn;
-- PG < 12: SET NOT NULL always scans the full table.
-- Ensure no NULLs exist first, then accept the brief lock.
```
### Add a Foreign Key
```sql
-- UNSAFE: validates all existing rows while holding a heavy lock
-- UNSAFE on large tables: blocks all writes for the entire build
-- DON'T: CREATE INDEX idx_orders_user ON orders (user_id);
-- SAFE: concurrent index creation (allows reads and writes)
CREATE INDEX CONCURRENTLY idx_orders_user ON orders (user_id);
-- IMPORTANT: if concurrent index creation fails (crashes, deadlock),
-- it leaves an INVALID index behind. Check and clean up:
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
AND indexrelname = 'idx_orders_user';
-- Check for invalid indexes
SELECT indexrelid::regclass AS index_name, indisvalid
FROM pg_index
WHERE NOT indisvalid;
-- Drop and retry if invalid
DROP INDEX CONCURRENTLY idx_orders_user;
CREATE INDEX CONCURRENTLY idx_orders_user ON orders (user_id);
```
### Add a Unique Constraint
```sql
-- A UNIQUE constraint creates an index. Use CONCURRENTLY to avoid blocking:
-- Step 1: create a unique index concurrently
CREATE UNIQUE INDEX CONCURRENTLY idx_orders_tracking_uniq ON orders (tracking_number);
-- Step 2: attach it as a constraint (instant)
ALTER TABLE orders ADD CONSTRAINT orders_tracking_uniq UNIQUE USING INDEX idx_orders_tracking_uniq;
```
### Redefine a Primary Key
Redefining a PK (e.g., switching from `id` to a composite key, or from `int` to `bigint`) requires both a UNIQUE constraint and NOT NULL — both of which can cause long-lasting locks if done naively. The zero-downtime approach builds each ingredient separately:
```sql
-- Step 1: add CHECK NOT NULL constraint without validation (brief lock)
ALTER TABLE orders ADD CONSTRAINT orders_new_id_nn
ALTER TABLE orders VALIDATE CONSTRAINT orders_new_id_nn;
-- Step 3: build unique index concurrently (non-blocking)
CREATE UNIQUE INDEX CONCURRENTLY idx_orders_new_pkey
ON orders (new_id);
-- Step 4: drop the old PK
ALTER TABLE orders DROP CONSTRAINT orders_pkey;
-- Step 5: add new PK using the existing index (instant — also implicitly adds NOT NULL)
ALTER TABLE orders ADD CONSTRAINT orders_pkey
PRIMARY KEY USING INDEX idx_orders_new_pkey;
-- Step 6: drop the now-redundant CHECK constraint
ALTER TABLE orders DROP CONSTRAINT orders_new_id_nn;
```
**Why this works:** Step 5 is fast because Postgres reuses the already-built unique index and recognizes the existing CHECK constraint, skipping both the index build and the full-table NOT NULL scan (PG12+).
### Drop a Constraint
```sql
-- SAFE: instant metadata change
ALTER TABLE orders DROP CONSTRAINT orders_tracking_uniq;
-- If dropping a FK that has a supporting index you no longer need:
ALTER TABLE orders DROP CONSTRAINT fk_user;
DROP INDEX idx_orders_user_id; -- only if no other queries use it
```
## Backfill Strategies
Always backfill in batches — never in a single UPDATE. See [backfill-strategies](references/backfill-strategies.md) for batch-by-PK patterns, resumable progress tracking, and tuning guidance.
## Migration Validation
Run validation queries before and after every migration. See [validation-queries](references/validation-queries.md) for the full set of checks: NULL detection, duplicate detection, orphan rows, cast failures, duration estimation, schema verification, data integrity, and query performance.
## Rollback Planning
Every migration should have a rollback plan documented before execution.
### Reversible Operations
| Operation | Rollback |
|-----------|----------|
| `ADD COLUMN` | `DROP COLUMN` |
| `ADD CONSTRAINT` | `DROP CONSTRAINT` |
| `CREATE INDEX` | `DROP INDEX` |
| `RENAME COLUMN x TO y` | `RENAME COLUMN y TO x` |
**This is where a database fork is invaluable.** If you forked before the migration, the original database has the pre-migration state. If the migration went wrong, your production data is untouched — just delete the fork and start over.
## Transaction Strategy
There are two approaches for executing multiple DDL statements. Each has tradeoffs:
**Wrapped in one transaction** — all changes succeed or all roll back. Use this when atomicity matters more than lock duration, and all operations are fast (milliseconds).
```sql
BEGIN;
ALTER TABLE orders ADD COLUMN priority INTEGER NOT NULL DEFAULT 0;
ALTER TABLE orders ADD COLUMN tags TEXT[] NOT NULL DEFAULT '{}';
CREATE INDEX ON orders USING GIN (tags);
ALTER TABLE orders DROP COLUMN old_priority;
-- Verify before committing
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'orders'
ORDER BY ordinal_position;
COMMIT;
-- Or ROLLBACK; if something looks wrong
```
**Separate transactions** — each DDL runs and commits independently. Use this when lock duration matters more than atomicity. In a single transaction, all locks are held until `COMMIT` — so if you have 5 DDL statements, the `AccessExclusiveLock` from the first one blocks traffic for the entire duration of all 5. Separate transactions release locks between statements.
```sql
-- Each statement auto-commits
ALTER TABLE orders ADD COLUMN priority INTEGER NOT NULL DEFAULT 0;
ALTER TABLE orders ADD COLUMN tags TEXT[] NOT NULL DEFAULT '{}';
ALTER TABLE orders DROP COLUMN old_priority;
```
**The tradeoff:** separate transactions can leave the schema in a partially migrated state if a later statement fails. You'll need a rollback plan for each step individually.
**Cannot use transactions with:**
- `CREATE INDEX CONCURRENTLY` (explicitly disallowed inside a transaction)
- `DROP INDEX CONCURRENTLY`
- Any statement that requires its own transaction context
## Dealing with Long-Running Queries
A fast `ALTER TABLE` can still hang if it's waiting to acquire `AccessExclusiveLock` behind a long-running query. Worse, the waiting DDL blocks all subsequent queries too — even simple SELECTs pile up behind it:
```
Session 1: SELECT COUNT(*) FROM orders; -- long query, holds AccessShareLock
Session 2: ALTER TABLE orders ADD COLUMN ...; -- waits for Session 1 (needs AccessExclusiveLock)
Session 3: SELECT * FROM orders WHERE id = 123; -- BLOCKED by Session 2's lock queue entry
Session 4: INSERT INTO orders (...) VALUES (...); -- also BLOCKED
-- All sessions freeze until Session 1 finishes and Session 2 completes or times out
```
This is why `lock_timeout` is critical — without it, a single slow query can cascade into an application-wide outage.
### Set Timeouts
**`lock_timeout`** — How long to wait for a lock before giving up. Use this on every production DDL statement. Without it, an `ALTER TABLE` can queue behind a long-running query and block all subsequent queries behind it indefinitely.
**`statement_timeout`** — How long the statement can run once it has the lock. This is a safety net against unexpectedly slow operations (e.g., a type change that triggers a table rewrite you didn't anticipate). The tradeoff: if the timeout fires mid-operation, the entire statement rolls back — which is safe for DDL (no partial changes), but means a long `CREATE INDEX CONCURRENTLY` could be killed near completion. For that reason, avoid setting `statement_timeout` on operations you know will be slow (like concurrent index builds on large tables) and instead monitor them manually.
**Choosing timeout values:**
There are two schools of thought:
- **Conservative (50-100ms lock_timeout, hundreds of retries):** Minimizes the window where a waiting DDL blocks other queries. Each attempt is nearly invisible to application traffic, but requires retry logic. Best for high-traffic OLTP systems where even a few seconds of blocked queries is unacceptable.
- **Pragmatic (3-5s lock_timeout, few retries):** Gives the lock a reasonable chance to be acquired on each attempt, reducing the need for complex retry logic. Acceptable for most applications where brief pauses are tolerable.
Pick based on your traffic profile: the higher your query throughput, the shorter your `lock_timeout` should be — because even a brief queue-up affects more queries per second. For `statement_timeout`, set it to a generous multiple of what you expect the operation to take (e.g., 30s for metadata-only changes, minutes for VALIDATE CONSTRAINT on large tables, disabled for CREATE INDEX CONCURRENTLY).
```sql
-- Fail fast instead of blocking all queries behind you
SET lock_timeout = '5s';
SET statement_timeout = '30s';
ALTER TABLE orders ADD COLUMN tracking_number TEXT;
-- If it fails with "canceling statement due to lock timeout":
-- 1. Find what's blocking
SELECT pid, state, query, now() - query_start AS duration
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;
-- 2. Wait for the blocker to finish, or cancel it if appropriate
-- SELECT pg_cancel_backend(<pid>);
-- 3. Retry the ALTER TABLE
SET lock_timeout = '5s';
ALTER TABLE orders ADD COLUMN tracking_number TEXT;
-- Reset timeouts when done
RESET lock_timeout;
RESET statement_timeout;
```
### The Retry-With-Timeout Pattern
For automated migration runners, wrap DDL in a retry loop with a short lock timeout:
```sql
DO $$
DECLARE
max_attempts INTEGER := 5;
attempt INTEGER := 1;
success BOOLEAN := FALSE;
BEGIN
WHILE attempt <= max_attempts AND NOT success LOOP
BEGIN
SET lock_timeout = '3s';
-- Replace with your DDL statement
ALTER TABLE orders ADD COLUMN tracking_number TEXT;
success := TRUE;
RAISE NOTICE 'DDL succeeded on attempt %', attempt;
EXCEPTION
WHEN lock_not_available THEN
RAISE NOTICE 'Attempt % failed (lock not available), retrying...', attempt;
PERFORM pg_sleep(2 * attempt); -- linear backoff
attempt := attempt + 1;
END;
END LOOP;
IF NOT success THEN
RAISE EXCEPTION 'DDL failed after % attempts', max_attempts;
END IF;
END $$;
```
This prevents the migration from creating a pile-up of blocked queries behind it. Each attempt either succeeds quickly or gives up and lets normal traffic flow.
**Alternative: `NOWAIT`** — For the highest-traffic systems, use `LOCK TABLE ... NOWAIT` to test lock availability before running DDL. Unlike `lock_timeout`, `NOWAIT` fails instantly without ever entering the lock queue, so there is zero risk of cascading blocked queries. The tradeoff is more retries:
```sql
BEGIN;
LOCK TABLE orders IN ACCESS EXCLUSIVE MODE NOWAIT;
-- If we get here, we have the lock — run DDL
ALTER TABLE orders ADD COLUMN tracking_number TEXT;
COMMIT;
-- If LOCK fails with "could not obtain lock", retry after a short sleep
```
## Fork-Based Migration Testing
The safest way to test a migration is to run it against a copy of your actual database — same schema, same data, same edge cases. The only two providers that support fast database forking are [Neon](https://neon.tech) and [Ghost](https://ghost.build). Without database forking, you need to manually dump and restore your database, which can take a long time for large datasets.
### With Forking
1. **Fork your database** — create a full copy using your provider's fork feature (takes seconds)
2. **Inspect the current schema** on the fork to confirm it matches production
3. **Run your migration** on the fork
4. **Validate** — run your checks (see Pre/Post-Migration Validation sections above)
5. **If it worked:** apply the same migration to production
6. **If it failed:** delete the fork — your production database is untouched
This catches problems that never show up in empty test databases:
- Data that violates a new constraint
- Type casts that fail on real values
- Migrations that are fast on 100 rows but lock the table for minutes on 10 million
- Index creation that runs out of memory or disk space
**Limitation:** fork-based testing runs your migration in isolation — it won't catch issues caused by concurrent database traffic (e.g., lock contention under load, deadlocks with concurrent writes, or replication lag from heavy WAL generation). For most applications, fork-based testing is sufficient. For very high-uptime applications, use [PgDog](https://pgdog.dev)'s mirroring feature to replay production traffic against the fork — it reproduces queries byte-for-byte with realistic timing, and you can filter to DDL-only or DML-only and control exposure percentage to ramp up gradually.
### Without Forking
Create a test database from a backup or dump:
```bash
# Dump your production database
pg_dump -Fc my_app_db > backup.dump
# Restore into a test database
createdb migration_test
pg_restore -d migration_test backup.dump
# Or clone from a live database (requires downtime on source during copy)
createdb migration_test -T my_app_db
```
## Complete Migration Example
For a full end-to-end walkthrough (plan, fork, run, validate, apply, clean up), see [complete-example](references/complete-example.md).
## Advanced Considerations
**Subtransactions in PL/pgSQL retry loops:** The `BEGIN/EXCEPTION WHEN/END` block in the retry-with-timeout pattern creates implicit subtransactions. Under high write throughput, this can trigger SubtransSLRU contention on replicas — especially if the retry loop runs as a long-lived transaction with many attempts. If you see replica lag during retries, move the retry logic to the application layer (separate transactions per attempt) instead of using PL/pgSQL exception handling.
**Autovacuum can block VALIDATE CONSTRAINT:** `VALIDATE CONSTRAINT` acquires `ShareUpdateExclusiveLock`, which conflicts with autovacuum running in transaction ID wraparound prevention mode. If `VALIDATE` hangs unexpectedly, check `pg_stat_activity` for autovacuum processes on the same table. You may need to wait for wraparound-prevention autovacuum to finish — do not cancel it, as that can lead to data loss if the table approaches the XID wraparound limit.
## Common Pitfalls
1. **Testing migrations on empty tables** — a migration that runs in 1ms on an empty table can lock a 10M-row table for minutes. Always test against realistic data volumes.
2. **Forgetting `CONCURRENTLY` on index creation** — `CREATE INDEX` (without `CONCURRENTLY`) blocks all writes. On a table with active traffic, this causes downtime.
3. **Adding NOT NULL without the two-step pattern** — on large tables in PG < 12, `SET NOT NULL` scans every row while holding `AccessExclusiveLock`. Use the CHECK constraint pattern.
4. **No lock timeout** — a fast ALTER TABLE can block behind a long-running query, and every subsequent query stacks up behind it. Always `SET lock_timeout` for production DDL.
5. **Backfilling in one big transaction** — a single `UPDATE orders SET x = y` on 10M rows generates enormous WAL, bloats the table, and holds locks for the entire duration. Always batch.
6. **Leaving invalid indexes behind** — if `CREATE INDEX CONCURRENTLY` fails, it leaves an invisible invalid index that consumes space and slows writes. Check `pg_index.indisvalid` after every concurrent index operation.
7. **Dropping columns before updating application code** — in a running system, the old code still references the column. Deploy the code change first, then drop the column in a subsequent migration.
8. **Not checking replication lag** — large backfills generate heavy WAL. If you have read replicas, monitor `pg_stat_replication` during and after the migration.
9. **Assuming ALTER COLUMN TYPE is safe** — most type changes rewrite the entire table. Use the add-new-column + backfill + swap pattern for large tables.
