Materialized view

A materialized view is a database object that stores the results of a query as physical data, rather than calculating the query output every time it is requested. It is commonly used to improve performance for reporting, analytics, dashboards, and other read-heavy workloads where the same joins, aggregations, or filters are used repeatedly.

In manufacturing and regulated operations, a materialized view may be used to present consolidated data from MES, ERP, quality, historian, or traceability systems in a form that is faster to query. For example, it might store a precomputed summary of production counts, nonconformance trends, equipment events, or lot genealogy relationships for reporting tools.

What it includes and excludes

A materialized view includes persisted query results that are refreshed on a schedule, on demand, or by a database-specific mechanism. It may look similar to a regular view when queried, but it is not just a saved SQL definition.

It does not mean the base source data has been replaced. The underlying tables still exist and remain the system of record unless a separate design says otherwise. A materialized view is also not the same as a data warehouse, data lake, or ETL pipeline, though it may be used within those architectures.

Operational meaning

Operationally, a materialized view is often used when teams need consistent read performance without repeatedly executing expensive queries across large transactional datasets. In integrated manufacturing environments, this can reduce load on operational systems while supporting KPI reporting, genealogy lookups, batch review summaries, or exception monitoring.

Because the stored results can become outdated between refreshes, the timing and method of refresh matter. Some environments refresh near real time, while others refresh hourly, daily, or after specific data loads. The acceptable delay depends on how the data is being used.

Common confusion

Materialized view vs. view: A regular view stores only the query definition and calculates results when queried. A materialized view stores the query results themselves.

Materialized view vs. cache: Both can improve read performance, but a cache is typically managed by an application or platform layer, while a materialized view is usually managed by the database.

Materialized view vs. replica: A replica copies underlying database data. A materialized view stores the output of a specific query, often transformed or aggregated.