PostgreSQL autovacuum详解（自动化清理空间）

---

1. 什么是autovacuum

PostgreSQL的autovacuum是一种自动化的维护工具，用于管理数据库中的空间和性能，以提高数据库的效率和可靠性。

2. autovacuum的作用

1. 自动回收空间：当数据库中的数据被删除或更新时，空间不会立即释放，而是被标记为可重用。autovacuum会定期检查数据库中的空间使用情况，并回收被标记为可重用的空间，以便将其重新分配给新的数据。

2. 避免表膨胀：当表中的数据被频繁删除或更新时，表可能会变得膨胀，即占用的空间超过实际需要的空间。autovacuum会自动检测并处理这种情况，以避免表膨胀导致性能下降。

3. 维护统计信息：autovacuum还会自动更新数据库中的统计信息，这些统计信息用于查询优化器生成最优的执行计划。通过定期更新统计信息，autovacuum可以帮助数据库优化查询性能。

4. 避免长时间锁定：当autovacuum执行时，它会获取表级别的锁定，以确保数据的一致性。然而，autovacuum会尽量避免长时间锁定，以允许其他查询和操作继续进行。

3. 如何开启autovacuum

默认情况下 autovacuum 是自动开启，但要正常工作还需要开启 track_counts，该参数也是默认开启的。

select name,setting,short_desc from pg_settings where name='track_counts'
union all
select name,setting,short_desc from pg_settings where name ='autovacuum';

4. autovacuum相关参数

# 默认为on，表示是否开启autovacuum, 自动执行VACUUM和ANALYZE命令。默认开启。特别的，当需要冻结xid时，尽管此值为off，PG也会进行vacuum。
autovacuum=on

# 两次vacuum间隔时间，默认10min。 这个naptime会被vacuum launcher分配到每个DB上。autovacuum_naptime/num of db。
autovacuum_naptime=1min

# 在规定时长内未完成的vacuum予以记录日志，单位ms，当vacuum动作超过此值时。 "-1"表示不记录。"0"表示每次都记录。
log_autovacuum_min_duration=500

# 默认值是3。 autovacuum最大线程数，CPU核多，并且IO好的情况下，可增加此值。
autovacuum_max_workers=3

# 每个worker可使用的最大内存数。
autovacuum_work_mem=64MB

# 当update,delete的tuples数量超过 autovacuum_vacuum_scale_factor * table_size + autovacuum_vacuum_threshold 时，进行vacuum。如果要使vacuum清理频繁，则将以下两个参数改小。
# 清理阀值，与autovacuum_vacuum_scale_factor配合使用，默认50。
autovacuum_vacuum_threshold=50
# 清理的缩放系数，默认值为0.2。
autovacuum_vacuum_scale_factor=0.2

# 以下两个参数控制analyze运行，和上面的两个参数配置类似。
# 当update,insert,delete的tuples数量超过 autovacuum_analyze_scale_factor * table_size + autovacuum_analyze_threshold时，进行analyze。
# analyze阀值，与autovacuum_analyze_scale_factor配合使用，默认50。
autovacuum_analyze_threshold=50
# analyze的缩放系数，默认值为0.1。
autovacuum_analyze_scale_factor=0.1

# 设置需要强制对数据库进行清理的XID上限值。
autovacuum_freeze_max_age=200000000
autovacuum_multixact_freeze_max_age=400000000

# 运行一次vacuum的时长，如果超过此值则休眠然后继续vacuum(vacuum很好I/O)，如果为-1，取vacuum_cost_delay值。
autovacuum_vacuum_cost_delay=20ms

# 这个值是所有worker的累加值，如果为-1，取vacuum_cost_limit的值
autovacuum_vacuum_cost_limit=200

4.1 触发条件

autovacuum会在两种情况下会被触发：

1、当update,delete的tuples数量超过 autovacuum_vacuum_scale_factor * table_size + autovacuum_vacuum_threshold

2、指定表上事务的最大年龄配置参数 autovacuum_freeze_max_age，默认为2亿，达到这个阀值将触发 autovacuum 进程，从而避免 wraparound。

4.2 参数建议

1、autovacuum_max_workers的建议值为CPU核数/3。CPU资源充足，I/O性能较好时，可以适当加大。

2、对于更新频繁的交易系统，如果系统资源充足，可以缩小autovacuum_vacuum_scale_factor 与 autovacuum_vacuum_threshold，让vacuum清理频繁

4.3 更改系统autovacuum相关参数

-- 一个比例因子，用于确定何时触发自动化清理（vacuum）操作。较低的值表示更频繁的清理操作
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.03;

-- 一个比例因子，用于确定何时触发自动化分析（analyze）操作。较低的值表示更频繁的分析操作
ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.03;

-- 一个阈值，用于确定何时触发自动化清理操作。当表中的已删除行的数量超过该阈值时，自动化清理操作将被触发
ALTER SYSTEM SET autovacuum_vacuum_threshold = 300;

-- 一个阈值，用于确定何时触发自动化分析操作。当表中的已更新行的数量超过该阈值时，自动化分析操作将被触发
ALTER SYSTEM SET autovacuum_analyze_threshold = 300;

-- 重新加载PostgreSQL的配置文件，不会重新启动数据库服务
select pg_reload_conf ();

4.4 更改单表autovacuum相关参数

alter table tableA set (autovacuum_vacuum_scale_factor=0.03);