What Is a Materialized View?

Quick answer
A view is a stored query that runs when you select from it; a materialized view stores the rows it produced, so reading it is as fast as reading a table.
The cost moves from read time to refresh time: the stored rows go stale until you refresh them.
PostgreSQL can refresh without blocking readers using REFRESH MATERIALIZED VIEW CONCURRENTLY, which requires a unique index on the result.
Use one for an expensive aggregate that is read far more often than the underlying data changes, such as a dashboard summary.
Do not use one as a substitute for indexing the query, and do not expect it to update itself — something has to schedule the refresh.

What is a materialized view?

It is a query whose result has been computed and stored, so subsequent reads fetch the stored rows instead of recomputing. A regular view is the opposite: it stores only the query text and runs it every time you select from it, which keeps the result current and does nothing for performance if the query is expensive. The materialized version is a cache with a name, and it inherits every property of a cache, including staleness.

The definition of the query is retained, which is what allows the rows to be recomputed. That recomputation is the refresh, and it is either full — discard and rebuild, which blocks readers — or, where the engine supports it, incremental or non-blocking. The choice between those two modes determines whether the refresh itself becomes the thing that hurts.

How refresh works, and what it costs

A full refresh recomputes the whole result and replaces the stored rows. It is simple and it blocks readers for the duration, so the cost scales with the size of the result and the complexity of the query. For a dashboard summary over a large table, that can be seconds to minutes, which is why a naive refresh on a schedule can be visible to users as an intermittent stall.

PostgreSQL’s CONCURRENTLY option avoids the block by computing a new result and updating the stored one in place, but it requires a unique index and is more expensive overall because it must diff rather than replace. Where the engine supports incremental maintenance — refreshing only the partitions that changed — the cost can be much lower, though support and constraints vary. Whichever mode you use, the refresh has to be triggered by something: a schedule, a trigger, or an application event. Nothing about the view updates itself.

-- The expensive query becomes the stored definition.
CREATE MATERIALIZED VIEW order_totals AS
SELECT customer_id,
       count(*)        AS order_count,
       sum(total)      AS lifetime_value,
       max(created_at) AS last_order_at
FROM orders
GROUP BY customer_id;

-- A unique index is required for a non-blocking refresh.
CREATE UNIQUE INDEX ON order_totals (customer_id);

-- Reads are now table-speed, but the rows are as old as the last refresh.
SELECT * FROM order_totals WHERE customer_id = 42;

-- Refresh without blocking readers.
REFRESH MATERIALIZED VIEW CONCURRENTLY order_totals;

When a materialized view is the right tool

Three conditions together. The query is expensive — a multi-table aggregate, not a lookup. It is read far more often than the underlying data changes, so the staleness window rarely matters. And the answer does not need to be current to the second, which is what makes a cache acceptable in the first place. A dashboard refreshed every five minutes fits all three; a balance shown at checkout fits none.

The alternative worth checking first is an index. If the read is slow because a large table is scanned, an index may remove the problem entirely without introducing staleness, and it stays current automatically. A materialized view is for when the expense is aggregation across many rows, which no index removes. Reaching for the view before considering the index is a common way to add a maintenance burden that was not needed.

The operational costs nobody budgets for

A materialized view is a table with a cron job. It needs storage, it needs a refresh schedule, it needs monitoring so you notice when refreshes fail or take progressively longer, and it needs a documented staleness bound that whoever reads it can rely on. It also changes the failure mode of the read: instead of slow, the data can be silently old, which is a harder class of bug because nothing errors.

Two disciplines keep it manageable. Expose the freshness — a refreshed_at column or a value in the response — so consumers know what they are getting. And alert on refresh duration, because a refresh that slowly drifts past its interval produces a view that is always stale without ever failing. Both are cheap, and both are the difference between a useful cache and a mystery.

Where this bites vibecoders
Generated code rarely reaches for a materialized view, but it does reach for recomputing an expensive aggregate on every page load, which is what the view would fix. The trade to raise explicitly is freshness: a stored summary is faster and older, and somebody has to decide how old is acceptable and who refreshes it. An assistant will implement the query; the schedule and the staleness bound are the parts it will not volunteer.

Where AI coding assistants get this wrong

Treating a materialized view as self-updating, so nothing refreshes it.
Refreshing fully on a schedule that blocks readers at busy times.
Adding a materialized view where an index would have made the query fast and fresh.
Omitting a unique index, which makes non-blocking refresh impossible.
Never exposing a freshness timestamp, so consumers cannot tell how old it is.
Assuming the stored result is consistent with the source tables at read time.

Checklist

Confirm the query is expensive because of aggregation, not a missing index.
Decide the maximum acceptable staleness and write it down.
Create a unique index so a non-blocking refresh is possible.
Schedule the refresh away from peak load, or use concurrent refresh.
Expose a refreshed-at value to consumers.
Alert on refresh failures and on refresh duration growing over time.

FAQ

What is the difference between a view and a materialized view?

A plain view is a stored query: selecting from it runs the query and always returns current data. A materialized view stores the computed rows, so reads are fast and the data is as old as the last refresh. The plain view costs read time and promises freshness; the materialized one trades freshness for speed.

Does a materialized view update automatically?

No. Something must run the refresh — a scheduled job, a trigger after a relevant write, or an application event. This is the most common surprise: the view is created, the data changes, and the view silently stays frozen. Pair it with a schedule and monitoring from the moment you create it.

How often should I refresh?

As often as the business needs and as rarely as the cost allows. Work backwards from the staleness consumers can tolerate, then measure the refresh duration at that frequency and confirm it fits in the window available. If the refresh cannot keep pace with the interval, the view is not viable at that frequency and needs incremental maintenance or a different design.

Why does concurrent refresh require a unique index?

Because it updates rows in place rather than replacing the whole relation, so it needs a stable key to identify which stored row corresponds to which new row. Without a unique index the engine cannot match them and refuses the operation. It is a small requirement with a large effect on whether refresh blocks readers.