Materialized Views in Postgres, in depth: plain vs concurrent refresh, locking, scheduling and incremental alternatives

What a materialized view is in PostgreSQL

Physically, a materialized view is a heap relation, stored like a table, plus the defining query, stored the way a view's query is. You can create indexes on it, ANALYZE it and VACUUM it, and the planner treats it as a table. You cannot INSERT, UPDATE or DELETE rows in it; the only way its contents change is REFRESH MATERIALIZED VIEW.

PostgreSQL never refreshes it automatically, and the planner never rewrites a query against base tables to use it. Queries must name the materialized view explicitly. It also has a populated flag. Created WITH NO DATA, it exists but cannot be queried until the first refresh; pg_matviews.ispopulated shows the state. Finally, an ORDER BY in the defining query is not guaranteed to survive a refresh, so sort when you read, not when you define.

Worked example: daily revenue per store

A shop has 50 million rows in sales.orders and a dashboard that shows revenue per store per day. The aggregate query scans the whole table and takes about 40 seconds, and 30 people open the dashboard every morning. The result has about 400 stores times 730 days, roughly 292,000 rows, which an index lookup reads in milliseconds.

CREATE MATERIALIZED VIEW reporting.daily_store_revenue AS
SELECT o.store_id,
       date_trunc('day', o.created_at)::date AS day,
       count(*)                              AS orders,
       sum(o.total_cents)                    AS revenue_cents
FROM   sales.orders o
WHERE  o.status = 'paid'
GROUP  BY 1, 2
WITH NO DATA;                                  -- create the shape, populate later

-- A unique index on plain columns, covering every row: required for CONCURRENTLY
CREATE UNIQUE INDEX daily_store_revenue_pk
    ON reporting.daily_store_revenue (store_id, day);

REFRESH MATERIALIZED VIEW reporting.daily_store_revenue;   -- first fill: plain
ANALYZE reporting.daily_store_revenue;

SELECT matviewname, ispopulated FROM pg_matviews WHERE schemaname = 'reporting';

The unique index on (store_id, day) does two jobs: it serves the dashboard's lookups, and it is the prerequisite for concurrent refresh. The explicit ANALYZE after the first fill gives the planner statistics immediately instead of waiting for autovacuum.

Plain REFRESH: fast, clean, and blocking

A plain REFRESH MATERIALIZED VIEW runs the defining query into a brand-new heap, rebuilds the indexes on it, and swaps the new storage in when it commits. It holds an ACCESS EXCLUSIVE lock throughout, which the documentation describes as locking out concurrent selects. Every dashboard query waits for the whole 40-second query plus the index builds.

The benefits are real. The work is one sequential write with no comparison, so for views where many rows change it is the fastest option. The new heap has no dead tuples, so there is nothing for vacuum to clean up. WITH NO DATA empties the view and marks it unpopulated, which is a quick way to free its space.

The trap is the lock queue. An ACCESS EXCLUSIVE request waits behind any running query on the view, and every query that arrives after it waits behind the request. One slow report holding the view for five minutes followed by a scheduled refresh means every dashboard query stalls for five minutes plus the refresh time. Always set lock_timeout on refresh sessions so a refresh that cannot get its lock gives up and retries later instead of blocking everyone.

REFRESH CONCURRENTLY: readers keep reading

REFRESH MATERIALIZED VIEW CONCURRENTLY runs the defining query into a temporary table, compares it with the current contents, and applies the difference as deletes and inserts in the existing heap. It takes an EXCLUSIVE lock, which blocks other refreshes and writes but allows SELECT, so readers see the old contents until the refresh commits and the new contents after.

The requirements, from the documentation: at least one UNIQUE index on the materialized view that uses only column names (no expressions) and covers all rows (no WHERE clause); the view must already be populated; and CONCURRENTLY cannot be combined with WITH NO DATA. Only one refresh runs against a view at a time either way.

The costs are equally concrete. The full query still runs, then a comparison join over old and new rows, then row-level changes. The documentation notes that concurrent refresh may be faster when few rows change and that plain refresh uses fewer resources when many do. Every updated row leaves a dead tuple, so a frequently refreshed view needs autovacuum to keep up; see vacuum and bloat for how to tune that per table. And the diff runs on every column, which produces a classic mistake: adding now() AS refreshed_at to the defining query changes every row on every refresh, turning a small diff into a full delete and insert of the whole view. Record refresh times in a separate log table instead.

Scheduling refreshes that cannot pile up

PostgreSQL has no built-in refresh schedule, so something external must call it: pg_cron, an orchestrator, or an application job. Whatever calls it, three guards belong in the refresh itself.

CREATE TABLE reporting.refresh_log (
  view_name text, started_at timestamptz, finished_at timestamptz);

-- One refresh at a time, never queued behind itself, never waiting forever for a lock
CREATE OR REPLACE PROCEDURE reporting.refresh_daily_store_revenue()
LANGUAGE plpgsql AS $$
DECLARE t0 timestamptz := clock_timestamp();
BEGIN
  IF NOT pg_try_advisory_xact_lock(hashtext('refresh:daily_store_revenue')) THEN
    RAISE NOTICE 'refresh already running, skipping';
    RETURN;
  END IF;
  SET LOCAL lock_timeout = '5s';
  SET LOCAL statement_timeout = '15min';
  REFRESH MATERIALIZED VIEW CONCURRENTLY reporting.daily_store_revenue;
  INSERT INTO reporting.refresh_log(view_name, started_at, finished_at)
  VALUES ('daily_store_revenue', t0, clock_timestamp());
END $$;

-- with the pg_cron extension installed
SELECT cron.schedule('refresh-daily-store-revenue', '*/15 * * * *',
                     $$CALL reporting.refresh_daily_store_revenue()$$);

