Materialized View Refresh Contention
partialSummary
When REFRESH MATERIALIZED VIEW is executed without the CONCURRENTLY option, it acquires an exclusive lock on the view that blocks all concurrent SELECT queries for the full duration of the refresh. For large views that require minutes to recompute, this causes a complete read outage on the view for the refresh window. Scheduled refreshes on high-traffic views produce predictable, repeated outage intervals that are often misattributed to query plan regressions rather than the lock acquisition pattern.
Description
PostgreSQL's REFRESH MATERIALIZED VIEW without CONCURRENTLY acquires an ExclusiveLock on the materialized view relation. This lock conflicts with AccessShareLock (the lock held by SELECT queries), meaning all active SELECT queries on the view must complete and all new SELECT queries must wait until the refresh completes and releases the ExclusiveLock. The lock is held for the entire refresh duration: the time required to execute the view's defining query from scratch plus the time to write the results to storage.
For a materialized view summarizing 100 million rows of time-series data across multiple joins, the refresh query may take 3–8 minutes. Every SELECT query issued against the view during this window queues at the lock. If the application issues 100 SELECT queries/second against this view, and each held SELECT holds its lock for 200ms, then after 10 seconds there are 1,000 queries waiting for the ExclusiveLock. Application connection pools exhaust. User-facing requests time out. The view appears completely unavailable even though the underlying tables are accessible and the refresh is making progress.
REFRESH MATERIALIZED VIEW CONCURRENTLY avoids the exclusive lock by building a new version of the view data in a temporary structure, diffing it against the current view contents, and applying only the changes (INSERT new rows, DELETE removed rows, UPDATE changed rows) to the live view using row-level locking. This approach holds only ShareUpdateExclusiveLock on the view (which does not conflict with SELECT queries), allowing reads to continue during the refresh. However, CONCURRENTLY requires a unique index on the materialized view, takes 3–5x longer than a non-concurrent refresh (because it does the diff computation), and is not suitable if the view definition changes the number of output rows dramatically between refreshes.
The operational hazard is that REFRESH MATERIALIZED VIEW CONCURRENTLY is not the default. DBAs and developers often discover this distinction the first time a large view refresh causes an outage, rather than before deployment. Scheduled jobs and cron tasks that were written with the simpler non-concurrent form and tested on small dev datasets only reveal the blocking behavior in production when view size reaches the minutes-of-refresh threshold.
Characteristics
Triggers
- ·Scheduled REFRESH MATERIALIZED VIEW (without CONCURRENTLY) executes on a view larger than the threshold where refresh duration exceeds request timeout
- ·Materialized view size grows over time until a previously-acceptable non-concurrent refresh duration exceeds tolerance
- ·Manual refresh triggered during high-traffic periods (e.g., triggered by a data pipeline completing during business hours)
- ·Application framework auto-refreshes materialized views on schema migrations without checking view size
Detection Signals
Mitigation Strategies
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.
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.
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.
Recovery Steps
- 1.Identify the blocking refresh via SELECT pid, query, state, wait_event FROM pg_stat_activity WHERE query LIKE '%REFRESH MATERIALIZED VIEW%'
- 2.Terminate the refresh with SELECT pg_terminate_backend(pid): this releases the ExclusiveLock and unblocks all waiting queries
- 3.Verify queued SELECT queries begin executing by monitoring pg_stat_activity wait_event drop to zero
- 4.Assess whether any queued queries timed out at the application layer and require client-side retry
- 5.Schedule the refresh during off-peak hours while implementing CONCURRENTLY as a permanent fix
Estimated recovery time: 30–90 seconds after the refresh query is terminated for the query queue to drain and application latency to normalize. Implementing CONCURRENTLY requires a schema change (unique index) that can be done with CREATE INDEX CONCURRENTLY (no downtime, 5–30 minutes for large views).
Affected Systems
Patterns
Technologies
Basis
PostgreSQL lock behavior for REFRESH MATERIALIZED VIEW is precisely documented in PostgreSQL source code and official documentation; CONCURRENTLY workaround is the official PostgreSQL recommendation; lock contention cascade to connection exhaustion is analytically deterministic