PostgreSQL 作為當下流行的資料庫，不少開發者因其開源、可靠、可擴展等特性把它應用到實際的生產環境中，幫助無數 PostgreSQL 廠商的 Percona 編制了一個最常見錯誤的串列，即使你認為自己已經正確地安裝配置 PostgreSQL，或許仍會發現此串列對于驗證你的安裝配置大有裨益，
原文鏈接：
https://www.infoworld.com/article/3681655/10-common-postgresql-mistakes-and-how-to-avoid-them.html
PostgreSQL 旨在應對廣泛的使用場景，但具有極大靈活性的同時也有不利的一面，使用時應注意不要犯本文所列舉的這些十分常見的設計、配置、調整或其他相關錯誤，
在安裝 PostgreSQL 時可能會忽略很多問題，其中某些問題也許因潛伏而未被發現，隨著時間的推移，它們可能突然爆發并產生重大影響，使其成為大家關注的焦點，這種情況尤其糟糕，
無論是性能明顯下降，還是資源消耗或使用成本的急劇上升，在出現這些情況前盡早發現問題都很重要，而更好的做法是，實施時通過配置以適應所需的作業負載來避免這些問題，
PostgreSQL 可以做到開箱即用，但其配置通常不能很好地滿足需求，默認配置沒有針對任何特定的作業負載進行調整，具有極大的局限性，這種過于保守的配置，目的在于允許 PostgreSQL 運行在任何環境下，并期望用戶根據自己的需要進行配置，
pgtune 工具提供了基于硬體資源和作業負載型別的配置子集，是根據作業負載需要配置 PostgreSQL 集群的良好起點，此外，還可能需要配置 autovacuum、日志、檢查點和 WAL（預寫日志）保留策略等變數，
為服務器進行優化配置，以滿足全部近期需求，同時避免任何不必要的重啟操作，這一點非常重要，所以有必要看一下 pg_settings 系統視圖中所有具有 “postmaster” 背景關系的 GUC，
SELECT name, setting, boot_val
FROM pg_settings
WHERE context = 'postmaster';
尤其在設定高可用（HA）群集時，這點更為重要，因為主服務器的任何停機都會降低群集性能，并導致將備用服務器提升為主服務器角色，
關于這點怎么強調都不為過，我曾親眼看到，僅僅因為未優化的資料庫設計和架構，用戶付出的成本是他們所需成本的五倍多，
這里最好的建議之一是看看現在和近期的作業負載需求時什么，而不是六個月到一年后可能需要什么，向前看得太遠可能意味著你的表是為永遠無法實作的未來需求而設計的，這只是其中的一個方面，
除此之外，過度依賴物件關系映射（ORM）也是性能不佳的主要原因之一，ORM 主要用于使用面向物件的編程語言將應用程式連接到資料庫，久而久之它們會逐漸簡化開發人員的作業，然而，了解 ORM 提供什么功能以及它引入了什么樣的性能影響至關重要，ORM 可能正在后臺執行多個查詢，不管是連接多個表、執行聚合，還是拆分查詢資料，總的來說，使用 ORM 時會引起更高的延遲和更低的事務吞吐量，
另外，改進資料庫架構還為了使資料更加結構化，以便對表或索引進行最佳的讀寫操作，另一種有用的方法是對資料庫進行反規范化，因為這會降低 SQL 查詢的復雜性，減少相關的表連接，進而可以從更少的表中獲取資料，
簡單來說最終驅動高性能的，是對具體環境中的應用程式和作業負載執行三步程序,即“定義、測量、優化”，
根據作業負載調整資料庫，需要深入了解要存盤的資料量、應用程式的性質，以及要執行的查詢型別，可以隨時修改配置并進行基準測驗，直到對高負載下的資源使用滿意為止，
例如，考慮是否可以將整個資料庫放入計算機的可用記憶體中，如果是，那么顯然希望增加資料庫的 shared_buffers 值，類似地，了解作業負載是如何正確配置檢查點和 autovacuum 行程的關鍵，例如，與滿足事務處理性能委員會 C 類基準的混合在線事務處理作業負載相比，為 append-only 型別作業負載進行的這些配置將非常不同，
有很多有用的工具提供了查看查詢性能的功能，關于更多查看查詢性能的說明，可以瀏覽我的博客文章，其中討論了一些可選的開源工具，還可以看下我在 YouTube 上的演示，
在 Percona，我們的兩個工具可以極大地幫助理解查詢性能狀況：
lPMM - Percona監控和管理，是一個免費的、完全開源的專案，提供了一個帶有詳細系統統計和查詢分析的圖形界面，可隨意試用適合MySQL、MongoDB或PostgreSQL的PMM演示程式，
lpg_stat_monitor - 這是pg_stat_statements的增強版本，可以借此更詳細地了解查詢性能狀況、實際的查詢計劃和帶有引數值的查詢文本，可以從我們的下載頁獲得它在Linux上的可用包，也可以從PostgreSQL社區的yum存盤庫獲得RPM包，
乍一看連接配置似乎沒問題，但是我見過太大的 max_connections 值導致記憶體不足錯誤的情況，所以配置 max_connection 還需要注意一下，
配置 max_connections 時必須考慮內核數、可用記憶體量和存盤型別，誰都不希望讓可能永遠不會使用的連接致使服務器資源過載，況且還要為每個連接分配內核資源，PostgreSQL 內核檔案有更多詳細資訊，
當客戶端執行花費很少時間的查詢時，連接池能顯著提高性能，因為在這種型別的作業負載中，生成連接的開銷相對變得很大，
希望 autovacuum 沒有被禁用，我們已經在許多生產環境中看到，用戶完全禁用了 autovacuum，這通常是由于一些潛在的問題所導致，如果 autovacuum 在具體環境中不起作用，那么只可能有以下三個原因：
1.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，
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 或計劃作業，就可以監控或回滾任何在其預期期限之后仍保持活動狀態的準備事務，
對表進行過度索引究竟有沒有問題呢？必須要了解 PostgreSQL 如何管理索引,才能使 PostgreSQL 實體獲得最佳性能，
PostgreSQL 中有多種型別的索引，每種都有不同的使用場景和開銷，B 樹是最常用的索引型別，也用于主鍵，在過去的幾個主要版本中，B樹索引中出現了許多與性能相關（或剝離）的改進，這里是我的一篇博文，討論了 PostgreSQL 14 中的重復版本變動，
當對表執行索引掃描時，對于每個匹配的行，索引會回表訪問以獲取資料和可見性資訊，以便只選擇當前事務可見的版本的資料，過度索引將導致更多的索引更新，因此會消耗更多資源而得不到預期的好處，
同樣，索引不足將導致更多的表掃描，這可能導致更多的 I/O 操作，從而導致性能下降，
創建索引時不僅要考慮表上的索引數量，還要考慮這些索引在所針對的查詢上如何進行優化，理想情況下，希望每次查詢只掃描一個索引，但有一些限制，盡管 B 樹索引支持所有運算子的索引掃描，但 GiST 和 SP GiST 索引僅支持某些運算子，有關詳細資訊，請參閱檔案，
以下簡單的檢查項，可以幫助驗證是否為系統進行了最佳索引設定：
確保配置正確（例如，為相關硬體調整了隨機頁訪問成本），
檢查統計資料是否最新，或者至少檢查運行analyze或vacuum命令的表上是否有索引，確保統計資料最新或接近最新，以便查詢計劃更有可能選擇索引掃描，
在正確的列上使用索引，不要忘記，在索引中包含查詢所需列可以避免回表訪問，即所謂索引覆寫，并非所有索引型別都允許索引覆寫，因此使用時請檢查檔案，
去除不必要的索引，請參閱pg_statio_user_indexes，了解有關索引和塊命中的更多資訊，
了解索引覆寫對重復資料消除、重復版本變動和僅索引掃描等功能的影響，
HA 的作用不僅是保持服務的正常運行，還要確保服務在定義的驗收標準內進行回應，并滿足 RPO（恢復點目標）和 RTO（恢復時間目標）目標，要達到系統正常運行時間要求的9的個數（正常使用時間與總時間之比），請參閱此wiki頁面以計算百分比，
為了滿足 RPO 和 RTO，必須考慮許多因素，包括計劃內停機時間、任何自動或手動操作及其頻率和持續時間，當然還有與計劃外停機相關的成本，
擁有準確和及時的備份，以及有效恢復備份的能力，在定義 RPO 和 RTO 這兩個引數方面起著關鍵作用,其中涉及資料備份的頻率是多少，怎樣管理 WAL 檔案，如何驗證備份和 WAL 檔案等諸多問題，
根據作業負載和可用的維護時間視窗，通常應至少每七天進行一次備份，除此之外，還應該定期測驗恢復程序，以便確認這些備份是有效的，事實上，只有應用程式能夠恢復并進行處理，備份才算成功，不應信任未經測驗的備份，
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();
除 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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/544803.html
標籤：PostgreSQL
上一篇：震驚！火爆全網的ChatGPT背后使用的資料庫居然是……
下一篇：10個常見的 PostgreSQL 錯誤及避坑指南
