PostgreSQL 物化视图并发刷新

示例说明

每次打开仪表盘都实时聚合千万行订单是对数据库的持续压榨——物化视图把聚合结果固化为表，读变成轻量索引查询。代价是数据滞后：REFRESH 定期重算。普通刷新会锁表换版本，读者全被阻塞；CONCURRENTLY 借助唯一索引做差异更新，刷新期间读者继续读旧版，业务无感切换。

技术要点

• 预聚合把重查询固化为轻读取
• CONCURRENTLY 刷新不阻塞读者
• 唯一索引是并发刷新的前提

代码解读

视图定义按天与区域聚合已支付订单；唯一索引建在分组键上，并发刷新靠它定位差异行。仪表盘查询直接读视图走索引；新鲜度查询输出当前时间减最新分区的滞后量与视图体积，滞后超阈值就该触发刷新告警。末尾给出 pg_cron 定时刷新的形态。

使用建议

刷新频率按业务容忍的滞后设定。视图定义变更要 DROP 重建，纳入迁移管理。底层表分批加载时刷新窗口会拉长，错峰执行。

代码示例

-- 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$$);