PG 14-PG18 vacuum&autovacuum总结，与常用脚本与大表参数调整

❝

开头还是介绍一下群，如果感兴趣PolarDB ,MongoDB ,MySQL ,PostgreSQL ,Redis, OceanBase, Sql Server等有问题，有需求都可以加群加群请联系 liuaustin3 ，（共3400人左右 1 + 2 + 3 + 4 +5 + 6 + 7 + 8 ）(1 2 3 4 5 6 7 8群已经爆满  9群 为纯聊天群，默认不加入不得发广告，自己公众号文章链接等，发一次直接踢，默认加入8群，开10群PolarDB专业学习群115+)

PostgreSQL 最与众不同的之一就是UNDO LOG 用每个表的表空间承接，基于此原理，PG需要一个回收每个表UNDO LOG空间的程序，我们俗称vacuum & autovacuum。

而vacuum的好坏直接与数据库的性能和数据库的稳定运行有关，PG14 -PG18在VACUUM和autovacuum中做了什么，我们今天来说一说。

1 MVCC 的版本周期。

MVCC (Multi-Version Concurrency Control) 是 PostgreSQL 实现并发控制的核心机制。它通过为每行数据维护多个版本，使得读写操作互不阻塞，从而在保证数据一致性的同时最大化并发性能。

之所以VACCUM重要其核心原因有三

1  表膨胀 (Bloat) 死元组占据的磁盘空间无法被重用，表文件持续增长。查询需要扫描更多页面，性能急剧下降。

2 索引膨胀 指向死元组的索引条目不会被自动清理，索引体积增大，查询效率降低。

3 事务 ID 回卷 PostgreSQL 使用 32 位事务 ID，约 42 亿后会回卷。VACUUM 冻结旧事务 ID，防止数据损坏。

事务 ID 回卷 (Transaction ID Wraparound) 这是 PostgreSQL 最严重的数据安全问题之一。当 32 位事务 ID 空间耗尽并回卷时，旧版本的数据可能被误判为"未来"的事务而不可见，导致数据丢失。

VACUUM 通过冻结 (freeze) 旧事务 ID 来防止这一问题。当 age(datfrozenxid) 接近 autovacuum_freeze_max_age 时，必须立即执行 VACUUM。

VACUUM 的三大核心功能

VACUUM 的工作类型

普通 VACUUM

锁级别与影响： 仅加共享锁（ShareLock），与常规的 SELECT、INSERT、UPDATE、DELETE 互不阻塞，对线上业务非常友好。

空间回收机制： 不会直接将磁盘空间释放还给操作系统，而是清理死元组并将空间标记为“可重用”，后续新增的数据可以直接复用这部分空间。

核心作用： 基础清理死元组，同时会进行增量式/选择性的事务冻结以预防回卷，但不会主动更新统计信息。

VACUUM ANALYZE

锁级别与影响： 同样加共享锁，完全不阻塞正常的读写查询。

空间回收机制： 机制与普通 VACUUM 一致，仅标记空间重用，不压缩物理文件。

核心作用： 在执行基础 VACUUM 清理的同时，强制触发 ANALYZE 重新收集并更新表及索引的统计信息，为查询优化器（Optimizer）生成准确的执行计划提供支撑。

VACUUM FULL

锁级别与影响： 施加最高级别的排他锁（ACCESS EXCLUSIVE），会彻底阻塞该表上的所有读写操作（包括常规 SELECT），需谨慎使用。

空间回收机制： 通过将表中的存活数据重写入一个崭新的磁盘文件中，实现彻底的物理空间回收，把空闲空间真正释放回操作系统。

核心作用： 解决严重的数据膨胀（Bloat）问题。由于需要全表重写，它不会在过程中更新查询统计信息，但依然会执行事务冻结。

VACUUM FREEZE

锁级别与影响： 仅加共享锁，不会阻塞常规读写查询。

