您正在啟動一個新專案或公司，並決定在 Postgres 上執行。困難的部分（選擇資料庫）已經結束，現在有趣的部分開始了：確保在未來幾年內不再需要考慮這個問題。
從連線池到安全基礎知識，再到擴充套件和索引，這篇文章將介紹一些鮮為人知的配置 Postgres 的技巧。
使用 PGBouncer 為 Postgres 設定連線池
預設情況下，Postgres 會為每個客戶端連線從主作業系統程序中分叉出一個單獨的程序。在容量較小的情況下，建立和銷燬這些程序所需的時間並不重要，而且這些程序也不會被重複使用。您可以手動設定最大連線數，但隨著規模的擴大，最終可能會遇到問題。連線池可以幫助你從本質上 “快取” 這些程序，並在客戶端連線和斷開資料庫時重複使用它們。
雖然可以在應用程式邏輯中建立連線池，但大多數人還是會選擇第三方工具，在 Postgres 中就是 PGBouncer。它是一個開源、輕量級的連線池，可以安裝在資料庫伺服器或應用伺服器上。有 3 種級別的連線池可供選擇：
- 會話池化：忠實於 “客戶端連線無限期” 模式，在客戶端連線的整個過程中保持連線開放。
- 事務池：連線只持續一個事務，之後會被送回池中。
- 語句池：連線只在一個查詢中持續，因此如果一個事務中有多個連線，則根本無法使用。
大多數人會選擇會話池，因為它最保守，丟棄連線的風險也最小，但每個應用程式都不一樣，你需要根據自己的限制條件找出正確的模式。
注：大多數網路框架都將其留給第三方工具處理（參見 Django 論壇中關於 PGBouncer 的主題），但 Rails 實際上提供了原生連線池功能。
連線池對效能的影響
百萬美元的問題：這真的有效嗎？Percona 執行了一系列基準測試，以瞭解 PGBouncer 對效能的影響。在併發客戶端數量較少（小於 60 個）的情況下，由於池的開銷，PGBouncer 實際上會大大降低每秒事務處理量 (TPS)。但當併發客戶端數超過 100 個時，效能優勢就會顯現出來。
PGBouncer
那麼，您需要立即使用連線池來支援最初的幾個使用者嗎？也許不需要。但是，一旦您的流量達到中/低水平，使用 PGBouncer 會對您有所幫助。
Postgres 安全入門
在開發專案的最初幾周，通常只有一兩個開發人員在一個空資料庫上工作，安全性並不是最重要的。但是，當您向全世界釋出應用程式時，您就需要考慮安全問題了。對於資料庫，有時似乎有無數種不同的方法來鎖定。
在主機或使用者級別限制訪問
讓我們從訪問開始。Postgres 通過兩種方式限制訪問許可權：
- 在主機層面-定義具有訪問許可權的 IP 地址和域
- 在使用者層面-定義資料庫使用者及其許可權
PGDATA 目錄中的 pg_hba.conf 檔案是定義誰可以連線哪些資料庫的地方。如果其中沒有客戶的條目，他們將無法訪問資料庫。假設你的應用伺服器執行在其他地方，下面是允許它訪問資料庫的方法：
# Trust any connection via TCP/IP from this machine host all 127.0.0.1 255.255.255.255 trust
除了 “信任來自本機的任何連線” 之外，還有大量不同的方法來驗證客戶端與資料庫伺服器之間的關係，包括密碼、身份標識和證書。如果你放棄了 RDS 的舒適性，在與資料庫相同的伺服器上執行後端，你可以通過 Unix 套接字而不是 TCP/IP 進行連線。
授權和許可權
一旦客戶端本身通過了身份驗證，就需要處理授權問題。SQL 標準定義了一個許可權系統，Postgres 中的每個物件（如表、行等）都有不同的許可權，可以分配給使用者：如 SELECT 和 UPDATE ，還有 TRUNCATE、 REFERENCES、 TRIGGER等。您可以使用 GRANT 命令授予使用者許可權。
最佳做法是遵循最小許可權原則，因此為客戶建立的資料庫使用者只能訪問其需要訪問的內容。
行級安全
最後要介紹的是行級安全。行級安全是從表（而不是使用者）的角度出發，限制哪些行可以被訪問、更新等。預設情況下，表不啟用 RLS，因此使用者可以按照自己的訪問策略行事。要為表啟用 RLS，可以從以下步驟開始：
ALTER TABLE [table_name] ENABLE ROW LEVEL SECURITY
然後新增策略。比方說，您想限制只有已定義在 jedi 使用者組中的可信人員才能讀取 lightsaber_internals 表，這樣只有 lightsaber 的所有者才能檢視其內部細節。您可以這樣做
ALTER TABLE lightsaber_internals ENABLE ROW LEVEL SECURITY CREATE POLICY jedi_only ON lightsaber_internals TO jedi USING (jedi = lightsaber_jedi);
當你需要比表格更細粒度的安全性時（涉及 PII 等的情況），像這樣的 RLS 策略就非常有用。
提前考慮擴充套件問題
在我工作過的每一家初創公司中，都會在某種程度上對資料庫進行手動擴充套件。有一天，你一覺醒來，發現 Datadog 因為你的 Postgres 伺服器完全沒有空間而抓狂。你會進行調查，更新事件頁面，最終升級磁碟大小，直到這種情況再次發生（不過下一次可能是記憶體問題）。提前解決這些問題會有所幫助！幾點建議
1. 設定資料庫監控
我工作過的大多數公司都使用 Datadog 進行資料庫監控。如果你使用的是受管理的資料庫服務，那麼使用他們的原生工具也許還能應付一下。Datadog 部落格上有一篇很好的文章，介紹了你應該關注的主要指標，如讀寫吞吐量、順序掃描、寫入磁碟的資料等。
2. 制定縱向擴充套件指南
當你的團隊被呼喚時–這種情況總會發生–你最不希望看到的就是每個人都需要手忙腳亂地解決問題，而在大多數情況下，簡單的擴充套件就能解決問題。當空間或計算能力不足時，最好為團隊制定一個基本計劃，說明哪些工作在計劃範圍內。
3. 手動或者自動維護資料庫
在 Postgres 中 DELETE 資料或 UPDATE 資料（在功能上等同於刪除和插入）時，Postgres 不會立即刪除資料（😱）。取而代之的是，通過在 xmax 標頭中儲存刪除的事務 ID，將其 “marked” 為已刪除；這樣做的原因是為了讓 Postgres 中的 MVCC 更簡單。但是，如果這些記錄最終沒有被真正刪除，它們就會開始浪費磁碟空間，給你帶來麻煩。
刪除這些記錄的最簡單方法是使用 VACUUM 命令。你可以在死行積累到一定程度時手動執行 vacuum，甚至可以將其設定為每隔 X 分鐘執行一次，但更好的策略是根據死行積累的數量進行自動維護（Autovacuum）。調整 Autovacuum 是一個細緻入微的話題，超出了本篇文章的討論範圍： 我強烈建議閱讀 2ndQuadrant 的相關文章。
4. 建立一個或兩個讀取副本
這一點很簡單。如果預計流量會顯著增加（即將釋出等），可以輕鬆建立只讀副本（或至少一個）；它們將有助於從主資料庫例項中解除安裝一些工作。
如果您選擇多個副本，那麼當其中任何一個副本因故宕機時，您將獲得提高可用性的額外好處。在大多數 DBaaS 提供商中，新增副本都非常簡單；只需注意成本：儘管副本是隻讀的，但其價格通常與主 DB 例項相同。
為（預計的）最大表新增索引
資料庫索引通過建立輔助資料結構來加快讀取查詢的速度，從而提高掃描速度。對於許多用例來說，為一兩個表新增索引基本上是不費吹灰之力的事。在 Postgres 中，可以使用 CREATE INDEX 命令建立索引（咄）。當你查詢一個表時，資料庫會檢查是否存在索引，如果存在，就使用它（順便說一下，你可以用 EXPLAIN 來驗證是否存在索引）。
Postgres 中最常用的索引型別（使用 CREATE INDEX 時的預設型別）是 B-Tree Index。從本質上講，B-Tree 索引會對要建立索引的列進行排序，並儲存指向排序行的指標。這樣，你就可以在任何你想要的列上獲得二進位制搜尋效率，而不僅僅是實際表的排序列（如果有的話）。關於這些樹是如何實現的，你可以在這裡的 Postgres 文件中閱讀更深入的內容。
索引雖然有用，但並不全是好玩的遊戲；它們會佔用空間，而且如果不注意建立索引的數量和型別，它們實際上會開始降低資料庫效能。沒有人比 Postgres 文件本身說得更清楚了：
索引主要用於提高資料庫效能（儘管使用不當會導致效能降低）。
在引擎蓋下，建立索引時，Postgres 會生成一個查詢表，其中包含索引和指向索引記錄的指標。過多的查詢表會佔用磁碟空間，延長 INSERT 查詢的時間，並迫使查詢引擎在選擇如何執行查詢之前考慮更多選項。
新增一些 Postgres 擴充套件
Postgres 的獨特之處在於它對第三方擴充套件的原生支援。你可以用 SQL 和 C 語言建立擴充套件，它們可以小到幾條語句，也可以大到整個軟體庫。使用公開/開源擴充套件就像使用軟體包一樣；既然可以使用別人的程式碼，為什麼還要自己寫呢？下面是一些比較流行的 Postgres 擴充套件：
Timescale
Timescale 是一個 Postgres 擴充套件，用於處理時間序列資料。簡而言之，它能讓你的查詢更快，並高效地儲存時間序列資料。你可以在這裡找到安裝說明，如果你的業務確實是基於時間序列資料，也可以考慮 Timescale 的雲託管選項（當然，你可能已經意識到這一點了）。
PostGIS
PostGIS 為 Postgres 新增了對地理資料（如線條、多邊形、位置等）的儲存、索引和查詢支援。如果你使用的是雲服務提供商，大多數都會預裝 PostGIS。但如果你需要自己安裝，可以在這裡找到安裝說明。
pg_stat_staements
pg_stat_statements 會在 Postgres 資料庫中建立一個檢視，其中包含資料庫中每次查詢的統計資料。你可以看到查詢執行所需的時間（平均值、中位數、平均值等）、誰執行了查詢、塊快取命中率、寫入塊的數量等統計資訊（該檢視中共有 44 列）。安裝時，只需將其新增到 .conf 檔案並重新啟動伺服器即可。
pg_audit
pg_audit 可以幫助那些需要接受詳細審計的公司（如政府、金融等）。你可以通過設定 `log_statement=all` 來讓 Postgres 記錄資料庫的每一條語句，但這並不意味著你需要的資訊很容易搜尋和找到。你可以在這裡找到安裝說明。
小結
Postgres是建立公司的最佳（也是最受歡迎的）選擇。希望這些技巧能幫助您開始執行併為擴大規模做好準備。您還有其他建議或想法嗎？請在這裡告訴我們。
評論留言