Materialized View Refresh Contention
SEV-3, Limited ImpactIsolated propagation · consistency · Affects 0 scenario(s)
Severity Classification
Classified as PARTIAL based on failure mode severity.
Propagation Chain
Origin component
Materialized View Refresh Contention begins at the source component. Trigger: Scheduled REFRESH MATERIALIZED VIEW (without CONCURRENTLY) executes on a view larger than the threshold where refresh duration exceeds request timeout.
Immediate (T+0) · Signal: Latency Spike
Blast Radius
All SELECT queries against the affected materialized view are blocked for the full refresh duration. If the view is used across multiple application features (dashboards, exports, API responses), all those features are simultaneously unavailable. Queries waiting for the lock accumulate in pg_stat_activity, consuming connection pool slots and potentially causing downstream connection pool exhaustion that affects unrelated queries.
Contributing Factors
This operational trigger enables Materialized View Refresh Contention: Scheduled REFRESH MATERIALIZED VIEW (without CONCURRENTLY) executes on a view larger than the threshold where refresh duration exceeds request timeout
This operational trigger enables Materialized View Refresh Contention: Materialized view size grows over time until a previously-acceptable non-concurrent refresh duration exceeds tolerance
This operational trigger enables Materialized View Refresh Contention: Manual refresh triggered during high-traffic periods (e.g., triggered by a data pipeline completing during business hours)
Remediation Plan
Identify the blocking refresh via SELECT pid, query, state, wait_event FROM pg_stat_activity WHERE query LIKE '%REFRESH MATERIALIZED VIEW%'
Effort: Minutes to hours (on-call response)
Terminate the refresh with SELECT pg_terminate_backend(pid): this releases the ExclusiveLock and unblocks all waiting queries
Effort: Minutes to hours (on-call response)
Verify queued SELECT queries begin executing by monitoring pg_stat_activity wait_event drop to zero
Effort: Minutes to hours (on-call response)
Add a unique index to the materialized view and switch all refresh calls to use CONCURRENTLY. The unique index can be on the view's natural key (CREATE UNIQUE INDEX CONCURRENTLY idx_mv_pk ON my_view (id)). After adding the index, replace REFRESH MATERIALIZED VIEW my_view with REFRESH MATERIALIZED VIEW CONCURRENTLY my_view in all scheduled jobs. Test refresh duration in staging: CONCURRENTLY takes 3–5x longer but causes no read outage.
Effort: 1 day to 1 week
Schedule REFRESH (even without CONCURRENTLY) during periods when read traffic on the view is below 5 queries/second. At low read rate, the lock contention impact is minimal (5 queries/second * 200ms = 1 queue depth). Combined with a circuit breaker that aborts scheduled refreshes if current connection pool utilization exceeds 70%, this prevents accidental production impact from scheduled jobs.
Effort: 1 day to 1 week
Configure alerts for: latency spike, connection exhaustion, alert. Set thresholds to fire at 70% of critical level to allow response before full failure.
Effort: 1–3 days
For views that aggregate append-only data (time-series, event streams), replace the materialized view with an incremental aggregation table that is updated by an outbox consumer or CDC stream. The aggregation table is updated with small incremental writes (INSERT or UPDATE on new rows only) rather than a full recompute. No locks are held on the output table beyond individual row writes. Staleness is bounded by the consumer lag rather than the refresh interval.
Effort: 1–4 sprints
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.