10個常見的 PostgreSQL 錯誤及避坑指南

PostgreSQL 作為當下流行的資料庫，不少開發者因其開源、可靠、可擴展等特性把它應用到實際的生產環境中，幫助無數 PostgreSQL 廠商的 Percona 編製了一個最常見錯誤的列表。即使你認為自己已經正確地安裝配置 PostgreSQL，或許仍會發現此列表對於驗證你的安裝配置大有裨益。 ... ...




PostgreSQL 作為當下流行的資料庫，不少開發者因其開源、可靠、可擴展等特性把它應用到實際的生產環境中，幫助無數 PostgreSQL 廠商的 Percona 編製了一個最常見錯誤的列表。即使你認為自己已經正確地安裝配置 PostgreSQL，或許仍會發現此列表對於驗證你的安裝配置大有裨益。




原文鏈接：

https://www.infoworld.com/article/3681655/10-common-postgresql-mistakes-and-how-to-avoid-them.html

PostgreSQL 旨在應對廣泛的使用場景，但具有極大靈活性的同時也有不利的一面。使用時應註意不要犯本文所列舉的這些十分常見的設計、配置、調整或其他相關錯誤。

在安裝 PostgreSQL 時可能會忽略很多問題，其中某些問題也許因潛伏而未被髮現。隨著時間的推移，它們可能突然爆發並產生重大影響，使其成為大家關註的焦點，這種情況尤其糟糕。

無論是性能明顯下降，還是資源消耗或使用成本的急劇上升，在出現這些情況前儘早發現問題都很重要。而更好的做法是，實施時通過配置以適應所需的工作負載來避免這些問題。

錯誤1：運行預設配置

PostgreSQL 可以做到開箱即用，但其配置通常不能很好地滿足需求。預設配置沒有針對任何特定的工作負載進行調整，具有極大的局限性。這種過於保守的配置，目的在於允許 PostgreSQL 運行在任何環境下，並期望用戶根據自己的需要進行配置。

pgtune 工具提供了基於硬體資源和工作負載類型的配置子集，是根據工作負載需要配置 PostgreSQL 集群的良好起點。此外，還可能需要配置 autovacuum、日誌、檢查點和 WAL（預寫日誌）保留策略等變數。

為伺服器進行優化配置，以滿足全部近期需求，同時避免任何不必要的重啟操作，這一點非常重要。所以有必要看一下 pg_settings 系統視圖中所有具有 “postmaster” 上下文的 GUC。


SELECT name, setting, boot_val
FROM pg_settings
WHERE context = 'postmaster';

尤其在設置高可用（HA）群集時，這點更為重要，因為主伺服器的任何停機都會降低群集性能，並導致將備用伺服器提升為主伺服器角色。

錯誤2：未優化的資料庫設計和架構

關於這點怎麼強調都不為過。我曾親眼看到，僅僅因為未優化的資料庫設計和架構，用戶付出的成本是他們所需成本的五倍多。

這裡最好的建議之一是看看現在和近期的工作負載需求時什麼，而不是六個月到一年後可能需要什麼。向前看得太遠可能意味著你的表是為永遠無法實現的未來需求而設計的。這隻是其中的一個方面。

除此之外，過度依賴對象關係映射（ORM）也是性能不佳的主要原因之一。ORM 主要用於使用面向對象的編程語言將應用程式連接到資料庫，久而久之它們會逐漸簡化開發人員的工作。然而，瞭解 ORM 提供什麼功能以及它引入了什麼樣的性能影響至關重要。ORM 可能正在後臺執行多個查詢，不管是連接多個表、執行聚合，還是拆分查詢數據。總的來說，使用 ORM 時會引起更高的延遲和更低的事務吞吐量。

另外，改進資料庫架構還為了使數據更加結構化，以便對錶或索引進行最佳的讀寫操作。另一種有用的方法是對資料庫進行反規範化，因為這會降低 SQL 查詢的複雜性，減少相關的表連接，進而可以從更少的表中獲取數據。

簡單來說最終驅動高性能的，是對具體環境中的應用程式和工作負載執行三步過程,即“定義、測量、優化”。

錯誤3：沒有針對工作負載調整資料庫

根據工作負載調整資料庫，需要深入瞭解要存儲的數據量、應用程式的性質，以及要執行的查詢類型。可以隨時修改配置併進行基準測試，直到對高負載下的資源使用滿意為止。

