Materialized Views & Caching

-- A REGULAR VIEW is just a saved query. It stores NO data.
-- Every SELECT against it re-runs the full aggregation underneath.
CREATE VIEW v_monthly_revenue AS
SELECT DATE_TRUNC('month', order_date) AS month,
       SUM(total)  AS revenue,
       COUNT(*)    AS order_count
FROM orders
GROUP BY DATE_TRUNC('month', order_date);

-- Reading it scans + groups the WHOLE orders table again, every time:
SELECT * FROM v_monthly_revenue;   -- slow on millions of rows (always fresh)

-- A MATERIALIZED VIEW runs the query ONCE and STORES the result on disk,
-- like a physical table. Reads are then instant — they touch the stored
-- rows, not the source tables.
CREATE MATERIALIZED VIEW mv_monthly_revenue AS
SELECT DATE_TRUNC('month', order_date) AS month,
       SUM(total)         AS revenue,
       COUNT(*)           AS order_count,
       ROUND(AVG(total),2) AS avg_order_value
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

-- Reads the pre-computed res
...

-- A materialized view does NOT update itself. New orders won't appear
-- until you REFRESH it. The simplest refresh fully rebuilds the result:
REFRESH MATERIALIZED VIEW mv_monthly_revenue;
-- ⚠️ Takes a lock — readers are BLOCKED until the rebuild finishes.

-- CONCURRENTLY rebuilds in the background and swaps the result in.
-- Readers keep seeing the OLD data, then see the NEW data — no blocking.
-- It REQUIRES the unique index you created earlier.
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_mon
...

-- Most teams refresh on a schedule so the data is "fresh enough".
-- Example with pg_cron (a PostgreSQL extension): rebuild every hour, on the hour.
SELECT cron.schedule(
    'refresh_revenue',                                    -- a name for the job
    '0 * * * *',                                          -- cron: minute 0 of every hour
    'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_revenue'
);

-- Choose the interval from the freshness the business actually needs:
--   "live to the s
...

-- Real-world: a dashboard that aggregates millions of rows lives off
-- materialized views. Compute the expensive numbers ONCE, read them instantly.

-- Customer Lifetime Value — a heavy join + GROUP BY you don't want per page load:
CREATE MATERIALIZED VIEW mv_customer_ltv AS
SELECT c.id                 AS customer_id,
       c.name,
       COUNT(o.id)          AS total_orders,
       SUM(o.total)         AS lifetime_value,
       ROUND(AVG(o.total),2) AS avg_order_value,
       MAX(o.order_dat
...

-- A materialized view is ONE caching layer. It lives inside the database
-- and you refresh it on a schedule. Other layers trade freshness differently.

-- 1) APP / REDIS CACHE — a key→value store OUTSIDE the database.
--    Your app checks Redis first; on a miss it runs the query, then stores
--    the result with a TTL (time-to-live) so it auto-expires. Pseudo-code:
--      value = redis.get("revenue:2026-06")        -- sub-millisecond read
--      if value is null:
--          value = db.que
...