阮胜昌的博客-Linux&MySQL数据库运维

你好,我是昌哥,进免费技术交流群或咨询加微信: rscpass

一、前置确认：这个参数到底存不存在

先回答标题之外的第一个问题：PostgreSQL 里到底有没有 `autovacuum_vacuum_insert_threshold` 这个参数。

答案是有，而且是确定的。这个参数是 PostgreSQL 13 新增的，由 commit b07642db 引入，作者是 Laurenz Albe 和 Darafei Praliaskouski，提交时间是 2020 年 3 月 28 日。它和 `autovacuum_vacuum_insert_scale_factor` 是一对，专门管"按插入行数"触发 VACUUM 这件事，而不是按死元组数量。

它的基本属性如下：

类型是整数，默认值是 1000 行，最小值是 -1，最大值是 2147483647。上下文是 sighup，也就是说改完不用重启实例，reload 就生效。它属于 autovacuum 这一组配置，可以在 postgresql.conf 里设全局值，也可以通过表的 storage parameter 做单表覆盖。

把 -1 赋给它，含义是关闭"按插入量"触发 VACUUM 这条路径。这条路径关掉之后，表并不会因此完全不 vacuum，它还会因为死元组积累、以及最关键的 anti-wraparound 而被清理。这个 -1 我后面会专门讲，因为我在生产环境里见过太多人误设。

那么既然参数存在、版本也对得上，接下来就按它原本要解决的问题往下讲。

二、它解决的是哪个历史遗留问题

要理解这个参数，得先理解它出现之前，autovacuum 是怎么决定一张表要不要 vacuum 的。

在 PostgreSQL 13 之前，autovacuum 判断一张表该不该 vacuum，看的是死元组数量。公式是：

vacuum threshold = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples

默认 50 + 0.2 × 表行数。死元组超过这条线，就排一个 worker 去 vacuum。

这套逻辑背后的假设是：vacuum 的价值在于回收死元组占用的空间。插入不产生死元组，所以插入为主的表不需要 vacuum。

这个假设看起来有道理，实际上错了，而且错的方式很隐蔽。它要等到表已经长成庞然大物、某一个时刻被 forced 着去做 vacuum 的时候，才会让你知道错了。

具体来说，插入为主的表也需要 vacuum，原因有两个，这两个原因都跟"回收空间"无关。

第一个是 visibility map。VACUUM 会扫描堆并刷新 visibility map，把那些页标记成 all-visible。优化器在决定是否走 index-only scan 时，会查 visibility map。一张插入量很大的表如果长期不做 vacuum，越来越多页就处于 not-all-visible 状态，index-only scan 这个优化就在你毫无察觉的情况下悄悄失效了。查询计划还是那个计划，但 heap fetch 变多了，慢下来得毫无征兆。

第二个更严重，是事务 ID 冻结。插入的元组带着一个 xmin，这个 xmin 必须在事务 ID wraparound 之前被冻结掉。在旧的触发逻辑下，一张纯插入表可能从来没被 autovacuum 光顾过，于是它第一次被 vacuum，就是 anti-wraparound vacuum。

anti-wraparound 这个东西有几个很讨厌的特性。它往往来得又晚又猛，要冻结整张表所有尚未冻结的页，可能要扫遍整个巨大的表。它很可能落在业务高峰。而且它不会因为你别的会话被阻塞就主动放弃——它会把等着拿 ACCESS EXCLUSIVE 锁的 DDL 全部堵住，被堵住的 DDL 又会堵住所有访问这张表的请求，处理直接停摆。

更要命的是，新集群或者刚 pg_restore 出来的集群，很多大表可能共享同一个 relfrozenxid。结果是：某一天，大量表在同一时刻集体达到阈值，同时需要 anti-wraparound vacuum。worker 被占满，其他表没人管。

PostgreSQL 13 给出的解法，不是把老的死元组公式改坏，而是并行地加一条独立的触发路径：看插入量。这就是 `autovacuum_vacuum_insert_threshold` 和 `autovacuum_vacuum_insert_scale_factor` 的由来。用独立的参数，是为了让"插入触发的 vacuum"能单独调优，甚至可以单独彻底关闭。

