postgres-database-migration

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.

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.

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:

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

Irreversible Operations

These require restoring from a backup or the database fork to undo:

• DROP COLUMN — data is gone once VACUUM reclaims it
• ALTER COLUMN TYPE with lossy cast (e.g., NUMERIC → INTEGER, TEXT → VARCHAR(50))
• DELETE / TRUNCATE during data cleanup
• DROP TABLE

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

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.

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

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

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:

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 and Ghost. 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'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:

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

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.