DBRaven
Start Review →
Relationship · Introduces Risk
schema migration lockIntroduces Riskwrite heavy transactional
Source: Failure Mode·Target: Workload

Summary

Schema migrations on write-heavy transactional tables acquire aggressive locks (AccessExclusiveLock) that block all reads and writes. On a high-traffic table receiving 5,000 writes/second, a migration lock that waits even 1 second queues 5,000 transactions behind it, causing a connection pool exhaustion cascade.

Evidence

  • ·ALTER TABLE ... ADD COLUMN NOT NULL with a DEFAULT requires a table rewrite in pre-11 PostgreSQL: blocks entire table
  • ·PostgreSQL 11+ allows adding columns with defaults without table rewrite, but locks still propagate
  • ·A 1-second lock wait on a 5K writes/second table queues 5,000 transactions: exhausting a 100-connection pool in milliseconds
  • ·GitHub's gh-ost and Percona's pt-online-schema-change exist specifically to work around migration locks
  • ·CREATE INDEX (without CONCURRENTLY) holds ShareLock: blocks all writes for the duration

Operational Context

  • ·Use CREATE INDEX CONCURRENTLY: takes longer but only holds a ShareUpdateExclusiveLock (does not block writes)
  • ·Use lock_timeout=100ms to prevent migrations from queuing a backlog of transactions on the locked table
  • ·Validate migrations in staging against production-scale data before deploying : lock duration scales with table size

Tradeoffs

  • ·Online schema change tools (gh-ost, pg_repack) add operational complexity but eliminate downtime risk
  • ·lock_timeout=100ms causes migration to fail rather than block: requires retry logic
  • ·{'Zero-downtime migrations require more code': 'add new column → backfill → add constraint → drop old column'}

Evidence grounding

Grounded, 5 supporting items

Schema migration locking on production OLTP tables is one of the most frequent production incidents documented in PostgreSQL operational guides, GitHub engineering blog, and Braintree/PayPal database teams.