MATERIALISED VIEW statement

It is a method that allows you to create a snapshot/view of a query result that is stored in a database table. It sits in a database until you tell it to refresh programatically.

How to use it

When you want to create a materialised view of your data, you use MATERIALISED VIEW keyword. It will create a new table with the materialised view based on that query.

CREATE MATERIALIZED VIEW user_order_summary AS  
SELECT  
  u.id AS user_id,  
  u.name,  
  COUNT(o.id) AS order_count,  
  SUM(o.total) AS total_spent  
FROM users u  
JOIN orders o ON u.id = o.user_id  
GROUP BY u.id, u.name;  

When you want to update it/refresh it, you can use one of the following:

  
REFRESH MATERIALIZED VIEW user_order_summary;  
  
  
REFRESH MATERIALISED VIEW CONCURRENTLY user_order_summary;  

Use cases

• Dashboards
• Aggregation-heavy queries
• Analytics
• Precomputed join-heavy datasets

Disadvantages

• You need to add logic in your application layer to refresh it manually