Postgres DELETE 如何在高負載下實現可擴充套件性

Postgres DELETE 具有不佳的名聲，許多高效能系統選擇完全避免刪除，而改以 截斷整張表 或 丟棄整個分割槽 來清理資料。如果刪除無法擴充套件，那就太遺憾了，因為 DELETE 比丟棄分割槽或表格更靈活，尤其在資料異質且需要長期保留部分專案時。這些指控並非錯誤：Postgres DELETE 使用大量昂貴的機制來確保刪除在交易上安全，因而刪除表中每一列實際上比直接丟棄錶慢數個數量級。但 DELETE 仍能擴充套件，尤其對於需要其靈活性與強大保證的工作負載。

在這篇部落格文章中，我們將示範如何透過適當的最佳化，使 Postgres DELETE 能夠在大規模環境下使用。我們將聚焦於如何在以 Postgres 為後端的耐久工作流程與佇列工作負載中調校 DELETE，最佳化快取與索引效能，以執行資料保留政策，處理每秒數萬個工作流程與步驟的系統。

Postgres 如何刪除一列

要了解為什麼 Postgres 的刪除操作如此昂貴，我們必須先了解其工作原理。當你在 Postgres 中刪除一列時，資料庫並不會實際物理刪除該列。這是因為 Postgres 必須在資料庫交易之間提供 隔離性：若你的交易刪除了一列，該刪除不應在你提交前對其他交易可見。若 Postgres 直接刪除列，這些刪除將立即對其他交易可見（除非使用昂貴的鎖），從而違反隔離性。

相反地，Postgres 實作了多版本併發控制（MVCC）。每次更新一列時，Postgres 會在你的表格與索引中插入一個包含更新資料的新版本。每個版本都會被標記上建立它的交易 ID 以及取代它的交易 ID。當你刪除一列時，Postgres 會將其最終版本標記為你的交易 ID，從而使該列對後續交易隱形。此設計提供交易隔離：交易所做的更新直到交易提交前都不可見，而長時間執行的交易則可存取舊版本以檢視資料的一致「快照」。

MVCC 的一個後果是頻繁變動。每次更新與刪除都會產生一個「死元組」，即列的舊版本，Postgres 必須在某個時點真正刪除它以釋放磁碟空間。Postgres 透過 VACUUM 操作（由 autovacuum 定期執行）來清理死元組，該操作會尋找並移除所有不再被任何活動交易可見的死元組。在構建必須定期刪除數百萬個工作流程及其步驟資料的耐久執行系統時，這種 MVCC 變動會以微妙的方式與 Postgres 快取與索引互動，使刪除的成本高於表面看來的程度。

與區域性性問題作鬥爭

工作流程刪除通常遵循保留政策：使用者設定一項政策（例如「刪除所有完成超過一週前的工作流程」），工作流程引擎則透過定期刪除已過期的工作流程來執行此政策。DELETE 操作以大小為 N 的批次發出，我們重複刪除最舊的 N 個工作流程，直到沒有剩餘工作流程超過保留截止日期。

有趣的是，效能挑戰不是刪除工作流程本身，而是刪除與它們相關聯的其他表中的資料。例如，工作流程通常包含多個步驟，這些步驟會將輸出寫入單獨的 step_outputs 表。為了保持步驟資料與工作流程資料同步，我們最初使用了外部索引鍵：step_outputs 中的每一行都引用 workflow_status 中對應工作流程的行，並設定 ON DELETE CASCADE，這樣刪除工作流程時也會自動刪除其步驟。

這是符合資料完整性觀點的經典設計：工作流程的步驟應該與工作流程一起被刪除。但在大規模運作時，其效能非常糟糕。原因在於快取區域性性。每次刪除工作流程時，PostgreSQL 必須找到其所有步驟並將它們一起刪除。由於工作流程通常在長時間內執行許多步驟，這些步驟記錄在磁碟上分散得很遠，因此每次刪除步驟都需要從磁碟讀取整個頁面到記憶體，更新後再寫回磁碟。這種快取頻繁移動導致刪除工作流程的速度遠慢於執行工作流程。

刪除外部索引鍵

