PostgreSQL如何标记插入后永不修改的表以提升查询性能？

PostgreSQL本身没有原生的「插入后数据永不修改」的专属状态标记，但可以通过组合操作实现完全一致的效果，大幅提升仅索引扫描等场景的性能。

你提到的仅索引扫描性能逻辑如下：

不过针对极少修改的数据，有方法可以解决这个问题。PostgreSQL会跟踪表的堆（heap）中每个页面，确认该页面存储的所有行是否都足够旧，对所有当前及未来事务可见。该信息存储在表的visibility map（可见性映射）的一个比特位中。index-only scan（仅索引扫描）找到候选索引条目后，会检查对应堆页面的visibility map比特位。如果该位被设置，就可以确定行可见，无需额外操作即可返回数据。如果该位未设置，则必须访问堆条目确认是否可见，此时相比标准索引扫描就没有性能优势了。

只要让PostgreSQL确认整张表所有行都对所有事务可见，就可以实现仅索引扫描永远不需要访问堆条目的优化效果，具体操作步骤如下：

• 第一步：对完成数据插入的表执行冻结操作，全表标记可见性：
  执行命令：VACUUM (FREEZE, ANALYZE) 目标表名;
  该操作会将表内所有页面的可见性映射比特位置为生效状态，同时把所有行的事务ID标记为永久冻结，只要后续不修改数据，该标记会永久生效，仅索引扫描会全程跳过堆访问。
• 第二步：设置表为只读状态，避免误修改：
  PostgreSQL 12及以上版本支持直接设置表只读，执行命令：ALTER TABLE 目标表名 SET READ ONLY;
  该设置会从权限层面禁止对表执行UPDATE、DELETE、TRUNCATE等修改操作，从规则上保证表数据永久不变。

如果后续有临时修改数据的需求，只需先执行ALTER TABLE 目标表名 SET READ WRITE;解除只读限制，修改完成后重新执行冻结命令即可恢复优化效果。

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