🥇PostgreSQL 反模式：與「死者」大軍作戰

PostgreSQL 內部機制的特殊性使其在某些情況下非常快，而在其他情況下「不太快」。今天我們將重點討論 DBMS 的工作方式與開發人員使用它的用途之間的衝突的經典範例 - UPDATE 與 MVCC 原則.

當透過 UPDATE 指令修改一行時，實際上執行了兩個動作：DELETE 和 INSERT。在 字串的當前版本 xmax 設定為等於執行 UPDATE 的交易數。然後就創建完成了 新版本 同一條線；其xmin值與先前版本的xmax值一致。

此事務完成後的某個時間，舊版本或新版本，取決於 COMMIT/ROOLBACK，會被認可 「死」（死元組） 經過時 VACUUM 根據表並清除。

但這不會立即發生，但「死者」的問題可以很快出現 - 透過重複或大量更新記錄在一張大桌子上，過一會兒你也會遇到同樣的情況 VACUUM 將無能為力.

＃1：我喜歡移動它

假設您的方法正在處理業務邏輯，突然它意識到有必要更新某些記錄中的 X 欄位：

UPDATE tbl SET X = <newX> WHERE pk = $1;

然後，隨著執行的進行，Y 欄位也應該更新：

UPDATE tbl SET Y = <newY> WHERE pk = $1;

……然後還有Z - 為什麼要在瑣事上浪費時間？

UPDATE tbl SET Z = <newZ> WHERE pk = $1;

現在資料庫中有該記錄的多少個版本？是的，4塊！其中，3 個是相關的，XNUMX 個必須在您之後透過 [auto]VACUUM 進行清理。

不要這樣做！使用 更新一個請求中的所有字段 - 幾乎總是可以像這樣更改方法的邏輯：

UPDATE tbl SET X = <newX>, Y = <newY>, Z = <newZ> WHERE pk = $1;

所以，你還想要更新表中很多很多記錄（例如，在使用腳本或轉換器期間）。腳本中會出現這樣的內容：

UPDATE tbl SET X = <newX> WHERE pk BETWEEN $1 AND $2;

類似這種形式的請求經常出現，並且幾乎總是不是填寫空的新字段，而是糾正資料中的一些錯誤。同時，她本人 根本不考慮現有數據的正確性 - 但徒勞！也就是說，記錄被重寫，即使它準確地包含了想要的內容 - 但為什麼呢？讓我們修復它：

UPDATE tbl SET X = <newX> WHERE pk BETWEEN $1 AND $2 AND X IS DISTINCT FROM <newX>;

很多人不知道有這樣一個出色的運算符的存在，所以這裡有一個備忘單 IS DISTINCT FROM 和其他邏輯運算子來幫助：

……以及一些關於複雜操作的信息 ROW()- 表達式：

正在啟動 兩個相同的平行進程，每個都嘗試將條目標記為“正在進行中”：

UPDATE tbl SET processing = TRUE WHERE pk = $1;

即使這些程序實際上彼此獨立地執行操作，但在同一 ID 內，第二個客戶端將「鎖定」此請求，直到第一個事務完成。

解決方案#1：任務減少到上一個

讓我們再次添加它 IS DISTINCT FROM:

UPDATE tbl SET processing = TRUE WHERE pk = $1 AND processing IS DISTINCT FROM TRUE;

在這種形式中，第二個請求根本不會改變資料庫中的任何內容，一切都已經是它應該的樣子 - 因此，不會發生阻塞。接下來，我們處理應用演算法中「找不到」記錄的事實。

解決方案#2：諮詢鎖

這是一篇單獨文章的大主題，您可以在其中閱讀推薦封鎖的應用方法和“耙子”.

解決方案#3: 愚蠢的電話

但這正是你該發生的事情 使用相同的記錄同時工作？或者，例如，您是否搞亂了在客戶端調用業務邏輯的演算法？如果你想一想？

來源： www.habr.com