Skip to main content
Definition Website Computer and Technology

Materialized View

Subject: Computer and Technology noun /məˈtɪr.i.əˌlaɪzd ˈvjuː/ 1 definition

  1. Definition (noun)

    Entry #67 · Updated

    A 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 orders table with products and aggregates daily revenue by region, refreshing it every 15 minutes via a scheduled REFRESH MATERIALIZED VIEW CONCURRENTLY job. 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.