PostgreSQL执行ANALYZE报错'tuple already updated by self'及CPU异常求助

问题背景 #

我们的PostgreSQL 9.6.8数据库出现异常：

• 日常CPU使用率仅1-2%，但运行额外后台服务对一张1000万行的users_user表执行每3秒1次的UPDATE和INSERT查询时，CPU直接飙升至98%，系统完全失控。
• 尝试执行VACUUM和ANALYZE排查，执行ANALYZE VERBOSE users_user时触发报错：

production=# ANALYZE VERBOSE users_user; 
INFO: analyzing "public.users_user" 
INFO: "users_user": scanned 280 of 280 pages, containing 23889 live rows and 57 dead rows; 23889 rows in sample, 23889 estimated total rows 
INFO: analyzing "public.users_user" 
INFO: "users_user": scanned 280 of 280 pages, containing 23889 live rows and 57 dead rows; 23889 rows in sample, 23889 estimated total rows 
ERROR: tuple already updated by self

• 所有表都无法完成ANALYZE，将数据库完整迁移至测试服务器、确认无其他会话连接后，问题依然存在。

排查方向建议 #

• 优先排查版本bug，尝试小版本升级
  你使用的PostgreSQL 9.6.8是比较老旧的版本，ERROR: tuple already updated by self这个ANALYZE报错大概率是版本相关的已知bug。建议先查看9.6系列的更新日志，你会发现9.6.9及后续补丁修复了不少元组处理、并发扫描相关的问题。可以先在测试环境升级到9.6系列的最新稳定版（比如9.6.24），小版本升级风险低，大概率能解决ANALYZE的报错问题，也可能缓解CPU飙升的情况。

• 检查users_user表的索引、触发器与约束
  1000万行的表，频繁的UPDATE/INSERT如果伴随过多索引或低效约束，会直接拉高CPU负载：

  • 用这条语句查看表的所有索引：SELECT * FROM pg_indexes WHERE tablename = 'users_user';，删除不必要的索引，尤其是UPDATE频繁字段上的冗余索引——每一次UPDATE都会更新对应索引，索引越多开销越大。
  • 检查表是否有触发器、外键约束：SELECT * FROM pg_trigger WHERE tgrelid = 'users_user'::regclass;，这些对象在写操作时会额外消耗CPU资源，如果不是必须的，可以临时禁用测试效果。

• 修复表的物理存储与元组异常
  ANALYZE报错提示“元组被自身更新”，说明表的元组或系统记录可能存在损坏：

  • 先在测试环境执行VACUUM FULL users_user，强制整理表的物理存储，彻底清理死元组并重建表结构，之后再尝试ANALYZE。注意VACUUM FULL会锁表，不要在生产环境直接操作。
  • 检查表的可见性映射是否异常：SELECT relname, relvisiblenaptup FROM pg_class WHERE relname = 'users_user';，如果可见性映射数据异常，会导致ANALYZE重复扫描元组引发错误。
  • 尝试用pg_dump导出users_user表的数据，然后导入到新表中，替换原表后再测试ANALYZE和CPU负载情况。如果新表正常，说明原表的元组结构或系统表记录存在损坏。

• 优化后台服务的查询语句
  每3秒一次的写操作频率不算高，但如果语句本身低效，也会导致CPU飙升：

  • 把后台服务中的UPDATE/INSERT语句拿出来，用EXPLAIN ANALYZE查看执行计划，确认是否走了合适的索引——如果UPDATE没有指定索引条件，会触发全表扫描，1000万行的全表扫描每3秒一次，CPU肯定会被打满。
  • 检查语句是否有不必要的锁机制，比如SELECT FOR UPDATE，如果锁的范围过大，会导致并发冲突和额外的CPU开销。

• 排查系统层面的资源瓶颈
  除了数据库本身，系统资源不足也会导致CPU异常飙升：

  • 检查PostgreSQL的shared_buffers设置，如果太小，会导致大量数据需要从磁盘读取，CPU要处理频繁的IO上下文切换。可以对比系统内存调整这个参数（一般建议设为系统内存的1/4）。
  • 用top、iostat、vmstat等工具，在问题出现时查看系统的CPU、磁盘IO、内存使用情况，确认是否是IO瓶颈导致的CPU飙升（比如磁盘读写慢，CPU一直在等待IO完成）。
  • 查看pg_stat_activity，确认是否有大量等待的会话：SELECT * FROM pg_stat_activity WHERE state != 'idle';，如果有锁等待或者IO等待，针对性解决这些问题。

内容的提问来源于stack exchange，提问作者Damian Gądziak