PostgreSQL 作爲一個優雅而且穩健的數據庫越來越被大家所採納，除了 Mysql 以外很多人都開始接觸並學習 PostgreSQL。但是 PostgreSQL 和傳統的一些數據庫系統還有有點點小小的鴻溝，有其一些架構和思維上變化，爲此我們給介紹一些 PostgreSQL 的基本技巧和原生思維方式給初學者幫助大家儘快入門並提高。

開始使用 PostgreSQL 既令人興奮又充滿挑戰。

tuples 元組是行的物理版本

很多初學者進入 PostgreSQL 世界遇到的第一個困惑或許要數元組了。元組是什麼？簡單來說，Postgres 中的元組是一行數據的物理版本。這意味着當一行中的數據發生更改時，Postgres 不會更改現有數據，而是爲該行增加一個新的版本（元組）。這個版本控制系統稱爲 MVCC（多版本併發控制），瞭解它對於設計性能良好的系統非常重要。

以下是各種寫入操作期間發生的情況：

當你執行一個 DELETE 命令，它不會立即回收磁盤空間。相反，舊元組被標記爲死亡，但會一直保留到 VACUUM 刪除它。 如果這些死元組可以累積並通過大量清理而被刪除，則會導致表和索引膨脹。

同樣，當 UPDATE 一行，Postgres 不會修改現有的元組。相反，它創建該行的新版本（一個新元組）並將舊的標記爲已死。

取消 INSERT 創建一個死元組，就是在插入一條記錄然後回滾該操作，則要插入的元組將被標記爲死亡。

爲了幫助掌握這些概念，Postgres 中的每個表都有可以選擇的隱藏列：ctid, xmin，和 xmax。ctid 表示元組的位置（頁碼 + 其中的偏移量），而 xmin 和 xmax 可以被視爲元組的 “出生日期” 和“死亡日期”。

通過儘早瞭解這種行爲，將能夠更好地應對與磁盤空間、膨脹和自動清理進程等機制。

上面，創建了一個只有一行的表，然後檢查該行的活動元組的位置（ctid），進行一個 UPDATE 操作，從邏輯上講，它不會做任何事情，它不會實際改變值。但地點變了，從 (0,1)（第 0 頁，偏移量 1），至 (0,2)。因爲在物理上，Postgres 創建了一個新的元組——一個新的行版本。瞭解 Postgres 的這種行爲將幫助設計更高效地工作的系統。

EXPLAIN

瞭解查詢的運行方式對於優化其性能至關重要。在 PostgreSQL 中可以使用 EXPLAIN 命令可以用來了解查詢運行的過程。爲了獲得更精細的視角，需要使用 特殊參數化的

EXPLAIN(ANALYZE, BUFFERS)

EXPLAIN 本身提供查詢計劃，讓用戶深入瞭解 Postgres 打算用來獲取或修改數據的操作。這包括順序掃描、索引掃描、連接、排序等等。該命令應單獨用於檢查查詢計劃而不執行。添加 ANALYZE 混合不僅顯示計劃的操作，還執行查詢並提供實際的運行時統計信息。

例如，可以將估計行數與實際行數進行比較，從而幫助瞭解 Postgres 可能出現問題的地方。它還提供每個執行乾的操作計時信息。

BUFFERS 選項，則提供有關緩衝區使用情況的信息。具體來說，緩衝池中命中了多少塊或從底層緩存或磁盤讀取了多少塊。這提供了有關查詢的 IO 密集程度的底層操作信息。

最佳 UI 工具選擇

要深入瞭解 Postgres 的世界時，初學者面臨的第一個選擇是使用哪個客戶端或界面。雖然許多初學者因爲 pgAdmin 的受歡迎程度和可訪問性而開始使用它，但隨着對 Postgres 瞭解一點點加深，就會發現一些更強大和通用的工具可用。

當然 PostgreSQL 最強大的客戶端之一是其內置的命令行工具 psql。雖然命令行界面對某些人來說可能看起來令人生畏或不方便，但 psql 包含了高效數據庫交互的功能。而且，它無需額外部署，始終和數據庫共存。Psql 在配合上 tmux 可以讓 DBA 和運維能夠輕鬆管理多個會話和腳本。

對於更加喜歡圖形界面的用戶，有一些界面可以在用戶友好性和高級功能之間提供平衡，而且使用圖形界面可以幫助初學者突破學習的屏障，讓學習曲線更加平滑。

Heidisql、DBeaver、 JetBrains DataGrip 和 Postico 提供了複雜的界面，支持查詢執行、數據可視化等。

Heidisql

DBeaver

DataGrip

Postico

最後要說的是，無論選擇哪種圖形工具，都需要投入一些時間來了解其細節 psql 可能會非常有益。

