为什么PostgreSQL的SELECT也会产生写？

PostgreSQL 的 SELECT 查询本质上是读操作，但在某些特定场景下，确实会触发写 IO（Write IO）。这通常不是直接写入用户数据，而是数据库为了维护内部状态、保证数据一致性或优化后续性能而进行的后台写入操作。

以下是导致 SELECT 查询产生写 IO 的主要原因：

1. 脏页刷新（Dirty Page Flushing）

这是最常见的原因。

• 机制：当 PostgreSQL 修改数据（INSERT/UPDATE/DELETE）时，它不会立即将数据写入磁盘，而是先修改内存中的“共享缓冲区”（Shared Buffers），并将该页面标记为“脏页”（Dirty Page）。
• SELECT 的触发作用：
  • 读取触发置换：当 SELECT 查询需要读取的数据页不在内存中时，必须从磁盘加载到共享缓冲区。如果此时缓冲区已满，数据库必须根据替换算法（如 Clock Sweep）腾出空间。
  • 写回脏页：如果被腾出的页面恰好是“脏页”，PostgreSQL 必须先将该页面写入磁盘（产生写 IO），然后才能加载新的数据页。
  • 结论：虽然 SELECT 本身不产生新脏页，但它的高并发读取可能导致缓冲区快速轮换，迫使后台进程（BgWriter 或 Checkpointer）更频繁地将旧脏页刷盘，从而在监控上表现为 SELECT 期间伴随写 IO。

2. 提示位更新（Hint Bits）

这是一个非常典型且容易被忽视的写 IO 来源，特别是在高并发读负载下。

• 机制：PostgreSQL 使用 MVCC（多版本并发控制）。每行数据都有事务 ID（xmin/xmax），表示创建和删除该行的事务。为了判断该行对当前事务是否可见，数据库通常需要检查事务提交状态。
• 写操作：如果某行的事务提交状态尚未缓存在页面头部，PostgreSQL 在第一次读取该行时，会去检查 WAL 日志确认事务已提交，然后在该数据页上设置“提示位”（Hint Bits），标记该行为“已提交”或“已回滚”，以避免下次重复检查。
• 结果：这个设置提示位的过程会修改内存中的数据页，使其变脏。随后，这个脏页会被后台进程刷写到磁盘，产生写 IO。
• 场景：在大表全表扫描或大量读取刚提交事务的数据时，这种写 IO 尤为明显。

3. 预写日志（WAL）记录

虽然普通的 SELECT 不生成 WAL，但上述的提示位更新在某些配置或特定情况下可能需要记录 WAL（取决于 wal_log_hints 参数及版本行为，通常 Hint Bit 更新不记 WAL 以节省开销，但在某些恢复场景或特定补丁下行为可能不同；不过更主要的是，Hint Bit 导致的脏页最终要落盘）。

• 注意：标准的 Hint Bit 更新通常不生成 WAL 日志（因为它是幂等的，崩溃后重新检查事务状态即可），但它确实会导致数据页变脏并产生物理写 IO。

4. 临时文件写入（Spill to Disk）

当 SELECT 查询过于复杂或处理数据量过大，内存不足以支撑操作时，PostgreSQL 会将中间结果写入磁盘上的临时文件。

• 场景：
  • 排序（ORDER BY）：当 work_mem 不足以完成排序时。
  • 哈希连接/聚合（Hash Join / Hash Aggregate）：当构建哈希表超过 work_mem 限制时。
  • 物化 CTE：某些复杂的公用表表达式。
• 结果：这些操作会产生大量的临时文件写 IO。可以通过调整 work_mem 参数来减少这种情况，但需警惕内存溢出风险。

5. 自动统计信息收集（Autovacuum）

虽然 autovacuum 是后台进程，但它往往由表的读写活动触发。

• 关联：频繁的 SELECT（特别是配合写操作）可能导致表的事务 ID 老化或死元组增加，触发 AUTOVACUUM。
• 写 IO：Vacuum 过程会清理死元组、更新可见性映射图（Visibility Map, VM）和自由空间映射图（FSM），这些操作都会产生写 IO。如果 SELECT 查询扫描了大量包含死元组的表，可能会间接加速这一过程。

6. 扩展页面的初始化

• 机制：如果 SELECT 查询触发了某些需要写入新页面的操作（例如，向一个稀疏表中插入数据的并发场景，或者某些索引扩展），或者读取了尚未初始化的扩展区域（较少见，通常发生在表刚扩展时），可能会触发零填充（Zero-filling）写入。

总结与排查建议

如果你发现 SELECT 查询期间有明显的写 IO，通常按以下优先级排查：

1. 检查临时文件使用：查看日志中是否有 “temporary file” 相关记录，或监控 pg_stat_database 中的 temp_files 和 temp_bytes。如果是排序或哈希溢出，考虑适当调大 work_mem。
2. 观察 Hint Bits 效应：如果是刚大批量导入或更新数据后立即进行全表扫描，极可能是 Hint Bits 更新导致的脏页刷写。随着时间推移，这种现象会减少。
3. 监控 BgWriter 和 Checkpoint：使用 pg_stat_bgwriter 视图。如果 buffers_checkpoint 或 buffers_clean 很高，说明后台正在努力刷脏页以腾出空间给 SELECT 读取的新页。
4. 检查 Autovacuum：确认是否有 Vacuum 进程正在运行，清理由读写混合负载产生的死元组。

一句话总结：SELECT 产生的写 IO 通常是“为了读而写”（如腾挪缓冲区、更新可见性标记）或“内存不足落盘”（如临时排序），而非直接修改用户业务数据。