A materialized view stores the result of a defining query so later reads can access those stored rows. It moves computation from every read into refresh operations, which can benefit repeated reporting queries. The tradeoff is an explicit freshness boundary and another relation to maintain; neither instant reads nor automatic synchronization with the source tables is guaranteed.

Stored results have their own lifecycle

A normal view stores a query definition, while a materialized view also stores its result. PostgreSQL can index the materialized result and plan reads against it without rerunning the original joins and aggregates. Ordinary source-table inserts and updates do not automatically update that stored result. Applications must query the materialized view intentionally; creating one does not transparently rewrite every equivalent source query.

REFRESH MATERIALIZED VIEW reruns the defining query and replaces the stored contents. Standard PostgreSQL refresh is not automatic incremental maintenance: even when only a few source rows changed, the defining query still has to produce the new result. Account for refresh computation, temporary work, storage, and index maintenance when comparing the total cost with repeated direct queries.

The stored rows can still be large or poorly indexed, so materialization does not make arbitrary reads free. Choose the aggregation grain and indexes according to the report’s filters and projections. Also use an ORDER BY in the reading query when ordering matters; refresh does not promise to preserve the physical order from the defining query.

Concurrent refresh changes reader access

An ordinary refresh can block readers while replacing the materialization. CONCURRENTLY permits concurrent selects to continue accessing the existing contents during refresh, with the new contents becoming visible according to transaction and snapshot rules. It still takes locks, uses resources, and can wait for other operations. Only one refresh at a time may run against a given materialized view.

Concurrent refresh requires an already populated materialized view and at least one eligible unique index using only column names and covering all rows. A partial unique index or expression index does not satisfy that requirement. The unique index lets PostgreSQL identify result rows during refresh; it is not a declaration that the source tables themselves have become immutable or unique at the same grain.

WITH NO DATA leaves a materialized view unpopulated and unscannable. It does not mean an ordinary empty result that SELECT can read, and it does not preserve the old cached answer. Populate it with a normal refresh before requesting a concurrent one. CONCURRENTLY and WITH NO DATA cannot be combined into a shortcut around these conditions.

When it breaks

A refresh schedule alone does not bound staleness. A job can start late, take a long time, fail, or wait behind another refresh; the source snapshot also precedes completion of the work. Track successful refreshes and the source coverage that the report needs, rather than assuming that a configured interval proves current data. Failed jobs must not advance a displayed freshness marker as if new results were published.

Missing or unsuitable unique indexes can make CONCURRENTLY fail when deployment expected an uninterrupted read path. Test that path before relying on it. In PostgreSQL 18, refresh also requires the applicable MAINTAIN privilege, so the role executing the scheduled job must actually be able to refresh the relation. Successful manual execution as its owner does not establish the job role’s permissions.

Check it quickly

Save this as materialized.sql and run psql -X -v ON_ERROR_STOP=1 -f materialized.sql in a disposable PostgreSQL 18 database. The role needs permission to create the test schema. The script creates matview_demo inside a transaction and rolls it back; if that name already exists, choose an unused name rather than deleting an existing schema.

\set ON_ERROR_STOP on
BEGIN;
CREATE SCHEMA matview_demo;
SET LOCAL search_path=matview_demo,pg_catalog;
CREATE TABLE sales (region text NOT NULL,amount integer NOT NULL);
INSERT INTO sales VALUES ('east',10),('west',5);
CREATE MATERIALIZED VIEW totals AS SELECT region,sum(amount) AS total FROM sales GROUP BY region;
UPDATE sales SET amount=17 WHERE region='east';
SELECT * FROM totals ORDER BY region;
DO $$ BEGIN
  REFRESH MATERIALIZED VIEW CONCURRENTLY totals;
  RAISE EXCEPTION 'concurrent refresh accepted without unique index';
EXCEPTION WHEN object_not_in_prerequisite_state THEN
  RAISE NOTICE 'concurrent refresh requires an eligible unique index';
END $$;
CREATE UNIQUE INDEX totals_region ON totals (region);
REFRESH MATERIALIZED VIEW CONCURRENTLY totals;
SELECT * FROM totals ORDER BY region;
REFRESH MATERIALIZED VIEW totals WITH NO DATA;
DO $$ BEGIN
  PERFORM * FROM totals;
  RAISE EXCEPTION 'unpopulated view was readable';
EXCEPTION WHEN object_not_in_prerequisite_state THEN RAISE NOTICE 'unpopulated view cannot be read';
END $$;
DO $$ BEGIN
  REFRESH MATERIALIZED VIEW CONCURRENTLY totals;
  RAISE EXCEPTION 'concurrent refresh populated an unreadable view';
EXCEPTION WHEN feature_not_supported THEN RAISE NOTICE 'first population needs a normal refresh';
END $$;
REFRESH MATERIALIZED VIEW totals;
DO $$ BEGIN
  IF (SELECT total FROM totals WHERE region='east') IS DISTINCT FROM 17::bigint OR
     (SELECT count(*) FROM totals) <> 2 THEN RAISE EXCEPTION 'refresh result wrong'; END IF;
END $$;
ROLLBACK;

PostgreSQL 18.6 continued showing east total 10 after the source changed to 17. Concurrent refresh without an eligible unique index failed with the expected prerequisite error. After creating the index, refresh changed the stored east total to 17 while west remained five.

WITH NO DATA then made reading fail, and a concurrent refresh could not repopulate the unscannable view. A normal refresh restored readable results and satisfied the final assertions. This fixture verifies state transitions and prerequisites, not refresh throughput. For operational sizing, measure the actual report query and test reader behavior during a representative refresh, including the effect of transaction boundaries and failed jobs.

Sources