PostgreSQL materialized views: refresh and concurrency

A PostgreSQL materialized view stores the result of a query as a database relation. Reading it uses those stored rows. Changes to the source tables become visible in the materialized view after a successful refresh, so the design needs an explicit decision about acceptable staleness.

This guide builds a small regional order summary and follows it through inserts, updates, deletes, ordinary refresh, and concurrent refresh. We ran 34 checks on isolated PostgreSQL 18.6, including separate database connections for reader-lock behavior and a role with the MAINTAIN privilege.

The SQL outputs were captured with psql in unaligned, tuples-only mode. Columns are separated by |, and booleans appear as t or f. Run the examples in order in a disposable database; the role example requires an administrator able to create roles and grant privileges.

Create and read a stored summary

CREATE SCHEMA mv_lab;
CREATE TABLE mv_lab.orders (
    id integer PRIMARY KEY,
    region text NOT NULL,
    amount numeric NOT NULL
);
INSERT INTO mv_lab.orders VALUES
    (1, 'east', 10), (2, 'east', 20), (3, 'west', 7);
CREATE MATERIALIZED VIEW mv_lab.region_totals AS
SELECT region, count(*) AS order_count, sum(amount) AS total
FROM mv_lab.orders
GROUP BY region;
SELECT region, order_count, total
FROM mv_lab.region_totals ORDER BY region;

east|2|30
west|1|7

The east group contains two orders totaling 30, and west contains one totaling 7. Creating the materialized view without WITH NO DATA runs the defining query and populates the stored result. Each later SELECT above reads the materialized relation.

The PostgreSQL materialized-view overview explains how the stored query supports later refreshes. We also checked the catalog: the relation had materialized-view type and was marked populated.

Keep three related concepts separate. A regular view evaluates its query when queried. A materialized view persists a result between statements. A CTE written with WITH ... AS MATERIALIZED concerns execution within one statement; it does not create a persistent refreshable object. The CTE documentation covers that statement-level behavior.

Observe stale rows after source changes

INSERT INTO mv_lab.orders VALUES (4, 'east', 5), (5, 'north', 12);
SELECT 'stored' AS source, region, order_count, total
FROM mv_lab.region_totals
UNION ALL
SELECT 'base' AS source, region, count(*), sum(amount)
FROM mv_lab.orders GROUP BY region
ORDER BY source, region;

base|east|3|35
base|north|1|12
base|west|1|7
stored|east|2|30
stored|west|1|7

The base query now includes north and a third east order. The stored summary still has only east and west, with east totaling 30. The total across stored groups remains 37, while the source amounts total 54.

This is useful behavior when the application can read a prepared result, but the gap must be intentional. A dashboard that requires the latest committed orders needs a different freshness arrangement from a report that can accept a scheduled snapshot.

Refresh the result explicitly

REFRESH MATERIALIZED VIEW mv_lab.region_totals;
SELECT region, order_count, total
FROM mv_lab.region_totals ORDER BY region;

east|3|35
north|1|12
west|1|7

The stored result now includes north, and east has three orders totaling 35. The original source rows remain in the source table. Refresh replaces the materialized view’s contents from its defining query; this workflow does not configure automatic or incremental maintenance.

We separately tried a direct UPDATE against the materialized view and PostgreSQL rejected it. Change the source data and refresh the derived result. If a consumer needs a stable display order, put ORDER BY on its SELECT, as every displayed read does here.

Meet the requirements for concurrent refresh

CREATE UNIQUE INDEX region_totals_region_key
ON mv_lab.region_totals (region);
UPDATE mv_lab.orders SET amount = 9 WHERE id = 3;
DELETE FROM mv_lab.orders WHERE region = 'north';
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_lab.region_totals;
SELECT region, order_count, total
FROM mv_lab.region_totals ORDER BY region;

east|3|35
west|1|9

This sequence creates a unique index on the region column, changes west’s amount to 9, removes north’s source row, and refreshes concurrently. The result has west at 9 and no north group. We checked equality with the base aggregate in both directions, so the comparison would catch missing as well as extra rows.

The REFRESH MATERIALIZED VIEW reference specifies the prerequisites: the view must already be populated, and a qualifying unique index must use column names and include all rows. An expression index or a partial index does not meet that requirement.

Our negative tests confirmed all three index cases. Concurrent refresh failed without a qualifying index, with only a unique index on lower(region), and with only a partial unique index. The partial index failed even though every row in this fixture satisfied its predicate. The ordinary column-based unique index allowed refresh to proceed.

Choose columns that identify a row in the result. In this summary, GROUP BY creates one row per non-NULL region. A different summary needs an index that matches its actual result structure; adding an arbitrary unique index is not a substitute for defining that structure.

Understand what concurrent refresh changes for readers

