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;

CREATE INDEX idx_daily_revenue_day ON daily_revenue (day);-- Partial index for a common dashboard filterCREATE INDEX idx_daily_revenue_high_value ON daily_revenue (day)    WHERE revenue > 10000;-- Covering index avoids a heap fetch entirely for this query shapeCREATE INDEX idx_daily_revenue_covering    ON daily_revenue (day) INCLUDE (revenue, order_count);-- Stats go stale after every REFRESH; re-analyze so the planner stays accurateANALYZE daily_revenue;

CREATE MATERIALIZED VIEW monthly_revenue ASSELECT date_trunc('month', day) AS month, sum(revenue) AS revenueFROM daily_revenueGROUP BY 1;-- Inspect what depends on daily_revenue before scripting a refresh chainSELECT dependent_ns.nspname, dependent_view.relnameFROM pg_dependJOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oidJOIN pg_class dependent_view ON pg_rewrite.ev_class = dependent_view.oidJOIN pg_class source_table ON pg_depend.refobjid = source_table.oidJOIN pg_namespace dependent_ns ON dependent_ns.oid = dependent_view.relnamespaceWHERE source_table.relname = 'daily_revenue';-- Refresh leaf-to-root or monthly_revenue reads stale dataREFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

CREATE TABLE mv_refresh_log (    mv_name TEXT PRIMARY KEY,    last_refreshed TIMESTAMPTZ);CREATE OR REPLACE FUNCTION refresh_and_log(mv TEXT) RETURNS void AS $$BEGIN    EXECUTE format('REFRESH MATERIALIZED VIEW CONCURRENTLY %I', mv);    INSERT INTO mv_refresh_log (mv_name, last_refreshed)    VALUES (mv, now())    ON CONFLICT (mv_name) DO UPDATE SET last_refreshed = now();END;$$ LANGUAGE plpgsql;SELECT refresh_and_log('daily_revenue');SELECT mv_name, now() - last_refreshed AS staleness FROM mv_refresh_log;

CREATE EXTENSION IF NOT EXISTS pg_ivm;SELECT create_immv('daily_revenue_ivm',    'SELECT date_trunc(''day'', created_at) AS day, sum(amount) AS revenue     FROM orders GROUP BY 1');-- No REFRESH call needed — the view updates inside the same transaction-- as the write to ordersINSERT INTO orders (created_at, amount) VALUES (now(), 42.00);SELECT * FROM daily_revenue_ivm WHERE day = date_trunc('day', now());