Part 113: Materialized Views: Caching Expensive Analytical Queries

Unlike standard views, a Materialized View physically persists the query results to disk, turning a 20-second aggregation query into a 1-millisecond table read!

1. Creating a Materialized View

CREATE MATERIALIZED VIEW mv_monthly_category_sales AS
SELECT 
  p.category,
  DATE_TRUNC('month', o.order_date) AS sales_month,
  COUNT(o.id) AS order_count,
  SUM(o.total_amount) AS revenue
FROM orders o
JOIN products p ON o.product_id = p.id
GROUP BY 1, 2;

-- Materialized views can have their own indexes!
CREATE INDEX idx_mv_sales_month ON mv_monthly_category_sales (sales_month);

Key Takeaways

• Materialized views physically cache expensive analytical summaries.
• You can add indexes to materialized views just like normal tables.

In Part 114, we explore Refreshing Materialized Views (Concurrent Refresh)!