vacuum_threshold = autovacuum_vacuum_threshold
                 + autovacuum_vacuum_scale_factor * n_live_tup

触发条件: n_dead_tup > vacuum_threshold

analyze_threshold = autovacuum_analyze_threshold
                  + autovacuum_analyze_scale_factor * n_live_tup

触发条件: n_mod_since_analyze > analyze_threshold

insert_threshold = autovacuum_vacuum_insert_threshold
                 + autovacuum_vacuum_insert_scale_factor * n_live_tup

触发条件: n_ins_since_vacuum > insert_threshold

假设表 orders 有 n_live_tup = 1,000,000 行

默认参数：
  autovacuum_vacuum_threshold = 50
  autovacuum_vacuum_scale_factor = 0.2
  autovacuum_analyze_threshold = 50
  autovacuum_analyze_scale_factor = 0.1

VACUUM 触发阈值 = 50 + 0.2 * 1,000,000 = 200,050
  -> 死元组超过 200,050 时触发 VACUUM
  -> 即约 20% 的行变成死元组时触发

ANALYZE 触发阈值 = 50 + 0.1 * 1,000,000 = 100,050
  -> 修改超过 100,050 行时触发 ANALYZE
  -> 即约 10% 的行被修改时触发

假设表 audit_log 有 n_live_tup = 20,000,000 行，只有 INSERT 没有 UPDATE/DELETE。

参数：
  autovacuum_vacuum_insert_threshold = 1000（默认）
  autovacuum_vacuum_insert_scale_factor = 0.2（默认）

INSERT 触发阈值 = 1000 + 0.2 * 20,000,000 = 4,000,100
  -> 累计插入超过 4,000,100 行时触发一次 VACUUM
  -> 该次 VACUUM 不清理死元组（没有死元组），主要工作是
     维护可见性映射与推进 relfrozenxid（防回卷水位）

-- 在 postgresql.conf 中设置（需重启）
autovacuum = on

-- 注意：关闭 autovacuum 极不推荐
-- 除非有完善的手动 VACUUM 调度方案

-- 推荐：根据 CPU 核数和磁盘 I/O 能力设置
-- 经验公式：min(CPU核数/2, 10)
ALTER SYSTEM SET autovacuum_max_workers = 6;
-- 需重启生效

-- 对于繁忙的 OLTP 系统，缩短检查间隔
ALTER SYSTEM SET autovacuum_naptime = '30s';
-- 重新加载配置即可（sighup）
SELECT pg_reload_conf();

-- 对大型高更新表设置更低的 scale_factor
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.02,  -- 2% 即触发
    autovacuum_vacuum_threshold = 1000      -- 至少 1000 死元组
);

-- 查看表的当前设置
SELECT reloptions FROM pg_class WHERE relname = 'orders';

每个 I/O 操作有成本权重：
  读取共享缓冲池中的页面: cost = 1   (vacuum_cost_page_hit)
  读取未在缓冲池的页面:   cost = 2   (vacuum_cost_page_miss)
  随机读取磁盘页面:       cost = 20  (vacuum_cost_page_dirty)

worker 执行 VACUUM 时累加成本：
  累积成本 += 每次操作的 cost

当累积成本 >= cost_limit 时：
  worker 休眠 cost_delay 毫秒
  重置累积成本为 0
  继续执行

效果：限制 VACUUM 的 I/O 吞吐量，保护在线业务

-- 对于低峰期，可以临时加速 autovacuum
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = 0;  -- 不休眠
ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 2000;  -- 提高配额
SELECT pg_reload_conf();

-- 高峰期恢复保守设置
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '5ms';
ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 500;
SELECT pg_reload_conf();

-- 对追加写大表设置 INSERT 触发（5% 新增即触发）
ALTER TABLE audit_log SET (
    autovacuum_vacuum_insert_scale_factor = 0.05,
    autovacuum_vacuum_insert_threshold = 100000
);

-- 预期效果：表新增约 5% 行数后，pg_stat_user_tables.last_autovacuum 开始更新，
-- 且 VACUUM 主要是推进 VM 与冻结水位，耗时远短于死元组清理。

