Postgres归档表运行一天后relallvisible变为0的问题问询

relallvisible是pg_class系统表中统计表内「所有元组对当前所有活跃事务均可见」的页面总数的字段，数值归零说明表的可见性映射（VM）中几乎没有标记为全可见的页面，会导致索引扫描时需要频繁回表校验元组可见性，直接引发性能下降。该问题的常见原因如下：

• 自动真空触发阈值未达标，未自动更新可见性映射
  Postgres默认的自动真空触发规则分为两类：
  1. 针对存在UPDATE/DELETE产生死元组的场景：触发阈值 = autovacuum_vacuum_threshold（默认值50） + autovacuum_vacuum_scale_factor（默认值0.2） * 表总行数
  2. 针对INSERT-only的归档表（PostgreSQL 13及以上版本支持）：触发阈值 = autovacuum_vacuum_insert_threshold（默认值1000） + autovacuum_vacuum_insert_scale_factor（默认值0.1） * 表总行数
     按照你的表1000万行的规模计算，死元组触发阈值约为200万行，INSERT触发阈值约为100万行，你每日仅写入2万行左右，远低于默认阈值，因此自动真空不会被触发，也就不会更新可见性映射标记页面为全可见。只要页面被写入就会清除原有全可见标记，如果新写入数据分布在大量不同页面中，会直接导致relallvisible快速下降。
• 写入操作涉及跨页变更，批量清除全可见标记
  只要页面内有任意一个元组被新增、修改、删除，该页面的全可见标记就会被移除。如果你的迁移逻辑为了避免数据重复，会先执行跨页的DELETE操作再写入新数据，哪怕仅操作2万行，如果这些行分布在2万个不同的页面上，就会直接导致2万个页面的全可见标记被清除，如果表原有全可见页面占比较低，就可能直接掉到接近0的水平。
• 存在长事务持有旧快照，阻塞可见性标记更新
  如果实例中存在运行时间超过一天的长事务，会持有极老的事务快照，此时哪怕手动执行了VACUUM ANALYZE，也无法将包含新写入数据的页面标记为全可见（因为这些数据对长事务的旧快照不可见），后续只要有新写入操作，relallvisible会快速回落。
• 表级自动真空配置被修改，禁用或延迟了自动真空执行
  可执行SELECT reloptions FROM pg_class WHERE relname = '你的归档表名';检查该表的自定义配置，若单独设置了过高的自动真空阈值、或者直接禁用了自动真空，也会导致自动真空不会按预期运行更新可见性映射。

可选优化方案 #

你可以针对该归档表单独调低自动真空触发阈值，不需要每次手动执行VACUUM ANALYZE：

-- 调低死元组触发的自动真空阈值
ALTER TABLE 你的归档表名 SET (
  autovacuum_vacuum_scale_factor = 0,
  autovacuum_vacuum_threshold = 20000
);

-- PostgreSQL 13及以上版本可额外调低INSERT触发的自动真空阈值
ALTER TABLE 你的归档表名 SET (
  autovacuum_vacuum_insert_scale_factor = 0,
  autovacuum_vacuum_insert_threshold = 20000
);

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