主題: PostgreSQL

PgBouncer 不會讓 PostgreSQL 無限擴張：先算連線預算，再選 pool mode

從 connection、session 與 transaction 的邊界，理解 PostgreSQL 連線預算、PgBouncer 三種 pool mode、session state 相容性，以及 `pg_stat_activity` 的觀測方法。

API 尖峰出現 too many connections 時，最直覺的反應往往是把 max_connections 調大。這個做法有時必要，但它不是免費的擴充按鈕。PostgreSQL 會為 client connection 建立 server process，max_connections 也會影響部分資源配置。官方文件把它定義為可同時連上資料庫的上限，不是每秒能跑多少 SQL 的速度上限。

連線池處理的是另一件事：讓許多 client connection 在不同時間重用較少、受控制的 PostgreSQL server connection。它能減少建立連線的成本，也能避免每個應用程式執行個體各自把連線數推到資料庫極限。不過，正在跑的慢查詢不會因為多了一層 PgBouncer 突然變快。

先把 connection、session 與 transaction 分開

這三個詞常被混著用，設定就容易跟著混亂：

PgBouncer 讓 client 先連到 pooler，再由 pooler 把實際的 PostgreSQL connection 借出去。關鍵不是「有沒有連線池」，而是「什麼時候可以把借出的 connection 交給下一個 client」。

假設一個服務從 2 個容器擴展到 8 個，每個容器的 application pool 上限是 10。光是這組設定就可能要求 80 條資料庫連線；還沒算背景 worker、管理工具、部署期間重疊的舊新版程式，以及維運保留空間。單看一個容器的 pool 很小，總和仍可能超出資料庫能承受的範圍。

max_connections 是預算，不是效能旋鈕

先從現況量測，再決定預算。pg_stat_activity 對每個 server process 提供一列活動資訊，能先把「有多少連線」拆成「有多少真的在跑、多少閒置、是否有 transaction 沒有結束」。例如可以先做這個彙總：

