Schema Migration Lock
SEV-2, Significant ImpactFan-Out propagation · capacity · Affects 3 scenario(s)
Severity Classification
Classified as CRITICAL based on failure mode severity. The fan out propagation pattern increases risk of broad impact beyond the initial failure point. This failure mode appears in 3 known architecture scenarios, indicating widespread relevance.
Propagation Chain
Origin component
Schema Migration Lock begins at the source component. Trigger: ALTER TABLE on a table with over 1M rows in production without an online schema change tool.
Immediate (T+0) · Signal: Disk Saturation
Downstream dependents
Failure propagates to directly dependent components via synchronous calls or shared resources. Latency increases and error rates rise on affected dependencies.
Detectable within seconds via pg_locks monitoring. Application impact (latency, errors) becomes visible within 30 to 120 seconds depending on connection pool size. If lock_timeout is unset (effectively 0, meaning wait forever), the DDL blocks indefinitely. · Signal: Latency spike, connection timeout, or error rate increase on dependents
Downstream of dependents (fan-out)
Failure spreads to multiple downstream systems simultaneously. Retry storms may amplify load on the failing component.
Within minutes of initial failure · Signal: Multiple services reporting elevated error rates
Blast Radius
All reads and writes to the affected table are blocked for the lock wait plus DDL execution time. Tables with foreign key relationships can also be affected if the DDL triggers a lock on the referenced table. Connection pool exhaustion is likely within 1 to 3 minutes as application threads queue on the blocked table. If multiple services share the database, all are affected simultaneously.
Contributing Factors
This operational trigger enables Schema Migration Lock: ALTER TABLE on a table with over 1M rows in production without an online schema change tool
This operational trigger enables Schema Migration Lock: Adding a NOT NULL constraint on a large table without splitting into: ADD COLUMN nullable, UPDATE, ADD CONSTRAINT NOT NULL
This operational trigger enables Schema Migration Lock: CREATE INDEX without CONCURRENTLY on a large table
Remediation Plan
Check whether the migration is queued (waiting) or running: SELECT pid, query, wait_event FROM pg_stat_activity WHERE state = 'active'
Effort: Minutes to hours (on-call response)
If the migration is queued and has not started, terminate it immediately with pg_cancel_backend(pid) to unblock the queue
Effort: Minutes to hours (on-call response)
If the migration is running (a table rewrite), assess whether to kill it (a partial rollback) or let it finish
Effort: Minutes to hours (on-call response)
SET lock_timeout = '5s' before any DDL statement. If the lock cannot be acquired within 5 seconds, the statement aborts with an error instead of queuing and blocking every subsequent query. Retry during a lower-traffic window or add statement-level retry logic.
Effort: 1 day to 1 week
CREATE INDEX CONCURRENTLY does not take ACCESS EXCLUSIVE, letting reads and writes continue throughout, at the cost of 2 to 10x the build time. It cannot run inside a transaction block, so migration tooling that wraps migrations transactionally by default needs that disabled for this statement specifically. If it fails mid-build, an invalid index is left behind and must be cleaned up with DROP INDEX CONCURRENTLY.
Effort: 1 day to 1 week
ALTER TABLE ... ADD CONSTRAINT ... CHECK (...) NOT VALID takes ACCESS EXCLUSIVE only briefly, to record the constraint without checking existing rows. A following ALTER TABLE ... VALIDATE CONSTRAINT does the expensive existing-row scan under SHARE UPDATE EXCLUSIVE instead, which does not block ordinary reads and writes. New rows are checked against the constraint immediately; only the historical-row check is deferred and de-locked.
Effort: 1 day to 1 week
Configure alerts for: disk saturation, error rate spike. Set thresholds to fire at 70% of critical level to allow response before full failure.
Effort: 1–3 days
pg_repack performs a table rewrite online without ACCESS EXCLUSIVE for most of the operation: it builds a new table, copies data in the background, and does a fast swap at the end. The swap itself briefly takes ACCESS EXCLUSIVE, typically under a second. Suited to long-running migrations (column type changes, adding a column with a default on pre-PG11 versions).
Effort: 1–4 sprints
Schema Migration Lock affects 3 architecture scenarios (Developer Tools Platform, Financial Ledger Platform, Healthcare Records Platform). Design a shared mitigation strategy or a platform-level safeguard that prevents this failure mode from manifesting across all affected services.
Effort: 1–3 months
This post-mortem framework is derived from structured architecture knowledge. It provides an evidence-grounded starting point, not a substitute for a live incident review conducted by the team closest to the system. Adjust remediation priorities based on actual runtime observations.