We tested two connections against the same view. One connection completed a refresh inside a transaction and deliberately kept that transaction open for eight seconds. While its relation lock was still held, another connection attempted a SELECT with a 500-millisecond lock timeout.

Refresh in the held transaction	Observed relation lock	Separate reader
Ordinary	AccessExclusiveLock	Reached the lock timeout
CONCURRENTLY	ExclusiveLock	Returned the stored summary

A second concurrent refresh also reached the lock timeout while the first concurrent refresh transaction remained open. The reader’s successful SELECT therefore does not mean refresh operations can overlap freely on the same materialized view.

This exercise isolates lock compatibility and transaction lifetime. It is not a measurement of refresh duration or production throughput. The held transactions were rolled back after the checks. Keep refresh transactions short and monitor failures rather than assuming the CONCURRENTLY keyword removes every possible wait.

Populate a WITH NO DATA view before concurrent refresh

CREATE MATERIALIZED VIEW mv_lab.delayed_totals AS
SELECT region, sum(amount) AS total
FROM mv_lab.orders GROUP BY region
WITH NO DATA;
CREATE UNIQUE INDEX delayed_totals_region_key
ON mv_lab.delayed_totals (region);
SELECT matviewname, ispopulated FROM pg_matviews
WHERE schemaname = 'mv_lab' AND matviewname = 'delayed_totals';
REFRESH MATERIALIZED VIEW mv_lab.delayed_totals;
SELECT matviewname, ispopulated FROM pg_matviews
WHERE schemaname = 'mv_lab' AND matviewname = 'delayed_totals';

delayed_totals|f
delayed_totals|t

The first catalog read reports false, and the ordinary refresh changes it to true. WITH NO DATA creates an unpopulated view that cannot be scanned until it has been populated. The CREATE MATERIALIZED VIEW reference describes this option.

Our extra checks returned the view to an unpopulated state with REFRESH ... WITH NO DATA. A SELECT then failed, as did concurrent refresh despite the existing unique index. An ordinary refresh restored population, after which concurrent refresh succeeded. We also checked that CONCURRENTLY cannot be combined with WITH NO DATA.

Use this sequence when you want to define the object before running its query: create it without data, create the required index, populate it with an ordinary refresh, and then use concurrent refresh when appropriate. Plan the initial population separately from later refreshes.

Grant a refresh role the required privilege

CREATE ROLE mv_refresh NOLOGIN;
GRANT USAGE ON SCHEMA mv_lab TO mv_refresh;
GRANT MAINTAIN ON TABLE mv_lab.region_totals TO mv_refresh;
SET ROLE mv_refresh;
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_lab.region_totals;
RESET ROLE;
SELECT region, order_count, total
FROM mv_lab.region_totals ORDER BY region;

east|3|35
west|1|9

In PostgreSQL 18, MAINTAIN on the materialized view permits refresh. The test role also receives USAGE on the schema so it can refer to the object. NOLOGIN makes it an authorization role for this exercise; the administrator switches to it with SET ROLE.

A separate role without MAINTAIN was denied refresh in our checks. The authorized refresh role completed the operation without a direct SELECT grant on the orders table, and a direct SELECT from that table was denied. The materialized view remained owned by the administrator in this isolated fixture.

These results describe these grants, this owner, and PostgreSQL 18. They do not configure application authentication or establish permissions for every function a defining query might call. Assign the production refresh identity and ownership deliberately, and verify the complete query under that arrangement.

Decide whether the stored result is worth maintaining

Measure both sides of the tradeoff: the queries served from the materialized view and the work required to refresh it. This tiny fixture establishes correctness, not a speedup. Use our PostgreSQL EXPLAIN ANALYZE guide to inspect real query plans, remembering that EXPLAIN ANALYZE executes the statement being analyzed. The pg_stat_statements guide helps identify repeated queries worth investigating.

Before scheduling refreshes, define the maximum acceptable data age, the refresh owner, a failure alert, and what readers should do if the last refresh is too old. Record successful refresh completion separately from source-data timestamps. A populated flag says the object can be read; it does not certify that its contents meet a freshness target.

Remove the exercise objects

REVOKE MAINTAIN ON TABLE mv_lab.region_totals FROM mv_refresh;
REVOKE USAGE ON SCHEMA mv_lab FROM mv_refresh;
DROP ROLE mv_refresh;
DROP MATERIALIZED VIEW mv_lab.delayed_totals;
DROP MATERIALIZED VIEW mv_lab.region_totals;
DROP TABLE mv_lab.orders;
DROP SCHEMA mv_lab;

The checks confirmed that refresh left the four remaining source orders intact, totaling 44. Cleanup removed the test role, materialized views, table, and schema, then stopped the isolated server. The downloadable checks include the two-session experiment and the expected error cases.