三、触发公式怎么算，以及 PG 18 的改进

公式本身是线性的，没什么玄学：

vacuum insert threshold = autovacuum_vacuum_insert_threshold + autovacuum_vacuum_insert_scale_factor × reltuples

自上次 vacuum 以来插入的行数超过这个值，autovacuum 就安排一次 vacuum。默认值 1000 + 0.2 × 表行数。

这里有个细节值得记一下：自 PG 18 起，scale factor 那一项要再乘以表的未冻结比例。公式变成：

vacuum insert threshold = autovacuum_vacuum_insert_threshold + autovacuum_vacuum_insert_scale_factor × reltuples × (1 - relallfrozen / relpages)

其中 relallfrozen 是 PG 18 新增的 pg_class 字段，用来直观展示冻结进度。

这个改动是有意义的。一张表在早期生命周期里大部分页都还没冻结，所以插入驱动的 VACUUM 会跑得比较勤，这时候正好有冻结的活要干，多做几次不亏。等到表里大部分页都稳定冻结了，阈值会自动抬高，插入触发的 VACUUM 就变少了。带来的直接好处是：大且基本静态的表，那种"表已经稳定了还被反复 VACUUM"的无效开销大幅减少。

如果你还在 PG 17 及更早版本，享受不到这个优化，只能靠手动把 scale factor 调大来压低频率。这也是我建议有能力就升 PG 18 的理由之一。

还有一点，这种由插入触发的 vacuum，不会跳过 index cleanup。这个设计是刻意的：插入为主的表很可能一直凑不够死元组去触发一次常规 vacuum，如果这时候还跳过索引清理，索引就可能慢慢膨胀。所以当你为了冻结效果去调这些参数时，索引是被照顾到的，不用额外担心。

四、参数优化建议

进入实操。我的建议可以用一句话概括：全局保持默认，只对真正插入密集的表做表级覆盖。

第一，全局不动。0.2 这个默认值比老的那套 `autovacuum_vacuum_scale_factor` 的默认 0.2 + 50 要合理，插入场景本来就该更激进一点。你一改全局，影响的是全库所有表，很可能把小表的触发频率搞乱。

第二，只对真正插入密集的大表做表级覆盖。典型的候选是事件表、日志表、审计流水、时序数据这些 append-only 或 append-mostly 的表。推荐把 scale factor 设到 0.01 ~ 0.02 之间，同时把 threshold 放大：

ALTER TABLE events SET (
    autovacuum_vacuum_insert_scale_factor = 0.02,
    autovacuum_vacuum_insert_threshold  = 10000
);

为什么 threshold 要跟着放大？因为你要避免的是"表短暂安静一下，insert 计数器刚归零，紧接着又插入几百行就再次触发"这种抖动。给一个足够大的 threshold，是给表一个喘息的余量。

反过来，如果你要调的是一张小表，scale factor 就不太合适了。即使很小的百分比，算出来的行数也可能比固定阈值 1000 还小。这种情况下直接把 scale factor 置零、用固定行数更干净：

ALTER TABLE app_settings SET (
    autovacuum_vacuum_insert_scale_factor = 0.0,
    autovacuum_vacuum_insert_threshold     = 500
);

第三，记得和清理强度预算一起看。插入触发变勤快之后，单次 vacuum 的工作量其实是变小的，但如果同时有多个大表都在频繁触发，总的 I/O 开销并不低。表级可以把 cost limit 和 delay 配起来，让 vacuum 干得快但又不饿死前台查询：

ALTER TABLE events SET (
    autovacuum_vacuum_cost_delay = 10,
    autovacuum_vacuum_cost_limit = 1000
);

一般规律是先抬高 cost_limit，再考虑降低 delay。如果一上来就把 delay 压得很低，前台查询的 I/O 会被挤。

