Materialized Views Cheat Sheet

CREATE MATERIALIZED VIEW daily_revenue ASSELECT    date_trunc('day', created_at) AS day,    sum(amount) AS revenue,    count(*) AS order_countFROM ordersGROUP BY 1WITH DATA;-- Required for CONCURRENTLY refresh: a unique indexCREATE UNIQUE INDEX ON daily_revenue (day);-- Refresh without locking out concurrent readsREFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;

-- ClickHouse: incrementally populated on every insert to the source tableCREATE MATERIALIZED VIEW daily_revenue_mvENGINE = SummingMergeTreeORDER BY day ASSELECT toDate(created_at) AS day, sum(amount) AS revenueFROM ordersGROUP BY day;-- TimescaleDB: continuous aggregate, refreshed on a scheduleCREATE MATERIALIZED VIEW daily_revenueWITH (timescaledb.continuous) ASSELECT time_bucket('1 day', created_at) AS day, sum(amount) AS revenueFROM ordersGROUP BY day;

CREATE EXTENSION IF NOT EXISTS pg_cron;SELECT cron.schedule(    'refresh-daily-revenue',    '*/15 * * * *',    $$REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue$$);-- Check scheduled jobsSELECT * FROM cron.job;