pg_stat_statements视图无事务时是否显示旧数据及近期查询方法

1. 无事务运行时，pg_stat_statements是否仅展示历史旧数据？ #

不是。pg_stat_statements会跟踪所有执行过的SQL语句——不管是在事务内执行的（比如BEGIN/COMMIT包裹的批量操作），还是单条无事务的语句（比如单独的SELECT/INSERT）。只要有新语句执行，视图就会实时更新统计数据，哪怕当前没有活跃事务在运行。如果数据库刚启动且还没执行过任何语句，视图会是空的；一旦有语句执行，就会立即加入统计。

2. pg_stat_statements的数据何时会被清理？ #

视图的统计数据不会自动定期清理，主要的清理/重置场景包括：

• 手动重置：调用SELECT pg_stat_statements_reset();，会清空所有已收集的统计数据，重新开始收集。
• 数据库重启：pg_stat_statements的统计数据存储在共享内存中，数据库重启后所有历史统计会丢失，需要重新收集。
• 达到条目上限：如果配置了pg_stat_statements.max参数（默认是5000），当统计条目数量达到这个上限时，会按照LRU（最近最少使用）原则自动淘汰最久未被执行的语句统计条目。

3. 查询过去几分钟内执行语句的实现方法 #

pg_stat_statements本身只存储聚合统计数据，不记录单条语句的执行时间戳，无法直接筛选出“过去N分钟内执行的语句”。可以通过以下方式实现：

方案：自定义统计历史表+定时刷新 #

1. 创建用于存储带时间戳的统计数据的表：

CREATE TABLE query_stats_history (
    queryid bigint PRIMARY KEY,
    query text,
    calls bigint,
    total_time double precision,
    min_time double precision,
    max_time double precision,
    mean_time double precision,
    stddev_time double precision,
    recorded_at timestamp with time zone DEFAULT now()
);

2. 创建刷新统计数据的函数：

CREATE OR REPLACE FUNCTION refresh_query_stats()
RETURNS void AS $$
BEGIN
    INSERT INTO query_stats_history (queryid, query, calls, total_time, min_time, max_time, mean_time, stddev_time)
    SELECT queryid, query, calls, total_time, min_time, max_time, mean_time, stddev_time
    FROM pg_stat_statements
    ON CONFLICT (queryid) DO UPDATE
    SET calls = EXCLUDED.calls,
        total_time = EXCLUDED.total_time,
        min_time = EXCLUDED.min_time,
        max_time = EXCLUDED.max_time,
        mean_time = EXCLUDED.mean_time,
        stddev_time = EXCLUDED.stddev_time,
        recorded_at = now();
END;
$$ LANGUAGE plpgsql;

3. 配置定时任务（需要先安装pg_cron扩展），比如每分钟刷新一次：

-- 先安装pg_cron（如果未安装）
CREATE EXTENSION IF NOT EXISTS pg_cron;
-- 定时执行刷新函数
SELECT cron.schedule('refresh-query-stats', '* * * * *', 'SELECT refresh_query_stats();');

4. 查询过去5分钟内的统计数据：

SELECT * FROM query_stats_history
WHERE recorded_at >= now() - interval '5 minutes';

这个方案通过定时快照pg_stat_statements的数据并加上时间戳，实现了按时间范围筛选历史执行语句的统计信息。

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