物化视图刷新从 40 分钟到 4 分钟

给运营搭的日报物化视图，数据源是几张亿级表的多层聚合，REFRESH MATERIALIZED VIEW 全量重建要 40 分钟，业务要求 9 点前出数，5 点开始刷新，窗口越缩越紧。优化后刷新 4 分钟，还支持了白天每小时的准实时更新。

第一步优化是 REFRESH 的语法层：普通 REFRESH 会先清空再重建，期间查询被锁（准确说拿排他锁，读全堵）。改用 CONCURRENTLY，PG 只增量比对差异行更新，查询不阻塞。但 CONCURRENTLY 有个硬前提——物化视图必须有唯一索引，加上唯一索引之后还白捡一个收益：差异比对基于唯一键，有变化才写，死元组大幅减少。

-- 1. 唯一索引是 CONCURRENTLY 的门票
CREATE UNIQUE INDEX ON mv_daily_report (stat_date, shop_id);

-- 2. 刷新命令换形态
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_report;

CONCURRENTLY 让刷新不锁读，但没让它变快——40 分钟还在。第二步是砍计算量。原视图的 SELECT 里有三个痛点：对亿级订单表做了两次重复扫描（两个子查询各扫一遍）、按表达式函数分组导致索引失效、聚合里混着 percentile_disc 这种昂贵函数。改写方向：用 CASE WHEN 把两个子查询合并成一次扫描分组；分组键从 date_trunc('day', create_time + interval '8 hour') 改成预生成的北京时间列；percentile 拆到每小时的轻量子视图，最终汇总时加权。这一轮下来刷新 11 分钟。

第三步是把"全量算"改成"增量算"。视图底层按天分区，刷新时 where 子句只扫当天和前一天（跨天边界修正）的分区，历史分区数据不变不重算。这个改动依赖物化视图定义改成"分区基表加过滤"的写法，刷新脚本按天传参。至此刷新 4 分钟，每小时跑一次也没压力。

调度的工程化也补齐了：用 pg_cron 挂每小时任务，刷新前用 advisory lock 防重入，失败写告警。物化视图体系跑起来后更像一个轻量数仓，运营提数需求从找开发写 SQL 变成了看板选时间范围，双方都省了力气。

复盘时总结的顺序感很关键：先解决锁（CONCURRENTLY 加唯一索引），再解决扫描量（SQL 改写加分区裁剪），最后解决重复计算（增量策略）。反过来做容易一开始就陷入增量架构的复杂度里。物化视图是"用存储换计算"的经典工具，但换多少、怎么换，还是得看执行计划和分区的脸色。