📊Materialized views

Precomputed snapshots

A materialized view persists the result of its defining query, reads are fast, but rows are stale until you refresh. Oracle and PostgreSQL have first-class support; other systems often approximate with indexed views, summary tables, or warehouse ETL.

SQL

PostgreSQL materialized view

CREATE MATERIALIZED VIEW reporting.mv_daily_orders AS
SELECT order_date::date AS d, COUNT(*) AS orders, SUM(total_usd) AS revenue_usd
FROM orders
GROUP BY 1;

REFRESH MATERIALIZED VIEW CONCURRENTLY reporting.mv_daily_orders;

✓Illustrative result set

d	orders	revenue_usd
2026-04-10	1200	184000.12
2026-04-11	980	151200.44

⚡ Advanced / dialect note

Concurrent refresh requires a unique index on the materialized view, plan that at creation time for large aggregates used in dashboards.

Implicit freshness

Serving money-moving workflows from MVs without stating maximum staleness.
Refresh storms that contend with OLTP during peak windows.
Assuming refresh time scales linearly as base tables grow.

Published SLAs and metadata

Expose last_refreshed_at (or equivalent) to every consumer and dashboard.
Backoff and alert on failed refresh jobs; throttle concurrent refreshes.
Re-benchmark refresh after large schema or data-shape changes.

🧱Physical design

Treat big MVs like tables.

Indexes

Add the same kinds of indexes you would on a summary table, especially unique keys when other relations join to the MV.

Partitioning

Partition very large materialized views the same way you would fact tables so maintenance and refreshes stay bounded.

Product contract

Phrase requirements as SLAs-“metrics at most five minutes stale”: and wire monitoring to refresh duration and row counts, not only HTTP 200s from the API reading the MV.

TL;DR

A materialized view stores the result of a query on disk and is refreshed on a schedule or manually, trading freshness for read speed. Unlike a plain view, it consumes storage and must be maintained (concurrent refresh where supported). Use for expensive aggregates dashboards tolerate being slightly stale; document RPO-style freshness and lock impact of refresh jobs.