日誌記錄設置

與許多系統一樣，在 Postgres 中，日誌是信息寶庫，可讓您詳細瞭解系統的操作和潛在問題。通過啓用全面的日誌記錄，可以領先於問題、優化性能並確保數據庫的整體健康狀況。

選擇要記錄的內容：有效記錄的關鍵是知道要記錄的內容而不會使系統不堪重負。 通過設置參數：

log_checkpoints = 0,
log_autovacuum_min_duration = 0,
log_temp_files = 0，

和

log_lock_waits = on，

可以瞭解檢查點、自動清理操作、臨時文件創建和鎖定等待。這些是一些最容易出現問題的領域，因此對於監控至關重要。

洞察力和開銷之間的平衡：

需要注意的是，雖然大量日誌記錄可以提供有價值的洞察力，但它也會帶來開銷。 如果設置 log_min_duration_statement 到一個非常低的值。例如，將其設置爲 200ms 會記錄每一條花費比這更長的時間的語句，這既可以提供信息，也可能會降低性能。始終保持謹慎並意識到 “觀察者效應” ——監控過程對被觀察系統的影響。但如果沒有日誌中的詳細見解，診斷問題就會更具挑戰性。

從本質上講，雖然日誌記錄是 Postgres 工具庫中一個非常強大的工具，但它需要仔細配置和定期審查，以確保它仍然是一種幫助，而不是一種障礙。

性能擴展

爲了維護 Postgres 數據庫的性能和健康狀況時，一些擴展可能是是最佳工具套件。比如 pg_stat_statements。

pg_stat_statements 模塊提供了一種跟蹤服務器成功執行的所有 SQL 語句的執行統計信息的方法。通俗地說，它可以幫助監控哪些查詢正在頻繁運行、哪些查詢消耗更多時間以及哪些可能需要優化。 通過此擴展，可以瞭解數據庫的操作，從而可以發現並糾正效率低下的情況。

儘管 pg_stat_statements 是自上而下查詢分析的核心，還有其他值得注意的擴展可以提供更深入的見解：

pg_stat_kcache：有助於瞭解實際的磁盤 IO 和 CPU 使用情況，這正是您識別導致高 CPU 利用率或磁盤 IO 的查詢的方法

pg_wait_sampling 或者 pgsentinel：這兩個可以更清晰地顯示您的查詢在哪裏花費時間等待 – 提供所謂的等待事件分析，又稱活動會話歷史記錄分析（類似於 RDS Performance Insights）

auto_explain：此擴展自動記錄慢語句的執行計劃，使理解和優化它們變得更簡單

請記住，這些擴展需要一些初始設置和調整才能獲得最佳結果和較低的開銷。另外，大多數託管 Postgres 提供商並不提供 pg_stat_kcache 等這些插件。

DB 分支

數據庫的開發和測試過程通常需要複製數據，這可能會佔用大量資源、速度緩慢且繁瑣。然而，通過精簡克隆和分支，有一種更聰明的方法。

精簡克隆

精簡克隆工具提供輕量級、可寫的數據庫克隆。這些克隆與源共享相同的底層數據塊，但對用戶來說顯示爲獨立的數據庫。當對克隆進行更改時，只有這些更改會消耗額外的存儲。這是使用寫時複製 (CoW) 實現的，類似於容器或 Git 的功能，但在塊級別而不是文件級別。這使得創建用於開發、測試或分析的多個副本變得異常快速和高效。

數據庫分支的好處

數據庫分支是精簡克隆的擴展，能夠保存進度並允許基於新狀態進一步創建克隆。 就像代碼版本控制一樣，數據庫上下文中的分支允許開發人員在主數據集之外創建分支。 這意味着您可以在隔離環境中測試新功能或更改，而不會影響主要數據。

DBLab 和 ChatGPT

Database Lab (DBLab) 等工具提供強大的精簡克隆和分支功能。 此外，當需要與 ChatGPT 等人工智能解決方案結合使用時，開發人員甚至可以通過 SQL 查詢實驗獲得即時結果，而不會影響生產或同事的工作。ChatGPT 經常出現幻覺問題，因此使用克隆驗證人工智能生成的建議始終很重要。分支提供了最具成本效益和時間效率的方法。

從本質上講，利用精簡克隆和數據庫分支意味着更快的開發週期、降低的存儲成本以及無風險實驗的能力。

數據校驗

數據完整性是任何數據庫的基石。 如果不相信數據的準確性和一致性，即使是最先進的數據庫結構或算法也會變得毫無用處。 這就是 Postgres 中的數據校驗和發揮關鍵作用的地方。

數據校驗和

