對具有大量更新的表進行 Autovacuum

我有一個包含兩個表的數據庫，每 5 分鐘更新一次這些表的全部內容（主要是一列）。兩個表中最大的有大約 170K 行。自動清理似乎在這些表上執行得非常頻繁，但它們的磁碟大小變得非常大（2 GB，很多死行），幾週後整個數據庫的性能明顯下降（更高的負載，更高的讀/寫延遲，更高的IOPS）。AVACUUM FULL解決了這個問題，將表恢復到可接受的大小，並且性能恢復到預期的水平。

現在，我知道這種類型的使用對於 autovacuum 來說並不理想。而且看起來沒有長時間執行的查詢阻塞自動清理。然而我有幾個問題：

1. 為什麼磁碟增加？我希望 autovacuum 能夠將死行標記為“已刪除”並重用磁碟空間而不是持續增長。
2. 我可以調整哪些參數來避免這種性能損失？
3. 如何更好地調試阻止 autovacuum 正確清理事物的原因？

我還注意到表的統計數據非常不准確：估計的行數比活動行數低一個數量級。

謝謝。

---

（編輯）

真空詳細

這裡是VACUUM VERBOSE ANALYZE兩個表之一的輸出：

vacuuming "public.table1"
index "leads_pkey" now contains 310038 row versions in 22625 pages
index "leads_uuid_key" now contains 310038 row versions in 40682 pages
"table1": found 0 removable, 280589 nonremovable row versions in 14781 out of 34929 pages
vacuuming "pg_toast.pg_toast_2042410"
index "pg_toast_2042410_index" now contains 0 row versions in 1 pages
"pg_toast_2042410": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
analyzing "public.table1"
"table1": scanned 30000 of 34929 pages, containing 148231 live rows and 121206 dead rows; 30000 rows in sample, 172585 estimated total rows

哪裡VACUUM FULL VERBOSE ANALYZE有輸出：

vacuuming "public.table1"
"table1": found 0 removable, 487921 nonremovable row versions in 34929 pages
analyzing "public.table1"
"table1": scanned 13614 of 13614 pages, containing 171987 live rows and 315934 dead rows; 30000 rows in sample, 171987 estimated total rows

如果您在單個語句中更新一個表的所有行，則該表將膨脹到其最小大小的兩倍（它將包含每行的一個活動元組和一個死元組），並且該膨脹不能被 autovacuum 刪除。如果 autovacuum 沒有足夠快地完成，下一次更新只會增加問題。

解決方案的想法：

• 分批執行更新，每批只更新一小部分行。在更新之間，執行顯式VACUUM. 這將阻止膨脹。
• autovacuum_vacuum_cost_delay = 0接受 50%+ 的膨脹，但通過設置和配置 autovacuum 以盡可能快地執行maintenance_work_mem = '1GB'。這樣，您就有機會使桌子不再臃腫。
• 故意創建表的填充因子為 45，因此它從一開始就故意膨脹，但要確保更新只修改未索引的列。然後您將獲得HOT 更新，這至少會減少對VACUUM.

引用自：https://dba.stackexchange.com/questions/306824