第四，worker 数量和内存要给够。autovacuum_max_workers 默认 3 偏保守。表多的库，把 workers 加到 5 ~ 8、naptime 从 60 秒缩到 10 ~ 20 秒，配合更激进的触发阈值，工作就被摊平到了一天里，而不是攒成几次尖峰。同时 `autovacuum_work_mem` 要给到 256MB ~ 512MB 量级，这个值是以每个 worker 为单位的，别忘了乘以 worker 数来估内存。

第五，对真正的 append-only 大表，把 `vacuum_freeze_min_age` 往下调。这个参数控制一行要老到什么程度才会被冻结。对纯插入、行基本不会被改动的表，可以放心设成 0：

ALTER TABLE large_append_only SET (
    autovacuum_vacuum_insert_scale_factor = 0.01,
    autovacuum_vacuum_insert_threshold    = 10000,
    autovacuum_freeze_min_age             = 0
);

效果是：每次由插入触发的 vacuum 扫到一页时，不仅把它标成 all-visible，还直接标成 all-frozen，后续 vacuum 就可以跳过这一页。冻结被摊进了日常，而不是留给某一天的 forced vacuum。

但要特别注意，这个 0 只适用于真正不再被更新的表。如果行很快会被改，刚冻结又被解冻重拿 XID，那就是白干。

五、参数避坑的方法

我列几个生产环境里高频踩的坑。

坑一：设成 -1 然后忘记。`autovacuum_vacuum_insert_threshold = -1` 会关闭这张表的插入触发路径。基本没有理由这么做。如果某天你在库里看到这张表被设了 -1，大概率是上一个运维的临时止血，或者是从别的库无脑抄过来的。排查卡顿的时候先查这个：

SELECT relname, reloptions
FROM pg_class
WHERE reloptions::text LIKE '%insert_threshold%'
   OR reloptions::text LIKE '%insert_scale_factor%';

坑二：只调 scale factor 不调 threshold。结果小表被频繁 vacuum，worker 被占满，大表反而排队排不上。scale factor 和 threshold 要成对改，这是一组参数，不是两个独立开关。

坑三：以为这个参数管所有 vacuum 触发。它只管"插入"。UPDATE 和 DELETE 的死元组走的是 `autovacuum_vacuum_threshold` 和 `autovacuum_vacuum_scale_factor` 那条老公式。如果你的表是混合负载，两边都要调，只改插入这组参数等于只做了一半。

坑四：以为调了它就能回收磁盘空间。不行。普通 VACUUM 只是把空间在文件内部标记成可复用，文件本身不会缩小。只有文件末尾的空页会被 truncate 掉。表真的膨胀了想回收空间，要走 `pg_repack` 之类在线重写工具，或者用 VACUUM FULL——后者要拿 ACCESS EXCLUSIVE 锁，大表上等于一次停服，生产慎用。

坑五：把 `vacuum_freeze_min_age` 全局设成 0。前面说了，只对纯插入表有意义。全局这么设，会让频繁被更新的表反复冻结刚被改动过的行，额外开销很大。另外冻结会提前丢失"到底是哪个事务插入或修改了这一行"的信息，排障的时候你会想念这个细节。

坑六：忘了监控。没有观测就谈不上调优。至少要把下面这些放进日常巡检：

SELECT c.relname,

       s.n_dead_tup,

       s.n_ins_since_vacuum,

       s.last_autovacuum,

       s.last_vacuum,

       age(c.relfrozenxid) AS xid_age

FROM pg_stat_user_tables AS s

JOIN pg_class AS c ON c.oid = s.relid

WHERE s.n_ins_since_vacuum > 1000000

   OR age(c.relfrozenxid) > 500000000

ORDER BY s.n_ins_since_vacuum DESC;

 

插入量堆积、冻结年龄、上次 vacuum 时间，这三个指标缺一个都看不全。

坑七：分区表的 ANALYZE 盲区。分区表本身不存元组，autovacuum 不处理它，只处理具体的分区。于是分区表整体的统计信息可能长期不准，导致引用分区表的查询计划走偏。首次灌入数据后以及数据分布显著变化时，要手动对分区表做 ANALYZE。

坑八：长事务。长事务会拖住 xmin 视界，间接影响 VACUUM 能回收多少东西。设一个 `idle_in_transaction_session_timeout`，把挂在那里的会话清掉，这比调任何 vacuum 参数都立竿见影。

