如何优化物化视图刷新流程，提升速度并降低CPU占用？

我有一个每10分钟运行一次的Cron任务用于同步数据，通过自定义函数refresh_mat_view()刷新物化视图。整体运行正常，但有时CPU占用会达到峰值。执行以下查询后发现，该物化视图刷新函数占用了23%的CPU，请问如何优化物化视图的刷新流程？

用于查询CPU占比的SQL语句 #

SELECT 
        pss.userid,
        pss.dbid,
        pd.datname as db_name,
        round((pss.total_exec_time + pss.total_plan_time)::numeric, 2) as total_time, 
        pss.calls, 
        round((pss.mean_exec_time+pss.mean_plan_time)::numeric, 2) as mean, 
        round((100 * (pss.total_exec_time + pss.total_plan_time) / sum((pss.total_exec_time + pss.total_plan_time)::numeric) OVER ())::numeric, 2) as cpu_portion_pctg,
        pss.query
FROM pg_stat_statements pss, pg_database pd 
WHERE pd.oid=pss.dbid
ORDER BY 7 desc

我的刷新函数 #

-- FUNCTION: refresh_mat_view()

-- DROP FUNCTION IF EXISTS refresh_mat_view();

CREATE OR REPLACE FUNCTION refresh_mat_view(
    )
    RETURNS void
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$

DECLARE
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY mat_view_1;   
    REFRESH MATERIALIZED VIEW CONCURRENTLY mat_view_2;  
    REFRESH MATERIALIZED VIEW CONCURRENTLY mat_view_3;  
    REFRESH MATERIALIZED VIEW CONCURRENTLY mat_view_4; 
    REFRESH MATERIALIZED VIEW CONCURRENTLY mat_view_5;  
    REFRESH MATERIALIZED VIEW CONCURRENTLY mat_view_6;  
    REFRESH MATERIALIZED VIEW CONCURRENTLY mat_view_7;   
    REFRESH MATERIALIZED VIEW CONCURRENTLY mat_view_8;  
    REFRESH MATERIALIZED VIEW CONCURRENTLY mat_view_9;
    REFRESH MATERIALIZED VIEW CONCURRENTLY mat_view_10;
    REFRESH MATERIALIZED VIEW CONCURRENTLY mat_view_11;
    REFRESH MATERIALIZED VIEW CONCURRENTLY mat_view_12;
END;
$BODY$;

优化方案 #

1. 改为并行刷新，避免串行执行 #

当前函数串行刷新12个物化视图，会让CPU长时间处于高负载状态。可以：

• 用pg_cron给每个物化视图单独设置刷新任务，错开或并行执行
• 在函数中通过dblink发起异步调用，让多个刷新操作同时进行，分散CPU压力

2. 按需使用CONCURRENTLY #

REFRESH MATERIALIZED VIEW CONCURRENTLY的开销远高于普通刷新，因为需要创建临时表对比新旧数据。如果业务允许刷新期间物化视图短暂不可用，直接去掉CONCURRENTLY，能大幅降低CPU和IO消耗；只有必须保证视图持续可访问时才保留该选项。

3. 差异化设置刷新周期 #

不是所有物化视图都需要每10分钟刷新一次。分析每个视图的数据更新频率和业务对数据新鲜度的要求：

• 对更新频繁、实时性要求高的视图保留10分钟周期
• 对更新缓慢、实时性要求低的视图延长周期（如30分钟、1小时），减少无效刷新

4. 优化物化视图本身的定义 #

• 检查底层表是否有合适的索引，减少刷新时的数据扫描开销
• 简化视图的查询逻辑，避免不必要的复杂JOIN、聚合或嵌套子查询
• 若数据量较大，考虑将物化视图改为分区模式，每次仅刷新数据有变化的分区

5. 调整数据库配置参数 #

• 增大maintenance_work_mem，让刷新操作能使用更多内存处理数据，减少磁盘IO和CPU占用
• 调整max_parallel_workers_per_gather，允许刷新过程使用更多并行进程（需PostgreSQL版本支持）

6. 定位高开销视图重点优化 #

用以下SQL查看每个物化视图的刷新情况，找出耗时最长的视图针对性优化：

SELECT matviewname, last_refresh, refresh_type,
       EXTRACT(EPOCH FROM age(now(), last_refresh)) AS seconds_since_refresh
FROM pg_stat_user_mviews;

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