Materialized Views SQL - Refresh Order Bug Doubles Revenue

-- Assume we have these base tables in a production analytics schema:
-- orders(order_id, customer_id, product_id, region, order_date, amount)
-- products(product_id, category, unit_cost)

-- This query is expensive: it joins two large tables, aggregates, and
-- gets called by the dashboard on every page load (~200 req/min).
-- Without a materialized view it takes ~4 seconds. Unacceptable.

CREATE MATERIALIZED VIEW mv_regional_sales_summary AS
SELECT
    o.region,
    p.category                          AS product_category,
    DATE_TRUNC('month', o.order_date)   AS sale_month,
    COUNT(o.order_id)                   AS total_orders,
    SUM(o.amount)                       AS total_revenue,
    SUM(o.amount - p.unit_cost)         AS total_profit,
    AVG(o.amount)                       AS avg_order_value
FROM orders o
JOIN products p ON o.product_id = p.product_id
GROUP BY
    o.region,
    p.category,
    DATE_TRUNC('month', o.order_date)
ORDER BY sale_month DESC, total_revenue DESC
WITH DATA;  -- Execute the query NOW and store results immediately.
            -- Use WITH NO DATA to create the structure without populating it.

-- Now build indexes on the most common filter/join patterns.
-- Without this step you've only solved half the problem.
CREATE INDEX idx_mv_sales_region_month
    ON mv_regional_sales_summary (region, sale_month);

CREATE INDEX idx_mv_sales_category
    ON mv_regional_sales_summary (product_category);

-- Verify what was stored — should return rows immediately at near-zero cost
EXPLAIN ANALYZE
SELECT region, total_revenue
FROM mv_regional_sales_summary
WHERE region = 'EMEA'
  AND sale_month >= '2024-01-01';

-- Check the physical size of the materialized view on disk
SELECT
    pg_size_pretty(pg_total_relation_size('mv_regional_sales_summary')) AS total_size,
    pg_size_pretty(pg_relation_size('mv_regional_sales_summary'))       AS table_size,
    pg_size_pretty(
        pg_total_relation_size('mv_regional_sales_summary')
        - pg_relation_size('mv_regional_sales_summary')
    )                                                                   AS index_size;

-- ============================================================
-- STRATEGY 1: Plain refresh — simple but locks out readers
-- Use this for: non-critical views, off-hours batch jobs,
--               views that are fast to recompute (< 1 second)
-- ============================================================
REFRESH MATERIALIZED VIEW mv_regional_sales_summary;
-- This acquires AccessExclusiveLock. Concurrent SELECTs will WAIT.
-- Safe for batch jobs at 2am. Not safe during business hours.


-- ============================================================
-- STRATEGY 2: Concurrent refresh — production-safe
-- Requirement: the materialized view MUST have a unique index.
-- ============================================================

-- First, add the required unique index. We create a surrogate
-- key by combining our natural key columns.
CREATE UNIQUE INDEX idx_mv_sales_unique_key
    ON mv_regional_sales_summary (region, product_category, sale_month);
-- ^^^ Without this, CONCURRENT refresh will throw:
-- ERROR: cannot refresh materialized view concurrently without a unique index

-- Now the safe, concurrent refresh:
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_regional_sales_summary;
-- PostgreSQL re-runs the full query in the background, compares
-- results, and performs targeted INSERT/UPDATE/DELETE operations.
-- Readers are NEVER blocked. Writers on the base tables are not blocked.
-- This takes ~2x longer but causes zero downtime.


-- ============================================================
-- STRATEGY 3: Automate refresh with pg_cron (or a scheduler)
-- Refresh every 15 minutes during business hours
-- ============================================================

-- Install pg_cron extension (requires superuser, done once)
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- Schedule concurrent refresh every 15 minutes, 8am-8pm weekdays
SELECT cron.schedule(
    'refresh-sales-summary',          -- job name
    '*/15 8-20 * * 1-5',              -- cron expression
    $$REFRESH MATERIALIZED VIEW CONCURRENTLY mv_regional_sales_summary$$
);

-- Check scheduled jobs
SELECT jobname, schedule, command, active
FROM cron.job
WHERE jobname = 'refresh-sales-summary';


-- ============================================================
-- MONITORING: How stale is our materialized view right now?
-- ============================================================
SELECT
    schemaname,
    matviewname,
    -- last_refresh is NULL if the view was never refreshed
    -- (i.e., created WITH NO DATA)
    last_refresh,
    NOW() - last_refresh AS data_age,
    CASE
        WHEN NOW() - last_refresh > INTERVAL '30 minutes'
        THEN 'STALE — consider refreshing'
        ELSE 'Fresh enough'
    END AS freshness_status
FROM pg_matviews
WHERE matviewname = 'mv_regional_sales_summary';

-- ============================================================
-- SCENARIO: Chained materialized views (A -> B)
-- mv_raw_order_metrics depends on base tables
-- mv_executive_dashboard depends on mv_raw_order_metrics
-- ============================================================

