PostgreSQL中如何在表更新后指定时间刷新materialized view

因为你的table1是固定周期执行海量更新，这种方案不需要额外配置数据库触发器，不会给海量更新带来额外性能开销，操作最简单。

1.1 用数据库内置pg_cron扩展实现 #

• 首先安装并开启pg_cron扩展：

CREATE EXTENSION IF NOT EXISTS pg_cron;

• 假设你的table1固定每5天凌晨2点启动更新，更新耗时最长不超过2小时，直接设置定时任务在每5天的凌晨4点刷新物化视图即可：

-- 替换mv_table1为你自己的物化视图名称
SELECT cron.schedule(
    'refresh_mv_table1', -- 定时任务名称
    '0 4 */5 * *', -- cron表达式：每5天的4点整执行
    'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_table1;'
);

• 如果你不确定table1的更新耗时，可以在你执行table1更新的脚本末尾加延迟逻辑，比如shell脚本中更新完table1后加sleep 7200（即等待2小时），再执行刷新物化视图的SQL命令即可。

1.2 无数据库扩展权限时用操作系统定时任务实现 #

如果你没有权限安装pg_cron，可以直接用Linux系统自带的crontab实现，添加如下定时规则即可：

# 替换对应的用户名、数据库名、物化视图名称
0 4 */5 * * psql -U your_username -d your_dbname -c "REFRESH MATERIALIZED VIEW CONCURRENTLY mv_table1;"

如果你的table1更新时间不固定，需要在每次更新完成后自动触发延迟刷新，可以用语句级触发器+单次定时任务实现，不会因为海量更新产生多余性能损耗。

• 第一步依然先开启pg_cron扩展：

CREATE EXTENSION IF NOT EXISTS pg_cron;

• 第二步创建延迟调度刷新任务的函数：

CREATE OR REPLACE FUNCTION schedule_mv_refresh()
RETURNS TRIGGER AS $$
BEGIN
    -- 先取消同名的旧延迟任务，避免重复调度
    PERFORM cron.unschedule('delayed_refresh_mv');
    -- 新建2小时后执行的单次刷新任务
    PERFORM cron.schedule(
        'delayed_refresh_mv',
        NOW() + INTERVAL '2 hours',
        'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_table1;'
    );
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

• 第三步给table1添加语句级触发器，整个更新操作完成后只会触发一次，不会逐行触发：

CREATE TRIGGER trigger_after_table1_update
AFTER UPDATE ON table1
FOR EACH STATEMENT -- 语句级触发，批量更新全程只触发一次
EXECUTE FUNCTION schedule_mv_refresh();

• 刷新物化视图时加CONCURRENTLY参数可以避免锁表，不会阻塞其他查询物化视图的请求，但要求你的物化视图必须先创建唯一索引。
• 可以执行SELECT * FROM cron.job;查看所有已配置的pg_cron定时任务，确认配置是否正确。

内容的提问来源于stack exchange，提问作者user16123142