SELECT state, count(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state
ORDER BY state;

一般 idle 表示連線還在，但目前沒有執行 query；它不等於一定發生洩漏。idle in transaction 則值得優先追查，因為 transaction 尚未結束，可能還持有 lock 或舊 snapshot，讓其他工作與清理更難進行。pg_stat_activity 文件是判讀欄位與狀態的準則。

連線預算至少要包含所有應用程式執行個體、背景工作、管理連線與可用餘裕。不要把每個 service 的 pool 上限都當成彼此無關的數字。提高 max_connections 前，也要確認總 server connection、記憶體、查詢併行量與尖峰等待時間，而不是只看到錯誤訊息就把上限加大。

Pool mode 決定可以重用到什麼程度

PgBouncer 的三種模式差別在於 server connection 何時回到 pool：

• session：client 斷線後才歸還。適合需要固定 session 的管理工具、listener 或長工作；重用率最低，但 session state 能持續。
• transaction：transaction 結束後歸還。適合大量短小、彼此獨立的 OLTP request；下一個 transaction 不能假設仍是同一個 session。
• statement：每個 query 結束後歸還。只適合少見的單 statement 工作；不允許跨多個 statement 的 transaction。

PgBouncer 的設定文件明確定義這三個歸還時機。多數網站 API 的 OLTP 工作負載適合先評估 transaction pooling。OLTP 是 Online Transaction Processing，例如建立訂單、讀取帳戶、更新庫存這類短小且頻繁的線上交易；它和需要長時間掃描大量資料的報表、分析或 ETL 工作不同。

Transaction pooling 的代價是不能依賴 session state

transaction pooling 的效果來自「一個 transaction 結束後就能換下一個 client 使用」。因此，下一個 transaction 可能接到完全不同的 PostgreSQL session。這些需求不應直接假設能在 transaction pooling 中延續：

• temporary table 要跨 transaction 使用。
• 長時間維持的 LISTEN／NOTIFY listener。
• 期待 session-level SET 在下一個 transaction 仍存在。
• 依賴 session-level advisory lock 的協作流程。

如果設定只需要一個 transaction，就把它限制在那個 transaction 內。例如 SET LOCAL 的範圍會隨 transaction 結束；它和「啟動時設定一次，之後永遠依賴同一條連線」是兩種不同的設計。PostgreSQL 的 SET 文件說明了 session 與 transaction-local 設定的生命週期。

BEGIN;
SET LOCAL statement_timeout = '2s';
SELECT * FROM orders WHERE id = 42;
COMMIT;

Prepared statement 也要看 driver 與 PgBouncer 版本、設定方式。現在的 PgBouncer 可以在 transaction pooling 中追蹤 protocol-level named prepared statement，但需要啟用 max_prepared_statements；SQL 層的 PREPARE／EXECUTE 不會靠同一套透明重寫機制處理。PgBouncer FAQ列出了這個邊界。遇到依賴 session state 的工作時，direct connection 或 session pooling 通常比硬塞進 transaction pooling 更好維護。

max_client_conn 可以排隊，不能製造資料庫容量

max_client_conn 控制 PgBouncer 可接受的 client connection 數量；default_pool_size 則是每個 user／database pool 的預設 server connection 上限。這兩個數字都不是 PostgreSQL 的全域處理能力。不同 user 或 database 會形成多個 pool，總 server connection 可能是多個 pool 相加；需要資料庫整體上限時，還要看 max_db_connections 等設定。

當 client 在 PgBouncer 等候，但可用的 server connection 都正在跑 query，提高 max_client_conn 只會讓更多 client 能在入口排隊。它不會讓查詢少掃資料、讓 lock 消失，或增加 CPU。這時應該一起看 query duration、等待種類、pool queue、active connection 與應用程式的並行量。近期的 PostgreSQL 社群討論也有相同情境：transaction pooling 能降低連線重建與閒置 session 的壓力，卻不能消除真正同時執行的高負載查詢。

上線前先做相容性清單與負載驗證

我會把 pooler 上線分成幾個小檢查，而不是先套一條「每個 CPU 幾條 connection」的公式：

1. 列出所有 service、worker 與執行個體的 pool 上限，計算尖峰時可能同時要求的數量。
2. 用 pg_stat_activity 區分 active、idle 與 idle in transaction，找出實際的壓力來源。
3. 搜尋 temporary table、LISTEN、session-level 設定、advisory lock 與 prepared statement，為每個需求決定 direct、session 或 transaction 路徑。
4. 以代表性流量測試 pool queue、查詢延遲、timeout 與錯誤率，再逐步調整上限。

這樣做的目的不是追求最小的 connection 數，而是讓資料庫在尖峰時仍有可預期的併行量與維運空間。pooler 是保護邊界，不是把排隊和慢查詢藏起來的黑盒子。

我學到什麼

• 我會先把所有執行個體的 pool 上限加總，再決定 PostgreSQL 的連線預算；單一容器的設定很容易掩蓋總量。
• transaction pooling 以 transaction 為歸還邊界，適合短小、獨立的 OLTP 工作；session state 需求則要走相容的連線路徑。
• max_client_conn 增加的是接入或等候空間，不是資料庫真正執行慢查詢的能力。
• 上線前應同時量測連線狀態、pool queue 與 query duration，並保留管理與突發狀況所需的連線餘裕。

外部參考資料

• PostgreSQL：Connections and Authentication
• PostgreSQL：Monitoring Database Activity
• PostgreSQL：SET
• PgBouncer：Configuration
• PgBouncer：FAQ
• Canonical：Connection pooling
• Reddit：PostgreSQL high connection load with PgBouncer

延伸學習

• Canonical：Connection pooling：先建立連線池與 PostgreSQL process 成本的直覺。
• PgBouncer：Configuration：核對 pool mode、queue 與 pool size 的精確語意。
• EDB：Postgres Connection Pooling：英文公開影片，適合先認識 PgBouncer 與 pgpool 的角色；設定細節仍以目前官方文件為準。