Materialized Views and Refresh Strategies in PostgreSQL

A materialized view is a view that stores its result. Where a plain view recomputes its query on every read, a materialized view runs the query once, saves the rows to disk, and serves them instantly until you refresh it. That's a powerful trade for expensive queries you read often and that don't need to be up to the second: dashboards, reports, leaderboards, rollups. The whole skill is deciding what to materialize and how to keep it fresh without disrupting readers.

This is part 29 of the PostgreSQL Masterclass, following views.

Creating and refreshing

You define a materialized view like a view, but the result is stored:

CREATE MATERIALIZED VIEW customer_lifetime_value AS
SELECT
  c.id AS customer_id,
  c.name,
  count(o.id)  AS order_count,
  sum(o.total) AS lifetime_value
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

Now querying it is a plain table read, no aggregation at query time, however expensive the underlying query was. The catch is that it's a snapshot: it reflects the data as of the last refresh and doesn't update as the base tables change. You refresh it explicitly:

REFRESH MATERIALIZED VIEW customer_lifetime_value;

That recomputes the whole query and replaces the stored rows. It's the price of the speed: you decide when the data updates, and between refreshes it's stale by design.

Refresh without locking readers

Plain REFRESH takes an exclusive lock, so queries against the materialized view block until the refresh finishes. On a dashboard people are actively viewing, that's a visible stall. The concurrent form avoids it:

-- required once: a unique index on the materialized view
CREATE UNIQUE INDEX clv_customer_id_idx
  ON customer_lifetime_value (customer_id);

-- refreshes without blocking reads
REFRESH MATERIALIZED VIEW CONCURRENTLY customer_lifetime_value;

REFRESH ... CONCURRENTLY computes the new data and updates the view in place without blocking readers, so queries keep working throughout. It requires a unique index on the view, and it's slower than a plain refresh because it diffs old against new, but on anything user-facing it's the right choice. Always index your materialized views anyway, since you query them like tables and want the same index benefits.

Deciding what to materialize

A materialized view is worth it when three things are true: the query is expensive, you read the result far more often than the data changes, and slightly stale data is acceptable. Good fits:

• Dashboards and reports aggregating large tables, viewed constantly, where "as of a few minutes ago" is fine.
• Leaderboards and rankings that would be costly to compute live and don't need per-second accuracy.
• Rollups and summaries, like daily revenue by region, that many queries read.

Bad fits are the mirror image: data that must be current to the second (a bank balance), or a view read so rarely that a plain view's recompute cost is cheaper than maintaining a stored copy.

Refresh strategies

The question that decides whether a materialized view is a help or a landmine is how you refresh it. A few approaches, from simplest up:

Scheduled refresh. Run REFRESH ... CONCURRENTLY on a schedule with pg_cron or an external scheduler: every 5 minutes, hourly, nightly, matched to how stale you can tolerate. This is the common default and it's simple to reason about.

-- with pg_cron: refresh every 10 minutes
SELECT cron.schedule('refresh-clv', '*/10 * * * *',
  'REFRESH MATERIALIZED VIEW CONCURRENTLY customer_lifetime_value');

Event-driven refresh. Trigger a refresh after a batch job that changes the underlying data, so the view updates exactly when its inputs do rather than on a fixed clock. Fits pipelines with clear "data changed" moments.

Incremental rollups instead. For very large or frequently-changing data, refreshing the whole materialized view repeatedly gets expensive, because each refresh recomputes everything. At that scale, a summary table you update incrementally (add today's numbers rather than recompute all history) often beats a materialized view. Postgres has no built-in incremental materialized view refresh, so incremental maintenance is something you build with triggers or scheduled jobs against a regular table.

The staleness contract

The one thing to be deliberate about is that a materialized view is always stale between refreshes, and everyone reading it needs to know that. Put the refresh interval where it's visible: label the dashboard "updated every 10 minutes," or expose the last refresh time. A surprising number of "the numbers are wrong" bug reports are really "the numbers are 10 minutes old and nobody said so." Track the last refresh explicitly if it matters:

-- store refresh times so consumers can display "as of ..."
SELECT max(refreshed_at) FROM materialized_view_log
WHERE view_name = 'customer_lifetime_value';

Materialized views turn an expensive query you run constantly into a cheap table read, at the cost of freshness you control. Materialize the heavy, frequently-read, staleness-tolerant queries; refresh concurrently on a schedule that matches your tolerance; and reach for an incrementally-maintained summary table once the full refresh gets too expensive. Used with those rules, they're one of the simplest big performance wins Postgres offers.

Next, we look at triggers and stored procedures: running logic inside the database, and the strong opinions about when that's smart versus when it hides behavior you'll regret.