PostgreSQL Autovacuum 参数调优与监控实战

全局参数（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;