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.

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

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

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: timescale
• Source: timescale/pg-aiguide
• License: Apache-2.0

Install and usage instructions live in the source repository linked above.