Materialized_views

Chapter 34: Materialized Views

Pre-computed Query Results

---

34.1 Understanding Materialized Views

Materialized views store the physical result of a query, unlike regular views which execute the query each time.

                    Materialized vs Regular Views
    ========================================================================

    ┌─────────────────────────────────────────────────────────────────────┐
    │                    Regular View                                     │
    │                                                                      │
    │   Query: SELECT * FROM view                                         │
    │         ↓                                                            │
    │   Executes underlying query                                         │
    │         ↓                                                            │
    │   Returns current data                                              │
    │                                                                      │
    │   Pros: Always fresh data, no storage                               │
    │   Cons: Slow for complex queries                                    │
    └─────────────────────────────────────────────────────────────────────┘

    ┌─────────────────────────────────────────────────────────────────────┐
    │                  Materialized View                                   │
    │                                                                      │
    │   Query: SELECT * FROM materialized_view                            │
    │         ↓                                                            │
    │   Returns stored data                                               │
    │         ↓                                                            │
    │   Must be REFRESHED to update                                       │
    │                                                                      │
    │   Pros: Fast access to complex data                                 │
    │   Cons: May have stale data, uses storage                          │
    └─────────────────────────────────────────────────────────────────────┘

---

34.2 Creating Materialized Views

-- Create materialized view for slow aggregate query
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT
    DATE_TRUNC('month', order_date) as month,
    COUNT(*) as order_count,
    SUM(total) as revenue,
    COUNT(DISTINCT user_id) as customers,
    AVG(total) as avg_order_value
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

-- Query materialized view (fast!)
SELECT * FROM monthly_sales;

-- Create with data
CREATE MATERIALIZED VIEW product_popularity AS
SELECT
    p.product_id,
    p.product_name,
    c.category_name,
    COUNT(oi.order_id) as times_ordered,
    SUM(oi.quantity) as total_quantity_sold,
    SUM(oi.quantity * oi.unit_price) as total_revenue
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
LEFT JOIN categories c ON p.category_id = c.category_id
GROUP BY p.product_id, p.product_name, c.category_name;

-- Create index on materialized view for faster queries
CREATE INDEX idx_monthly_sales_month ON monthly_sales(month);
CREATE INDEX idx_product_popularity_revenue ON product_popularity(total_revenue DESC);

---

34.3 Refreshing Materialized Views

-- Full refresh (rebuilds entire view)
REFRESH MATERIALIZED VIEW monthly_sales;

-- Concurrent refresh (PostgreSQL 9.4+)
-- Allows reads during refresh, requires UNIQUE index
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;

-- Add unique index for CONCURRENTLY
CREATE UNIQUE INDEX idx_monthly_sales_month_unique
ON monthly_sales(month);

-- Schedule automatic refresh (via cron or pgAgent)
-- 0 2 * * * psql -c "REFRESH MATERIALIZED VIEW monthly_sales" mydb

-- Create function for refreshing all materialized views
CREATE OR REPLACE FUNCTION refresh_all_materialized_views()
RETURNS void AS $$
DECLARE
    v_mv TEXT;
BEGIN
    FOR v_mv IN
        SELECT matviewname
        FROM pg_matviews
        WHERE schemaname = 'public'
    LOOP
        EXECUTE 'REFRESH MATERIALIZED VIEW ' || v_mv;
        RAISE NOTICE 'Refreshed: %', v_mv;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

---

34.4 Managing Materialized Views

-- List materialized views
SELECT
    matviewname,
    matviewowner,
    schemaname,
    ispopulated,
    definition
FROM pg_matviews
WHERE schemaname = 'public';

-- Check if populated
SELECT * FROM pg_matviews
WHERE matviewname = 'monthly_sales';

-- Get size of materialized view
SELECT
    matviewname,
    pg_size_pretty(pg_total_relation_size(matviewname::regclass)) as size
FROM pg_matviews
WHERE schemaname = 'public';

