---

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, 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

Every schema change acquires a lock. The critical question is: does it block reads and writes, and for how long?

Fast, Non-Blocking Operations

These complete in milliseconds regardless of table size. They only hold a brief AccessExclusiveLock for the catalog update, not for data rewriting.

| Operation | Lock Level | Notes | |-----------|-----------|-------| | ADD COLUMN (nullable, no default) | AccessExclusiveLock (brief) | Fast. No table rewrite. Metadata-only change. | | ADD COLUMN ... DEFAULT x (PG 11+) | AccessExclusiveLock (brief) | Fast. Non-volatile defaults stored in catalog, not backfilled. | | 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. | | RENAME COLUMN | AccessExclusiveLock (brief) | Metadata change only. Fast. | | RENAME TABLE | AccessExclusiveLock (brief) | Metadata change only. Fast. | | ADD CONSTRAINT ... NOT VALID | ShareUpdateExclusiveLock | Adds constraint for new rows only. Does not scan existing data. | | VALIDATE CONSTRAINT | ShareUpdateExclusiveLock | Scans existing rows but allows concurrent reads and writes. | | CREATE/DROP TRIGGER | ShareRowExclusiveLock | Brief catalog update. |

Slow or Blocking Operations

These rewrite the table or scan all rows. On large tables, they can lock out all access for seconds to hours.

| Operation | Lock Level | Why It's Slow | |-----------|-----------|---------------| | ADD COLUMN ... DEFAULT x (volatile, e.g. now(), gen_random_uuid()) | AccessExclusiveLock | Full table rewrite. Every row gets the computed value. | | ALTER COLUMN TYPE (most type changes) | AccessExclusiveLock | Full table rewrite to convert stored data. | | SET NOT NULL (PG < 12, or without existing CHECK) | AccessExclusiveLock | Full table scan to verify no NULLs. See safe pattern below. | | ADD CONSTRAINT ... CHECK/UNIQUE/FK (validated) | AccessExclusiveLock or ShareRowExclusiveLock | Scans all rows to verify, blocks writes. | | CREATE INDEX (without CONCURRENTLY) | ShareLock | Blocks writes for the entire build duration. | | CLUSTER | AccessExclusiveLock | Rewrites entire table in index order. | | VACUUM FULL | AccessExclusiveLock | Rewrites table to reclaim space. |

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

-- 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
-- DON'T: ALTER TABLE orders ADD COLUMN created_at TIMESTAMPTZ DEFAULT now();
-- 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

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

-- 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:

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

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

-- UNSAFE: validates all existing rows while holding a heavy lock
-- DON'T: ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);

-- SAFE: two-step approach
-- Step 1: add without validation (blocks writes briefly, doesn't scan data)
ALTER TABLE orders ADD CONSTRAINT fk_user
    FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;

-- Step 2: validate existing rows (allows concurrent reads and writes)
ALTER TABLE orders VALIDATE CONSTRAINT fk_user;

Add an Index

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

-- 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:

-- Step 1: add CHECK NOT NULL constraint without validation (brief lock)
ALTER TABLE orders ADD CONSTRAINT orders_new_id_nn
    CHECK (new_id IS NOT NULL) NOT VALID;

-- Step 2: validate existing rows (allows concurrent reads and writes)
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

-- 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 for batch-by-PK patterns, resumable progress tracking, and tuning guidance.

Migration Validation

Run validation queries before and after every migration. See validation-queries for the full set of checks: NULL detection, duplicate detection, orphan