CREATE MATERIALIZED VIEW mv_raw_order_metrics AS
SELECT
    customer_id,
    DATE_TRUNC('day', order_date)  AS order_day,
    COUNT(*)                        AS daily_order_count,
    SUM(amount)                     AS daily_spend
FROM orders
GROUP BY customer_id, DATE_TRUNC('day', order_date)
WITH DATA;

CREATE UNIQUE INDEX idx_raw_metrics_unique
    ON mv_raw_order_metrics (customer_id, order_day);

-- This view depends on the one above, NOT on the base table directly
CREATE MATERIALIZED VIEW mv_executive_dashboard AS
SELECT
    order_day,
    COUNT(DISTINCT customer_id)     AS active_customers,
    SUM(daily_order_count)          AS platform_orders,
    SUM(daily_spend)                AS platform_revenue,
    AVG(daily_spend)                AS avg_customer_spend
FROM mv_raw_order_metrics          -- <-- reading from another mat view!
GROUP BY order_day
ORDER BY order_day DESC
WITH DATA;

-- WRONG: refreshing in the wrong order gives stale-on-stale data
-- REFRESH MATERIALIZED VIEW CONCURRENTLY mv_executive_dashboard;
-- REFRESH MATERIALIZED VIEW CONCURRENTLY mv_raw_order_metrics;
-- ^^^ This refreshes the exec dashboard BEFORE the raw metrics are updated.
-- The exec dashboard will reflect yesterday's raw metrics for another cycle.

-- CORRECT: always refresh in dependency order (leaves first)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_raw_order_metrics;
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_executive_dashboard;
-- Now the exec dashboard reflects today's raw data.

-- ============================================================
-- DEFENSIVE PATTERN: Wrap chained refreshes in a transaction
-- with error handling using a stored procedure
-- ============================================================
CREATE OR REPLACE PROCEDURE refresh_dashboard_views()
LANGUAGE plpgsql
AS $$
BEGIN
    -- Refresh in strict dependency order
    REFRESH MATERIALIZED VIEW CONCURRENTLY mv_raw_order_metrics;
    RAISE NOTICE 'mv_raw_order_metrics refreshed at %', NOW();

    REFRESH MATERIALIZED VIEW CONCURRENTLY mv_executive_dashboard;
    RAISE NOTICE 'mv_executive_dashboard refreshed at %', NOW();

EXCEPTION
    WHEN OTHERS THEN
        -- Log the error but don't crash silently.
        -- In production you'd INSERT into an error_log table here.
        RAISE WARNING 'Dashboard refresh failed: % — %', SQLSTATE, SQLERRM;
        -- The materialized views retain their last successful data.
        -- Stale data is better than no data for a dashboard.
END;
$$;

-- Schedule this procedure instead of raw REFRESH commands
SELECT cron.schedule(
    'refresh-dashboard-chain',
    '*/15 8-20 * * 1-5',
    'CALL refresh_dashboard_views()'
);

-- ============================================================
-- DETECTING SCHEMA DRIFT BEFORE IT SILENTLY CORRUPTS REPORTS
-- Run this check as part of your deployment pipeline
-- ============================================================
SELECT
    mv.matviewname,
    mv.definition,
    -- If the view references columns that no longer exist, this
    -- will surface as an error when you try to refresh.
    -- Pre-validate by attempting a dry-run EXPLAIN:
    'EXPLAIN (FORMAT TEXT) SELECT * FROM ' || mv.matviewname || ' LIMIT 0' AS validation_query
FROM pg_matviews mv
WHERE mv.schemaname = 'public'
ORDER BY mv.matviewname;

-- ============================================================
-- SCENARIO: Compare the same business question answered three ways.
-- Question: What is the total lifetime spend per customer?
-- Base table: orders (500 million rows, growing 1M rows/day)
-- ============================================================

-- OPTION 1: Regular View — always fresh, always slow on 500M rows
CREATE OR REPLACE VIEW vw_customer_lifetime_spend AS
SELECT
    customer_id,
    COUNT(order_id)     AS total_orders,
    SUM(amount)         AS lifetime_spend,
    MIN(order_date)     AS first_order_date,
    MAX(order_date)     AS most_recent_order
FROM orders
GROUP BY customer_id;
-- Querying this for a single customer does a full 500M-row aggregation.
-- EXPLAIN shows: HashAggregate -> Seq Scan on orders (~12 seconds)

-- OPTION 2: Materialized View — refreshed nightly, fast reads
CREATE MATERIALIZED VIEW mv_customer_lifetime_spend AS
SELECT
    customer_id,
    COUNT(order_id)     AS total_orders,
    SUM(amount)         AS lifetime_spend,
    MIN(order_date)     AS first_order_date,
    MAX(order_date)     AS most_recent_order
FROM orders
GROUP BY customer_id
WITH DATA;

CREATE UNIQUE INDEX idx_mv_clv_customer
    ON mv_customer_lifetime_spend (customer_id);
