A materialized view is a database object that physically stores the results of a query. Unlike a traditional view, which stores a query definition, a materialized view maintains computed data that can be queried directly or used by the optimizer to accelerate compatible queries.

  1. Precomputation and persistent storage
    Materialized views compute and store results from operations such as joins, aggregations, and time-based summaries. Reusing these results avoids repeatedly processing the same underlying data, trading additional storage and maintenance work for faster queries.
  2. Refresh and data freshness
    When source data changes, materialized views need to be updated. Depending on the database and configuration, refreshes may be synchronous, scheduled, or triggered by changes. Refreshes can recompute the full result or update supported portions of the data. The refresh strategy determines data freshness and maintenance overhead.
  3. Automatic query rewrite
    Databases with materialized view rewrite capabilities can identify when a query can use precomputed results. This allows users to query base tables while the optimizer selects an eligible materialized view. Rewrite eligibility depends on query compatibility, supported operations, and freshness requirements.
  4. Typical use cases
    Materialized views are useful for recurring multi-table joins, aggregate reporting, time-series summaries, and frequently accessed dashboards. They are especially valuable when many queries reuse the same expensive calculations.
  5. Materialized views in StarRocks
    The supplied article describes how StarRocks combines distributed execution, columnar storage, and vectorized processing to build and query materialized views. It highlights multi-table materialized views and automatic query rewrite as ways to accelerate analytics and simplify data warehouse modeling.

Materialized views are most effective when query acceleration benefits outweigh storage and refresh costs. Frequently changing query patterns, limited reuse, or expensive refresh workloads can reduce their value.

FAQ

How does a materialized view differ from a traditional view?

A traditional view stores a query definition, while a materialized view physically stores its results. Materialized views can reduce repeated computation but require additional storage and a refresh mechanism.

Do materialized views always contain the latest data?

No. Data freshness depends on the refresh mechanism and configuration. Asynchronously refreshed views may lag behind source tables, so refresh policies should match business freshness requirements.

When should you use a materialized view?

Use one when expensive joins or aggregations run repeatedly and their results can be reused. Evaluate query frequency, latency requirements, acceptable data freshness, storage consumption, and refresh overhead before implementation.