空间回收机制： 仅标记重用空间，不进行物理截断。

核心作用： 专为防范事务 ID 回卷而生的积极模式。它会强制将表中符合条件的元组直接标记为“已冻结（Frozen）”，大幅推进表的 relfrozenxid 进度，常用于归档历史数据或应对回卷危机。

VACUUM的处理流程

Autovacuum的系统架构

操作中autovacuumde 的调度决策逻辑

1 每隔 autovacuum_naptime (默认 1min) 检查所有数据库

2 每张表是否进行autovacuum处理 dead_tuples > threshold + scale_factor × rel_tuples

3 按紧急程度排序 (接近 freeze 阈值的优先)

4 分配 Worker 执行 (不超过 max_workers 个并发)

5 等待下一轮工作

AutoVacuum 不会执行 ANALYZE 吗？ AutoVacuum 默认同时执行 VACUUM 和 ANALYZE。但如果表上有长时间运行的事务，ANALYZE 可能跳过。建议定期手动执行 ANALYZE 或使用 pg_stat_user_tables 监控统计信息的时效性。

常用autovaucum 参数配置和查询语句

-- 为大表设置更积极的清理策略
ALTER TABLE large_orders SET (
    autovacuum_vacuum_threshold = 1000,
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_analyze_threshold = 500,
    autovacuum_analyze_scale_factor = 0.01
);

-- 为高频更新的小表设置更积极的策略
ALTER TABLE session_cache SET (
    autovacuum_vacuum_threshold = 100,
    autovacuum_vacuum_scale_factor = 0.05
);

-- 为几乎不变的参考表禁用 AutoVacuum (不推荐)
ALTER TABLE country_codes SET (
    autovacuum_enabled = false
);

-- 查看表的自定义参数
SELECT relname, reloptions
FROM pg_class
WHERE reloptions IS NOT NULL
  AND reloptions::text LIKE '%autovacuum%';

-- 1. 基本 VACUUM
VACUUM table_name;

-- 2. VACUUM + ANALYZE (推荐日常使用)
VACUUM (ANALYZE) table_name;

-- 3. VACUUM FULL (需要排他锁，阻塞所有操作)
VACUUM FULL table_name;

-- 4. VACUUM FREEZE (强制冻结所有事务 ID)
VACUUM (FREEZE) table_name;

-- 5. 带详细输出的 VACUUM
VACUUM (VERBOSE, ANALYZE) table_name;

-- 6. 仅处理特定索引
VACUUM (INDEX_CLEANUP ON) table_name;

-- 7. 跳过索引清理 (加速 VACUUM)
VACUUM (INDEX_CLEANUP OFF) table_name;

-- 8. 并行 VACUUM (PG13+ 支持)
VACUUM (PARALLEL 4) table_name;

-- 9. 截断空页 (释放末尾空页给 OS)
VACUUM (TRUNCATE TRUE) table_name;

-- 10. 跳过截断 (保留空间供后续使用)
VACUUM (TRUNCATE FALSE) table_name;

-- 11. 跳过 Visibility Map 更新
VACUUM (SKIP_LOCKED) table_name;

-- 12. 处理全库所有表
VACUUM (ANALYZE);  -- 不指定表名则处理所有表

-- 13. 处理特定 schema 的所有表
-- 使用 DO 块批量处理
DO $$
DECLARE
    t record;
BEGIN
    FOR t IN SELECT schemaname, tablename
             FROM pg_tables
             WHERE schemaname = 'public'
    LOOP
        EXECUTE 'VACUUM ANALYZE ' || t.schemaname || '.' || t.tablename;
    END LOOP;
END $$;

-- 查看当前正在运行的 VACUUM
SELECT pid, datname, relid::regclass, phase,
       heap_blks_total, heap_blks_scanned,
       heap_blks_vacuumed, index_vacuum_count,
       max_dead_tuple_bytes, dead_tuple_bytes