-- Rename materialized view
ALTER MATERIALIZED VIEW monthly_sales RENAME TO sales_summary;

-- Rename columns
ALTER MATERIALIZED VIEW product_popularity
RENAME COLUMN times_ordered TO order_count;

-- Add column
ALTER MATERIALIZED VIEW product_popularity
ADD COLUMN last_updated TIMESTAMP;

-- Drop materialized view
DROP MATERIALIZED VIEW IF EXISTS old_materialized_view;

---

34.5 Practical Examples

-- Dashboard summary materialized view
CREATE MATERIALIZED VIEW dashboard_summary AS
SELECT
    (SELECT COUNT(*) FROM users WHERE status = 'active') as active_users,
    (SELECT COUNT(*) FROM orders WHERE created_at > NOW() - INTERVAL '24 hours') as orders_today,
    (SELECT SUM(total) FROM orders WHERE created_at > NOW() - INTERVAL '24 hours') as revenue_today,
    (SELECT COUNT(*) FROM products WHERE stock_quantity > 0) as products_in_stock,
    (SELECT COUNT(*) FROM orders WHERE status = 'pending') as pending_orders;

-- Refresh daily
REFRESH MATERIALIZED VIEW dashboard_summary;

-- User activity report
CREATE MATERIALIZED VIEW user_activity AS
SELECT
    u.user_id,
    u.username,
    u.created_at as member_since,
    COUNT(o.order_id) as total_orders,
    COALESCE(SUM(o.total), 0) as lifetime_value,
    MAX(o.created_at) as last_order_date,
    CASE
        WHEN MAX(o.created_at) < NOW() - INTERVAL '90 days' THEN 'Inactive'
        WHEN MAX(o.created_at) < NOW() - INTERVAL '30 days' THEN 'At Risk'
        ELSE 'Active'
    END as engagement_status
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.username, u.created_at;

-- Product performance ranking
CREATE MATERIALIZED VIEW product_rankings AS
SELECT
    p.product_id,
    p.product_name,
    c.category_name,
    p.price,
    COALESCE(SUM(oi.quantity), 0) as units_sold,
    COALESCE(SUM(oi.quantity * oi.unit_price), 0) as revenue,
    RANK() OVER (ORDER BY COALESCE(SUM(oi.quantity * oi.unit_price), 0) DESC) as revenue_rank,
    RANK() OVER (ORDER BY COALESCE(SUM(oi.quantity), 0) DESC) as units_rank
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
LEFT JOIN categories c ON p.category_id = c.category_id
GROUP BY p.product_id, p.product_name, c.category_name, p.price;

---

34.6 Incremental Refresh

-- Create base and incremental views
CREATE MATERIALIZED VIEW sales_daily_base AS
SELECT
    order_date,
    COUNT(*) as order_count,
    SUM(total) as revenue
FROM orders
GROUP BY order_date;

-- For incremental, use append-only approach
CREATE TABLE sales_daily_incremental (
    order_date DATE PRIMARY KEY,
    order_count INTEGER,
    revenue NUMERIC
);

-- Function to refresh incrementally
CREATE OR REPLACE FUNCTION refresh_sales_incremental()
RETURNS void AS $$
BEGIN
    -- Get last refresh date
    INSERT INTO sales_daily_incremental
    SELECT
        order_date,
        COUNT(*),
        SUM(total)
    FROM orders
    WHERE order_date > (
        SELECT MAX(order_date)
        FROM sales_daily_incremental
    )
    ON CONFLICT (order_date) DO UPDATE
    SET order_count = EXCLUDED.order_count,
        revenue = EXCLUDED.revenue;

    -- Refresh the materialized view from incremental table
    REFRESH MATERIALIZED VIEW sales_daily_base;
END;
$$ LANGUAGE plpgsql;

---

Summary

Feature	Regular View	Materialized View
Data	Query result	Stored data
Refresh	Always current	Manual refresh
Speed	Depends on query	Fast
Storage	None	Uses disk

---

Next: Chapter 35: Transactions Overview