作者：Laurenz Albe是CYBERTEC的進階顧問和支援工程師。自2006年以來，他一直在PostgreSQL上工作并為PostgreSQL做貢獻。
譯者：類延良，任職于瀚高基礎軟體股份有限公司，PostgreSQL資料庫技術愛好者，10g &11g OCM，OGG認證專家。
在許多PostgreSQL資料庫中，您無需考慮或擔心調整autovacuum。它會在背景自動運作，并在不妨礙您的情況下進行清理。
但是有時預設配置還不夠好，您必須調整autovacuum以使其正常工作。本文介紹了一些典型的問題方案，并介紹了在這些情況下的處理方法。
autovacuum的任務
有許多autovacuum的配置參數，這會使調整變得複雜。主要原因是autovacuum具有許多不同的任務。從某種意義上說，autovacuum必須解決由PostgreSQL的多版本并發控制（MVCC）實作引起的所有問題：
- 清理UPDATE或DELETE操作後留下的“死元組”
- 更新可用空間映射(free space map)，以跟蹤表塊中的可用空間
- 更新僅索引掃描所需的可見性圖(visibility map)
- “當機”(freeze)表行，以便事務ID計數器可以安全地環繞
根據這些功能中的哪個會導緻問題，您需要不同的方法來調整autovacuum。
調整autovacuum以清除死元組
最有名的autovacuum任務是清理UPDATE或DELETE操作中的死元組。如果autovacuum不能跟上清理死元組的速度，則應遵循以下三個調整步驟：
確定沒有任何東西可以阻止autovacuum回收死元組
檢查 防止vacuum清除死元組的已知原因
https://www.cybertec-postgresql.com/en/reasons-why-vacuum-wont-remove-dead-rows/罪魁禍首通常是長期事務。除非您可以消除這些障礙，否則調整autovacuum将無用。
如果您不能從根本上解決問題，則可以使用配置參數
idle_in_transaction_session_timeout使PostgreSQL終止會話，這些會話在事務中處于“idle in transaction”的時間過長。這會在用戶端導緻錯誤，但是如果您沒有其他方法可以保持資料庫正常運作，這可能是有道理的。同樣，要解決長期運作的query，可以使用statement_timeout配置參數。
調整autovacuum以使其運作更快
如果autovacuum無法跟上清理死元組的速度，那麼解決方案就是使其工作更快。這看起來似乎很明顯，但是許多人陷入了使autovacuum更早開始或更頻繁運作将解決問題的陷阱。
VACUUM是一項耗費資源的操作，是以預設情況下，autovacuum操作的速度故意降低。目的是使其在背景運作而不妨礙正常的資料庫操作。但是，如果您的工作負載建立了很多死元組，那麼您将不得不使其更具侵略性：
- 從 autovacuum_vacuum_cost_limit預設值200開始增加（這是一種柔和的方法）
- 從autovacuum_vacuum_cost_delay從預設值2開始減少（在舊版本：20）毫秒（這是有效的方法）
設定autovacuum_vacuum_cost_delay為零将使autovacuum與手動VACUUM速度一樣快，即盡可能快。
由于并非所有表都以相同的速度增長死元組，是以通常最好不要更改中的全局設定postgresql.conf，而要單獨更改繁忙表的設定：
ALTER TABLE busy_table SET (autovacuum_vacuum_cost_delay = 1); 對表進行分區還可以幫助更快地完成工作。請參閱本文partition部分了解更多資訊。
更改工作負載，以便生成更少的死元組
如果沒有其他效果，則必須看到生成的死元組更少。也許将幾個UPDATE合并為一行UPDATE通常，您可以使用“ HOT更新”來顯着減少死元組的數量：
- 将表的fillfactor參數設定為小于100的值，以使INSERTs在每個塊中保留一些可用空間
- 確定你在update語句中修改的列不是索引列
然後，任何SELECT或DML語句都可以清除死元組，而對VACUUM的需求則更少。
調整僅索引掃描的autovacuum
索引掃描的昂貴部分是查找實際的表行。如果您想要的所有列都在索引中，則完全不需要通路該表。但是在PostgreSQL中，您還必須檢查一個元組是否可見，并且該資訊僅存儲在表中。
為了解決這個問題，PostgreSQL對每個表都有一個“可見性圖”(visibility map)。如果一個表塊在可見性圖(visibility map)中被标記為“所有可見”，則不必通路該表以擷取可見性資訊。
是以，要獲得真正的僅索引掃描，autovacuum必須處理表并經常更新可見性圖(visibility map)。為此，如何配置autovacuum取決于查詢收到的資料修改類型：
為接收UPDATEs或DELETEs的表的僅索引掃描調整autovacuum
為此，您可以減少表的存儲參數autovacuum_vacuum_scale_factor，例如
ALTER TABLE mytable SET (autovacuum_vacuum_scale_factor = 0.01); 按照如上所述加快autovacuum是一個好主意。
調整僅接收INSERTs的表的僅索引掃描的autovacuum
從v13開始，這很簡單：對配置參數autovacuum_vacuum_insert_scale_factor進行調整，調整方法是如上所示對autovacuum_vacuum_scale_factor的調整。
對于較舊的PostgreSQL版本，您可以做的最好方法就是降低autovacuum_freeze_max_age，最佳值取決于您使用事務id的速率，如果您每天消耗100000個事務id，并且希望每天自動清理table，則可以進行如下設定：
ALTER TABLE insert_only SET (autovacuum_freeze_max_age = 100000); 要測量事務ID消耗的速率，請在一個長的時間間隔内使用兩次txid_current()函數(或者從V13開始的pg_current_xact_id()函數)，然後取其內插補點。
調整autovacuum以避免事務回繞問題
正常時，autovacuum關心并啟動一個特殊的“anti-warparound” autovacuum worker，不論一個表中最老的transaction id比autovacuum_freeze_max_age參數值更老或者一個表中的最老的multiact比autovacuum_multixact_freeze_max_age 參數值更老
確定anti-wraparound vacuum可以freeze掉所有表中的行
再次強調，你不得不確定：沒有阻塞autovacuum進行freeze老元組和改進pg_database.datfrozenxid 以及pg_database.datminmxid。這些阻塞者包括：
- 運作時間很長的session，這些session保持一個事務處于open狀态或者有臨時表（autovacuum不能處理臨時表）
- 資料損壞，這會導緻所有的autovacuum worker運作失敗。
為了防止資料損壞，請使用更好的硬體，并總是運作最新的PostgrSQL的次要版本。
為接收updates或者Deletes的tables調優anti-wraparound vacuum
在接收updates或者deletes的table上，你不得不做的一切是看autovacuum正在運作，并且足夠快以便及時完成（參見上文）
為接收inserts的tables調優anti-wraparound vacuum
從PostgreSQL v13開始，在這種情況下沒有特殊考慮，因為您也可以在此類表上定期運作autovacuum。
在此之前，僅插入表是有問題的：由于沒有死元組，是以永遠不會觸發正常的autovacuum運作。
然後，一旦autovacuum_freeze_max_age或autovacuum_multixact_freeze_max_age超過該值，您可能會突然獲得大量的autovacuum運作，進而當機整個大表，花費很長時間并導緻大量的I / O。
為避免這種情況，請減少autovacuum_freeze_max_age該表：
ALTER TABLE insert_only SET (autovacuum_freeze_max_age = 10000000); 分區
對于很大的表，建議使用分區。這樣做的好處是您可以讓多個autovacuum workers 并行處理多個分區，是以整個分區表的完成速度比單個autovacuum worker快。
如果您有多個分區，則應增加autovacuum_max_workers，該參數是指autovacuum workers的最大數量。
隻要更新影響所有分區，分區還可以幫助清理接收大量更新的表。
調優autoanalyze
更新表統計資訊是自動清理的“輔助工作”。
您知道，如果您的查詢計劃在手工對表執行ANALYZE後變得更好，那麼自動統計資訊收集将不會經常發生。
在這種情況下，您可以降低autovacuum_analyze_scale_factor以使autoanalyze更頻繁地處理表：
ALTER TABLE mytable SET (autovacuum_analyze_scale_factor = 0.02); 另一種選擇是不使用scale factor，而是使用set autovacuum_analyze_threshold，以便每當固定數量的行發生更改時，就計算表統計資訊。
例如，要配置每當超過一百萬行更改時要分析的表：
ALTER TABLE mytable SET (
autovacuum_analyze_scale_factor = 0,
autovacuum_analyze_threshold = 1000000
); 結論
根據您的特定問題和PostgreSQL版本，有不同的調整開關可以使autovacuum正确執行其工作。autovacuum的許多任務和許多配置參數并沒有使它變得更容易。
如果本文中的提示還不夠，請考慮尋求專業咨詢
(https://www.cybertec-postgresql.com/en/services/postgresql-consulting/)
原文連結：
https://www.cybertec-postgresql.com/en/tuning-autovacuum-postgresql/
更多精彩内容，請關注以下平台、網站：
中國Postgre SQL分會官方公衆号（技術文章、技術活動）：
開源軟體聯盟PostgreSQL分會
中國Postgre SQL分會技術問答社群：
www.pgfans.cn