Materialized Views

Code

-- Create materialized view
CREATE MATERIALIZED VIEW sales_summary AS
SELECT
    product_id,
    DATE_TRUNC('month', order_date) AS month,
    SUM(quantity) AS total_qty,
    SUM(total) AS revenue
FROM orders
GROUP BY product_id, DATE_TRUNC('month', order_date);

-- Create unique index
CREATE UNIQUE INDEX idx_sales_summary
ON sales_summary (product_id, month);

-- Query
SELECT * FROM sales_summary WHERE month = '2024-01-01';

-- Refresh
REFRESH MATERIALIZED VIEW sales_summary;

-- Concurrent refresh
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;

-- Delete
DROP MATERIALIZED VIEW IF EXISTS sales_summary;

Explanation

Materialized views store query results as physical tables, suitable for reporting; they need periodic refresh.

More SQL Snippets