一文带你走进autovacuum

# 1. 控制 autovacuum 进程的唤醒间隔（默认1分钟，可缩小让它更频繁但轻量化）
autovacuum_naptime = 30s  # 每30秒唤醒一次，分散清理压力（默认1min）

# 2. 降低 autovacuum 的资源消耗（避免单轮清理占用过多CPU/IO）
autovacuum_vacuum_cost_delay = 20ms  # 每消耗一定“成本”就暂停20ms（默认0，不暂停）
autovacuum_vacuum_cost_limit = 100   # 单次清理的“成本上限”（默认-1，继承全局vacuum_cost_limit）

# 3. 避开12点高峰期：让 autovacuum 主要在凌晨3-6点运行（需配合 pg_cron）
# 先启用 pg_cron 插件（需提前安装，多数云数据库已预装）
shared_preload_libraries = 'pg_cron'  # 新增或添加到现有列表
cron.database_name = '你的数据库名'   # 指定 pg_cron 生效的数据库

# 然后在数据库中执行SQL，禁用默认自动触发，改为定时执行
-- 1. 全局禁用自动 autovacuum（仅禁用触发，保留进程）
ALTER SYSTEM SET autovacuum = off;
-- 2. 用 pg_cron 定时在凌晨3点执行全库 vacuum analyze（低峰期）
SELECT cron.schedule('daily-autovacuum', '0 3 * * *', 'VACUUM ANALYZE;');
-- 3. 可选：对大表单独定时（比如凌晨4点清理大表）
SELECT cron.schedule('vacuum-big-table', '0 4 * * *', 'VACUUM ANALYZE public.你的大表名;');

-- 给大表设置：降低触发阈值（更早清理，避免堆积后单次消耗过大）+ 调整运行时段
ALTER TABLE public.你的大表名 
SET (
  autovacuum_vacuum_threshold = 5000,  -- 死元组达到5000就触发（默认50）
  autovacuum_vacuum_scale_factor = 0.05,  -- 死元组占比达到5%也触发（默认20%）
  autovacuum_vacuum_cost_delay = 50ms  -- 单表清理时更慢，减少资源占用
);

# postgresql.conf 中调整
vacuum_cost_page_hit = 1    # 内存中命中数据页的“成本”（默认1）
vacuum_cost_page_miss = 10  # 磁盘中读取数据页的“成本”（默认10）
vacuum_cost_page_dirty = 20 # 修改脏数据页的“成本”（默认20）
autovacuum_vacuum_cost_limit = 50  # 单次清理的最大“成本”（默认-1，继承全局）

-- 查看当前正在运行的 autovacuum 进程
SELECT pid, query, state, now() - query_start AS duration 
FROM pg_stat_activity 
WHERE query LIKE '%autovacuum%' AND state = 'active';

-- 查看各表的 autovacuum 统计（比如死元组数量、最后清理时间）
SELECT 
  relname AS 表名,
  n_dead_tup AS 死元组数量,
  last_vacuum AS 最后手动清理时间,
  last_autovacuum AS 最后自动清理时间,
  autovacuum_count AS 自动清理次数
FROM pg_stat_user_tables 
ORDER BY n_dead_tup DESC;