Materialized View

Materialized View

A materialized view is a database object that stores the results of a query as a physical table. In contrast to a standard (virtual) view that computes results on demand, a materialized view pre-computes and caches its data for faster access. This makes querying large or complex data much faster, because the database can read the precomputed results instead of re-running the query each time. For example, a materialized view might join and aggregate data from sales and product tables into one pre-joined summary table, as illustrated by the materialized view pattern. Because the data is stored, queries against a materialized view return quickly. However, a materialized view must be refreshed periodically to stay up-to-date with its source tables. Databases typically offer full refreshes (recompute the entire view) or incremental refreshes (apply only new changes). In practice, administrators set a schedule or triggers so that the materialized view is rebuilt when needed. This adds some overhead: materialized views consume extra storage (since they duplicate data) and refresh work can use CPU/IO resources. For example, Snowflake warns that materializing intermediate results incurs additional costs, so materialized views are best used when the performance benefit of frequent query reuse outweighs the maintenance cost.

Key Points:

• Precomputation: Materialized views store query results physically, so queries run faster on large or complex data.

• Refresh: Data isn’t live by default. The view must be refreshed on a schedule or by trigger to remain current.

• Use cases: Commonly used for accelerating reports or dashboards (e.g. end-of-day financial summaries). They act like a specialized cache that can be rebuilt from source data.

• Trade-offs: Improves query performance at the cost of additional storage and maintenance. Systems must balance freshness vs. overhead.