PostgreSQL的表膨胀及对策 PostgreSQL的表膨胀及对策

PostgreSQL的MVCC机制在数据更新时会产生dead元组，这些dead元组通过后台的autovacuum进程清理。一般情况下autovacuum可以工作的不错，但以下情况下，dead元组可能会不断堆积，形成表膨胀(包括索引膨胀)。

autovacuum清理速度赶不上dead元组产生速度

由于以下因素导致dead元组无法被回收

主库或备库存在长事务

主库或备库存在未处理的未决事务

主库或备库存在断开的复制槽

方法1：查询pg_stat_all_tables系统表

方法2:使用pg_bloat_check工具

以上方法包含了对索引膨胀的检查。但需要注意的是，表中不能被回收的dead tuple在索引页里是作为正常tuple而不是dead tuple记录的。考虑到这一点，索引的实际膨胀要乘以对应表的膨胀率。

调整autovacuum相关参数，加快垃圾回收速度

对于写入频繁的系统，默认的autovacuum_vacuum_cost_limit参数值可能过小，尤其在SSD机器上，可以适当调大。

监视并处理以下可能导致dead元组无法被回收的状况

长事务

未决事务

断开的复制槽

强制回收

设置old_snapshot_threshold参数，强制删除为过老的事务快照保留的dead元组。这会导致长事务读取已被删除tuple时出错。

old_snapshot_threshold不会影响更新事务和隔离级别为RR只读事务。old_snapshot_threshold参数也不能在线修改，如果已经设置了old_snapshot_threshold但又需要运行更长的RR只读事务或单个大的只读SQL，可以临时在备机上设置max_standby_streaming_delay = -1，然后在备机执行长事务(会带来主备延迟)。

杀死长事务

设置可以部分避免长事务的参数