REFRESH MATERIALIZED VIEW Lock Modes: CONCURRENTLY Still Blocks Writes

REFRESH MATERIALIZED VIEW CONCURRENTLY takes an EXCLUSIVE lock on the materialized view. That lock allows plain SELECT, but it blocks writes, locking reads, schema changes, and another refresh on the same materialized view.

Plain REFRESH MATERIALIZED VIEW takes ACCESS EXCLUSIVE. It blocks everything, including ordinary reads, for the full refresh.

That is the whole answer. The word CONCURRENTLY protects readers. It does not make the refresh lock-free, and it does not let two refresh jobs overlap.

Quick reference

Statement	Lock mode	Blocks plain SELECT	Blocks writes	Allows another refresh
REFRESH MATERIALIZED VIEW sales_summary	ACCESS EXCLUSIVE	Yes	Yes	No
REFRESH MATERIALIZED VIEW sales_summary WITH NO DATA	ACCESS EXCLUSIVE	Yes	Yes	No
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary	EXCLUSIVE	No	Yes	No

The lock modes come directly from PostgreSQL’s table-level lock documentation. That page lists concurrent refresh under EXCLUSIVE and plain refresh under ACCESS EXCLUSIVE.

What lock does REFRESH MATERIALIZED VIEW CONCURRENTLY take?

PostgreSQL’s lock names are easy to misread. EXCLUSIVE is not the strongest table lock. ACCESS EXCLUSIVE is stronger.

An EXCLUSIVE lock permits only ACCESS SHARE from other transactions. That is the lock a plain SELECT takes. Everything stronger conflicts with it.

Keeps running during REFRESH ... CONCURRENTLY:

• SELECT * FROM sales_summary (ACCESS SHARE)
• queries against unrelated tables

Waits behind the refresh:

• SELECT ... FOR UPDATE or FOR SHARE (ROW SHARE)
• INSERT, UPDATE, DELETE, or MERGE (ROW EXCLUSIVE)
• VACUUM, ANALYZE, or CREATE INDEX CONCURRENTLY on the materialized view (SHARE UPDATE EXCLUSIVE)
• CREATE INDEX without CONCURRENTLY (SHARE)
• schema changes that need ACCESS EXCLUSIVE
• another REFRESH MATERIALIZED VIEW CONCURRENTLY (EXCLUSIVE, which conflicts with itself)

A materialized view is not normally an application write target, so “blocks writes” can sound irrelevant. Operationally, the important cases are locking reads, maintenance commands, DDL, and overlapping scheduled refreshes.

Why overlapping refresh jobs pile up

Imagine sales_summary refreshes every five minutes, but one run starts taking eight minutes after the source tables grow:

REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;

At minute five the next scheduler run starts. It asks for the same self-conflicting EXCLUSIVE lock and waits. At minute ten a third run starts and waits behind the second. The jobs are now serializing faster than they can finish.

PostgreSQL states the same constraint on the REFRESH MATERIALIZED VIEW command page: only one refresh at a time can run against a materialized view, even when CONCURRENTLY is present.

This is not a deadlock. It is an unbounded queue. The immediate safeguards are to prevent overlapping scheduler runs and to set a short lock_timeout before the refresh:

SET lock_timeout = '2s';
SET statement_timeout = '10min';
SET application_name = 'refresh:sales_summary';

REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;

If another refresh already holds the lock, the new run fails quickly instead of sitting in the queue until the earlier job finishes. Your scheduler can then record the skipped run or retry with backoff.

CONCURRENTLY has three prerequisites

PostgreSQL requires all of these:

1. The materialized view is already populated.
2. It has at least one qualifying UNIQUE index that uses only column names and covers all rows.
3. The statement does not use WITH NO DATA.

A partial unique index does not qualify. An expression index does not qualify. The index must identify each materialized-view row across the whole view.

For example:

CREATE MATERIALIZED VIEW sales_summary AS
SELECT shop_id, sale_date, sum(total_cents) AS total_cents
FROM sales
GROUP BY shop_id, sale_date;

CREATE UNIQUE INDEX sales_summary_identity
  ON sales_summary (shop_id, sale_date);

REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;

The unique index is not merely a performance hint. PostgreSQL uses it to match old and new rows while keeping the previous contents readable.

Plain refresh is a different production decision

Without CONCURRENTLY, PostgreSQL takes ACCESS EXCLUSIVE on the materialized view. That is the only table-level lock that blocks a plain SELECT.

SET lock_timeout = '2s';
SET statement_timeout = '10min';

REFRESH MATERIALIZED VIEW sales_summary;

The plain form can use fewer resources and finish faster. That can be a good trade for an offline reporting view or a maintenance window. It is a poor default when an API, dashboard, or background job reads the view during the refresh.

WITH NO DATA still takes ACCESS EXCLUSIVE. It avoids executing the backing query, so the lock can be brief, but it leaves the view unscannable until a later refresh with data. It is not a concurrency substitute.

How to see the lock in production

While a refresh is running, find its backend and inspect the relation lock:

SELECT
  a.pid,
  a.application_name,
  a.state,
  a.wait_event_type,
  a.wait_event,
  l.mode,
  l.granted,
  l.relation::regclass AS relation
FROM pg_stat_activity AS a
JOIN pg_locks AS l ON l.pid = a.pid
WHERE a.query ILIKE 'refresh materialized view%'
ORDER BY a.query_start;

For the concurrent form you should see ExclusiveLock on the materialized view. A queued second refresh shows the same mode with granted = false.

What pgfence reports

pgfence distinguishes both forms instead of treating CONCURRENTLY as a blanket safe keyword:

REFRESH MATERIALIZED VIEW sales_summary;
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;

The first statement is reported as ACCESS EXCLUSIVE, HIGH risk, because it blocks reads and writes for the duration of the refresh. The second is reported as EXCLUSIVE, LOW risk, with the explicit note that reads continue and writes do not.

npx @flvmnt/pgfence analyze migrations/*.sql

That distinction matters in review. The safe question is not “does it say CONCURRENTLY?” It is “which traffic must continue while this refresh runs, and what happens when the next scheduled run overlaps it?”

Review checklist

• Use CONCURRENTLY when readers must keep working.
• Confirm a full-column UNIQUE index exists and is valid.
• Prevent the scheduler from starting a second run while one is active.
• Put lock_timeout before the refresh, not after it.
• Set statement_timeout longer than the expected refresh duration but short enough to stop a runaway query.
• Set application_name so the job is recognizable in pg_stat_activity.
• Use the plain form only when blocking readers is an explicit trade.

For the transaction question, including the misleading cannot run inside a transaction block search result, see REFRESH MATERIALIZED VIEW CONCURRENTLY runs fine inside a transaction block. For the full conflict matrix, see the Postgres lock mode cheat sheet.