Materialized Views
Summary: A materialized view is a disk-based copy of the results of a database query, used to speed up expensive aggregate calculations in analytical workloads.
Sources: chapter3
Last updated: 2026-04-15
Purpose
Analytical queries often involve aggregate functions (e.g., COUNT, SUM, AVG). If these aggregates are used frequently, it is more efficient to cache them than to recalculate them from raw data every time (source: chapter3).
Characteristics
- Persistence: Unlike a virtual view (which is just a shortcut for writing queries), a materialized view is an actual copy of the data written to disk (source: chapter3).
- Maintenance: When underlying data changes, the materialized view must be updated. This makes writes more expensive, which is why they are less common in oltp systems (source: chapter3).
Data Cubes
A common special case is a data cube (or OLAP cube), which is a multi-dimensional grid of precomputed aggregates (source: chapter3).