PostgreSQL 的高效能調校指南

Contents

像專家一樣調校 PostgreSQL 12 的技巧 | 瞭解如何防止遇到 Postgres 的效能問題

從這 2.5 小時的課程，你會學到

PostgreSQL 架構的理論和它在內部的運作方式
瞭解如何設定 shared_buffers 以提高效能
學習如何配置 Vacuum 以保持資料庫的乾淨和快速
如何有效地使用索引
練習索引最佳化策略
如何理解統計學的意義
掌握如何發現查詢問題
練習查詢最佳化技術
PostgreSQL 擴展( Scaling ) 和複製( Replication )的基本概念

要求

你需要使用一台 Windows/Mac/Linux 電腦，有10GB的可用磁碟空間
需要對資料庫物件（如表格和索引）有基本的熟悉。
對 Linux 有一定的瞭解會有幫助

課程說明

PostgreSQL 是最強大和最容易使用的資料庫管理系統之一。它得到了社群的大力支援，並且正在積極開發，每年都有新的版本。

PostgreSQL 支援 SQL 標準中包含的最先進的功能。它還提供 NoSQL 功能和非常豐富的資料型別和擴充套件。所有這些都使 PostgreSQL 成為軟體系統中非常有吸引力的解決方案。

然而，從它那裡獲得最佳效能並不是一個容易解決的課題。你需要恰到好處地結合經驗法則來起步，適當的測試、紮實的監控和維護來保持系統的良好執行，並使用附加工具來增加核心資料庫不試圖自己處理的功能。

本課程的結構是為您提供實現高效能 Postgres 的理論和實踐兩個方面。它將幫助你使用最新版本的 PostgreSQL 12 為企業應用程式建立動態資料庫解決方案。

你將詳細研究 PostgreSQL 12 的所有高階方面，包括邏輯複製、資料庫叢集、效能調整和監控。你還將使用PostgreSQL 最佳化器，透過研究事務、鎖定、索引和最佳化查詢來配置 Postgres 的高速執行。

你應該對資料庫有一些接觸。對資料庫物件（如表格和索引）的基本熟悉是可以預期的。如果您沒有接觸過PostgreSQL，或者只接觸過一點，您會發現這個課程非常有用。如果您已經使用 PostgreSQL 工作了幾年，您仍然會發現一些您不知道的有用的命令或一些您沒有嘗試過的最佳化方法。你也會對資料庫的工作原理有更多的瞭解。

Postgres高效能調優指南概述：

瞭解PostgreSQL伺服器架構

在本節中，我們將探討 PostgreSQL 的架構
共享緩衝區( Shared Buffers )與作業系統的緩衝區合作工作，而不是取代它。這些快取透過減少必要的物理 I/O 來提高效能
為什麼我們需要仔細設定檢查點( Checkpoints )，以限制崩潰恢復時間，同時不影響系統的其他效能
本節將給大家介紹一下為什麼我們需要 WAL 寫入器，同時也需要背景寫入器
請看查詢處理器( Query Processor )為了獲得結果而必須經過的階段
實用子系統提供了維護資料庫的方法，如申請儲存、更新統計資料和記錄

配置真空( Vacuum )以提高效能

我們將理解為什麼在我們做 UPDATE 或 DELETE 的時候，我們會留下一條死行( dead row )（可能還有一個死的索引條目），需要在以後透過某種形式的真空進行清理
我們將瞭解到，當表格因為過多的死亡 tuples 而變得非常大時，效能將趨於下降。因此，真空過程永遠不應該被避免
本節解釋了為什麼使用資料庫的自動真空功能，進行穩定的低強度的真空工作，而不是禁用該功能，不得不在較大的區塊中進行這種清理

如何有效地使用索引

要知道，每次你在表格中新增或改變行( row )時，新增索引都會增加開銷。每個索引都需要滿足足夠多的查詢，以證明其維護成本的合理性
在本節中，我們將解釋為什麼查詢的執行計劃取決於表內的資料。如果資料的肉體性很低，PostgreSQL很可能會忽略這個索引
在這一節中，我們將學習為什麼索引只有在有選擇性的情況下才有用；它可以用來只返回表格中的一小部分記錄
在這一節中，我們將探討如何有效地使用點陣圖掃描( bitmap scans )

索引最佳化提示

在僅僅使用索引的基礎上，還可以為你的特定應用實施客製化策略，以加快速度
如何能夠透過使用覆蓋索引( covering indexes )只使用索引中的資料來回答查詢
本節介紹了為什麼在外來鍵( foreign keys )上定義索引是一個好的做法
在這一節中，我們將探討部分索引( partial indexes )以及如何享受小而有效的索引
索引可能需要定期重建以使其恢復到最佳效能，針對索引順序對底層資料進行聚類( clustering )也可以幫助提高其查詢速度
我們將解釋什麼時候修改一個表格的填充因子( fill factor )參數是有用的
在這一節中，我們將看到在哪些情況下，使用一個組合索引與多個獨立索引更好

利用統計資料

在這一節中，你將探討統計資料，這些資料可以幫助你找到和分類那些對系統的大部分負載負責的查詢
PostgreSQL 提供了大量的統計資料。在本節中，我們將使你更容易利用它們的洞察力
我們將看到檢測缺失索引的最快方法，但我們也將探索何時有必要放棄索引

發現查詢問題

我們將解釋如何閱讀查詢計劃，並瞭解每個基礎節點型別是如何運作的
我們將看到查詢是如何作為一系列節點執行的，每個節點都做一個小任務，如獲取資料聚合或排序
我們將探索查詢中實際執行時間最長的部分，並看看它們是否有適當的匹配成本
估計的行數和實際的行數之間的差異會導致重大的計劃問題。我們將探討在這種情況下我們能做什麼

如何設定 shared_buffers 以提高效能

我們將看到 PostgreSQL 預設的 shared_buffers 的分配是非常低的，我們需要增加它以允許資料庫有適當的共享記憶體大小
我們將介紹 shared_buffers 是如何與作業系統的快取合作的，而不是取代它，我們應該把它的大小作為總 RAM 的一個適度的百分比
我們將瞭解到，如果我們想做得比相對於作業系統快取而言的共享緩衝區的分配比例更好，我們需要分析緩衝區的快取內容

擴展( Scaling )和複製( Replication )

我們將看到複製也可以用來提高軟體系統的效能，使其有可能在幾個資料庫伺服器上分配負載
在某些情況下，PostgreSQL 提供的複製功能是不夠的。有一些第三方解決方案圍繞 PostgreSQL 運作，提供額外的功能，如 Pgpool-II 可以作為負載平衡器工作，Postgres-XL 實現了多伺服器分散式資料庫解決方案，可以操作非常大的資料量，處理巨大的負載。