SQL Materialized View Pattern

-- Create materialized view for dashboard stats
CREATE MATERIALIZED VIEW mv_daily_stats AS
SELECT
    DATE(created_at) AS date,
    COUNT(*) AS total_orders,
    SUM(amount) AS revenue,
    AVG(amount) AS avg_order_value,
    COUNT(DISTINCT user_id) AS unique_customers
FROM orders
WHERE status != 'cancelled'
GROUP BY DATE(created_at)
ORDER BY date DESC
WITH DATA;

-- Create index on materialized view
CREATE UNIQUE INDEX idx_mv_daily_stats_date ON mv_daily_stats (date);

-- Query it like a regular table (fast!)
SELECT * FROM mv_daily_stats
WHERE date >= CURRENT_DATE - 30;

-- Refresh data (full refresh, blocks reads)
REFRESH MATERIALIZED VIEW mv_daily_stats;

-- Concurrent refresh (no blocking, requires unique index)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_stats;

-- Check when last refreshed
SELECT
    schemaname, matviewname,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || matviewname)) AS size
FROM pg_matviews
WHERE matviewname = 'mv_daily_stats';

-- Materialized view for a leaderboard
CREATE MATERIALIZED VIEW mv_leaderboard AS
SELECT
    u.id, u.name, u.avatar_url,
    COUNT(p.id) AS post_count,
    SUM(p.likes) AS total_likes,
    RANK() OVER (ORDER BY SUM(p.likes) DESC) AS rank
FROM users u
JOIN posts p ON p.author_id = u.id
GROUP BY u.id, u.name, u.avatar_url
WITH DATA;

-- Auto-refresh with pg_cron (PostgreSQL extension)
-- SELECT cron.schedule('refresh-stats', '*/15 * * * *',
--   'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_stats');