Materialized view

A materialized view is a database object that stores the precomputed result of a query as a physical table. The query behind a materialized view typically involves joins, aggregations, or both — the kinds of queries that are expensive to repeat on every read.

Materialized views are a query optimization technique. It is a structured form of denormalization, in which the database engine itself manages redundant copies. It can also be seem as a form of caching applied to query results.

Materialized views versus views

A regular view is a named query. It stores no data of its own. Each time it is read, the database re-runs the underlying query against the base tables, so the result is always current, but you still pay the full compute cost.

A materialized view goes further by storing the result. The stored data is a snapshot that drifts from the base tables until the view is refreshed. The choice between them is a straight trade-offs between freshness and performance.

Refresh strategies

Because the stored result is a snapshot, it must be refreshed to reflect changes in the base tables. There are several possible refresh strategies.

  • Full refresh. The view is recomputed from scratch and overwritten. Simple and correct, but it’s expensive for large views, and disruptive if it locks the table during rebuild.
  • Incremental refresh. Only the rows affected by changes since the last refresh are recomputed and applied. Far cheaper than a full refresh on large, slowly-changing views, but the engine must be able to compute the delta.
  • On-demand refresh. The view is refreshed when an operator or application triggers it, eg. on a schedule or after a batch load. This gives precise control over when staleness is resolved, at the cost of careful scheduling.
  • Continuous refresh. The view is updated as the base tables change, driven by change data capture or a streaming pipeline. This minimizes staleness but adds the operational cost of the pipeline.

Between refreshes the view is a stale copy. Readers that need the latest value must go to the base tables instead. The staleness window is a form of eventual consistency applied within a single database.

Where they fit

Materialized views suit workloads where reads dominate, the hot queries are known and stable, and a degree of staleness is acceptable.

  • Analytics and reporting. Analytical databases use materialized views to precompute the aggregates and joins that dashboards and reports run repeatedly. Amazon Redshift, for example, can create and refresh them automatically.
  • CQRS read models. In a CQRS architecture the read side is a projection of the write side, and a materialized view is a natural shape for that projection when the write model is a conventional database.
  • Event sourcing projections. Paired with event sourcing, materialized views hold the projected state derived from the event log, so reads do not have to replay the log.
  • Remote and distributed reads. A materialized view can cache the result of a query against a remote or slow source, so local reads avoid repeated network round trips.

Trade-offs

Materialized views duplicate data, so they share the costs of any single source of truth violation. The redundant copy consumes storage, and the refresh path consumes CPU and I/O on the base tables. The more frequently a view is refreshed, the more it costs the write side. The less frequently, the staler the reads. Picking the refresh policy is the central decision, and it is a trade-off rather than a solved problem.

Not every engine supports them in the same way. PostgreSQL requires an explicit REFRESH MATERIALIZED VIEW. Oracle can refresh automatically or on demand. SQL Server exposes them as indexed views, materialized by creating a unique clustered index on a regular view. MySQL has no native materialized view type, so the pattern there is implemented by hand as tables populated by triggers or scheduled jobs.

See also

References