上面已经介绍过了以下设置表级 AUTOVACUUM 相关参数和 autovacuum_max_workers：
ALTER TABLE pgbench_accounts SET (autovacuum_vacuum_scale_factor = 0.1, autovacuum_vacuum_threshold = 2000);ALTER TABLE pgbench_accounts SET (autovacuum_analyze_scale_factor = 0.05, autovacuum_analyze_threshold = 2000);下面就以下常用 VACUUM 参数详细介绍如何进行调优。
maintenance_work_mem 参数
#maintenance_work_mem = 64MB # min 1MB#autovacuum_work_mem = -1 # min 1MB, or -1 to use maintenance_work_memvacuum_cost_delay 参数
#vacuum_cost_delay = 0#autovacuum_vacuum_cost_delay = 20msvacuum_cost_limit 参数
#vacuum_cost_limit = 200#autovacuum_vacuum_cost_limit = -1使用 pgbench 生成 5000 万测试数据。
pgbench -i -s 500 alvindb设置表级 AUTOVACUUM 相关参数：
ALTER TABLE pgbench_accounts SET (autovacuum_vacuum_scale_factor = 0.1, autovacuum_vacuum_threshold = 2000);ALTER TABLE pgbench_accounts SET (autovacuum_analyze_scale_factor = 0.05, autovacuum_analyze_threshold = 2000);删除 2000 万数据：
DELETE FROM pgbench_accounts WHERE aid>=1500001 AND aid <=3500000;DELETE FROM pgbench_accounts WHERE aid>=15000001 AND aid <=17000000;DELETE FROM pgbench_accounts WHERE aid>=25000001 AND aid <=28000000;DELETE FROM pgbench_accounts WHERE aid>=35000001 AND aid <=38000000;DELETE FROM pgbench_accounts WHERE aid>=40000001; 另外，将 autovacuum_naptime 设置为 3min 以给删除数据留够时间。
alvindb=> SHOW autovacuum_naptime; autovacuum_naptime -------------------- 3min(1 row)首先采用默认参数：
SHOW maintenance_work_mem; maintenance_work_mem ---------------------- 64MB(1 row)SHOW autovacuum_work_mem; autovacuum_work_mem --------------------- -1(1 row)SHOW autovacuum_vacuum_cost_delay; autovacuum_vacuum_cost_delay ------------------------------ 20ms(1 row)SHOW autovacuum_vacuum_cost_limit; autovacuum_vacuum_cost_limit ------------------------------ -1(1 row)SHOW vacuum_cost_limit; vacuum_cost_limit ------------------- 200(1 row)执行测试用例的同时，通过如下 SQL 查询
SELECT * FROM pg_stat_activity WHERE backend_type ~ 'autovacuum worker' AND pid <> pg_backend_pid();\watch 1可以看到， AUTOVACUUM 已触发，并且 autovacuum worker 已启动，先是 query 为空，而后 query 为 VACUUM ANALYZE public.pgbench_accounts。
2021年11月07日 星期日 23时17分09秒 (every 1s)-[ RECORD 1 ]----+------------------------------datid | 37509datname | alvindbpid | 16660usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | 2021-11-07 23:17:09.427627+08xact_start | 2021-11-07 23:17:09.426378+08query_start | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | 13180269query | backend_type | autovacuum worker2021年11月07日 星期日 23时17分10秒 (every 1s)-[ RECORD 1 ]----+---------------------------------------------------datid | 37509datname | alvindbpid | 16660usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | 2021-11-07 23:17:09.427627+08xact_start | 2021-11-07 23:17:09.459083+08query_start | 2021-11-07 23:17:09.459083+08state_change | 2021-11-07 23:17:09.459084+08wait_event_type | wait_event | state | activebackend_xid | backend_xmin | 13180269query | autovacuum: VACUUM ANALYZE public.pgbench_accountsbackend_type | autovacuum worker完成后，根据 last_autoanalyze 和 last_autovacuum 得知是先做了 VACUUM，然后做 ANALYZE:
schemaname | publicrelname | pgbench_accountsautovacuum_vacuum_scale_factor | 0.1autovacuum_vacuum_threshold | 2000autovacuum_analyze_scale_factor | 0.05autovacuum_analyze_threshold | 2000n_live_tup | 30000000reltuples | 30000000autovacuum_analyze_trigger | 1502001n_mod_since_analyze | 0rows_to_mod_before_analyze | 1502001last_autoanalyze | 2021-11-07 23:22:41.640812+08autovacuum_vacuum_trigger | 3002001n_dead_tup | 142848rows_to_delete_before_vacuum | 2859153last_autovacuum | 2021-11-07 23:22:14.06792+08查看 PostgreSQL 日志，得知 VACUUM 用时 304.60 s，ANALYZE 用时 27.56 s。
[ 2021-11-07 23:22:14.067 CST 16660 6187edf5.4114 1 3/238595 13180270]LOG: automatic vacuum of table "alvindb.public.pgbench_accounts": index scans: 2 pages: 35699 removed, 165914 remain, 576 skipped due to pins, 0 skipped frozen tuples: 6190880 removed, 30142834 remain, 0 are dead but not yet removable, oldest xmin: 13180269 buffer usage: 419596 hits, 40795 misses, 100492 dirtied avg read rate: 4.185 MB/s, avg write rate: 10.310 MB/s system usage: CPU: user: 14.02 s, system: 1.41 s, elapsed: 304.60 s[ 2021-11-07 23:22:41.640 CST 16660 6187edf5.4114 2 3/238596 13180271]LOG: automatic analyze of table "alvindb.public.pgbench_accounts" system usage: CPU: user: 3.25 s, system: 7.58 s, elapsed: 27.56 s通过调整参数，逐步并反复测试，结果如下：
| maintenance_work_mem | autovacuum_vacuum_cost_delay | vacuum_cost_limit | AUTOVACUUM 用时 |
|---|---|---|---|
| 64MB | 20ms | 200 | 304.60 s + 27.56 s |
| 64MB | 2ms | 200 | 39.45 s +8.73 s |
| 64MB | 2ms | 2000 | 18.79 s + 5.50 s |
| 64MB | 2ms | 200 | 42.04 s + 8.00 s |
| 64MB | 20ms | 200 | 329.72 s + 22.82 s |
| 64MB | 0ms | 2000 | 17.72 s + 3.45 s |
| 512MB | 0ms | 2000 | 12.75 s + 3.35 s |
| 64MB | 0ms | 2000 | 15.13 s + 5.45 s |
根据如上测试，可以验证，适当增大 autovacuum_work_mem 和 autovacuum_vacuum_cost_limit、减少 autovacuum_vacuum_cost_delay 可提高 AUTOVACUUM 性能。
下面测试手动 VACUUM ANALYZE。测试方法基本与 AUTOVACUUM 一样。
这里主要简单测试下 maintenance_work_mem，其他不再重复测试。
关闭 autovacuum 并将 vacuum_cost_delay 设置为 0，并手动执行如下 SQL:
VACUUM ANALYZE pgbench_accounts;用时统计如下：
| maintenance_work_mem | vacuum_cost_delay | vacuum_cost_limit | AUTOVACUUM 用时 |
|---|---|---|---|
| 64MB | 0ms | 2000 | 23.137 s |
| 128MB | 0ms | 2000 | 18.284 s |
| 64MB | 0ms | 2000 | 24.144 s |
根据如上测试，可以验证，适当增大 maintenance_work_mem 和 vacuum_cost_limit、减少 vacuum_cost_delay 可提高 AUTOVACUUM 性能。
与 AUTOVACUUM 不同的是，手动 VACUUM 可以通过如下方式设置参数。这样，在实际工作中，就可以灵活调整参数而不需要改配置文件了。
SET vacuum_cost_delay = 10;VACUUM ANALYZE pgbench_accounts;关注 DBA Daily 公众号，第一时间收到文章的更新。
通过一线 DBA 的日常工作，学习实用数据库技术干货！
PostgreSQL VACUUM 之深入浅出
华山论剑之 PostgreSQL sequence
[PG Upgrade Series] Extract Epoch Trap
[PG Upgrade Series] Toast Dump Error
GitLab supports only PostgreSQL now
MySQL or PostgreSQL?
PostgreSQL hstore Insight
ReIndex 失败原因调查
PG 数据导入 Hive 乱码问题调查
PostGIS 扩展创建失败原因调查