六、生产环境最佳配置建议

我把上面的内容收敛成一份可以直接落地的清单，按层次来说。

全局层面，保持两个插入参数的默认值不动：

autovacuum = on
track_counts = on
autovacuum_vacuum_insert_threshold = 1000
autovacuum_vacuum_insert_scale_factor = 0.2

`track_counts` 必须开，否则 autovacuum 无法工作，这一点经常被忽略。同时给 workers 留够余量：

autovacuum_max_workers = 5
autovacuum_naptime    = '20s'
autovacuum_work_mem   = '256MB'

表级层面，分类处理。事件、日志、审计这类 append-only 大表：

ALTER TABLE events SET (
    autovacuum_vacuum_insert_scale_factor = 0.01,
    autovacuum_vacuum_insert_threshold    = 10000,
    autovacuum_vacuum_cost_limit          = 2000,
    autovacuum_freeze_min_age             = 0
);

更新频繁的热表，重点是另一组参数：

ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor  = 0.01,
    autovacuum_vacuum_threshold     = 5000,
    autovacuum_analyze_scale_factor = 0.05,
    autovacuum_analyze_threshold    = 5000
);

小表、配置表，用固定阈值绕开比例因子：

ALTER TABLE app_settings SET (
    autovacuum_vacuum_insert_scale_factor = 0.0,
    autovacuum_vacuum_insert_threshold     = 500
);

冻结层面，一般负载可以把 `autovacuum_freeze_max_age` 从默认 2 亿往上调到 4 亿 ~ 5 亿，减少不必要的 anti-wraparound vacuum 发生频率。前提是你的常规 vacuum 周期工作正常。但如果库里全是 append-only 表，那就反过来，把单表的 `autovacuum_freeze_max_age` 设小、配合 `vacuum_freeze_min_age = 0`，让冻结被切成小块。

版本层面，优先 PG 18。它的插入触发公式引入了未冻结比例因子，加上 eager freezing 和新的 `autovacuum_vacuum_max_threshold` 上限，大表的 vacuum 行为明显更合理。还在 PG 12 的同学，这一整套机制你是没有的，升级本身就是最有效的调优。

最后提醒一句，参数调完不等于结束。改完之后要观察几个周期，看 `pg_stat_user_tables` 里 `last_autovacuum` 的间隔是否稳定、`n_ins_since_vacuum` 是否不再堆积、以及 `age(relfrozenxid)` 的增长曲线是否平滑。出现尖峰就说明触发太稀，出现频繁到影响 I/O 就说明太密。在这个区间里找一个你能接受的平衡点，那就是这张表的最优配置。

字里行间，幸得你驻足品读。我是昌哥，欢迎关注与留言，共赴一场思想的碰撞。
技能提升|培训认证|数据救援|运维保障 找昌哥 微信：rscpass

-----------------------END-----------------------
各大平台都可以找到我：
 ————————————————————
公众号：昌哥知识星球
技术博客:http://www.linuxmysql.com
墨天轮：https://www.modb.pro/u/427810
CSDN ：https://blog.csdn.net/rscpass
51CTO: https://blog.51cto.com/u_16068254
博客园：https://home.cnblogs.com/u/rscpass
知乎:https://www.zhihu.com/people/shukuinfo
掘金：https://juejin.cn/user/2801995051703454
百家号：https://author.baidu.com/home/1780697309880431

作者：阮胜昌 拥有:MySQL8.0 OCP、Oracle OCP、TIDB PCTA/PCTP/PCSD、Kingbase KCP,软考中级数据库系统工程师、RHCE7.0等行业认证 擅长主流数据库MySQL、Oracle、PostgreSQL的备份恢复,SQL调优、监控运维、故障应急处理等
可提供的技术服务：
1.数据库故障处理/疑难杂症远程支援
2.MySQL/PG/Oracle/SQLSERVER数据库技术服务

欢迎关注我的博客:http://www.linuxmysql.com 一起学习新知识！
技能提升|培训认证|数据救援|运维保障  微信：rscpass