Materialized views

Why it matters

A dashboard query that joins five tables and aggregates a year of orders takes thirty seconds. Run it on every page load and the database dies; run it once a minute and store the result, and every page load reads a small table in milliseconds. That is a materialised view: the database's built-in form of denormalisation, with the refresh handled for you. The idea extends far beyond the database into read models and caches, and the trade is always the same: freshness for speed.

The idea

A view is a saved query that runs each time you read it. A materialised view runs the query once, stores the result as a real table (which can be indexed), and serves reads from that table until the next refresh. Reads are as fast as any table; the data is as stale as the last refresh.

Refresh strategies

Strategy	How	Fits	Cost
Full refresh on a schedule	Re-run the whole query every N minutes and swap the result (Postgres REFRESH MATERIALIZED VIEW CONCURRENTLY)	Reporting, dashboards, anything that tolerates minutes of staleness	Full query cost every refresh; staleness up to N minutes
Incremental refresh	Apply only the changes since last refresh (TimescaleDB continuous aggregates, Oracle fast refresh, some Postgres extensions)	Aggregates over append-heavy data; large views with small deltas	Supported query shapes are limited; engine-specific
Trigger-maintained table	Hand-written triggers or application code update a summary table on each write	Counters and small aggregates needing near-real-time freshness	Write-path cost and the denormalisation discipline from the previous lesson
Event-fed read model	A consumer applies change events to a purpose-built table or store, possibly in a different database	Cross-service views, search indexes, CQRS read sides	Pipeline to run; eventual consistency measured in seconds

Staleness as a contract

A materialised view is correct only if its consumers accept how old it can be. Say it explicitly: "revenue dashboards are at most five minutes behind", "the product search index lags writes by about a second". Expose the refresh time so people stop filing bugs about numbers that are merely a minute old. Where a screen mixes live and materialised data, label or align them, or the inconsistency looks like corruption.

Operating refreshes

Full refreshes should rebuild into a new table and swap atomically so readers never see a half-built view or a lock; Postgres does this with the CONCURRENTLY option over a unique index. Schedule refreshes off peak when they are heavy, and run them on a replica if the source tables are hot. Monitor refresh duration: a refresh that takes longer than its interval is a backlog, exactly like compaction or queue depth. Index the view for the queries that read it, since it is a table.

The same idea at the system level

CQRS splits a system into a write model (normalised, transactional) and read models shaped for each screen, kept in sync by events. Search indexes, feed tables, analytics warehouses and caches are all materialised views of the system of record, each with a refresh mechanism and a staleness contract. Recognising them as one pattern makes the whole architecture easier to reason about: there is one truth, and many derived, rebuildable views.