如何追踪Postgres的autovacuum运行历史并查询对应系统表

• PostgreSQL默认的系统表没有持久化存储完整的autovacuum历史运行记录，pg_stat_user_tables仅保存累计运行次数、最近运行时间两个统计值，实例重启或统计信息重置后数据会清零，无法回溯历史运行频率。
• 你可以通过以下几种方式获取历史记录：
  1. 开启autovacuum运行日志（推荐）
     修改postgresql.conf配置文件，调整以下参数：

  # 运行时长超过该值的autovacuum任务会记录到日志，单位毫秒，设为0则记录所有任务
  log_autovacuum_min_duration = 1000
  # 日志前缀增加时间、数据库名、进程号字段，方便筛选
  log_line_prefix = '%t %d %p [%c] '
  

  配置修改后执行pg_ctl reload重载即可生效，无需重启实例，后续所有符合阈值的autovacuum运行记录都会写入数据库日志，你可以直接筛选日志获取完整的触发时间、运行时长、清理的dead tuple数量等信息。
  2. 使用高版本内置动态视图
  PostgreSQL 14及以上版本自带pg_stat_autovacuum系统视图，可以查询当前运行中、以及近期已完成的autovacuum任务的详细信息，包括表名、启动时间、已扫描的页面数、清理的dead tuple数等，该视图为内存级统计，实例重启后数据会丢失。
  3. 自建统计留存表
  如果需要长期留存历史统计数据，可自行建表定时拉取统计值，示例建表语句：

  CREATE TABLE IF NOT EXISTS autovacuum_history (
      record_time timestamptz PRIMARY KEY DEFAULT now(),
      schemaname name NOT NULL,
      relname name NOT NULL,
      autovacuum_count bigint NOT NULL,
      last_autovacuum timestamptz,
      n_dead_tup bigint NOT NULL,
      n_live_tup bigint NOT NULL
  );
  

  配合定时任务（如crontab、pg_cron）每10~30分钟执行一次插入语句，拉取目标大表的统计值存入该表，后续通过相邻两条记录的autovacuum_count差值即可算出指定时间段内的autovacuum触发频率。
• 额外提示：调整大表autovacuum阈值时，建议单独针对表设置参数，无需修改全局配置，示例语句如下，可根据你的表数据量灵活调整阈值：

  ALTER TABLE 你的大表名 SET (
      autovacuum_vacuum_scale_factor = 0.01,
      autovacuum_vacuum_threshold = 10000
  );
  

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