FROM pg_stat_progress_vacuum;

-- 查看 AutoVacuum Worker 状态
SELECT pid, query, state, wait_event_type, wait_event,
       now() - query_start AS duration
FROM pg_stat_activity
WHERE query LIKE 'autovacuum:%'
ORDER BY duration DESC;

-- 查看需要紧急清理的表 (接近 freeze 阈值)
SELECT relname,
       age(relfrozenxid) AS frozen_age,
       current_setting('autovacuum_freeze_max_age')::bigint AS freeze_max,
       round(age(relfrozenxid)::numeric /
             current_setting('autovacuum_freeze_max_age')::bigint * 100, 2)
             AS pct_towards_freeze
FROM pg_class
WHERE relkind = 'r'
ORDER BY age(relfrozenxid) DESC
LIMIT 20;

-- 查看表膨胀估算
SELECT
    schemaname || '.' || relname AS table_name,
    n_live_tup,
    n_dead_tup,
    round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2)
        AS dead_pct,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

注意以上脚本部分，适用于高版本PG15及以上版本，低版本PG部分会报错，因没有某些字段或函数。

![(https://files.mdnice.com/user/47359/9067ebfa-d525-4ddf-9665-2f19e40561e6.png)

PG14 -PG18的 vacuum演进

PostgreSQL 14 PG14 VACUUM/AutoVacuum 关键改进：

并行 VACUUM 索引扫描 - 支持 VACUUM (PARALLEL n)，多个 Worker 并行处理索引清理

改进的 VACUUM 内存管理 - 更高效的死元组收集，减少 I/O

AutoVacuum 信号改进 - 更精确的唤醒机制

新增 pg_stat_progress_vacuum 增强 - 更详细的进度信息

改进的冻结逻辑 - 减少不必要的冻结操作

-- PG14 新增: 并行 VACUUM

PostgreSQL 15 PG15

VACUUM/AutoVacuum 关键改进：

VACUUM 跳过已清理页面 - 利用 Visibility Map 跳过全可见页面，大幅加速

AutoVacuum 成本限制改进 - 更智能的 I/O 节流

新增 vacuum_freeze_min_age 表级覆盖 - 更精细的冻结控制

改进的 dead tuple 存储 - 减少内存使用

VACUUM 日志改进 - 更清晰的日志输出

PostgreSQL 16 PG16

VACUUM/AutoVacuum 关键改进：

AutoVacuum 增强调度 - 改进 Worker 分配算法，优先处理最紧急的表

VACUUM 性能提升 - 优化的页面扫描算法

新增 track_activity_query_size 影响 - AutoVacuum 查询可追踪更长

改进的冻结策略 - 减少冻结风暴 (freeze storms) VACUUM 与逻辑复制协调 - 避免冲突

新增 log_autovacuum_min_duration 改进 - 更精确的日志控制

PG17 重要变化提醒

autovacuum_vacuum_cost_delay 从 2ms 改为 0ms 是一个重大行为变化。这意味着 AutoVacuum 将不再主动限速，可能在高写入负载下与查询竞争 I/O。如果你的系统对 I/O 延迟敏感，建议在升级到 PG17 后手动设置此参数。

大表专项优化

- 1. 为超大表 (100GB+) 设置独立参数
ALTER TABLE very_large_table SET (
    autovacuum_vacuum_threshold = 5000,
    autovacuum_vacuum_scale_factor = 0.005,  -- 0.5% 就触发
    autovacuum_analyze_threshold = 1000,
    autovacuum_analyze_scale_factor = 0.002,
    autovacuum_vacuum_cost_delay = '0ms',
    autovacuum_vacuum_cost_limit = 2000
);

-- 2. 为高频更新表设置激进清理
ALTER TABLE hot_update_table SET (
    autovacuum_vacuum_threshold = 100,
    autovacuum_vacuum_scale_factor = 0.01
);