PostgreSQL：pg数据库死行（dead rows）问题的排查和解决

1. 产生原因

   PostgreSQL 使用 MVCC 来处理多用户并发访问数据库，确保读操作不会被写操作阻塞，反之亦然。在 MVCC 中，每当一个记录被更新，原来的记录不会立即被覆盖，而是会创建一个新的版本，旧版本的记录变成了“死行”。类似地，当记录被删除时，该记录被标记为不可见，但物理上并未从磁盘上移除。

2. 主要出现操作

   死行主要由以下操作产生：

   • 更新操作：每次更新都会保留一个旧的记录版本，这个版本对于开始该事务之前的其他事务可能仍然可见，但对于新事务不可见。
   • 删除操作：删除操作会将行标记为删除，但实际数据直到执行 VACUUM 操作之前仍然占据物理空间
3. 主要影响

   • 占用磁盘空间
   • 性能问题。在查询的时候死行虽然不可见，但是依然会被处理，如果存在大量死行会导致高磁盘占用和高CPU负载
4. 排查方式

   1. 现象单表简单查询出现了高磁盘读写或者高CPU且查询缓慢时可以考虑是dead rows问题
   2. 确定方法方法一：使用SQL语句查询

      SELECT relname AS table_name,
             n_live_tup AS live_tuples,
             n_dead_tup AS dead_tuples
      FROM pg_stat_user_tables
      WHERE n_dead_tup > 0;

      方法二：使用pgstattuple插件

      使用

      CREATE EXTENSION pgstattuple;

      安装拓展

      然后使用

      SELECT * FROM pgstattuple('your_table_name');

      检查

      dead_tuple_percent是dead rows所占的百分比

      free_percent 是空闲空间所占百分比

5. 管理死行

   • autovacuum： pg提供了来自动回收dead rows。
   • VACUUM：这个操作会扫描表中的死行，并将它们所占据的空间标记为可重用，从而供将来的插入操作使用。这个操作不会阻塞读写操作。
   • VACUUM FULL：与普通的 VACUUM 不同，VACUUM FULL 会重写表以压缩其占用的磁盘空间，这个操作会锁定表

本文转载自：唯依博客,版权归原作者所有，本博客仅以学习目的的传播渠道，不作版权和内容观点阐述，转载时根据场景需要有所改动。