例如，考慮是否可以將整個資料庫放入電腦的可用記憶體中。如果是，那麼顯然希望增加資料庫的 shared_buffers 值。類似地，瞭解工作負載是如何正確配置檢查點和 autovacuum 進程的關鍵。例如，與滿足事務處理性能委員會 C 類基準的混合線上事務處理工作負載相比，為 append-only 類型工作負載進行的這些配置將非常不同。

有很多有用的工具提供了查看查詢性能的功能。關於更多查看查詢性能的說明，可以瀏覽我的博客文章，其中討論了一些可選的開源工具。還可以看下我在 YouTube 上的演示。

在 Percona，我們的兩個工具可以極大地幫助理解查詢性能狀況：

lPMM - Percona監控和管理，是一個免費的、完全開源的項目，提供了一個帶有詳細系統統計和查詢分析的圖形界面。可隨意試用適合MySQL、MongoDB或PostgreSQL的PMM演示程式。

lpg_stat_monitor - 這是pg_stat_statements的增強版本，可以藉此更詳細地瞭解查詢性能狀況、實際的查詢計劃和帶有參數值的查詢文本。可以從我們的下載頁獲得它在Linux上的可用包，也可以從PostgreSQL社區的yum存儲庫獲得RPM包。

錯誤4：連接管理不當

乍一看連接配置似乎沒問題，但是我見過太大的 max_connections 值導致記憶體不足錯誤的情況，所以配置 max_connection 還需要註意一下。

配置 max_connections 時必須考慮內核數、可用記憶體量和存儲類型。誰都不希望讓可能永遠不會使用的連接致使伺服器資源過載，況且還要為每個連接分配內核資源。PostgreSQL 內核文檔有更多詳細信息。

當客戶端執行花費很少時間的查詢時，連接池能顯著提高性能，因為在這種類型的工作負載中，生成連接的開銷相對變得很大。

錯誤5：Vacuum 工作異常

希望 autovacuum 沒有被禁用。我們已經在許多生產環境中看到，用戶完全禁用了 autovacuum，這通常是由於一些潛在的問題所導致。如果 autovacuum 在具體環境中不起作用，那麼只可能有以下三個原因：

1.vacuum 過程沒有被觸發，或者至少沒有像應該的那樣頻繁。

2.Vacuuming 太慢。

3.vacuum 沒有清理沒用的舊版本數據。

其中 1 和 2 都與配置選項直接相關。可以通過查詢 pg_settings 系統視圖來查看vacuum相關選項。


SELECT name
, short_desc
, setting
, unit
, CASE
WHEN context = 'postmaster' THEN 'restart'
WHEN context = 'sighup'     THEN 'reload'
ELSE context
END "server requires"
FROM pg_settings
WHERE name LIKE '%vacuum%';

通過調整 autovacuum_work_mem 和並行工作線程的數量，可以潛在提高速度。vacuum 過程的觸發可以通過配置比例因數或閾值來調節。

當 vacuum 過程沒有清理沒用的舊版本數據時，表明有某種東西阻礙了獲取關鍵資源，罪魁禍首可能是以下一項或多項：

長時間運行的查詢或事務；

複製環境中的備用伺服器啟用了 hot_Standby_feedback 選項；

大於所需的 vacuum_defer_cleanup_age 值；

保持 xmin 值的複製槽阻止了 vacuum 清理沒用的舊版本數據。

如果想手動管理表的 vacuum 過程，那麼請遵循帕累托定律（即80/20法則）。將集群調整到適合 80% 的表的最佳配置，然後專門針對剩下 20% 的表進行調整。記住，可以通過在 create 或 alter 語句中指定相關的存儲選項，來為特定表禁用 autovacuum 或 toast.autovacuum。

錯誤6：惡意連接和長時間運行的事務

PostgreSQL 集群可能會受到很多因素的影響，而惡意連接就是其中之一。除了占用可能被其他應用程式使用的連接槽外，惡意連接和長時間運行的事務占用關鍵資源，這可能會對整個系統造成嚴重破壞。看一個較小程度的影響：在啟用了 hot_standby_feedback 的複製環境中，備用伺服器上的長時間事務可能會阻止主伺服器上的 vacuum 完成其工作。

試想一個有問題的應用程式，它打開一個事務，然後停止響應。程式可能會持有鎖，或者只是阻止 vacuum 清理舊版本數據，因為這些數據在此事務中仍然可見。如果該應用程式打開了大量此類事務怎麼辦？