The advisory lock skips a run if the previous one is still going, rather than queueing a second refresh behind the first. lock_timeout bounds the wait for the view's lock. statement_timeout bounds the refresh itself, so a plan change that makes the query slow fails loudly instead of running forever. The log table gives you staleness: the dashboard can show 'data as of 09:15', and an alert can fire when the newest entry is older than two intervals.

When materialized views depend on other materialized views, refresh them in dependency order in the same job; refreshing a view does not refresh the views it reads from. Pick the interval from how fresh readers actually need the data and how long the refresh takes. If the refresh takes 12 minutes, a 15-minute schedule leaves almost no margin once the data grows.

The swap pattern for large rebuilds

When a view is very large, changes almost completely on each refresh, and must stay readable, neither refresh mode fits: plain blocks readers for the whole rebuild and concurrent does a full diff for nothing. Build a new materialized view under a temporary name with its indexes, then in one short transaction drop the old one and rename the new one. Readers are blocked only for the rename.

The catch is dependencies. Views and other objects that reference the materialized view point at the object, not the name, so dropping the old one fails while dependents exist, and DROP ... CASCADE removes them. Keep dependents as simple views you recreate in the same transaction, or give readers a stable view that you redefine to point at the new materialized view.

Permissions, search_path and indexes

Refreshing requires the MAINTAIN privilege on the materialized view, which PostgreSQL 17 introduced along with the pg_maintain role. Grant it to the refresh job's role instead of running refreshes as the owner or a superuser.

From PostgreSQL 17, during a refresh search_path is temporarily set to pg_catalog, pg_temp. A defining query that calls your own functions without schema qualification, or functions that themselves rely on the caller's search path, can fail during refresh even though the original CREATE worked. Schema-qualify references and give such functions an explicit SET search_path clause.

Index the materialized view for its readers, exactly as you would a table; the PostgreSQL indexes guide covers the choices. Every index is rebuilt by plain refresh and maintained row by row by concurrent refresh, so each one adds refresh time.

When you need incremental maintenance

Core PostgreSQL has no incremental refresh: both modes re-run the entire defining query. When the base table is large and only recent data changes, two alternatives are common.

-- Hand-rolled incremental maintenance: a plain table, re-aggregating only recent days
CREATE TABLE reporting.daily_store_revenue_t (
  store_id bigint, day date, orders bigint, revenue_cents bigint,
  PRIMARY KEY (store_id, day));

INSERT INTO reporting.daily_store_revenue_t AS t
SELECT store_id, date_trunc('day', created_at)::date, count(*), sum(total_cents)
FROM   sales.orders
WHERE  status = 'paid' AND created_at >= current_date - 2      -- late-arriving window
GROUP  BY 1, 2
ON CONFLICT (store_id, day) DO UPDATE
SET orders = EXCLUDED.orders, revenue_cents = EXCLUDED.revenue_cents;

-- Or let an extension maintain it with triggers (pg_ivm); its README asks for
-- pg_ivm in shared_preload_libraries or session_preload_libraries first
CREATE EXTENSION pg_ivm;
SELECT pgivm.create_immv('store_revenue_ivm',
  'SELECT store_id, count(*) AS orders, sum(total_cents) AS revenue_cents
     FROM sales.orders GROUP BY store_id');

The first is a plain summary table maintained by your own job, re-aggregating only a recent window and upserting the results. It is fully under your control, can use normal writes and partial indexes, and needs you to define correctly which window can still change; late-arriving or corrected orders outside it will be missed, so pair it with a periodic full rebuild.

The second is the pg_ivm extension, which supports PostgreSQL 13 through 18. pgivm.create_immv creates an incrementally maintainable view that AFTER triggers on the base tables update inside the same transaction as each write, so it is never stale. The price is paid by writers: every insert into sales.orders now also updates the view. Supported queries include inner and outer joins, DISTINCT, and the count, sum, avg, min and max aggregates; window functions, HAVING, ORDER BY, LIMIT and set operations are not. It is an extension, so check that your managed provider offers it. If the real need is feeding a separate analytics store, logical replication and a store built for OLAP workloads may be the better tool.

Failure modes and trade-offs

• Lock-queue outage. A plain refresh waits behind a long read and every new read waits behind it. Use lock_timeout or concurrent refresh.
• Silent staleness. The refresh job has been failing for a week and nobody knew. Log every refresh and alert on age.
• Concurrent refresh errors on duplicates. If the new result contains identical duplicate rows, the refresh fails; make the defining query produce a genuine key.
• Bloat. Frequent concurrent refreshes that change many rows produce dead tuples faster than default autovacuum removes them.
• Volatile columns. now() or random() in the query makes every row differ on every refresh.
• search_path surprises. Unqualified function calls fail only at refresh time.

The overall trade-off is freshness against cost. A materialized view gives cheap reads by accepting staleness and paying the full query cost on each refresh. Plain refresh minimises that cost and blocks readers; concurrent refresh keeps readers and adds diff work and vacuum load; incremental approaches remove staleness and move the cost onto writers or onto your own code.

What to do next

1. List your materialized views with pg_matviews and note for each how it is refreshed, how often, and how long the refresh takes.
2. Add a unique index on plain columns to every view that is read during business hours, and switch those to concurrent refresh.
3. Wrap refreshes in a procedure with an advisory lock, lock_timeout, statement_timeout and a log insert, and alert when the newest log entry is too old.
4. Remove volatile columns from defining queries and schema-qualify every function reference.
5. Grant MAINTAIN to the refresh role on PostgreSQL 17 or later instead of refreshing as owner.
6. For views over large, append-mostly tables, prototype a windowed summary table or pg_ivm and compare write overhead against refresh cost.