-- 高频更新表：统计信息跟随更紧
ALTER TABLE orders SET (
    autovacuum_analyze_scale_factor = 0.02,
    autovacuum_analyze_threshold = 500
);

-- 验证统计信息新鲜度
SELECT relname, n_mod_since_analyze, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'orders';
-- 预期输出（示例）：
--  relname | n_mod_since_analyze |        last_analyze        |     last_autoanalyze
-- ---------+---------------------+----------------------------+---------------------------
--  orders  |               18302 | 2026-09-27 03:00:02.11+08 | 2026-09-27 09:41:18.7+08

ALTER SYSTEM SET log_autovacuum_min_duration = '1s';
SELECT pg_reload_conf();

-- 批量查看所有按表设置的 autovacuum 参数
SELECT
    c.relname,
    c.reloptions
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE c.relkind = 'r'
  AND c.reloptions::text LIKE '%autovacuum%'
ORDER BY c.relname;

-- 批量设置多张表的 scale_factor
-- 假设要为所有以 'log_' 开头的表设置激进 autovacuum
DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN
        SELECT relname FROM pg_class
        WHERE relkind = 'r' AND relname LIKE 'log_%'
    LOOP
        EXECUTE format(
            'ALTER TABLE %I SET (autovacuum_vacuum_scale_factor = 0.05)',
            r.relname
        );
    END LOOP;
END $$;

-- postgresql.conf 全局设置
autovacuum = on
autovacuum_max_workers = 6          -- 增加 worker 应对高吞吐
autovacuum_naptime = '30s'          -- 缩短检查间隔
autovacuum_vacuum_cost_delay = '1ms' -- 降低延迟提升清理速度
autovacuum_vacuum_cost_limit = 1000  -- 提高成本配额

-- 高频更新表按表设置
ALTER TABLE user_sessions SET (
    autovacuum_vacuum_scale_factor = 0.05,   -- 5% 即触发
    autovacuum_vacuum_threshold = 1000,
    autovacuum_analyze_scale_factor = 0.02   -- 2% 即更新统计
);

ALTER TABLE order_status SET (
    autovacuum_vacuum_scale_factor = 0.02,   -- 2% 即触发（极高频）
    autovacuum_vacuum_threshold = 500,
    autovacuum_vacuum_cost_delay = '0.5ms'   -- 几乎不休眠
);

-- postgresql.conf 全局设置
autovacuum = on
autovacuum_max_workers = 3          -- 保持默认，并发需求低
autovacuum_naptime = '2min'         -- 延长检查间隔
autovacuum_vacuum_cost_delay = '5ms' -- 保守，避免影响分析查询

-- 大型事实表按表设置（利用 PG13+ 的 INSERT 触发）
ALTER TABLE sales_fact SET (
    autovacuum_vacuum_insert_scale_factor = 0.05,  -- 5% INSERT 触发
    autovacuum_vacuum_insert_threshold = 100000,
    autovacuum_analyze_scale_factor = 0.05
);

-- 维度表保持默认（变更少）

-- postgresql.conf 全局设置
autovacuum = on
autovacuum_max_workers = 5
autovacuum_naptime = '45s'
autovacuum_vacuum_cost_delay = '2ms'
autovacuum_vacuum_cost_limit = 500

-- 热点表激进设置
ALTER TABLE hot_table SET (
    autovacuum_vacuum_scale_factor = 0.03,
    autovacuum_vacuum_threshold = 500
);

-- 冷数据表保守设置
ALTER TABLE cold_table SET (
    autovacuum_vacuum_scale_factor = 0.2,   -- 保持默认
    autovacuum_vacuum_threshold = 1000
);

-- 全局配置：适度激进的 autovacuum
ALTER SYSTEM SET autovacuum_naptime = '30s';
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '1ms';
ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 1000;

-- 热点表：低 scale_factor + 低 threshold
ALTER TABLE user_sessions SET (
    autovacuum_vacuum_scale_factor = 0.03,
    autovacuum_vacuum_threshold = 200,
    autovacuum_analyze_scale_factor = 0.02,
    autovacuum_analyze_threshold = 100
);

-- 定期手动 ANALYZE 保持统计信息新鲜
-- 在低峰期执行
ANALYZE user_sessions;

