Materialized View

A materialized view stores the result of a query physically, trading freshness and write cost for fast reads of expensive aggregations or joins.

databases

Definition

A materialized view persists the result of a query as a table-like object. Reads query the stored result instead of recomputing the join or aggregate. The result becomes stale when the underlying data changes; it must be refreshed on a schedule, on demand, or incrementally. The refresh strategy is the central design decision.

Context and scope

Materialized views are used for dashboards, reporting, search projections, and expensive joins that are read far more often than the base data changes. They are also useful when a read replica cannot keep up with a complex query.

Common misconceptions

A materialized view is always up to date — it is a snapshot until refreshed
Refreshing is free — a full refresh rewrites the result and can lock or load the database
Materialized views replace caching — they are persistent database objects with their own storage, refresh, and indexing costs

Anti-patterns

Refreshing a large materialized view on every write
Building a dashboard on a view that refreshes once a day without showing the data timestamp
Forgetting indexes on the materialized view's query columns

Related concepts

Related examples

Authoritative source

https://www.postgresql.org/docs/current/rules-materializedviews.html

Store the expensive result

A dashboard query may join five tables and aggregate millions of rows. Running it for every page load is wasteful. A materialized view computes the result once and stores it. Reads become simple scans of the stored rows.

CREATE MATERIALIZED VIEW daily_revenue AS
SELECT date_trunc('day', created_at) AS day,
       sum(total_cents) AS revenue,
       count(*) AS orders
FROM orders
GROUP BY 1;

CREATE UNIQUE INDEX daily_revenue_day_idx ON daily_revenue (day);

REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;

The unique index is required for CONCURRENTLY, which avoids blocking reads during refresh at the cost of more work. A plain refresh takes an exclusive lock and blocks reads. Choose based on the read availability requirement and the size of the result.

Freshness is a product decision

Every materialized view has a staleness window. Show the data timestamp in the UI and define how stale the view may be. A daily report can be a day old; a fraud dashboard cannot. For near-real-time needs, use incremental materialization, change data capture, or a streaming aggregation instead of a full refresh.

Refresh cost grows with the result size and the query complexity. Schedule refreshes outside peak traffic, monitor duration and lock waits, and avoid refreshing more often than the underlying data changes. A view refreshed every minute from a table that changes hourly is wasted work.

Index the result

A materialized view is a physical table. It needs indexes for the queries that read it. Add indexes on the columns used in filters and joins, and analyze the view after refresh so the planner has statistics. A materialized view without statistics can produce worse plans than the original query.

When to use it

Use a materialized view when a complex read is frequent, the base data changes less often, and a bounded staleness window is acceptable. The advantage is fast, predictable reads; the drawback is storage, refresh cost, and stale data. Compared to an application cache, it lives in the database, can be indexed and joined, and is refreshed by the database rather than by application invalidation logic.