SQL 物化视图的自动刷新与手动 CONCURRENTLY 结合使用

postgresql物化视图不支持原生自动刷新，需通过外部定时任务调用refresh materialized view concurrently实现低干扰更新；必须建唯一索引才能并发刷新，否则报错；默认刷新会阻塞读取，concurrently仅需短时共享锁，保障查询不中断。

物化视图（Materialized View）在 PostgreSQL 中不支持真正的“自动刷新”，但可通过定时任务 + REFRESH MATERIALIZED VIEW CONCURRENTLY 实现近似自动、低干扰的更新效果。关键在于：不能依赖数据库原生调度，必须外部触发；而 CONCURRENTLY 是实现业务无锁刷新的必要条件。

为什么必须用 CONCURRENTLY？

默认的 REFRESH MATERIALIZED VIEW 会获取排他锁，阻塞所有对物化视图的读取（select），在高并发场景下极易引发查询超时或级联失败。而 CONCURRENTLY 版本仅需一个短时的共享锁，允许查询持续进行——前提是物化视图必须有唯一索引（通常是主键或含 UNIQUE 的索引）。

• 没有唯一索引？执行 REFRESH ... CONCURRENTLY 会直接报错
• 索引字段需覆盖物化视图中用于去重/合并逻辑的关键列（如聚合键）
• 刷新期间，新旧数据版本并存，应用层看到的是“原子切换”后的结果，无中间态

如何模拟“自动刷新”？

PostgreSQL 自身无内置调度器（pg_cron 是常用扩展，非默认）。推荐方案是：用系统 cron 或 kubernetes CronJob 定期调用 SQL 命令。

• 示例 shell 脚本（refresh_mv.sh）：
  psql -U myuser -d mydb -c "REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary_by_month;"
• 添加到 crontab（每小时整点执行）：
  0 * * * * /path/to/refresh_mv.sh >> /var/log/mv_refresh.log 2>&1
• 生产环境建议加简单重试和超时控制（例如用 timeout 300 防止卡住）

手动刷新与自动流程如何协同？

自动刷新负责常规节奏（如每小时/每天），手动刷新用于应急场景：

• 上游数据异常修复后，立即补刷：REFRESH MATERIALIZED VIEW CONCURRENTLY ...
• 发现自动任务失败（日志告警），人工介入检查锁冲突或索引缺失
• 上线新物化视图时，首次必须手动执行一次（因无历史数据，CONCURRENTLY 无法跳过初始化）
• 避免在手动刷新时与自动任务时间重叠——可错开分钟级窗口，或用轻量锁文件做互斥

常见坑与验证要点

部署后务必验证是否真“并发安全”且“按时运行”：

• 查索引：d+ sales_summary_by_month 确认存在 UNIQUE 或 PRIMARY KEY 索引
• 测并发：开两个终端，一个持续 SELECT count(*) FROM mv_name;，另一个执行 REFRESH ... CONCURRENTLY，观察是否全程无中断
• 看日志：确认 cron 调用成功，且无 Error: cannot refresh materialized view "xxx" concurrently 类报错
• 监控刷新耗时：长时间运行可能说明底层查询变慢或锁等待严重