大删除后SELECT变慢且触发WAL归档的原因排查

1. SELECT count(*) 耗时暴涨的根源 #

当表中存在大量死元组（半量删除产生2500万死元组，后续插入新数据后总元组量进一步膨胀）时，count(*)需要扫描表内所有元组，同时逐个判断元组的可见性（依赖pg_clog/pg_xact的事务状态记录）。死元组的存在大幅增加了扫描范围与可见性检查的CPU、IO开销，直接导致查询耗时从1.5秒飙升至22.5秒。

2. SELECT触发WAL归档并等待归档完成的原因 #

这一现象并非SELECT本身生成业务数据WAL，而是源于可见性映射（VM, Visibility Map）的强制更新：

• 大量死元组存在时，VM未标记对应数据页的可见性状态，SELECT扫描数据页时，必须更新VM以记录该页是否包含需可见性检查的元组。
• VM属于关系元数据，其更新操作需要写入WAL以保证崩溃恢复的一致性，进而触发WAL归档流程。
• 由于PostgreSQL需要等待WAL归档完成后，才会确认VM更新对应的脏页刷盘操作完成，因此SELECT必须等待归档结束才能返回结果。

3. VACUUM操作恢复正常的原因 #

手动执行VACUUM会完成两个关键动作：

• 清理表内的死元组，缩小了count(*)的扫描范围；
• 更新可见性映射（VM），标记数据页的可见性状态，后续SELECT扫描时无需再修改VM，自然不会产生WAL，查询耗时也恢复至正常水平。

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