Writing Safe Migrations

Writing Safe Migrations

Overview

Two questions before any migration runs against a live table:

1. Does this rewrite the table? A rewrite holds ACCESS EXCLUSIVE for the whole rewrite — every read and write blocks.
2. Can it wait for its lock without taking the table down? A statement waiting for ACCESS EXCLUSIVE queues ahead of every query that arrives after it. One long-running SELECT plus one unguarded ALTER TABLE stalls the entire table, even for statements the migration itself would never have blocked.

Question 2 causes more outages than question 1, and lock_timeout is the whole fix.

When to Use

• Adding an index to an existing large table.
• ALTER TABLE, adding or dropping columns, changing types, adding constraints.
• Backfilling a column across many rows.
• Any migration on a table with meaningful traffic.

Always set a lock timeout

Never issue DDL against a live table without bounding the wait:

SET lock_timeout = '5s';        -- fail instead of building a lock queue
SET statement_timeout = '0';    -- but let a long index build finish
ALTER TABLE tasks ADD COLUMN priority int;

If the lock isn't acquired in 5s the statement errors — retry it later. That is the correct outcome: a failed migration is recoverable, a stalled table is an incident.

lock_timeout only bounds acquiring a lock, not holding one. It will not save you from a rewrite that takes ten minutes once it starts — check the lock table for that.

Find what's blocking you:

SELECT pid, pg_blocking_pids(pid), wait_event_type, left(query, 80) AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

Quick Reference

Full per-operation lock levels and which operations rewrite: reference/lock-levels.md.

Patterns

-- Index without blocking writes. Cannot run inside a transaction block,
-- and do NOT set statement_timeout here — the build may be long.
SET lock_timeout = '5s';
CREATE INDEX CONCURRENTLY idx_tasks_tenant ON tasks (tenant_id);

Never wrap CREATE INDEX CONCURRENTLY in a transaction block: PostgreSQL rejects it. Run it as a top-level statement. Do not set a short statement_timeout; a healthy build on a large table may take a long time.

A failed CREATE INDEX CONCURRENTLY leaves an invalid index behind. It is not automatically cleaned up, it consumes writes and disk, and the planner won't use it. Always check before retrying:

SELECT c.relname
FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
WHERE NOT i.indisvalid;

DROP INDEX CONCURRENTLY idx_tasks_tenant;  -- then retry the create

-- Constraint without a write-blocking full-table scan
ALTER TABLE tasks ADD CONSTRAINT chk_status
    CHECK (status IN ('pending','running','done')) NOT VALID;

-- Validate separately: SHARE UPDATE EXCLUSIVE, does not block reads or writes
ALTER TABLE tasks VALIDATE CONSTRAINT chk_status;

-- Batched backfill: one transaction per chunk, not one for the whole table
UPDATE tasks SET priority = 0
WHERE id IN (
    SELECT id FROM tasks WHERE priority IS NULL LIMIT 5000
);
-- commit, sleep briefly, repeat until zero rows updated

Large column-type change

Never use one in-place ALTER COLUMN TYPE for a large live table when it rewrites the table. Changes such as integer to bigint rewrite all rows and rebuild dependent indexes while ACCESS EXCLUSIVE is held. Use expand-and-contract across separate deploys:

1. Add the new column with lock_timeout set.
2. Dual-write old and new columns, then backfill bounded key ranges in separate transactions.
3. Build replacement indexes with CREATE INDEX CONCURRENTLY; add replacement constraints with NOT VALID and validate separately where supported.
4. Switch reads, stop writing the old column, verify, and only then remove old objects in a later deploy.

Rules

• Bound every lock. SET lock_timeout before DDL, then retry on failure.
• Additive migrations. Prefer adding over removing. For unavoidable changes use expand-and-contract across separate deploys: add new → backfill → switch reads/writes → drop old.
• Adding a column with a default is cheap in PostgreSQL 11+ — a non-volatile default is stored as metadata and does not rewrite the table. Do not backfill merely to materialize that value. A volatile default (gen_random_uuid(), random()) does rewrite it; add the column nullable and backfill in batches instead.
• Short transactions. Every UPDATE holds row locks until commit, and a long transaction holds back the oldest-transaction horizon so autovacuum cannot reclaim dead tuples (see tuning-autovacuum-and-bloat).
• No external calls mid-transaction. Don't hold a transaction open across an HTTP/RPC round trip — the locks stay held for its full duration.
• Wrap in a transaction where possible so failure rolls back cleanly. CREATE INDEX CONCURRENTLY and DROP INDEX CONCURRENTLY cannot run inside one.

Common Mistakes

• DDL with no lock_timeout — the statement queues for ACCESS EXCLUSIVE and every query behind it queues too, taking the table down without ever acquiring the lock.
• CREATE INDEX without CONCURRENTLY on a big live table — blocks writes for the whole build.
• Retrying a failed CREATE INDEX CONCURRENTLY without dropping the invalid index it left behind.
• Adding a check or FK constraint without NOT VALID — the validation scan blocks writes.
• SET NOT NULL directly on a large table — full scan under ACCESS EXCLUSIVE.
• Backfilling a whole large table in one UPDATE — long transaction, mass dead tuples, autovacuum stalled behind it.
• A transaction that makes an external API call while holding row locks.
• Destructive column drops in the same release as the code change — no clean rollback.