-- 全局配置：保守的 autovacuum
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '5ms';

-- 事实表：利用 INSERT 触发（PG13+）
ALTER TABLE sales_fact SET (
    autovacuum_vacuum_insert_scale_factor = 0.05,
    autovacuum_vacuum_insert_threshold = 100000,
    autovacuum_analyze_scale_factor = 0.05
);

-- 批量加载后手动 ANALYZE
-- ETL 流程末尾执行
ANALYZE sales_fact;

-- 对于大批量 DELETE 后的表，手动 VACUUM
-- 例如分区表的老数据清理后
VACUUM (ANALYZE, VERBOSE) old_partition;

-- 时序表按分区管理
-- 新分区：激进 autovacuum（频繁 INSERT）
ALTER TABLE metrics_2026_08 SET (
    autovacuum_vacuum_insert_scale_factor = 0.03,
    autovacuum_analyze_scale_factor = 0.02
);

-- 老分区：保守 autovacuum（只读或即将 DROP）
ALTER TABLE metrics_2026_01 SET (
    autovacuum_vacuum_scale_factor = 0.5,
    autovacuum_vacuum_threshold = 100000
);

-- 优于 VACUUM 的方案：直接 DROP 老分区
DROP TABLE metrics_2025_01;
-- 这比 VACUUM 回收空间高效得多

-- 全局配置：平衡的 autovacuum
ALTER SYSTEM SET autovacuum_max_workers = 6;
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '2ms';
ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 500;

-- 热点表：低频但快速的 VACUUM
ALTER TABLE hot_table SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_vacuum_threshold = 1000,
    autovacuum_vacuum_cost_delay = '1ms'  -- 按表覆盖cost_delay
);

-- 低峰期窗口手动 VACUUM 关键表
-- 通过 cron 调度
-- 0 3 * * * psql -c "VACUUM (ANALYZE, VERBOSE) hot_table;"

-- 全面的表级 VACUUM 状态查询
SELECT
    schemaname,                                          -- 模式名
    relname,                                             -- 表名
    n_live_tup,                                          -- 活元组数(估计)
    n_dead_tup,                                          -- 死元组数(估计)
    round(
        100.0 * n_dead_tup /
        NULLIF(n_live_tup + n_dead_tup, 0), 2
    ) AS dead_tuple_pct,                                 -- 死元组百分比
    last_vacuum,                                         -- 上次手动VACUUM
    last_autovacuum,                                     -- 上次自动VACUUM
    last_analyze,                                        -- 上次手动ANALYZE
    last_autoanalyze,                                    -- 上次自动ANALYZE
    vacuum_count,                                        -- 手动VACUUM次数
    autovacuum_count,                                    -- 自动VACUUM次数
    analyze_count,                                       -- 手动ANALYZE次数
    autoanalyze_count,                                   -- 自动ANALYZE次数
    pg_size_pretty(pg_relation_size(relid)) AS size      -- 表大小
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 30;

-- 实时 VACUUM 进度监控
SELECT
    pid,                                               -- 后端进程ID
    datname,                                           -- 数据库名
    relid::regclass AS table_name,                     -- 表名
    phase,                                             -- 当前阶段
    heap_blks_total,                                   -- 堆总块数
    heap_blks_scanned,                                 -- 已扫描块数
    heap_blks_vacuumed,                                -- 已清理块数
    index_vacuum_count,                                -- 索引清理轮数
    max_dead_tuples,                                   -- 死元组数组容量
    num_dead_tuples,                                   -- 当前死元组数
    round(
        100.0 * heap_blks_scanned / NULLIF(heap_blks_total, 0), 2
    ) AS scan_pct                                      -- 扫描进度百分比
FROM pg_stat_progress_vacuum;

-- 查看所有 VACUUM 相关会话及其等待状态
SELECT
    pid,
    usename,
    application_name,
    backend_type,                                     -- 后端类型
    state,                                            -- 会话状态
    query,                                            -- SQL语句
    wait_event_type,                                  -- 等待事件类型
    wait_event,                                       -- 等待事件
    now() - xact_start AS transaction_age,            -- 事务年龄
    now() - query_start AS query_age                  -- 查询年龄
