Materialized View

In practice

Refresh strategies, and what each one costs:

Strategy	Freshness	Cost	Where
Full refresh on schedule	Minutes to hours	Recomputes everything	Postgres REFRESH MATERIALIZED VIEW
Concurrent full refresh	Same, no read lock	Needs a unique index, slower	Postgres ... CONCURRENTLY
Incremental	Seconds	Only changed partitions	ClickHouse, Snowflake, dbt incremental models
Continuous / streaming	Sub-second	Constant compute	Materialize, Flink, ksqlDB

A worked case: a dashboard aggregate over 50 million order rows takes 8 seconds live and is loaded 200 times an hour. Materialised and refreshed every 5 minutes, reads drop to 15 ms and the aggregate runs 12 times an hour instead of 200. The product question is whether five-minute-old revenue is acceptable — it usually is, and nobody asks.

When it matters

Dashboards, leaderboards, per-tenant rollups, expensive joins on a read-heavy path, and denormalised read models in CQRS.

Common mistake

A non-concurrent refresh on a view the application reads. REFRESH MATERIALIZED VIEW takes an ACCESS EXCLUSIVE lock — every reader blocks for the duration, so a nightly refresh that grew to 90 seconds becomes a 90-second outage on a page nobody associated with it.