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