Skip to content

Materialized View

Precomputed read structure — instantiates Access-Optimized Redundant Representation

Stores the precomputed result of a query as a physical table so an expensive join or aggregation is paid once at refresh time instead of on every read.

A Materialized View is a query whose result is stored as a real, physical table that the database maintains. Where an ordinary (virtual) view re-runs its query on every read, a materialized view runs the query once — at refresh time — and hands every subsequent read the already-computed rows. Its defining move is that the redundant copy is defined entirely by its query: the view's contents are nothing but the derivation applied to the base tables, so the database can rebuild or refresh it on demand from an explicit specification. That makes it the canonical way to move a heavy join or aggregation off the read path, at the price of a copy that is only as current as its last refresh.

Example

A usage-billing dashboard has to show "current-month cost per account," which means aggregating tens of millions of metered events joined against a pricing table. Run live, the query takes ≈20 seconds — unusable on a page people open all day. Defined as a materialized view refreshed hourly, the same question answers in ≈50 ms because the aggregation already sits in a table, indexed by account. In PostgreSQL this is a literal CREATE MATERIALIZED VIEW plus a scheduled REFRESH; the read path now touches one small table instead of the event firehose. The cost moved, it didn't vanish: every refresh re-pays the aggregation, and between refreshes the dashboard is up to an hour stale — which the team makes explicit rather than pretending the number is live.

How it works

  • The query is the specification. The view is declared as a stored query over base tables; its contents are wholly derived, so it can always be rebuilt from that definition.
  • Materialize, then serve. The query runs at refresh time and lands in a physical, indexable table; reads hit that table, never the base data.
  • Refresh on a cadence. Full refresh recomputes everything; incremental refresh applies only what changed since the last run. The cadence sets how stale a read can be.

Tuning parameters

  • Refresh strategy — full recompute vs. incremental maintenance. Incremental is far cheaper on large views but only works when the query and change pattern support it.
  • Refresh cadence — more frequent refresh means fresher reads and higher steady-state cost; the gap between refreshes is the staleness bound.
  • Materialization scope — how much of the query to precompute (the whole result, or just the expensive sub-aggregate). More scope saves more read cost but enlarges the refresh.
  • Concurrent refresh — whether reads see the old copy while a new one builds (no read outage, briefly stale) or block for a swap.

When it helps, and when it misleads

Its strength is turning a repeated expensive query into a cheap lookup with almost no application change — the database owns the copy and its refresh. It misleads when the base data churns fast relative to the read rate: refresh cost then approaches or exceeds the read savings, and you are paying to precompute rows nobody reads before they change again.[1] The other classic trap is treating a materialized view as live — reading an hourly-refreshed figure as if it were current. The discipline is to publish the staleness bound alongside the number and to pick the refresh strategy from the actual read/write ratio, not from whatever makes a demo look fresh.

How it implements the components

Materialized View realizes the representation-and-derivation side of the archetype — it is a redundant copy, defined and refreshed:

  • redundant_representation_specification — the view definition is the spec of the redundant form: exactly what columns and shape the copy holds.
  • derivation_and_duplication_mapping — the defining SQL query is the derivation, stating precisely how the copy is computed from the source.
  • freshness_and_staleness_bound — the refresh cadence sets how far behind the source a read may be.

It does not keep itself current between refreshes (a Scheduled Incremental Refresh or Change-Data-Capture Propagation drives that), detect its own drift (that's Checksum and Sample Reconciliation), or evolve its shape safely (that's Versioned Schema Change).

Notes

Full vs. incremental refresh is the decision that quietly governs cost. On a large view, a full refresh can cost more than all the reads it saves in a cycle — so a view that "works" at small scale can become a net loss as the base data grows, without anything visibly breaking. Size the refresh against the read savings, not just the read latency.

References

[1] Incremental view maintenance — the family of techniques for updating a materialized view from only the base-table changes since its last refresh, rather than recomputing it wholesale. It is what makes frequently-refreshed views affordable, and its absence (or inapplicability to a given query) is the usual reason a view's refresh cost balloons. ↩