Postgres14分区表并发场景下SELECT查询出现DataFileWrite等待原因咨询

为什么只读SELECT会等待DataFileWrite事件 #

你观察到的是PostgreSQL中非常典型的场景，主要和内核的几个核心机制有关：

1. 共享缓冲区脏页淘汰触发的同步写入
   PostgreSQL的所有数据访问都需要先加载到shared_buffers共享内存中，如果当前共享内存没有足够空闲页，且待淘汰的页是未刷入磁盘的脏页时，不管是什么类型的查询，都需要先把这部分脏页同步写入磁盘才能腾出空间加载新数据。你的并发INSERT写入会产生大量脏页，如果后台刷写进程（bgwriter）跟不上脏页产生速度，就会出现SELECT查询被迫自己刷脏的情况，此时就会等待DataFileWrite类型的IO事件。
   你提供的执行计划里Planning阶段明确标注了written=11和write=0.138，说明本次查询在规划阶段就触发了写入操作，就是典型的查询过程中淘汰脏页的情况。

2. 可见性Hint Bit写入
   PostgreSQL为了加速行可见性判断，会在首次读取行的时候，把事务提交状态的hint bit写入对应数据页中，这个操作会让原本只读的SELECT查询修改数据页产生脏页。如果此时你读取的页刚好正在被后台进程刷入磁盘，或者你修改后的脏页需要立即淘汰刷盘，也会触发写入等待。

3. 分区表元数据访问的额外开销
   你使用的是分区表，查询规划阶段需要加载所有相关分区的元数据、索引信息、统计信息等系统表数据，如果这些元数据页是脏页，或者加载过程中需要淘汰脏页，也会在规划阶段就触发写入操作，和你执行计划里Planning阶段产生写入的现象完全吻合。

优化建议 #

• 调整PostgreSQL刷脏相关参数：适当调大shared_buffers（建议为系统内存的25%左右），降低bgwriter_flush_after参数值提升后台刷写进程的刷写频率，调大max_wal_size和checkpoint_timeout减少频繁检查点带来的集中刷盘压力，避免前端查询被迫刷脏。
• 非高峰时段对分区表执行VACUUM ANALYZE，提前完成所有行的hint bit设置和统计信息更新，减少业务查询过程中修改数据页的概率。
• 建议升级到PostgreSQL 14系列的最新稳定小版本，14后续版本优化了大量分区表元数据访问的性能和内存占用问题，可以大幅降低规划阶段的IO开销。
• 如果分区数量过多，建议优化分区策略，尽量让单个查询只需要访问少量分区，减少规划阶段需要加载的元数据量。

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