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，然後在備機執行長事務(會帶來主備延遲)。

殺死長事務

設定可以部分避免長事務的參數