PostgreSQL Autovacuum 参数调优与监控实战

---

title: PostgreSQL Autovacuum 参数调优与监控实战

keywords: autovacuum, analyze_scale_factor, vacuum_threshold, vacuum_cost_limit, pg_stat_all_tables

description: 调整 Autovacuum 全局与表级参数，结合统计视图监控与人工维护，保证膨胀与统计更新受控。

tags:

• PostgreSQL
• analyze_scale_factor
• autovacuum
• pg_stat_all_tables
• vacuum_cost_limit
• vacuum_threshold
• 数据库
• 维护

categories:

• 文章资讯
• 技术教程

---

全局参数（postgresql.conf）：

autovacuum = on
autovacuum_naptime = '10s'
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.2
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.1
vacuum_cost_limit = 200
vacuum_cost_delay = '20ms'

表级覆盖（高写入表）：

ALTER TABLE public.events SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_analyze_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 1000,
  autovacuum_analyze_threshold = 500
);

监控与人工维护：

SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum,
       last_analyze, last_autoanalyze
FROM pg_stat_all_tables
WHERE schemaname = 'public'
ORDER BY n_dead_tup DESC
LIMIT 20;

VACUUM (VERBOSE, ANALYZE) public.events;