Recipe: Postgres lock strategy for migrations

Safe zero-downtime schema changes under heavy write load.

Problem

ALTER TABLE acquires an ACCESS EXCLUSIVE lock, blocking all reads and writes for the duration of the statement. On large tables this causes outages.

Strategy

Set lock_timeout to a short value (e.g. 2s) so the migration fails fast instead of queueing behind long-running transactions.
Split the DDL into multiple steps, each holding the lock for milliseconds.
Use NOT VALID for CHECK constraints and validate later with a non-blocking scan.
Add indexes with CONCURRENTLY.

Example

BEGIN;
SET LOCAL lock_timeout = '2s';

ALTER TABLE orders
  ADD COLUMN IF NOT EXISTS priority INT DEFAULT 0;

COMMIT;

-- Later, in a separate transaction:
ALTER TABLE orders
  ADD CONSTRAINT priority_range
  CHECK (priority BETWEEN 0 AND 10) NOT VALID;

ALTER TABLE orders
  VALIDATE CONSTRAINT priority_range;

Guardrails

Never run migrations inside a long-lived application transaction.
Monitor pg_stat_activity for blocked queries.
Test on a staging replica with production traffic replay first.