Blast Radius Analysis · Healthcare Records Platform

Schema Migration Lock

criticalContained

capacity failure · fan out propagation

Blast Radius

0/19 nodes

Time to Detect

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.

Preventive Mitigations

Confidence

Strong

Failure Cascade

(no topology nodes mapped for this failure mode)

This failure mode is referenced in the scenario knowledge but has no connected topology nodes. Impact scope is scenario-level, all components should be considered potentially affected.

Severity at this step: critical

Detection Signals

Disk SaturationError Rate Spike

Recovery time estimate: Seconds once the blocking DDL is terminated. If the DDL was running a table rewrite, terminating it triggers a full rollback of the partial rewrite; on a 500M-row table that rollback alone can take 10 to 30 minutes.

Mitigation Checklist(4 preventive, 2 reactive)

Use pg_repack or pt-online-schema-change for table rewritespreventshigh

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).

Decompose NOT NULL additions into multiple backward-compatible stepspreventsmedium

Step 1: ADD COLUMN new_col TEXT (nullable, no lock impact on existing rows). Step 2: UPDATE table SET new_col = 'default' WHERE new_col IS NULL, in batches of 10,000 rows. Step 3: ALTER TABLE ALTER COLUMN new_col SET NOT NULL (a lock is still taken, but briefly, since every row already has a value). This separates the expensive data migration from the lock-acquiring schema change.

Use CREATE INDEX CONCURRENTLY for new indexes on live tablespreventslow

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.

Add constraints as NOT VALID, then VALIDATE CONSTRAINT separatelypreventslow

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.

Schedule migrations during low-traffic windows with a reduced connection poolmedium

Temporarily reduce the application connection pool size before running the migration, bounding how many queries can queue behind the DDL, and run during the lowest-traffic window available. Still set lock_timeout regardless, to bound the worst case.

Always set lock_timeout before running migrationslow

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.

Affected Systems

Workloads

Event StreamingMixed OLTP (SaaS Core)Write-Heavy Transactional

Blast radius analysis is derived from structured topology and failure mode knowledge. It models structural propagation patterns, not measured production behavior. Actual incident scope depends on runtime conditions, traffic, and recovery actions in place at the time of failure.