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.