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

Operation	Danger	Safe way
CREATE INDEX	Blocks writes for the whole build	CREATE INDEX CONCURRENTLY, outside a transaction
Add check / FK constraint	Validation scan blocks writes	NOT VALID, then VALIDATE CONSTRAINT
SET NOT NULL	Full scan blocks reads and writes	Add an equivalent CHECK (col IS NOT NULL) NOT VALID, validate, then SET NOT NULL
ALTER COLUMN TYPE	Usually a full rewrite	New column + backfill + swap (expand-and-contract)
Backfill in one UPDATE	Long transaction, bloat, blocked autovacuum	Batch in separate transactions
Dropping columns	Hard to roll back	Keep migrations additive; expand-and-contract