Materialized View Pattern
A design pattern to handle read workload efficiently
--
You need to design a product recommendation solution for an e-commerce site to handle high customer traffic with lower latency and quick response time. The recommendation must take care of the customer's previous purchases and items bought by other customers who are buying similar products and recommend new items.
A complex query using multiple base tables, different joins, and aggregating data from various sources decreases the system's performance.
Traditionally a view would be the solution to query data from multiple base tables to present unified data to the users. The data in the views is computed each time they are accessed to get the most recently updated data. There is a performance cost associated with views.
A better and more common solution is to use Materialized views, especially when the queries have
- Data Aggregation
- Filtering data to query a subset of the table.
- Multi Joins between large and small tables.
Materialized views materializes the data in advance in a view suited for the final results set generating query efficiency.
Material view pattern is highly applicable for microservice when each microservice has its own database.
Materialized view simplifies complex data by saving query information by prepopulating views. It stores the physical copy of the data and does not run in real-time like a normal view.
How is data prepopulated in the Materialized views?
Materialized views are populated at the creation time and must be manually refreshed at regular intervals or based on certain events. The view must be updated whenever the source data changes to include new information.
You can schedule the update of the Materialized view automatically or when the system detects a change by subscribing to change notifications from the source. They can also be updated through an efficient batch process.
