Migration Safety Rules

Migration deploy jobs must fail fast on lock contention and never block application traffic unbounded. Encode timeouts and phased DDL in team rules.

Recipe

-- First lines of every production migration file
SET lock_timeout = '5s';
SET statement_timeout = '0';        -- allow long CONCURRENTLY
SET idle_in_transaction_session_timeout = '60s';

# Deploy job wrapper
flyway migrate || { echo "migration failed - app NOT rolled forward"; exit 1; }

When to reach for this: CI template for SQL migrations, incident retro after lock pile-up, Flyway/Liquibase standardization.

Working Example

SET lock_timeout = '3s';
 
BEGIN;
CREATE TABLE app.new_feature (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL
);
COMMIT;

-- Separate file: executeInTransaction=false (Flyway)
SET lock_timeout = '10s';
SET statement_timeout = '0';
 
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_new_feature_name
  ON app.new_feature (name);

Deploy pipeline rules:

1. Migration failure blocks app deploy (same pipeline stage or earlier)
2. Alert on lock timeout in migration logs
3. Invalid index check post-deploy:

SELECT indexrelid::regclass
FROM pg_index
WHERE NOT indisvalid;

Deep Dive

Timeout Matrix

Lock-Aware DDL Order

1. ADD COLUMN nullable (fast)
2. Backfill (chunked, separate job)
3. CREATE INDEX CONCURRENTLY
4. ADD CONSTRAINT NOT VALID
5. VALIDATE CONSTRAINT off-peak
6. DROP COLUMN only after app contract phase

Deploy Job Rules

• Single migrator connection (not parallel Flyway on same DB)
• No migration during overlapping maintenance that also restarts Postgres
• Schema diff smoke test after migrate
• Roll forward documented; no history table surgery

Gotchas

• Zero lock_timeout - Migration waits behind weekend analytics query for hours. Fix: Always set explicit lock_timeout.
• Same statement_timeout for all SQL - CONCURRENTLY killed at 30s on huge table. Fix: Split files; disable txn for long runners.
• Migration success + invalid index - App queries ignore broken index, seq scan storm. Fix: Post-deploy validity query in CI.
• Deploy app before migration - Old app hits missing column. Fix: Migrate-first or expand-compatible order.
• Running migrations from laptop - Bypasses CI grants check. Fix: Pipeline-only prod migrate.

Alternatives

FAQs

Related

• Zero-Downtime DDL - phased DDL
• Migrations Best Practices - full list
• Rollback Strategy - forward-only ADR

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.