Postgres materialized views turned my 12-second report query into 50ms

-- The slow query we want to cache
CREATE MATERIALIZED VIEW monthly_revenue_summary AS
SELECT
    DATE_TRUNC('month', o.created_at) AS month,
    p.category,
    COUNT(DISTINCT o.id) AS order_count,
    COUNT(DISTINCT o.user_id) AS unique_customers,
    SUM(o.amount_cents) / 100.0 AS revenue,
    AVG(o.amount_cents) / 100.0 AS avg_order_value
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.status = 'completed'
GROUP BY 1, 2
ORDER BY 1 DESC, 2;

-- Index the materialized view for fast queries on it
CREATE INDEX idx_mrv_month ON monthly_revenue_summary (month DESC);
CREATE INDEX idx_mrv_category ON monthly_revenue_summary (category);

-- Standard refresh: LOCKS the view, queries block during refresh
REFRESH MATERIALIZED VIEW monthly_revenue_summary;

-- CONCURRENTLY: no lock, queries continue to run during refresh
-- Requires a UNIQUE index on the materialized view
CREATE UNIQUE INDEX idx_mrv_unique 
ON monthly_revenue_summary (month, category);

-- Now this refresh does not block readers:
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue_summary;

-- Install pg_cron extension (available on most managed Postgres providers)
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- Refresh every hour
SELECT cron.schedule(
    'refresh-revenue-summary',
    '0 * * * *',  -- Every hour at :00
    'REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue_summary'
);

-- Check scheduled jobs
SELECT * FROM cron.job;

-- Remove a job
SELECT cron.unschedule('refresh-revenue-summary');

import asyncpg

async def refresh_report_views(pool: asyncpg.Pool) -> None:
    """Refresh all materialized views. Safe to call while app is running."""
    views = [
        'monthly_revenue_summary',
        'user_cohort_retention',
        'product_performance_summary',
    ]
    
    async with pool.acquire() as conn:
        for view in views:
            print(f"Refreshing {view}...")
            await conn.execute(
                f"REFRESH MATERIALIZED VIEW CONCURRENTLY {view}"
            )
            print(f"  Done: {view}")

# In a FastAPI background task:
from fastapi import BackgroundTasks

@app.post("/admin/refresh-reports")
async def trigger_refresh(background_tasks: BackgroundTasks):
    background_tasks.add_task(refresh_report_views, pool)
    return {"status": "refresh queued"}

-- Realtime-ish: refresh every 5 minutes (for dashboard KPIs)
SELECT cron.schedule('kpi-refresh', '*/5 * * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY dashboard_kpis');

-- Hourly: for reports users check a few times per day
SELECT cron.schedule('revenue-refresh', '0 * * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue_summary');

-- Daily: for heavy aggregations (cohort analysis, etc.)
SELECT cron.schedule('cohort-refresh', '0 2 * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY user_cohort_retention');

-- Add a refresh timestamp to know how fresh the data is
CREATE TABLE materialized_view_metadata (
    view_name TEXT PRIMARY KEY,
    last_refreshed_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- After each refresh:
INSERT INTO materialized_view_metadata (view_name, last_refreshed_at)
VALUES ('monthly_revenue_summary', NOW())
ON CONFLICT (view_name) DO UPDATE SET last_refreshed_at = NOW();