通常情況下，可以通過將 idle_in_transaction_session_timeout 配置為針對查詢調整的值來消除此類事務。當然每次修改參數時，都要記住應用程式的行為。

除了調整 idle_in_transaction_session_timeout 之外，還要監控 pg_stat_activity 系統視圖以查看任何長時間運行的查詢，或等待客戶端相關事件的時間超過預期時間的會話。註意時間戳、等待事件和狀態列。


backend_start | 2022-10-25 09:25:07.934633+00
xact_start | 2022-10-25 09:25:11.238065+00
query_start | 2022-10-25 09:25:11.238065+00
state_change | 2022-10-25 09:25:11.238381+00
wait_event_type | Client
wait_event | ClientRead
state | idle in transaction

此外，準備事務（尤其是孤立的準備事務）也可能持有關鍵系統資源（如鎖或xmin值等）。我建議為準備事務設置一個命名法來定義它們的存在期限。比如，一個最長存在時間為 5 分鐘的準備事務可以創建為 PREPARE TRANSACTION 'foo_prepared 5m'。


SELECT gid
, prepared
, REGEXP_REPLACE(gid, '.* ', '') AS age
FROM pg_prepared_xacts
WHERE prepared + CAST(regexp_replace(gid, '.* ', '') AS INTERVAL) < NOW();

這為應用程式提供了一種定義其準備事務的期限的方案。繼而使用 cronjob 或計劃作業，就可以監控或回滾任何在其預期期限之後仍保持活動狀態的準備事務。

錯誤7：索引過度或索引不足

對錶進行過度索引究竟有沒有問題呢？必須要瞭解 PostgreSQL 如何管理索引,才能使 PostgreSQL 實例獲得最佳性能。

PostgreSQL 中有多種類型的索引，每種都有不同的使用場景和開銷。B 樹是最常用的索引類型，也用於主鍵。在過去的幾個主要版本中，B樹索引中出現了許多與性能相關（或剝離）的改進。這裡是我的一篇博文，討論了 PostgreSQL 14 中的重覆版本變動。

當對錶執行索引掃描時，對於每個匹配的行，索引會回表訪問以獲取數據和可見性信息，以便只選擇當前事務可見的版本的數據。過度索引將導致更多的索引更新，因此會消耗更多資源而得不到預期的好處。

同樣，索引不足將導致更多的表掃描，這可能導致更多的 I/O 操作，從而導致性能下降。

創建索引時不僅要考慮表上的索引數量，還要考慮這些索引在所針對的查詢上如何進行優化。理想情況下，希望每次查詢只掃描一個索引，但有一些限制。儘管 B 樹索引支持所有運算符的索引掃描，但 GiST 和 SP GiST 索引僅支持某些運算符。有關詳細信息，請參閱文檔。

以下簡單的檢查項，可以幫助驗證是否為系統進行了最佳索引設置：

確保配置正確（例如，為相關硬體調整了隨機頁訪問成本）。

檢查統計數據是否最新，或者至少檢查運行analyze或vacuum命令的表上是否有索引。確保統計數據最新或接近最新，以便查詢計劃更有可能選擇索引掃描。

創建正確類型的索引（B樹、哈希或其他類型）。

在正確的列上使用索引。不要忘記，在索引中包含查詢所需列可以避免回表訪問，即所謂索引覆蓋。並非所有索引類型都允許索引覆蓋，因此使用時請檢查文檔。

去除不必要的索引。請參閱pg_statio_user_indexes，瞭解有關索引和塊命中的更多信息。

瞭解索引覆蓋對重覆數據消除、重覆版本變動和僅索引掃描等功能的影響。

錯誤8：備份和 HA 不足

HA 的作用不僅是保持服務的正常運行，還要確保服務在定義的驗收標準內進行響應，並滿足 RPO（恢復點目標）和 RTO（恢復時間目標）目標。要達到系統正常運行時間要求的9的個數（正常使用時間與總時間之比），請參閱此wiki頁面以計算百分比。

為了滿足 RPO 和 RTO，必須考慮許多因素，包括計劃內停機時間、任何自動或手動操作及其頻率和持續時間，當然還有與計劃外停機相關的成本。

擁有準確和及時的備份，以及有效恢復備份的能力，在定義 RPO 和 RTO 這兩個參數方面起著關鍵作用,其中涉及數據備份的頻率是多少，怎樣管理 WAL 文件，如何驗證備份和 WAL 文件等諸多問題。

