京公网安备11010502063487号“autovacuum 到底什么时候跑、为什么大表一直不清理、日志怎么看” 是 PostgreSQL 运维里最常被问的问题。这篇在本机自己起一个 PostgreSQL 16.15,只把 autovacuum_naptime 调成 1s,其余阈值保持默认,用 pg_stat_user_tables 和 log_autovacuum_min_duration = 0 的日志逐个验证。
reltuples = 10000 → vacuum 门槛 = 50 + 0.2 × 10000 = 2050 ,analyze 门槛 = 50 + 0.1 × 10000 = 1050 建好之后的基线:n_dead_tup、autovacuum_count、autoanalyze_count = (0, 0, 0) 更新 2000 行、等 6 秒之后:n_dead_tup = 2000,autovacuum_count = 0,autoanalyze_count = 1 再更新 100 行(累计 2100)之后:autovacuum_count 在 0.3 s 内变成 1,n_dead_tup = 0
vacuum 门槛 = 50 + 0.2 × 行数;analyze 门槛 = 50 + 0.1 × 行数,analyze 先到。
LOG: automatic analyze of table "postgres.public.t"
LOG: automatic vacuum of table "postgres.public.t": index scans: 1
pages: 0 removed, 66 remain, 24 scanned (36.36% of total)
tuples: 1950 removed, 8692 remain, 0 are dead but not yet removable
index "t_pkey": pages: 36 in total, 0 newly deleted, 0 currently deleted, 0 reusable
index "t_v_idx": pages: 35 in total, 5 newly deleted, 5 currently deleted, 0 reusablelog_autovacuum_min_duration = 0 会把每次自动清理的页数、清掉的行数、各索引的页数记进日志。
默认参数:更新 5000 行、等 6 秒之后:n_dead_tup = 5000,autovacuum_count = 0(门槛是 50 + 0.2 × 100000 = 20050) 给 big 设了 scale_factor = 0、threshold = 1000 之后:autovacuum_count 在 1.0 s 内变成 1,n_dead_tup = 0
门槛随行数线性增长;给单张表设 autovacuum_vacuum_scale_factor = 0 加一个固定 threshold,才能及时清理。
手动 ANALYZE 之后:reltuples = 10000,autoanalyze_count = 0 插入 1500 行之后 1.0 s:reltuples = 11500,autoanalyze_count = 1
插入也算修改行数,超过门槛后统计信息自动更新,reltuples 跟上。
老事务还开着:autovacuum_count = 2,n_dead_tup = 5000
日志:tuples: 0 removed, 15000 remain, 5000 are dead but not yet removable
老事务结束后约 0.8 s:autovacuum_count = 3,n_dead_tup = 0autovacuum 照跑但回收不掉,日志写 dead but not yet removable;事务结束后才清掉。