Postgresql 9.4开启autovacuum后部分表dead tuples未被正常清理

问题现象 #

业务使用PostgreSQL 9.4版本，开启默认配置的autovacuum后，多表持续出现死元组无法清理的问题，单表autovacuum单次执行耗时至少200秒，无任何元组被清理，相关日志如下：

< 2021-11-30 00:31:30.703 EST >LOG:  automatic vacuum of table "erp.public.example_table": index scans: 0
    pages: 0 removed, 71343 remain
    tuples: 0 removed, 2106528 remain, 1133145 are dead but not yet removable
    buffer usage: 62922 hits, 103385 misses, 172 dirtied
    avg read rate: 2.413 MB/s, avg write rate: 0.004 MB/s
    system usage: CPU 1.24s/0.82u sec elapsed 334.71 sec

< 2021-11-30 00:48:10.794 EST >LOG:  automatic vacuum of table "erp.public.example_table": index scans: 0
    pages: 0 removed, 71343 remain
    tuples: 0 removed, 2107145 remain, 1133466 are dead but not yet removable
    buffer usage: 62876 hits, 103433 misses, 75 dirtied
    avg read rate: 2.998 MB/s, avg write rate: 0.002 MB/s
    system usage: CPU 1.15s/0.73u sec elapsed 269.55 sec

< 2021-11-30 00:58:27.625 EST >LOG:  automatic vacuum of table "erp.public.example_table": index scans: 0
    pages: 0 removed, 71343 remain
    tuples: 0 removed, 2107347 remain, 1133476 are dead but not yet removable
    buffer usage: 62876 hits, 103433 misses, 14 dirtied
    avg read rate: 2.419 MB/s, avg write rate: 0.000 MB/s
    system usage: CPU 1.18s/0.85u sec elapsed 333.99 sec

< 2021-11-30 01:39:59.626 EST >LOG:  automatic vacuum of table "erp.public.example_table": index scans: 0
    pages: 0 removed, 71343 remain
    tuples: 0 removed, 2107627 remain, 1133724 are dead but not yet removable
    buffer usage: 62857 hits, 103454 misses, 44 dirtied
    avg read rate: 2.446 MB/s, avg write rate: 0.001 MB/s
    system usage: CPU 1.28s/0.83u sec elapsed 330.44 sec

< 2021-11-30 01:52:16.303 EST >LOG:  automatic vacuum of table "erp.public.example_table": index scans: 0
    pages: 0 removed, 71343 remain
    tuples: 0 removed, 2107741 remain, 1133724 are dead but not yet removable
    buffer usage: 62878 hits, 103431 misses, 3 dirtied
    avg read rate: 2.426 MB/s, avg write rate: 0.000 MB/s
    system usage: CPU 0.75s/0.78u sec elapsed 333.01 sec

< 2021-11-30 02:33:24.258 EST >LOG:  automatic vacuum of table "erp.public.example_table": index scans: 0
    pages: 0 removed, 71343 remain
    tuples: 0 removed, 2107731 remain, 1133919 are dead but not yet removable
    buffer usage: 62880 hits, 103447 misses, 32 dirtied
    avg read rate: 3.414 MB/s, avg write rate: 0.001 MB/s
    system usage: CPU 1.09s/0.71u sec elapsed 236.70 sec

已排除长事务、未提交预备事务、过期复制槽三类常见诱因，可按以下方向继续排查：

排查方向 #

• 检查旧事务快照持有情况：执行SELECT datname, usename, backend_xid, backend_xmin, state, query FROM pg_stat_activity WHERE backend_xmin IS NOT NULL ORDER BY backend_xmin ASC LIMIT 10;，PostgreSQL 9.4版本中，即使没有超时长事务，零散的长运行只读查询、短时间内大量idle in transaction状态的连接，都可能持有过老的事务快照，导致vacuum判定死元组仍需被保留无法清理。
• 检查vacuum延迟清理配置：先执行SHOW vacuum_defer_cleanup_age;检查全局配置，再执行SELECT relname, reloptions FROM pg_class WHERE relname = 'example_table';检查表级配置，如果该参数值非0，会强制vacuum延迟清理死元组直到对应事务年龄超过设定阈值。
• 检查autovacuum资源限制配置：执行SHOW autovacuum_vacuum_cost_delay;和SHOW autovacuum_vacuum_cost_limit;，PostgreSQL 9.4默认autovacuum_vacuum_cost_delay为20ms，该配置会让autovacuum每次达到IO成本阈值后就进入休眠，大表场景下多次触发也无法推进清理进度，和日志中vacuum运行IO速率极低的表现吻合。
• 检查表级锁持有情况：执行SELECT * FROM pg_locks WHERE relation = 'erp.public.example_table'::regclass AND mode IN ('AccessExclusiveLock', 'ShareLock') AND granted = true;，持续存在的表级排他锁会阻塞vacuum执行修改操作，即使autovacuum触发也无法清理死元组。
• 手动执行带verbose的vacuum验证：业务低峰期执行VACUUM VERBOSE erp.public.example_table;，9.4版本的verbose输出会明确标注限制死元组清理的最老xmin来源，可直接定位根因。

内容的提问来源于stack exchange，提问作者dssof