How to Fix PostgreSQL Materialized View Refresh Locking: CONCURRENTLY vs Blocking Refresh Explained

Threat/Impact Level: HIGH | Exploitability/Downtime Risk: HIGH | Time to Fix: 5 mins

TL;DR

• What broke: REFRESH MATERIALIZED VIEW without CONCURRENTLY acquires an AccessExclusiveLock, making the view completely unreadable and unwritable for the entire refresh duration.
• How to fix it: Add the CONCURRENTLY keyword and ensure a unique index exists on the materialized view.
• Action: Use our Client-Side Sandbox below to auto-refactor this — paste your refresh statement and get the corrected SQL with index scaffolding instantly.

---

The Incident (What Does the Error Mean?)

There is no Postgres error message thrown to the application. That's what makes this insidious. The query executes "successfully" while silently locking out every other session. What you see in pg_stat_activity and pg_locks is this:

SELECT pid, state, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE wait_event = 'relation' AND state = 'active';

-- Result: dozens of sessions in 'Lock' wait, query = 'SELECT * FROM reporting.daily_summary'

SELECT relation::regclass, mode, granted
FROM pg_locks
WHERE NOT granted;

-- Result: AccessExclusiveLock on reporting.daily_summary | granted = false

Immediate consequence: Every SELECT, INSERT, UPDATE against that materialized view queues behind the exclusive lock. If the refresh takes 90 seconds on a large dataset, you have a 90-second full read blackout on that view. Connection pool exhaustion follows within seconds on high-traffic systems.

---

The Attack Vector / Blast Radius

REFRESH MATERIALIZED VIEW (non-concurrent) takes AccessExclusiveLock — the most restrictive lock level in Postgres. It conflicts with every other lock mode, including plain AccessShareLock from a SELECT.

Cascading failure chain:

1. Refresh job fires (cron, pg_cron, application scheduler).
2. AccessExclusiveLock granted on the materialized view relation.
3. All incoming queries against the view queue. They do not fail — they wait.
4. Your connection pool (PgBouncer, RDS Proxy, application pool) fills with waiting connections.
5. New application requests cannot acquire a connection → HTTP 500s or timeouts surface to end users.
6. If the refresh is triggered repeatedly (e.g., every 60s cron with a 90s runtime), locks stack. The previous refresh never releases before the next one attempts acquisition.
7. In the worst case: deadlock or full Postgres connection exhaustion requiring pg_terminate_backend.

Blast radius is proportional to view size and query concurrency. A 10M-row materialized view refreshing every 5 minutes on a reporting dashboard with 200 concurrent users is a guaranteed production incident.

---

How to Fix It (The Solution)

Basic Fix

Add CONCURRENTLY. That's it. CONCURRENTLY uses a two-phase refresh internally — it builds the new data in a temp structure, then swaps using row-level locks only, never blocking reads.

- REFRESH MATERIALIZED VIEW reporting.daily_summary;
+ REFRESH MATERIALIZED VIEW CONCURRENTLY reporting.daily_summary;

Hard requirement: The materialized view must have at least one unique index. Without it, Postgres refuses the concurrent refresh:

ERROR: cannot refresh materialized view "reporting.daily_summary" concurrently
DETAIL: One or more rows in the materialized view do not have a unique index.

Create the index first:

CREATE UNIQUE INDEX CONCURRENTLY idx_daily_summary_id
  ON reporting.daily_summary (summary_id);

Enterprise Best Practice

For production systems, wrap the refresh in a function with lock timeout protection and pg_cron scheduling. Never fire blind refreshes from application code.

-- BAD: application-triggered, no timeout, no concurrency guard
- REFRESH MATERIALIZED VIEW reporting.daily_summary;

-- GOOD: pg_cron managed, lock timeout, concurrent, idempotent
+ DO $$
+ BEGIN
+   SET LOCAL lock_timeout = '5s';  -- fail fast instead of queuing
+   REFRESH MATERIALIZED VIEW CONCURRENTLY reporting.daily_summary;
+ EXCEPTION
+   WHEN lock_not_available THEN
+     RAISE WARNING 'Skipping refresh: lock not available, will retry next cycle';
+ END;
+ $$;

Schedule with pg_cron (not external cron):

SELECT cron.schedule(
  'refresh-daily-summary',
  '*/5 * * * *',  -- every 5 minutes
  $$DO $inner$
  BEGIN
    SET LOCAL lock_timeout = '5s';
    REFRESH MATERIALIZED VIEW CONCURRENTLY reporting.daily_summary;
  EXCEPTION WHEN lock_not_available THEN
    RAISE WARNING 'refresh skipped: lock contention';
  END;
  $inner$$$
);

Why lock_timeout? Without it, a hung or slow refresh attempt will queue indefinitely, blocking all subsequent queries. lock_timeout = '5s' means the refresh aborts rather than stacking locks.

---

💡 Tired of pasting proprietary configs into ChatGPT? Generic AI tools log your company's ARNs, DB strings, and private keys. StackEngine is a zero-backend, pure Client-Side WASM utility. Drop your failing config into the sandbox above. We redact your secrets locally in the browser and auto-generate the refactored code using your own API key.

---

Prevention in CI/CD

1. Lint SQL migrations with squawk

Squawk is a PostgreSQL migration linter. Add it to your CI pipeline:

# .github/workflows/db-lint.yml
- name: Lint SQL migrations
  run: |
    squawk migrations/*.sql

Squawk flags non-concurrent materialized view refreshes and missing unique indexes out of the box.

2. Enforce with a pre-commit grep hook (zero-dependency)

# .git/hooks/pre-commit or pre-commit config
- repo: local
  hooks:
    - id: no-blocking-matview-refresh
      name: Block non-concurrent materialized view refresh
      language: pygrep
      entry: 'REFRESH\s+MATERIALIZED\s+VIEW\s+(?!CONCURRENTLY)'
      args: [--multiline, --ignore-case]
      files: \.sql$

3. OPA/Conftest policy for Terraform-managed DB migrations

If you manage schema via Terraform (postgresql provider or Flyway/Liquibase via null_resource):

# policies/matview_refresh.rego
package database.migrations

deny[msg] {
  input.sql_statement
  regex.match(`(?i)REFRESH\s+MATERIALIZED\s+VIEW\s+(?!CONCURRENTLY)`, input.sql_statement)
  msg := "Non-concurrent materialized view refresh detected. Use REFRESH MATERIALIZED VIEW CONCURRENTLY to avoid AccessExclusiveLock."
}

4. Monitor for lock waits in production (catch it before users do)

-- Add this to your Datadog/Grafana custom query monitor
SELECT count(*)
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
  AND wait_event = 'relation'
  AND state = 'active';
-- Alert threshold: > 5 waiting sessions