PostgreSQL是否存在纯累积等待统计？求类SQL Server/Oracle的统计方式

1. 使用 pg_stat_wait_events（PostgreSQL 14+） #

PostgreSQL 14及以上版本原生提供了pg_stat_wait_events视图，这就是对标SQL Server sys.dm_os_wait_stats、Oracle V$SYSTEM_EVENT的累积等待统计视图，直接查询就能获取从数据库启动以来的所有等待事件累积数据：

SELECT wait_event_type, wait_event,
       count AS total_waits,
       total_time AS total_wait_time_ms,
       avg_time AS avg_wait_time_ms
FROM pg_stat_wait_events
ORDER BY total_time DESC;

该视图会自动持续累加等待次数、总等待时长、平均等待时长等核心指标，无需手动做快照对比。

2. 自定义快照持久化（适配PostgreSQL 14以下版本） #

如果你的版本低于14，没有pg_stat_wait_events，可以通过定期快照pg_stat_activity并存储到自定义表的方式，自己计算累积统计：

• 第一步：创建存储快照数据的表

CREATE TABLE wait_stats_snapshots (
    snapshot_time TIMESTAMPTZ PRIMARY KEY,
    wait_event_type TEXT,
    wait_event TEXT,
    active_count INT
);

• 第二步：定期插入快照（可通过crontab或PostgreSQL定时任务自动执行）

INSERT INTO wait_stats_snapshots
SELECT now(), wait_event_type, wait_event, count(*)
FROM pg_stat_activity
WHERE state = 'active'
GROUP BY wait_event_type, wait_event;

• 第三步：基于快照计算累积统计

SELECT 
    curr.wait_event_type,
    curr.wait_event,
    SUM(curr.active_count - prev.active_count) AS total_waits,
    SUM(EXTRACT(EPOCH FROM (curr.snapshot_time - prev.snapshot_time)) * 1000 * (curr.active_count + prev.active_count)/2) AS total_wait_time_ms
FROM wait_stats_snapshots curr
JOIN wait_stats_snapshots prev 
  ON curr.snapshot_time > prev.snapshot_time
  AND curr.wait_event_type = prev.wait_event_type
  AND curr.wait_event = prev.wait_event
GROUP BY curr.wait_event_type, curr.wait_event
ORDER BY total_wait_time_ms DESC;

3. 后台进程等待统计：pg_stat_bgwriter #

如果需要关注后台写入进程的等待情况，pg_stat_bgwriter视图提供了后台writer进程的累积统计数据，包含checkpoint、buffer写入等相关的等待时长和操作次数：

SELECT checkpoint_write_time, checkpoint_sync_time,
       buffers_checkpoint, buffers_clean,
       maxwritten_clean, buffers_backend,
       buffers_backend_fsync, buffers_alloc
FROM pg_stat_bgwriter;

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