在數據庫上下文中，校驗和是從數據塊中所有字節之和得出的值。如果 啓用了數據校驗和，Postgres 將使用它來驗證磁盤上存儲的數據的完整性。 當數據寫入磁盤時，Postgres 會計算並存儲校驗和值。隨後，當該數據被讀回內存時，Postgres 會重新計算校驗和並將其與存儲的值進行比較，以確保數據沒有被損壞。

重要性

磁盤級損壞可能是由多種因素引起的，從硬件故障到軟件錯誤。 啓用數據校驗和後，Postgres 可以在損壞的數據影響您的應用程序或導致更大問題之前識別出損壞的數據。

激活

需要注意的是，數據校驗和需要在數據庫集羣創建時激活（initdb）。如果不轉儲和恢復數據，或者不使用特殊工具，則無法爲現有數據庫集羣打開它們， pg_checksums（這需要經驗）。與數據校驗和相關的開銷相對較小，特別是與確保數據完整性的好處相比。

自動清理

Postgres 中的自動清理過程就像數據庫的清潔人員。自動清理進程在後臺工作，清理舊數據併爲新數據騰出空間，以確保數據庫保持高效。

Postgres 中的 INSERT, UPDATE，或者 DELETE 的操作都會創建行（元組）的一個版本。隨着時間的推移，這些舊版本會累積並需要清理。自動清理通過回收存儲空間、刪除死行來進行清理。它還負責保持表統計信息最新並防止事務 ID 環繞事件。

如果不定期進行自動清理，數據庫可能會出現膨脹——數據庫保留未使用的空間，這會減慢查詢速度並浪費磁盤空間。另一個問題是過時的統計數據，可能導致計劃選擇次優和性能下降。

配置自動清理使其運行更頻繁並更快地完成任務。在高層次上，調整必須在兩個方向上進行：

給予 autovacuum 更多權限

更多的工作人員，更大的配額，因爲默認情況下，它只允許 3 個工作進程，並且受到相當保守的限制。

讓它更頻繁地觸發

因爲默認情況下，只有當元組的 10-20% 發生重大更改時，它纔會觸發；

在 OLTP 中，可能需要將其減少到 1% 甚至更低。

查詢優化

當談到 Postgres 的性能時，在大多數情況下，最好 “足夠好” 地優化 Postgres 配置，不經常重新審視決策（僅當發生 Postgres 主要升級等重大變化時），然後完全專注於查詢調優。

通過初期調整 Postgres 配置可以提高性能。但隨着應用程序的增長和發展，性能的主要爭奪通常從配置轉移到查詢優化。正確結構化的查詢可能是平滑擴展的應用程序和在負載下逐漸停止的應用程序之間的區別。

調整與優化

初學者中有一個常見的誤解：“如果我將配置調整得足夠好，就不會有問題。” 配置調整至關重要，但這只是開始。 最終，重點將不得不轉向不斷優化查詢。

工具調優化工具

前面提到老的 pg_stat_statements 是識別有問題查詢的寶貴工具。它提供了 SQL 語句的排名列表，按各種指標排序。當與 EXPLAIN (ANALYZE, BUFFERS)，我們在上面也討論過，可以瞭解查詢的執行計劃並查明效率低下的地方。

索引維護

在任何關係數據庫系統，影響性能的最關鍵因素是索引，Postgres 中更是如此。

隨着時間的推移，隨着數據的變化，索引變得碎片化並且效率降低。即使使用最新 Postgres 版本（特別是使用 btree 優化的 Postgres13 和 14）和經過跳優的 autovacuum，索引健康狀況仍然會隨着時間的推移而下降，同時發生大量寫入。

健康指數

當插入、更新或刪除數據時，反映該數據的索引會發生變化。這些更改可能會導致索引結構變得不平衡或出現死條目，從而降低搜索性能。

索引重建

索引不會無限期地保持其最佳結構。它們需要定期重建。此過程涉及創建新版本的索引，這通常會產生更緊湊、更高效的結構。爲這些重建做好準備（最好以自動化方式進行）可確保數據庫性能保持一致。

清理

除了重建之外，刪除未使用或冗餘的索引也同樣重要。它們不僅浪費存儲空間，還會減慢寫入操作。定期檢查和清理不必要的索引應該成爲日常維護的一部分。

重申一個關鍵點：索引至關重要，但像所有工具一樣，它們需要維護。保持它們的健康對於維持 Postgres 數據庫的快速性能至關重要。

總結

無論剛剛開始使用 Postgres 的初學者還是有一定基礎的 Postgres 用戶我們給出這些技巧都會有會有所獲益。

除了這些，Postgres 官方的文檔和一些教程都是學習的最權威和最佳材料，當然如果你對其代碼感興趣的話，可以直接從代碼層次獲得更加底層和深入的見解。

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/3Zu6WyEv35sz7tuK6Q0vUw