Materialized View
Subject: Computer and Technology noun /məˈtɪr.i.əˌlaɪzd ˈvjuː/ 1 definition
- database-systems
- query-optimization
- data-warehousing
- relational-databases
-
Definition (noun)
Entry #67 · UpdatedA Materialized View is a database object that executes a defined query and persists its resulting rows physically on disk or in memory, rather than recomputing the output dynamically on each request. Unlike a standard virtual view, which merely stores a query specification and evaluates underlying base tables every time it is referenced, a materialized view acts as a cached, queryable table that can even maintain its own indexes to speed up analytical aggregations, complex joins, and recurring read-heavy lookups. Because the stored snapshot does not automatically mirror base-table mutations without synchronization, database systems trade storage capacity and periodic or incremental refresh overhead for dramatically lower read latency and reduced compute load.
Common Types
Full-Refresh (On-Demand) Materialized Views: Recompute the entire query result from base tables and replace the stored snapshot when triggered manually or on a schedule, keeping base-table writes fast while tolerating temporary staleness between updates.
Incremental (Delta-Refresh) Materialized Views: Track base-table changes via materialized view logs or write-ahead log (WAL) streams to merge only newly inserted, updated, or deleted rows, substantially reducing refresh cost on large datasets.
Immediate (Eagerly Maintained) Materialized Views: Update stored view rows synchronously within the same transaction that modifies underlying base tables—as seen in SQL Server indexed views—guaranteeing strong consistency at the expense of higher write latency.
Streaming Materialized Views: Continuously maintain precomputed query state in near real time over unbounded event streams using incremental dataflow operators, eliminating batch refresh windows in streaming databases.
Example
An e-commerce analytics team defines a materialized view in PostgreSQL that joins a 500-million-row
orderstable withproductsand aggregates daily revenue by region, refreshing it every 15 minutes via a scheduledREFRESH MATERIALIZED VIEW CONCURRENTLYjob. Executive dashboards querying that view read pre-aggregated rows in under 20 milliseconds instead of scanning millions of raw transaction records on every page load.