FROM pg_stat_activity
WHERE backend_type = 'autovacuum worker'
   OR query ILIKE '%vacuum%'
ORDER BY query_start;

-- 查看阻塞 VACUUM 的会话
SELECT
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query,
    now() - blocked.query_start AS blocked_duration
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
    ON blocking.pid = ANY (pg_blocking_pids(blocked.pid))
WHERE blocked.query ILIKE '%vacuum%';

-- 设置 autovacuum 日志阈值
-- 0 = 记录所有 autovacuum 操作
-- -1 = 禁用日志（默认）
-- 正数 = 仅记录耗时超过此值(毫秒)的操作
ALTER SYSTEM SET log_autovacuum_min_duration = 0;
SELECT pg_reload_conf();

LOG:  automatic vacuum of table "mydb.public.orders":     -- [1] 自动清理开始
      index scans: 1                                       -- [2] 索引扫描轮数
      pages: 0 removed, 8421 remain                        -- [3] 页面统计
      tuples: 15234 removed, 897623 remain, 0 are dead but not yet removable -- [4]
      buffer usage: 16842 hits, 2341 misses, 456 dirtied   -- [5] 缓冲池统计
      avg read rate: 12.345 MB/s, avg write rate: 2.345 MB/s -- [6] I/O速率
      system usage: CPU: user: 1.23 s, system: 0.45 s, elapsed: 15.67 s -- [7] 资源使用
      WAL records: 12345 (full page images: 0)            -- [8] WAL统计

#!/bin/bash
# autovacuum 日志分析脚本
# 统计每日 autovacuum 运行情况

LOGFILE="/var/log/postgresql/postgresql-*.log"

echo "=== Autovacuum 日志分析报告 ==="
echo "日期: $(date)"
echo ""

# 统计每日 autovacuum 次数
echo "--- 每日 autovacuum 次数 ---"
grep "automatic vacuum of table" $LOGFILE | \
    awk '{print $1}' | \
    sort | uniq -c | sort -rn | head -10

echo ""

# 统计 autovacuum 耗时最长的表
echo "--- 耗时最长的 autovacuum (Top 10) ---"
grep -A7 "automatic vacuum of table" $LOGFILE | \
    grep "elapsed:" | \
    sed 's/.*elapsed: //' | \
    sort -t' ' -k1 -rn | head -10

echo ""

# 统计无法清理的死元组
echo "--- 无法清理死元组最多的表 ---"
grep "are dead but not yet removable" $LOGFILE | \
    sed 's/.*tuples: //' | \
    awk -F',' '{print $3}' | \
    sort -rn | head -10

-- 1. autovacuum worker 状态
SELECT
    count(*) FILTER (WHERE backend_type = 'autovacuum worker') AS active_workers,
    (SELECT setting FROM pg_settings WHERE name = 'autovacuum_max_workers') AS max_workers
FROM pg_stat_activity;

-- 2. 死元组 Top 10 表
SELECT
    relname,
    n_dead_tup,
    n_live_tup,
    round(100.0 * n_dead_tup / NULLIF(n_live_tup, 0), 2) AS dead_ratio_pct,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC
LIMIT 10;

 active_workers | max_workers
----------------+-------------
              2 | 3

     relname      | n_dead_tup | n_live_tup | dead_ratio_pct |        last_autovacuum
------------------+------------+------------+----------------+-------------------------------
  orders          |     412350 |    2100000 |          19.64 | 2026-09-27 08:12:44.9+08
  user_sessions   |      98210 |     150000 |          65.47 | 2026-09-27 02:31:07.2+08

-- 错误做法
VACUUM FULL orders;  -- 阻塞业务数小时

-- 正确做法
-- 使用 pg_repack 在线重建
CREATE EXTENSION pg_repack;
SELECT repack_table('orders');  -- 几乎不影响业务

-- 错误：反复 VACUUM 无法解决持续写入导致的膨胀
VACUUM orders;
VACUUM orders;
VACUUM orders;
-- 死元组持续产生，VACUUM 跟不上

-- 正确：调整 autovacuum 参数使其更激进
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.02,
    autovacuum_vacuum_cost_delay = '0.5ms'
);