PostgreSQL Materialized View Concurrent Refresh

-- PostgreSQL 物化预聚合：重报表一次算好多次读
CREATE MATERIALIZED VIEW mv_daily_region_revenue AS
SELECT
  date_trunc('day', o.placed_at) AS day,
  c.region,
  SUM(o.total_cents) AS revenue_cents,
  COUNT(*) AS order_count
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid'
GROUP BY 1, 2;

-- 唯一索引是 CONCURRENTLY 的前提
CREATE UNIQUE INDEX idx_mv_daily_region
  ON mv_daily_region_revenue (day, region);

-- 并发刷新：读不被阻塞，刷新期间读旧版本
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_region_revenue;

-- 仪表盘查询直接打物化视图
SELECT day, region, revenue_cents
FROM mv_daily_region_revenue
WHERE day >= '2026-10-15'
ORDER BY day DESC, revenue_cents DESC
LIMIT 30;

-- 数据新鲜度检查：刷新滞后超过阈值告警
SELECT
  now() - max(day) AS staleness,
  pg_size_pretty(pg_total_relation_size('mv_daily_region_revenue')) AS view_size
FROM mv_daily_region_revenue;

-- 定时刷新（pg_cron 形态）：
-- SELECT cron.schedule('refresh-revenue', '17 * * * *',
--   $$REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_region_revenue$$);