PostgreSQL Aurora只读副本查询冲突：参数调整风险与数据时效性疑问

报错与数据延迟的关联 #

• 报错ERROR: canceling statement due to conflict with recovery不代表副本数据已落后主库14秒以上。这个冲突源于只读副本上的长时查询占用了主库更新操作需要覆盖的数据页（Buffer pin冲突）：当备库要应用主库的WAL日志时，发现目标数据页被查询锁住，超过max_standby_streaming_delay（默认30秒）或max_standby_archive_delay（默认14秒）的等待时长后，就会终止查询抛出错误，和副本整体的数据延迟没有直接关系。
• 增大这两个参数值不会主动导致查询数据更陈旧，只是让备库遇到Buffer pin冲突时，等待更久再终止查询。如果备库本身WAL应用延迟很低，查询拿到的还是较新的数据；只有冲突发生且备库等待的这段时间内主库有新更新，才会让查询结果相对主库有短暂延迟，但这是被动等待，不是参数主动拉大数据延迟。

ReplicaLag指标与数据陈旧度的判断 #

• AWS的ReplicaLag指标主要体现WAL日志从主库发送到备库的写入延迟，并非备库已应用WAL与主库的差距（也就是实际数据的陈旧程度）。
• 可以通过以下方式获取更准确的数据延迟：
  • 查询备库的pg_stat_replication视图，其中replay_lag字段代表备库应用WAL相对于主库的延迟，这是实际数据的延迟值。
  • 执行pg_last_xact_replay_timestamp()函数，对比主库的pg_current_timestamp()，两者的差值就是备库数据的时间延迟。

Buffer pin冲突的应对方案 #

通过pg_stat_database_conflicts确认是Buffer pin复制冲突的话，调整max_standby_streaming_delay和max_standby_archive_delay是直接有效的方案。除此之外，还可以尝试：

• 优化只读副本上的长时查询，缩短运行时间，降低数据页被长时间锁定的概率。
• 对查询涉及的大表做合理分区，减少单查询占用的数据页数量。
• 如果业务允许，把长时查询转移到专门的分析型只读副本，避免影响核心业务查询。

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