為了改善保留效能，我們刪除了參照 workflow_status 表的外部索引鍵，並分別從每個表中刪除資料。外部索引鍵消失後，保留過程分階段進行。首先，我們像以前一樣刪除工作流程行。沒有外部索引鍵可傳遞，這一步很快。接著，我們對儲存工作流程資料的表（如 step_outputs）執行類似的批次刪除。這些批次刪除之所以快速，是因為與 workflow_status 的批次刪除相同：它們按實際建立順序刪除行，因而快取區域性性高。

這種設計的一個變數是，工作流程是根據完成時間刪除的，而資料表只儲存 建立 時間。 為了避免刪除仍在執行、長時間執行的工作流程所關聯的資料，我們在批次刪除中加入了反向連線（anti‑join），只刪除已被刪除工作流程所關聯的資料。

除錯 20 分鐘的索引查詢

雖然這種新的刪除機制在理論上是合理的，但在實作時我們遇到了一個真正怪異的效能缺陷：一個索引查詢耗時 20 分鐘。問題出在批次刪除中的第一個查詢，它會尋找表中第 batch size‑th 古老行的 created_at 時間戳。該查詢正確地使用了 created_at 的索引，應該很快，通常也很快。但有時在接近飽和的資料庫上，其效能崩潰，這個看似無害的查詢竟耗時 20 分鐘以上，即使查詢計劃正確使用了索引。

這個查詢效能問題的根源與 Postgres MVCC 執行刪除的方式有關。為了刪除一行，PostgreSQL 會在該行上打上刪除交易的交易 ID，這樣之後的交易就知道要忽略它，autovacuum 也能清理它。然而，這個標記只套用於該行本身，而不會套用到參照該行的任何索引。這意味著通過索引尋找行的查詢，必須檢查行本身（而不只是其索引項）以確認該行不是已死。

通常這不是問題，因為死 tuple 均勻分佈在索引中。但這個 SELECT 查詢遇到最壞情況。為了找到表中第 batch_size 個最舊的行，查詢從最舊到最新遍歷 created_at 索引。通常這在 O(batch size) 時間內完成，速度相當快。然而問題是行是按最舊到最新刪除的。因此如果最近又有一次刪除執行，且 autovacuum 尚未追上，索引前端可能有數百萬個死 tuple，查詢必須抓取並檢查每一個以確認它們已死，然後再繼續。這些數百萬個死 tuple 的檢查使查詢失敗，耗時 20 分鐘。

幸好，一旦我們知道發生了什麼，修復很簡單：我們在每一次保留過程前後插入手動 VACUUM。這樣，死 tuple 在阻塞未來保留期之前就被清理。透過提升快取區域性性並避免病態索引掃描，手動 VACUUM 與移除外部索引鍵的結合將刪除效能提升了一個量級。

為什麼不使用分割槽？

在結束之前，我們想回到丟棄分割槽（或截斷表）這一替代方案，並說明我們為什麼不採用它。重申一下，一些高效能的 Postgres 系統（如 pgmq 或 PgQue）完全避免刪除，通過按時間分割槽資料表，並在資料不再需要時丟棄舊分割槽。這比 SQL DELETE 更快，因為它完全不經過 MVCC，Postgres 直接刪除磁碟上包含舊資料的檔案。

分割槽的問題在於它不夠彈性。除非它儲存的每一個資料片段都不再需要，否則無法刪除分割槽。這對於某些系統（如訊息佇列）是合適的，因為資料只需要可預測的時間。但對於支援長期工作流程的工作流程系統則不合適。數個持續數週或數月的長期工作流程可能會阻塞數十億行短期工作流程的刪除。此外，基於分割槽的系統無法支援某些使用者需要的複雜保留政策，例如為不同類別的工作流程保留不同時段，或將失敗工作流程保留時間長於成功工作流程。

進一步瞭解

如果你喜歡打造可擴充、可靠的系統，我們很樂意聽聽你的想法。在 DBOS，我們的目標是讓基於 Postgres 的耐久執行盡可能簡單且高效。點此瞭解：

• 快速啟動: https://docs.dbos.dev/quickstart 
• GitHub: https://github.com/dbos-inc
• Discord 社群: https://discord.gg/eMUHrvbu67