Google Cloud PostgreSQL与BigQuery物化视图创建更新方案咨询

关于BigQuery方案的判断 #

这个方案不适合你当前的场景，原因很直接：

• 成本和性能都不划算：BigQuery对接Cloud SQL PostgreSQL的官方能力只有两种，要么是联邦查询直接连源库读，每次查询都打回PostgreSQL执行，既占源库连接，延迟也高，根本起不到物化视图加速的作用；要么是定时导出数据到BigQuery存储，要做到5-10分钟级的近数据更新，得搭CDC链路实时同步变更，不管是用什么同步服务，运维成本和存储计算成本都比在PostgreSQL内部做高好几倍。
• 你找不到对应拉取实现方法不是查漏了，是BigQuery本身定位就是离线数仓，官方从来没给过这种对接业务库做高频小增量物化视图的现成方案，所有公开的接入指南都是面向小时/天级的离线分析同步场景，硬凑能用，但完全没必要。

PostgreSQL内部实现的落地方案 #

首先要明确：PostgreSQL原生的物化视图没有内置自动刷新，默认的REFRESH MATERIALIZED VIEW是全量重算，如果你直接建一个覆盖所有时间范围的单物化视图，不管怎么配刷新规则，要么刷新慢锁表，要么重算时把库打挂，根本满足不了你两种频率的更新需求，正确的做法是按时间冷热拆分物化视图+内置定时任务分层刷新，全程不用额外搭服务，Cloud SQL原生支持所有组件：

• 第一步：按冷热数据拆分两个物化视图
  把数据按时间边界拆成近几天的热数据、更早的冷数据，分别建物化视图：
  • 热数据视图：只存最近N天（比如7天，可根据自己的业务调整）的数据，因为数据量小，哪怕10分钟全量重算一次，对实例的压力也可以忽略。注意建完视图要加唯一索引，才能用CONCURRENTLY参数刷新，避免刷新时锁表阻塞正常业务查询：

    -- 建近7天热数据物化视图
    CREATE MATERIALIZED VIEW mv_hot_recent AS
    SELECT * FROM your_monthly_partition_table
    WHERE create_time >= NOW() - INTERVAL '7 days'
    WITH DATA;
    -- 建唯一索引支持无锁刷新
    CREATE UNIQUE INDEX idx_mv_hot_recent_pk ON mv_hot_recent(id);
    

  • 冷数据视图：存7天之前到你需要保留的最长时间范围的历史数据，这部分数据几乎不会再发生增删改，不需要高频刷新，每小时或者每天低峰期刷一次就行：

    -- 建历史冷数据物化视图
    CREATE MATERIALIZED VIEW mv_cold_history AS
    SELECT * FROM your_monthly_partition_table
    WHERE create_time < NOW() - INTERVAL '7 days'
    WITH DATA;
    

• 第二步：用Cloud SQL内置的pg_cron配置定时刷新规则
  不用额外搭外部调度服务，直接开pg_cron扩展就能在库内配置定时任务：

  -- 开启pg_cron扩展，Cloud SQL PostgreSQL默认支持该扩展
  CREATE EXTENSION IF NOT EXISTS pg_cron;
  
  -- 配置每10分钟刷新一次热数据视图，加CONCURRENTLY不阻塞读
  SELECT cron.schedule(
    'refresh_hot_mv_every_10min',
    '*/10 * * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_hot_recent;'
  );
  
  -- 配置每天凌晨2点（业务低峰期）刷新一次冷数据视图，数据量大可以不加CONCURRENTLY，低峰期锁表影响极小
  SELECT cron.schedule(
    'refresh_cold_mv_daily',
    '0 2 * * *',
    'REFRESH MATERIALIZED VIEW mv_cold_history;'
  );
  

• 第三步：做一层视图封装，对上层业务透明
  不用让业务方感知到底层拆了两个物化视图，直接建一个普通视图把两个物化视图的结果合并，业务查这个总视图就行，查询时PostgreSQL会自动做分区裁剪，不会扫无关的数据：

  CREATE VIEW v_full_data AS
  SELECT * FROM mv_hot_recent
  UNION ALL
  SELECT * FROM mv_cold_history;
  

实操注意点 #

• 因为你的底表是按月分区的，刷新物化视图的时候PostgreSQL会自动裁剪掉时间范围外的月份分区，不会扫全表，刷新速度会非常快
• 如果你的业务里3天之前的数据就几乎没有变更了，可以把热视图的时间窗口缩到3天，进一步降低刷新带来的开销
• 别折腾触发器实现的实时物化视图，对你这种按时间分区的时序数据场景来说，触发器带来的写入开销、维护成本远高于定时刷新的方案，稳定性还差

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