PostgreSQL 18: 获取后端 IO 统计信息的函数 pg_stat_get_backend_io

你想要知道 PostgreSQL 中哪个后端进程的 IO 使用率较高吗？现在，PostgreSQL 提供了后端 IO 统计功能。

特性提交日志

为 pgstats 添加后端级统计功能。

本次修改在 PostgreSQL 统计系统中新增了一种可变数量的统计类型，该类型统计条目的对象 ID 键基于后端进程的进程编号生成。这一设计为同时存在的统计条目数量设定了上限，条目会在后端进程完成认证启动后创建，在后端进程退出时删除，因此统计条目仅在对应后端进程运行期间存在。数据库关闭时，这些统计条目不会写入 pgstats 文件（需注意，作为安全措施，写入文件的功能已被禁用）。

目前，这类统计仅包含后端进程产生的 IO 相关信息，其统计层级与pg_stat_io（PostgreSQL 全局 IO 统计视图）一致；不同之处在于，现在可以获取每个后端进程单独的 IO 活动量，而非所有进程 IO 活动的整体聚合数据。本次修改还新增了pg_stat_get_backend_io()函数，可通过后端进程的 PID（进程标识符）获取对应进程的 IO 统计数据。未来，根据实际需求或设计构想，这一统计结构可进一步扩展，纳入更多与后端进程相关的其他统计信息。

辅助进程（如检查点进程、后台写进程等）未被纳入这类统计。相较于普通后端进程，辅助进程的统计价值较低，因为它们在pg_stat_io中已有专门的统计条目，且拥有各自专属的统计类型。

此外，本次提交还包含pg_stat_reset_backend_stats()函数，该函数可重置单个后端进程关联的所有统计数据。

讨论：https://postgr.es/m/ZtXR+CtkEVVE/LHF@ip-10-97-1-34.eu-west-3.compute.internal

示例

本次提交引入了后端级 IO 统计功能，首次实现了按进程拆分 IO 统计的能力。这一特性解决了传统全局 IO 统计（如pg_stat_io）无法定位具体进程 IO 负载的痛点，为运维排查和性能调优提供了细粒度工具。

当数据库服务器系统 IO 使用率过高（如iostat显示磁盘使用率 100%），需快速定位哪个进程是问题源头：

-- 步骤1：关联 pg_stat_activity 与 pg_stat_get_backend_io，获取各进程的 IO 总耗时
SELECT
  a.pid,                      -- 后端进程 ID
  a.usename,                  -- 连接用户
  a.application_name,         -- 应用名称
  a.query,                    -- 当前执行的 SQL
  -- 计算该进程的 IO 总耗时（读+写+fsync，单位：毫秒）
  COALESCE(SUM(io.read_time + io.write_time + io.fsync_time), 0) AS total_io_time
FROM pg_stat_activity a
-- 左连接当前进程的 IO 统计（排除辅助进程）
LEFT JOIN pg_stat_get_backend_io(a.pid) io
WHERE a.backend_type = 'backend'  -- 仅关注业务连接进程
GROUP BY a.pid, a.usename, a.application_name, a.query
-- 按 IO 总耗时降序，取前 5 个最繁忙进程
ORDER BY total_io_time DESC
LIMIT 5;

若某进程的total_io_time远高于其他进程，且对应query是复杂查询（如全表扫描），则该进程是 IO 瓶颈源头，可进一步优化 SQL（如加索引）或终止异常会话。

另外，当发现某个进程响应缓慢，需判断是“读 IO 过多”还是“写 IO 过多”，进行针对性优化：

-- 步骤1：查看指定进程（如 PID=12345）的 IO 类型分布
SELECT
  object,          -- 目标对象类型
  context,         -- IO 上下文
  reads,           -- 读次数
  read_time,       -- 读耗时（毫秒）
  writes,          -- 写次数
  write_time       -- 写耗时（毫秒）
FROM pg_stat_get_backend_io(12345)  -- 替换为目标 PID
WHERE object IN ('relation', 'temp_file')  -- 关注核心对象类型
ORDER BY read_time + write_time DESC;

• 若reads和read_time高：可能是查询缺少索引导致全表扫描，需优化 SQL；
• 若writes和write_time高：可能是批量插入/更新操作，可考虑调整wal_buffers或使用COPY替代INSERT。

非常不错的新特性，这将会给运维人员的工作带来很多方便。感谢社区的所有相关人员。

参考

提交日志：https://git.postgresql.org/pg/commitdiff/9aea73fc61d4e77e000724ce0b2f896590a10e03