PostgreSQL大量死元组无法清理问题求助

1. 优先处理长事务（核心原因） #

长事务会持有旧的事务快照，PostgreSQL无法清理比快照中最小事务ID（xmin）更早的死元组，这是当前死元组无法清理的最可能原因：

• 找出所有运行超过20小时的长事务：

  SELECT pid, datname, usename, query_start, state, query 
  FROM pg_stat_activity 
  WHERE now() - query_start > '20 hours'::interval;
  

• 终止这些长事务（注意确认业务影响，避免终止关键业务进程）：

  SELECT pg_terminate_backend(pid);
  

• 事务终止后立即执行清理：

  VACUUM (VERBOSE, ANALYZE) example_table;
  

  执行后再次查询pg_stat_user_tables查看死元组数量变化。

2. 排查隐藏的事务快照 #

除了活跃长事务，idle in transaction状态的连接也会持有旧快照：

• 排查这类连接：

  SELECT pid, datname, usename, xact_start, state 
  FROM pg_stat_activity 
  WHERE state = 'idle in transaction' AND now() - xact_start > '1 hour'::interval;
  

• 同时检查表的冻结事务ID，确认是否有事务卡住了冻结进程：

  -- 查看目标表的冻结ID
  SELECT relname, relfrozenxid FROM pg_class WHERE relname = 'example_table';
  -- 查看数据库的冻结ID
  SELECT datname, datfrozenxid FROM pg_database WHERE datname = 'your_database_name';
  

  如果表的relfrozenxid远低于数据库的datfrozenxid，说明有事务阻止了表的冻结，进而影响死元组清理。

3. 验证VACUUM FULL的实际执行情况 #

执行VACUUM FULL后死元组未变化，大概率是VACUUM FULL未真正完成（因为需要排他锁，可能被阻塞）：

• 检查目标表是否持有排他锁：

  SELECT * FROM pg_locks 
  WHERE relation = 'example_table'::regclass AND mode = 'AccessExclusiveLock';
  

  如果没有对应的锁记录，说明VACUUM FULL被阻塞，没有执行成功。
• 若VACUUM FULL确实执行完成，手动更新统计信息后再查看死元组：

  ANALYZE example_table;
  SELECT relname, n_dead_tup, n_live_tup FROM pg_stat_user_tables WHERE relname = 'example_table';
  

  VACUUM FULL会重建表，也可以通过pg_total_relation_size('example_table')查看表大小是否缩小来验证。

4. 检查权限与autovacuum状态 #

• 确认执行VACUUM的用户拥有目标表的VACUUM权限，或为超级用户。
• 检查autovacuum是否针对该表运行过：

  SELECT relname, vacuum_count, autovacuum_count, last_autovacuum 
  FROM pg_stat_user_tables WHERE relname = 'example_table';
  

  如果autovacuum_count为0，可能是autovacuum被禁用（检查表的autovacuum_enabled属性）：

  SELECT relname, reloptions FROM pg_class WHERE relname = 'example_table';
  

5. 临时缓解IOPS耗尽问题 #

在彻底解决死元组问题前，调整autovacuum的IO消耗限制，避免影响查询性能：

ALTER TABLE example_table SET (
  autovacuum_vacuum_cost_limit = 200,
  autovacuum_vacuum_cost_delay = 20ms
);

根据实际IO情况调整参数，autovacuum_vacuum_cost_limit越大，autovacuum使用的IO越多；autovacuum_vacuum_cost_delay越大，autovacuum会暂停更久，减少IO占用。

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