PostgreSQL Autovacuum 调优指南

关键参数（`postgresql.conf`）

autovacuum = on
autovacuum_naptime = 10s
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.05
vacuum_cost_limit = 200

手动验证

VACUUM (VERBOSE, ANALYZE) public.items;
SELECT relname, n_dead_tup FROM pg_stat_all_tables WHERE relname = 'items';

观测与维护

• 关注 `pg_stat_user_tables` 的死元组与自动清理触发
• 根据写入速率与表大小调整 scale_factor 与 threshold

总结

合理的 autovacuum 策略能避免表膨胀并保持查询计划准确。