-- Now a point lookup for one customer: ~0.1ms. Nightly full refresh: ~45 seconds.
-- Staleness: up to 24 hours. Acceptable for a CRM 'about this customer' panel.
-- NOT acceptable for a real-time fraud check.

-- OPTION 3: Running summary table — always accurate, incremental updates
-- Best when you need real-time accuracy without the full-scan cost.
CREATE TABLE customer_spend_summary (
    customer_id     BIGINT PRIMARY KEY,
    total_orders    BIGINT      NOT NULL DEFAULT 0,
    lifetime_spend  NUMERIC(14,2) NOT NULL DEFAULT 0,
    first_order_date DATE,
    last_order_date  DATE,
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Trigger that maintains the summary table on every new order
CREATE OR REPLACE FUNCTION fn_update_customer_spend_summary()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO customer_spend_summary
        (customer_id, total_orders, lifetime_spend, first_order_date, last_order_date, updated_at)
    VALUES
        (NEW.customer_id, 1, NEW.amount, NEW.order_date, NEW.order_date, NOW())
    ON CONFLICT (customer_id) DO UPDATE SET
        total_orders     = customer_spend_summary.total_orders + 1,
        lifetime_spend   = customer_spend_summary.lifetime_spend + NEW.amount,
        -- LEAST/GREATEST keep the min/max without scanning historical data
        first_order_date = LEAST(customer_spend_summary.first_order_date, NEW.order_date),
        last_order_date  = GREATEST(customer_spend_summary.last_order_date, NEW.order_date),
        updated_at       = NOW();
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_orders_update_spend_summary
    AFTER INSERT ON orders
    FOR EACH ROW
    EXECUTE FUNCTION fn_update_customer_spend_summary();

-- Result: point lookups in ~0.1ms AND data is accurate to the last millisecond.
-- Cost: every INSERT into orders now also writes to customer_spend_summary.
-- On 1M orders/day: ~12ms extra latency per insert batch. Usually acceptable.

-- ============================================================
-- MONITORING: PostgreSQL mat view health check
-- Run every minute from a monitoring tool (cron, pg_cron, or external)
-- ============================================================

-- Signal 1: Freshness lag
SELECT
    matviewname,
    last_refresh,
    EXTRACT(EPOCH FROM (NOW() - last_refresh)) / 60 AS minutes_since_refresh,
    CASE
        WHEN last_refresh IS NULL THEN 'NEVER_REFRESHED'
        WHEN NOW() - last_refresh > INTERVAL '30 minutes' THEN 'STALE'
        ELSE 'FRESH'
    END AS status
FROM pg_matviews
WHERE schemaname = 'public';

-- Signal 2: Refresh duration (if you log start/end to a custom table)
CREATE TABLE mv_refresh_log (
    id SERIAL PRIMARY KEY,
    matview_name TEXT NOT NULL,
    started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    finished_at TIMESTAMPTZ,
    duration_ms INTEGER,
    success BOOLEAN,
    error_message TEXT
);

-- Wrap refresh with logging (example for one mat view)
INSERT INTO mv_refresh_log (matview_name, started_at)
VALUES ('mv_regional_sales_summary', NOW());

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_regional_sales_summary;

UPDATE mv_refresh_log
SET finished_at = NOW(),
    duration_ms = EXTRACT(EPOCH FROM (NOW() - started_at)) * 1000,
    success = TRUE
WHERE id = currval('mv_refresh_log_id_seq');

-- Query recent refresh performance
SELECT
    matview_name,
    AVG(duration_ms)::INT AS avg_duration_ms,
    MAX(duration_ms) AS max_duration_ms,
    COUNT(*) AS total_refreshes,
    SUM(CASE WHEN success = FALSE THEN 1 ELSE 0 END) AS failures
FROM mv_refresh_log
WHERE started_at > NOW() - INTERVAL '7 days'
GROUP BY matview_name
ORDER BY avg_duration_ms DESC;

-- Signal 3: Alert on failures
SELECT matview_name, started_at, error_message
FROM mv_refresh_log
WHERE success = FALSE AND started_at > NOW() - INTERVAL '1 hour';

-- ============================================================
-- Heartbeat table approach (cross-database portable)
-- ============================================================
CREATE TABLE mv_heartbeat (
    matview_name TEXT PRIMARY KEY,
    last_refresh_ok TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expected_refresh_interval INTERVAL NOT NULL
);

-- Update heartbeat after every successful refresh
UPDATE mv_heartbeat
SET last_refresh_ok = NOW()
WHERE matview_name = 'mv_regional_sales_summary';

-- Monitoring query
SELECT
    matview_name,
    last_refresh_ok,
    NOW() - last_refresh_ok AS age,
    expected_refresh_interval,
    CASE
        WHEN NOW() - last_refresh_ok > expected_refresh_interval * 2
        THEN 'ALERT: stale'
        ELSE 'OK'
    END AS status
FROM mv_heartbeat;