使用 pg_stat_io 分析 I/O 来源（后端类型、对象、上下文）

命令

SELECT backend_type,
       object,
       context,
       reads,
       pg_size_pretty(reads * op_bytes) AS read_size,
       writes,
       pg_size_pretty(writes * op_bytes) AS write_size,
       extends,
       hits,
       round(100.0 * hits / NULLIF(hits + reads, 0), 1) AS hit_pct,
       evictions,
       reuses,
       fsyncs,
       round(read_time::numeric, 1) AS read_ms,
       round(write_time::numeric, 1) AS write_ms
FROM pg_stat_io
WHERE coalesce(reads, 0) + coalesce(writes, 0) + coalesce(extends, 0) + coalesce(evictions, 0) > 0
ORDER BY coalesce(reads, 0) + coalesce(writes, 0) DESC;

-- Who writes? Ideally most writes come from the checkpointer and background writer
SELECT backend_type,
       pg_size_pretty(sum(writes * op_bytes)) AS written,
       round(100.0 * sum(writes) / NULLIF(sum(sum(writes)) OVER (), 0), 1) AS pct_of_writes,
       sum(fsyncs) AS fsyncs
FROM pg_stat_io
WHERE object = 'relation' AND writes > 0
GROUP BY backend_type
ORDER BY sum(writes) DESC;

作用

按后端类型（client backend、autovacuum、checkpointer...）、对象（relation/temp relation）和上下文（normal、vacuum、bulkread、bulkwrite）显示读取、写入、扩展、驱逐和 fsync；以及写入来自哪些进程类型。

适用场景

在 I/O 瓶颈期间，用于判断负载来自查询、autovacuum 还是 checkpoint，并验证 shared_buffers/bgwriter 设置。

注意

pg_stat_io 是集群范围的，自上次重置以来累计；请取两次采样的差值来观察变化（重置：SELECT pg_stat_reset_shared('io');）。read_time/write_time 只有在 track_io_timing = on 时才会填充。reads 也包括由操作系统页缓存提供的读取。client backend 的写入或淘汰较高，表示后端进程自己写出缓冲区以腾出空间；请检查 shared_buffers、bgwriter_lru_maxpages 和 checkpoint 设置。context = 'vacuum'/'bulkread' 的行反映的是环形缓冲区的使用；其中的 reuses 属于正常现象。