Hatchet 的 Postgres 生存指南 – 重點摘要與 HN 討論

概觀

本指南提供一份簡潔的檢查清單，說明在生產環境中執行 Postgres 的要點，涵蓋資料表設計、查詢效能、autovacuum，以及像 FOR UPDATE SKIP LOCKED 與分割表等進階功能。

Schema 設計

從迭代式的 schema 開始：先草擬資料表與主鍵，針對它們撰寫查詢，之後再逐步優化。主鍵建議使用 identity 欄位（自動遞增整數）或內建的 UUID。時間戳記一律使用 timestamptz。務必為每張表定義主鍵。外鍵的級聯刪除僅在低流量且一致性極為重要的表上使用；在高流量情況下需特別謹慎。

查詢效能

為避免緩慢的順序掃描，請使用已建立索引的欄位、唯一限制或主鍵作為過濾條件。當 ORDER BY 欄位是索引的最後幾個鍵且排序方向相同時，可使用複合索引。保持交易時間短，僅鎖定必要的列。若在大型已有資料表上建立索引，務必使用 CREATE INDEX CONCURRENTLY 以免阻塞寫入。

大量寫入與 Autovacuum

透過批次寫入（例如使用 pgx SendBatch）可將插入吞吐量提升約 10 倍。監控 autovacuum：若 autovacuum 查詢持續超過約 1 小時，需調整設定以防止死元組累積與 transaction ID 繞回。使用 pg_repack（或即將在 Postgres 19 推出的 REPACK…CONCURRENTLY）處理表膨脹，使用 REINDEX INDEX CONCURRENTLY 處理索引膨脹。

進階功能

使用 FOR UPDATE SKIP LOCKED 來實作工作佇列或租約分配，避免阻塞其他會話。對時間序列資料採用宣告式分割表，可讓 autovacuum 獨立運作，並透過刪除分割表即時移除舊資料。對於無法在單一交易內完成的大型表遷移，可結合觸發器與交易外的批次回填，依賴唯一限制避免重複寫入。

社群見解（HN 評論）

評論者提供了實務建議與反思：

• Backup strategy: 「你在資料庫上做的第一件事不應該是制定備份策略嗎？」 – @theallan
• UUID 版本與鎖定順序: 「一般使用 uuidv7 而非 uuid（通常是 v4）」，以及「確保你的鎖在所有查詢中以確定性的順序排列（例如依 id 升序，始終如此），否則會發生死結」 – @ComputerGuru
• 查詢規劃器提示: 「使用 explain (generic_plan) 以便 a) 直接複製貼上含參數佔位符的查詢，b) 觀察當 Postgres 無法得知具體參數值時，查詢實際會如何被最佳化」 – @ComputerGuru
• 索引類型: 「大家預設使用 btree 索引，這會較重且增加索引膨脹。如果僅需依欄位/ID 查找而不需要排序或比較大小，考慮改用 hash 索引。」以及「學習 GIN（以及 GIST）索引。」 – @ComputerGuru
• 外鍵級聯刪除: 「我討厭級聯，原因很簡單：大多數情況下，開發者都在使用 Python/Node/Go/... 與資料庫互動的應用程式中… 級聯刪除… 基本上是魔法，且很難理解為何刪除 A 表的一列會自動刪除 B 表的資料」 – @mjr00
• 監控 XID 繞回: 「如果接近 XID 繞回，AWS 會發送電子郵件。對於新創公司而言，這封郵件很可能被忽略… 你希望 AWS 監控的通知能連結到呼叫器（pager）上」 – @thundergolfer
• 正規化 vs JSONB: 「我發現正規化有時會與查詢效能與使用便利性衝突… 有時直接把資料放入 jsonb 欄位比較簡單。」 – @sgarland（同時提到 BRIN 在時間序列上的效用）
• 時間戳記建議的分歧: 「我傾向使用 timestamp（不含時區），這會迫使我在所有地方都使用 UTC…」 – @lennoff
• 避免死結: 「有沒有什麼『規範』或實踐方法有效？比如在真實且混亂的業務程式碼基礎中，能否實際對資料表施加『排序』以避免哲學家就餐問題？」 – @ucarion
• 記憶體內部連接: 「我們的程式碼基礎中有幾個地方，會先分別執行兩個或更多較簡單的查詢，然後在迴圈中使用映射（map）匹配相關列…」 – @mrkaye97（Hatchet 的 Matt）
• 遷移工具與 JSON 欄位: 「有個叫 Grate 的 .Net 工具，我常用於 schema 遷移… 有一點未提及… 在許多情境下利用 JSON 欄位並完全避免 join」 – @tracker1
• 缺少儲存函式與 text 類型: 「我在那篇文章中搜尋「function」結果為零。令人失望。甚至沒有最簡單的儲存函式討論？」以及「完全沒提到 text，在 Postgres 中強烈建議使用 text 而非老舊的 varchar(255)」 – @traceroute66
• 連線池風險: 「難道你不會有洩漏權限或其他請求資訊的風險嗎？」 – @groundzeros2015
• 託管建議: 「資料庫管理的第一條規則是，除非你願意付錢讓人全職管理，否則不要自行託管或管理資料庫」 – @thisismyswamp
• 正規化與角色: 「必須更強調這點的重要性！… 也不應害怕擁有多個 Postgres 實例… 最後，Postgres 角色（即「使用者」系統）擁有驚人的權力」 – @zer00eyz
• 清單式建議: 列出九點，包括避免 ORM、使用 serial 主鍵、謹慎使用 jsonb、僅使用追加式真實來源、連線池、避免顯式交易、避免 SELECT FOR UPDATE、不要重新發明型別系統，以及不要重新發明圖形資料庫。 – @frollogaston
• 分析工作負載: 「如果你的領域以分析為主，別試圖為分析優化 Postgres。遵循將資料鏡像至資料倉儲的標準模式，然後在那裡進行分析」 – @hasyimibhar

這些社群意見為指南補充了原文未涵蓋的營運層面考量。