根據工作負載和可用的維護時間視窗，通常應至少每七天進行一次備份。除此之外，還應該定期測試恢復過程，以便確認這些備份是有效的。事實上，只有應用程式能夠恢復併進行處理，備份才算成功。不應信任未經測試的備份。

錯誤9：錯誤管理擴展模塊

PostgreSQL 自帶 50 多個擴展模塊，而後還有個人或組織提供的第三方擴展模塊。PostgreSQL 內核提供了一些常用的擴展模塊，如 pg_stat_statements，此外還有一些著名的擴展模塊，例如 PostGIS，它們不是內核的一部分。

首先應該確保所部署的任何一組擴展模塊都能夠一起工作，而不會相互影響。此外還有性能方面的考慮。有些擴展模塊只是簡單的 SQL 擴展，而另一些擴展模塊帶有共用對象或 DLL，這會消耗更多資源並影響整體性能，一定要瞭解這些擴展模塊將消耗哪些資源。

更重要的是，任何預載入的擴展模塊都會成為伺服器的一部分。無論是否通過發出 CREATEEXTENSION… 語句創建了 SQL 介面，這些預載入擴展模塊都將在後臺工作。例如，無論是否創建了 SQL 介面，將 pg_stat_statements 添加到共用預載入庫中都會導致性能下降。這裡的總體經驗是仔細考慮是否真的需要這些擴展模塊。

下麵是一些很有用的關於對擴展模塊的查詢。

可以查詢系統視圖pg_extension以獲取有關已安裝擴展模塊的信息。

SELECT * FROM pg_extension;

類似地，可以找出系統上所有可用的擴展模塊。

SELECT * FROM pg_available_extensions();

還可以查詢所擁有的擴展模塊的可用版本。

SELECT * FROM pg_available_extension_versions();

錯誤10：忽略支持工具

除 PostgreSQL 集群本身以外，還應該考慮需要哪些其他支持工具以改善 PostgreSQL 的使用體驗，因此有必要瞭解可用的工具。由於舊版本存在嚴重問題，人們對某些工具存有誤解，所以應看下新版本、各個社區的活躍性以及發佈的頻率。

例如，讓我們回顧一下 PostgreSQL 生態系統中用於連接池和負載均衡的幾個工具：PgBouncer、HAProxy 和 Pgpoo II。

HAProxy 是一個負載均衡器。請註意，與各種操作系統發行版一起打包的 HAProxy 版本很舊。如 CentOS 7 中版本為 1.5，CentOS 8 中版本為 1.8，而 HAProxy 的最新版本是 2.6。作為參考，HAProxy 2.4 有 1687 個新提交代碼。雖然使用操作系統發行版提供的包更容易，但這些包可能太舊了。

PgBouncer 是一款輕量級連接池。雖然它是單線程的，但如果運行多個 PgBouncer 實例並監聽同一埠，則支持 SO_REUSEPORT 選項的內核可能會允許負載均衡。需要檢查內核文檔，看看它是否支持負載均衡或輪詢，也許根本不支持。使用 systemd 模板，可以以非常簡單和優雅的方式運行多個 PgBouncer 實例。只需創建文件 /etc/systemd/system/[email protected]，並使用 systemctl start pgbouncer@1、systemctl start pgbouncer@2 等命令運行任意數量的 PgBouncer 實例。

Pgpool II 在過去幾年中取得了很大進步，添加了很多功能，包括監控和仲裁，所以它提供的不僅僅是連接池。

要選擇那種工具呢？PgBouncer、HAProxy、Pgpool II，還是 PgBouncer 和 HAProxy？答案取決於多種因素，例如要使用 HAProxy，需要考慮是否配置流複製，是否要為讀取和寫入設置單獨的埠等。最後的選擇將取決於具體的使用場景（在某些情況下還有誤用案例！）。

多種原因使 PostgreSQL 成為了一個非常流行的開源資料庫。它被設計為易於使用和可擴展，以滿足廣泛的用戶需求。然而，這種靈活性同時也意味著在使用它時必須審視用法，併在安裝時就考慮有針對性地進行相應的配置。這樣會使應用程式的性能更好，更能使用戶怡然自得，而且從長遠看，還可以節省大量成本。

作者 | Hamid Akhtar 編譯 | 王雪迎

本文來自博客園，作者：古道輕風，轉載請註明原文鏈接：https://www.cnblogs.com/88223100/p/10-common-postgresql-mistakes-and-how-to-avoid-them.html