Materialized View Pattern

Speed up quering by having a 'materialized' copy of data

Published on Tuesday, November 3, 2020

Materialized view for Databases

Materialized view is a database object that contains the results of a query. Data can be anything from local copy of remote data, complex joins of data, or data calculated with aggregate function.

The process of creating/refreshing data is called materialization. It is the form of caching query results, and it is used for performance reasons.

In a materialized view, anything can be an index.

Depending on the database, materialized views have different implementations. SQL Server does not have a classic materialized view, but the "Indexed Views" - data always synchronized to the original data.

Materialized View Pattern

Generate prepopulated results of a query in one or more data stores, when the data is not formatted for quick enough query operations. This can speed up application performance drastically.

We are almost always focused on how to store data and do not take into account reading efficiency often. Depending on the data storage, we can have complex and slow queries in relational databases or document NoSQL databases, it can be very "painful" to return a list of documents by some criteria. It is even more pronounced when you need just a small subset of returned data.

The solution is to generate, in advance, a view that materialize data in the format suited for that query. Similar to databases, it can be complex joins or aggregate functions, but data from other services as well (especially for microservices). A single materialized view can be optimized for single or more queries, depending on the needs.

Data in the materialized view is completely disposable and can be rebuilt from scratch from original (or other) data sources. A materialized is never updated directly! It is always rebuilt, after data in the data sources is changed.

The materialization of the data can be automatic, by some trigger that detects changes in data sources or can be scheduled. Sometimes can even be trigger manually.

Materialized View Pattern is especially useful with Event Sourcing because it is much easier to query static tables than event data.