如何获取PostgreSQL 9.6高事务数据库指定时间段内的库级增删改统计数据

嘿，针对你在PostgreSQL 9.6里统计指定时间段数据库级INSERT/UPDATE/DELETE操作量的需求，我整理了几个实用的方法，都是基于系统自带的功能，不用额外装插件：

方法1：聚合表级统计得到数据库总量 #

这个是你熟悉的pg_stat_user_tables的延伸思路，把所有用户表的DML统计值聚合起来，就能得到整个数据库的操作总量。

操作步骤： #

1. 先在时间段的起始点记录初始统计值：

SELECT
  SUM(n_tup_ins) AS total_ins_start,
  SUM(n_tup_upd) AS total_upd_start,
  SUM(n_tup_del) AS total_del_start
FROM pg_stat_user_tables;

把这些结果存下来——可以存在应用变量里，或者临时表中。

2. 等指定时间段（30分钟/1小时/24小时）过后，再次查询并计算差值：

SELECT
  (SUM(n_tup_ins) - <你的total_ins_start值>) AS insert_count,
  (SUM(n_tup_upd) - <你的total_upd_start值>) AS update_count,
  (SUM(n_tup_del) - <你的total_del_start值>) AS delete_count
FROM pg_stat_user_tables;

替换掉尖括号里的初始值，就能得到这段时间的DML操作数了。

注意：这个方法默认只统计用户自定义表，如果需要包含系统表，把pg_stat_user_tables换成pg_stat_all_tables即可。

方法2：直接用pg_stat_database获取数据库级统计 #

这个方法更高效，因为PostgreSQL的pg_stat_database视图本身就提供了整个数据库的DML累计统计，不用手动聚合表数据。

操作步骤： #

1. 记录时间段起始点的初始值（记得替换成你的数据库名your_db_name）：

SELECT
  tup_inserted AS total_ins_start,
  tup_updated AS total_upd_start,
  tup_deleted AS total_del_start
FROM pg_stat_database
WHERE datname = 'your_db_name';

2. 等待指定时间后，再次查询并计算差值：

SELECT
  (tup_inserted - <你的total_ins_start值>) AS insert_count,
  (tup_updated - <你的total_upd_start值>) AS update_count,
  (tup_deleted - <你的total_del_start值>) AS delete_count
FROM pg_stat_database
WHERE datname = 'your_db_name';

优势：直接返回数据库级统计，性能更好；注意点：这些统计值从数据库启动后开始累计，如果统计期间数据库重启，累计值会重置，要避开这种情况。

方法3：用pg_stat_statements做精细化统计（可选） #

如果你需要更细粒度的统计（比如按具体SQL语句分类），可以启用pg_stat_statements扩展，它能跟踪所有执行的SQL语句。

操作步骤： #

1. 先启用扩展（需要超级用户权限）：

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

2. 查询指定时间段内的DML总量：

SELECT
  SUM(CASE WHEN query ILIKE 'INSERT%' THEN calls ELSE 0 END) AS insert_count,
  SUM(CASE WHEN query ILIKE 'UPDATE%' THEN calls ELSE 0 END) AS update_count,
  SUM(CASE WHEN query ILIKE 'DELETE%' THEN calls ELSE 0 END) AS delete_count
FROM pg_stat_statements
WHERE query_start >= NOW() - INTERVAL '1 hour'; -- 替换成你需要的时间段

注意：用ILIKE是为了忽略大小写，但如果SQL语句有换行或者复杂格式，匹配可能不准确，这种情况下前面两种方法更可靠。

额外建议 #

如果需要长期跟踪统计数据，可以创建一个自定义表来存储每次的统计结果：

CREATE TABLE IF NOT EXISTS dml_stats (
  stat_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  insert_count BIGINT,
  update_count BIGINT,
  delete_count BIGINT
);

然后定期把计算好的差值插入这个表，就能生成数据库的DML操作趋势报表了。

另外要说明的是，这些系统视图的统计值是近似值，因为PostgreSQL是异步更新统计的，但对于高事务量的场景，误差基本可以忽略。

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