Learning center

When a PostgreSQL materialized view refresh blocks readers

A plain REFRESH MATERIALIZED VIEW takes an ACCESS EXCLUSIVE lock, so every query on the view waits until the refresh finishes. REFRESH ... CONCURRENTLY lets reads continue, but only on a populated view with a unique index that has no WHERE clause. A migration that creates or replaces a materialized view decides which of the two the refresh job can use.

A dashboard reads a daily revenue view#

A reporting dashboard reads daily totals from a materialized view, and a job refreshes it every few minutes. We ran this on PostgreSQL 16.15.

CREATE TABLE sales (id bigint PRIMARY KEY, day date NOT NULL, amount numeric NOT NULL);
INSERT INTO sales
SELECT g, date '2026-01-01' + (g % 300), g % 97
FROM generate_series(1, 3000000) g;

CREATE MATERIALIZED VIEW daily_revenue AS
SELECT day, sum(amount) AS revenue FROM sales GROUP BY day;

Start a refresh in one session, and 100 ms later read one day in another:

-- session 1
REFRESH MATERIALIZED VIEW daily_revenue;               -- Time: 198.721 ms

-- session 2
SELECT revenue FROM daily_revenue WHERE day = '2026-03-01';   -- Time: 89.648 ms

The single-row read waited for the rest of the refresh. Inside a transaction, the refresh holds these locks on the view:

mode
ShareLock
ExclusiveLock
AccessExclusiveLock

ACCESS EXCLUSIVE conflicts with the ACCESS SHARE lock every SELECT needs. At this size the wait is a tenth of a second. It grows with the query behind the view, and a refresh that takes a minute stops the dashboard for a minute, every time the job runs.

Refresh concurrently#

CONCURRENTLY builds the new result separately and applies the differences, so readers keep seeing the old rows meanwhile. It refuses to run without a suitable unique index:

REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;
ERROR:  cannot refresh materialized view "public.daily_revenue" concurrently
HINT:  Create a unique index with no WHERE clause on one or more columns of the materialized view.

With the index in place, the same experiment gives:

CREATE UNIQUE INDEX daily_revenue_day ON daily_revenue (day);

-- session 1
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;  -- Time: 166.780 ms

-- session 2
SELECT revenue FROM daily_revenue WHERE day = '2026-03-01';   -- Time: 0.668 ms

The concurrent refresh takes an EXCLUSIVE lock instead, which blocks other refreshes and writes but not reads. It also does more work than a plain refresh when much of the view changes, as the REFRESH MATERIALIZED VIEW documentation notes.

A replaced view starts unpopulated#

Migrations that change a view's query usually drop and recreate it. Created WITH NO DATA to keep the migration fast, the new view cannot be read, and it cannot be refreshed concurrently either:

CREATE MATERIALIZED VIEW weekly_revenue AS
SELECT date_trunc('week', day) AS week, sum(amount) FROM sales GROUP BY 1
WITH NO DATA;
CREATE UNIQUE INDEX ON weekly_revenue (week);

SELECT * FROM weekly_revenue LIMIT 1;
-- ERROR:  materialized view "weekly_revenue" has not been populated
-- HINT:  Use the REFRESH MATERIALIZED VIEW command.

REFRESH MATERIALIZED VIEW CONCURRENTLY weekly_revenue;
-- ERROR:  CONCURRENTLY cannot be used when the materialized view is not populated

Until a plain refresh runs, every reader of the view fails, and a refresh job configured for CONCURRENTLY fails too. Created WITH DATA, the default, the migration runs the full query itself, and drop and create hold their locks in the migration's transaction. Either way, the replacement interrupts readers. Build the new view under a new name, populate it, add its unique index, and switch readers in an application release, or swap names in one short transaction.

What to rehearse#

Time a plain refresh and a concurrent refresh on a copy with production data, while another session runs the dashboard's queries, and record both durations in the pull request. Check that every materialized view the refresh job touches has a unique index without a WHERE clause. When the migration recreates a view, run it in staging while the refresh job and the readers are active, and confirm that neither fails.

Find the other changes that need a rehearsal in which PostgreSQL migration needs a rehearsal.

One month of free code review.

New organizations get one calendar month of hosted code review for private and public GitHub repositories. No card or cloud connection required. Eligibility and fair-use terms.

Start free with GitHub

Related reading