Materialized View
1. Overview
A. Definition
A materialized view is a precomputed, persisted view that stores the result set of the view-defining query (SELECT) as an actual physical table on disk and keeps it synchronized with the source (base) tables through refresh whenever the sources change. Unlike an ordinary view (a virtual view) that recomputes every time it is referenced, a materialized view is a textbook space-time trade-off structure that "precomputes the result and spends space to buy time."
B. Background and Necessity
In relational databases, views have long been used to abstract complex joins, aggregations, and filters under a single name, improving reusability and security. A traditional view, however, is a virtual object whose definition is stored but whose data is not, so every time the view is queried the source tables are re-joined and re-aggregated. If a dashboard repeatedly calls, at minute-level frequency, a multi-join GROUP BY aggregation over a fact table of hundreds of millions of rows, the same heavy computation is repeated endlessly and CPU·I/O saturate.
This problem is especially pronounced in OLAP (analytics)·data warehouse environments. Analytic queries like "total sales by region for last quarter" are read-heavy, and their query frequency (seconds·minutes) is far higher than the change frequency of the source (days·hours). In other words, recomputing the same result every time is clear waste. A materialized view computes this result once and stores it physically, so later queries are replaced by a simple scan of the stored result. When the source changes, the view reflects the whole result or only the changed portion (incremental), so one can choose a staleness tolerance that matches the requirement, somewhere between "always fresh" and "always fast."
This necessity stems from the fact that the two natures of the "view" abstraction — logical reuse and physical performance — were separated in a traditional view. A traditional view provides logical reuse (abstraction·security) but leaves physical performance to recomputation on every call. A materialized view adds a physical substance (the stored result) to integrate both natures into one object; it is a compromise that "keeps the convenience of a view while removing repeated computation." This is precisely why materialized views came to dominate over the old practice of hand-building summary tables and managing them with triggers.
Moreover, a materialized view goes beyond a simple cache in that it is integrated with the query optimizer, which distinguishes it from an application cache. Major systems such as Oracle·PostgreSQL·SQL Server (indexed views)·Snowflake·BigQuery automatically rewrite the plan so that, even when a user queries a source table directly, the optimizer judges whether a materialized view can satisfy that query instead and reads the view (Query Rewrite). As a result, performance gains can be enjoyed transparently without changing application code, establishing materialized views as a core acceleration mechanism in large-scale reporting·BI architectures.
C. Characteristics
The nature of a materialized view must be understood through the fact that "the benefit gained from storage" and "the cost arising from storage" are two sides of one coin. Because the result is stored physically, queries become faster, but the stored result does not match the source instantly and converges to the latest only through a separate operation called refresh. Thus adopting a materialized view amounts to designing "how much staleness, at how much refresh cost, one is willing to accept." The following characteristics all derive from this essence.
- Physical storage of results: the query result is stored as an actual table, so indexing·partitioning can be additionally applied.
- Deferred synchronization (eventually fresh): consistency with the source is deferred until the refresh moment, and freshness is tuned by the refresh strategy.
- Transparent acceleration (query rewrite): the optimizer can automatically substitute a source query with the materialized view.
- Incrementally maintainable: fast refresh that reflects only the delta minimizes refresh cost.
- Cost of redundant storage: because the result is kept separately from the source, storage·refresh burdens increase and consistency-management responsibility is added.
- Separable by purpose: multiple views with different freshness·granularity can be built from the same source, each with a different refresh policy suited to reports·dashboards·exploratory analysis.
2. Overall Structure of a Materialized View
flowchart TB
subgraph SRC["Base (source) tables"]
B1["Sales (fact)"]
B2["Region (dimension)"]
end
MVLOG["Materialized view log (MV Log)<br/>records delta"]
subgraph MV["Materialized view (physical storage)"]
MVT[("Stored result set<br/>sales total by region")]
IDX["Secondary index/partition"]
end
OPT["Query optimizer<br/>(Query Rewrite)"]
USR["User query (SELECT)"]
B1 -->|"change (INSERT/UPDATE/DELETE)"| MVLOG
B2 --> MVLOG
MVLOG -->|"refresh (incremental/complete)"| MVT
MVT --- IDX
USR --> OPT
OPT -->|"scan MV instead of source"| MVT
OPT -. "when rewrite impossible" .-> B1
A materialized view is clearest when understood as four logical elements. First, the base tables are the sources that the materialized-view definition query references; changes occurring here are the origin of the view's loss of freshness. Second, the stored result set is the actual segment holding the rows obtained by executing the view query; like an ordinary table, it can have indexes·partitions·compression applied, accelerating queries further. Third, the materialized view log is an auxiliary structure that separately records the delta of the base tables for incremental refresh; only with it can one reflect "only changed rows" without full recomputation. Fourth, the optimizer's query rewrite detours even source queries to the view, providing transparent acceleration.
The key to this structure is the separation of the write path and the read path. Source changes accumulate in the log (write), and queries read the precomputed result (read). Because the two paths are separated, query performance is decoupled from the size·join complexity of the source table. This resonates with the CQRS (Command Query Responsibility Segregation) idea of separating read and write models; a materialized view can be seen as a means of declaratively constructing a "read-only read model" inside the DBMS. However, unlike CQRS, which explicitly operates two models at the application layer, a materialized view lets the DBMS take responsibility for refresh·consistency, so the operational burden is lower.
The table below shows where the differences among an ordinary view·materialized view·application cache originate. An ordinary view, having no storage, is always fresh but slow because it recomputes every time; a materialized view is fast through storage but incurs refresh delay; and an application cache lives outside the DBMS, so the optimizer is unaware of it and automatic rewrite is impossible.
| Category | Ordinary (virtual) view | Materialized view | Application cache |
|---|---|---|---|
| Data storage | None (definition only) | Physically stored | External store (e.g. Redis) |
| Freshness | Always fresh | Deferred until refresh | Depends on expiry·invalidation |
| Query performance | Recompute each time (slow) | Precomputed (fast) | Fast |
| Optimizer awareness | N/A | Automatic rewrite possible | Impossible (no transparency) |
| Consistency responsibility | DBMS | DBMS (refresh policy) | Application |
3. Refresh Mechanism
sequenceDiagram
participant App as Application
participant Base as Base table
participant Log as MV log (delta)
participant MV as Materialized view
App->>Base: INSERT/UPDATE/DELETE
Base->>Log: load delta (row key·operation)
alt ON COMMIT (immediate incremental)
Base-->>MV: reflect delta at commit (fast refresh)
else ON DEMAND (deferred)
App->>MV: call REFRESH
MV->>Log: read delta and apply incrementally
Note over MV,Log: full recompute if no delta or complex aggregate
end
App->>MV: analytic query (SELECT)
MV-->>App: return precomputed result
The decisive factor that separates a materialized view's value from its cost is the refresh strategy. It splits broadly along two axes — what to recompute (complete vs incremental) and when to do it (immediate vs deferred).
Here "what" and "when" are orthogonal axes, so combinations are possible. Complete refresh can be run as a nightly batch (ON DEMAND), and incremental refresh can be attached at commit time (ON COMMIT). The designer chooses the two axes independently to pick a point matching the business requirement, such as "complete+nightly," "incremental+periodic," or "incremental+real-time." Below we examine the options on each axis in turn.
A. Complete Refresh re-executes the view-defining query from scratch and replaces the result wholesale. It is simple to implement and applicable to any query, but the cost grows linearly with the source size. Running a full refresh of a multi-join aggregation over a fact table of hundreds of millions of rows every hour becomes yet another heavy batch in itself. Therefore complete refresh is best limited to cases where the source is small, changes are wholesale, or the complex aggregation cannot be made incremental.
B. Fast/Incremental Refresh reflects only the delta accumulated in the materialized view log. For example, if 10,000 rows were added in the last hour out of 10 million sales rows, only the aggregation groups affected by those 10,000 rows are updated. Because the cost is proportional not to "source size" but to "change volume," it has a dramatic effect on large tables with low change rates. However, for incremental refresh to hold, the view definition must be in an incrementally maintainable form. In general, additive aggregates like SUM·COUNT are easy to maintain incrementally, but AVG must be decomposed into SUM/COUNT, MAX·MIN may require recomputation when deletions are mixed in, and some DISTINCT·nested subqueries·outer joins are restricted. Designing without knowing these constraints leads to the trap of "setting fast refresh that actually degrades into complete refresh."
C. Refresh Timing (synchronous vs asynchronous) sets the balance point between freshness and write burden. ON COMMIT (synchronous) refreshes the view together when the source transaction commits, guaranteeing near-real-time consistency, but it loads the refresh cost onto the write transaction, increasing OLTP write latency. ON DEMAND (asynchronous) refreshes periodically (e.g. every 5 minutes·1 hour) via a scheduler or manual call, keeping the write path light, but the data becomes as much as one period stale. In practice it is common to first define the staleness the business tolerates, such as "a dashboard tolerates 5 minutes of delay," and set the ON DEMAND period accordingly.
D. Availability During Refresh (concurrency) also matters in operation. Traditional complete refresh had the problem that queries were blocked or saw empty results while the view was emptied and refilled. To solve this, PostgreSQL supports non-blocking refresh with REFRESH MATERIALIZED VIEW CONCURRENTLY (which keeps the existing view readable, builds the new result, then swaps; it requires a unique index), and Oracle tunes transaction atomicity with the atomic_refresh option. In 24×7 services, one must verify the concurrency model so that the refresh itself does not break the query SLA, and for large views the technique of partition exchange, which confines the impact to a single partition, is widely used.
4. Query Rewrite and Usage
The point where a materialized view differs decisively from a mere summary table is query rewrite. Even when a user throws SQL that aggregates the source fact table directly, the optimizer checks whether a semantically equivalent materialized view exists and, if it does, changes the execution plan to read the view instead of the source. The application need not know of the view's existence, and the DBA can tune performance without code changes simply by adding·removing views at the points where performance is needed. For rewrite to hold, the view must "contain" the requested query; for instance, if a day-level aggregate view exists, a month-level query can be rewritten as a rollup that re-GROUP BYs that view. Conversely, if a finer level is required, rewrite is impossible and it falls back to the source.
Another premise of rewrite is aggregate navigability. A coarser-grained view cannot satisfy a finer-grained query, so the view's granularity should be set to the finest common denominator of expected query patterns. A single day-level view can cover day·week·month·quarter queries via rollup, but once hour-of-day queries are needed it falls back to the source. Therefore how many views, at what granularity, is a design decision grounded in query-log analysis; placing many views indiscriminately only increases refresh·storage costs.
However, query rewrite carries the premise that the optimizer must be able to trust the relationship between the view and the source. In Oracle, depending on the QUERY_REWRITE_INTEGRITY level (ENFORCED·TRUSTED·STALE_TOLERATED), it is tuned whether to rewrite only when foreign keys·constraints are validated, or to allow declared constraints (RELY) or even somewhat stale views. Rewriting to a view whose freshness has dropped may give a fast but wrong answer, so it is important to design the rewrite integrity level and the view refresh period together. This is why a materialized view must be treated as a "consistency object" as well as a "performance object."
From a usage standpoint, materialized views stand out in three areas. First, OLAP aggregate acceleration. Building materialized views of region·period·product summaries frequently used in a data warehouse's star schema often reduces dashboard response from tens of seconds to hundreds of milliseconds. Second, remote·distributed data replication. When the source is in another DB, replicating·periodically refreshing results locally via a materialized view acts as a read replica that reduces network round-trips and remote load. Third, pre-join. Heavy many-to-many joins are unrolled and stored in advance, removing repeated join costs.
5. Type Comparison and Application Cases
The choice of refresh strategy varies with business characteristics. The table below organizes representative combinations and the situations they fit; the rationale for each reduces to two axes, "change volume vs query volume" and "tolerated delay."
| Strategy combination | Freshness | Refresh cost | Fitting situation |
|---|---|---|---|
| Complete + ON DEMAND (nightly batch) | Low (daily) | High (whole) | Daily reports, complex aggregates not incrementable |
| Incremental (fast) + ON DEMAND (5~15 min) | Medium | Low (delta) | Near-real-time BI dashboards |
| Incremental (fast) + ON COMMIT | High (real-time) | Added to writes | Small aggregates requiring latest·low write volume |
The judgment criterion that recurs in this comparison reduces to three questions. (1) How often does the data change (change rate)? (2) How old data is tolerated (freshness SLA)? (3) How often is the same result queried (reuse rate)? The lower the change rate, the higher the reuse rate, and the looser the freshness requirement, the greater the benefit of a materialized view; in the opposite situation an ordinary view or index is more reasonable. Scoring candidate queries on these three axes to decide whether to introduce a view avoids over-design.
As a concrete case, suppose a telecom's billing-analysis dashboard took over 3 hours in a nightly batch to fully refresh an entire fact of 200 million rows. Changing this to day-partition-level incremental refresh + 15-minute ON DEMAND reflects only the day's new volume (about 700,000 rows, 0.35% of the total), so refresh shortens to within minutes and dashboard freshness improves from one day to 15 minutes. The key is that the refresh cost was changed to be proportional not to the entire 200 million but to the changed 0.35%, and this principle that "the lower the change rate, the greater the benefit of incremental" works as a general law of strategy selection.
In financial cases, it is common to use a mixed configuration in which metrics where consistency is absolute, such as regulatory reports, are guaranteed real-time agreement via ON COMMIT incremental, while metrics where delay is tolerated, such as aggregates for marketing exploration, save resources with hourly ON DEMAND. Since even the same data has different freshness needs depending on "what it is used for," separating views by purpose and applying different refresh policies is a realistic design. In cloud DWs too, Snowflake's auto-maintained materialized views, BigQuery's incremental MVs, and PostgreSQL's REFRESH MATERIALIZED VIEW CONCURRENTLY (non-blocking query during refresh) differ in characteristics by implementation, so verifying the platform's incremental support scope and concurrency model first is the starting point of design.
6. Deep Dive — Incremental View Maintenance and Extension to Streaming Analytics
Recently, materialized-view technology has evolved toward incrementalizing a wider range of queries based on the theory of IVM (Incremental View Maintenance). Unlike traditional fast refresh, which was limited to simple aggregates·joins, IVM algebraically differentiates the view definition to compute the change the view's result receives from arbitrary inserts·deletes·updates (ΔResult = f(ΔInput)). PostgreSQL's pg_ivm extension and streaming databases like Materialize·RisingWave have commercialized this approach, providing streaming materialized views that refresh the view in milliseconds the moment the source changes. This redefines the traditional notion of "a summary table periodically refreshed in batch" into "a continuous query that streams changes through and always keeps the result up to date."
IVM matters because it widens the boundary of "incrementable queries." Whereas traditional fast refresh handled only limited patterns such as additive aggregates·simple joins, IVM defines a change-propagation rule for each relational-algebra operator, so composite queries combining joins·aggregates·filters are also maintained incrementally. For example, when a row is added to one table in a join view, only the join-result delta with the other side is computed and added to the view. As long as the change volume is small, this approach achieves a refresh cost independent of source size, enabling near-real-time aggregation even over tables of billions of rows. However, when deletions·updates are mixed in, negative-delta (retraction) handling is required, making implementation complex, and the memory·storage cost of maintaining state remains a trade-off.
Another trend is automation in data warehouses·lakehouses. dbt's incremental model, Databricks' Materialized Views·Delta Live Tables, and Snowflake's Dynamic Tables let the platform automatically manage incrementality·refresh period·dependency order once the user merely declares the final result query and the target freshness (target lag). That is, the center of gravity is shifting from an era of humans coding "how to refresh (how)" to declarative data transformation that declares only "what·how fresh (what·how fresh)." This change, combined with CDC (change data capture)·message queues, is elevating materialized views to a standard component of real-time analytic pipelines, and from a Professional Engineer's perspective it should be read as one facet of the macro trend of batch-ETL-centric architectures converging on streaming·declarative maintenance.
Meanwhile, it must also be noted that such automation is not a panacea. Declarative platforms promise a "target freshness," but to keep that promise they internally perform more frequent incremental computation and state maintenance, so compute cost increases almost linearly with freshness. In a cloud billing environment, a "dashboard that looks real-time" can quietly incur large costs, so freshness-cost monitoring from a FinOps perspective is essential. In the end, technology has automated only the "how"; the business judgment of "how fresh it needs to be" still remains the designer's responsibility.
7. Considerations and Implications
A materialized view is powerful but is not "free acceleration," and the following Professional Engineer-perspective trade-offs must be designed together. In particular, there are many cases of adopting it focused only on the performance gain and then suffering the aftermath of refresh cost·consistency management·governance, so a stance of quantifying the cost side first before adoption is needed.
- Explicit agreement on freshness vs cost: first the delay (SLA) the business tolerates must be defined. Distinguish metrics that truly need real time from those that do not, mix ON COMMIT·ON DEMAND, and avoid the over-design of setting a blindly short refresh period and consuming resources. "Everything real-time" is usually unreasonable in terms of cost·write latency.
- Prior verification of incrementability: whether the view definition meets fast-refresh requirements (additive aggregates·simple joins·the needed MV log) must be verified at design time. If incremental does not hold, it silently degrades to complete refresh and the performance goal collapses. Reflect in advance the
AVG→SUM/COUNTdecomposition,MAX/MINdeletion handling,DISTINCTrestrictions, and so on. - Storage·index overhead and operational complexity: because a materialized view redundantly stores the result, storage grows, and indexes built on the view are maintained together on every refresh, adding write burden. As the number of views grows, tracking·governing which query is rewritten to which view becomes difficult, so operate a view catalog·naming conventions·usage monitoring together.
- Consistency boundaries and comparison with alternatives: because a materialized view presupposes "eventual consistency," it is unsuitable for transaction processing that must match the source instantly. In that case, choose by comparing with alternatives such as ordinary views·covering indexes·columnstore·result caches. In particular, if the goal is accelerating a single query an index may be better, if repeated-aggregate acceleration a materialized view, and if wholesale analytic acceleration an OLAP cube·columnar store may fit better.
- Monitoring and lifecycle management: view-wise refresh time·failure rate·last refresh time·rewrite hit rate must be turned into metrics and continuously observed. An unused view is a liability that only consumes refresh cost, so lifecycle management that boldly retires low-usage views is needed.
- Outlook and related technologies: with the maturation of streaming IVM·declarative DW (Dynamic Table·DLT), materialized views are expanding beyond batch summaries into a first-class component of real-time analytics. Considering integration design with CDC·message queues·lakehouses together makes it possible to build a consistent analytic architecture spanning batch·streaming.
References
- Oracle, "Database Data Warehousing Guide — Basic Materialized Views / Refresh", https://docs.oracle.com/en/database/oracle/oracle-database/19/dwhsg/basic-materialized-views.html
- PostgreSQL Documentation, "CREATE MATERIALIZED VIEW / REFRESH MATERIALIZED VIEW", https://www.postgresql.org/docs/current/sql-creatematerializedview.html
- Snowflake Documentation, "Working with Materialized Views", https://docs.snowflake.com/en/user-guide/views-materialized
- Google Cloud, "Introduction to materialized views (BigQuery)", https://cloud.google.com/bigquery/docs/materialized-views-intro
In one line: A materialized view is a space-time trade-off structure that physically stores the view query's result and synchronizes it with complete·incremental refresh; it extends from transparent OLAP acceleration via query rewrite to streaming IVM-based real-time analytics, and its essence is designing "freshness vs cost" against the business SLA.