前言
「Connection refused」、「too many clients already」——如果你見過這些錯誤訊息，你就知道連線池（Connection Pool）有多重要。
資料庫連線是昂貴的資源。每建立一個連線，背後要經過 TCP 三向握手、SSL 握手（如果有的話）、認證驗證、後端程序 fork（PostgreSQL）等步驟，通常需要 50-200 毫秒。如果每次查詢都開新連線再關閉，光是連線管理的開銷就會把效能吃掉大半。
連線池的概念很簡單：預先建立一批連線，用完放回池子裡給別人用，避免反覆建立和銷毀。但要把連線池調好，需要理解一些底層的原理和取捨。
這篇文章會先講連線池的核心概念，然後分別介紹 PgBouncer（PostgreSQL 的外部連線池）和 HikariCP（Java/JVM 的應用層連線池），最後聊聊監控和調校的實務經驗。
為什麼需要連線池
連線建立的成本
# 實驗：測量連線建立的時間
import time
import psycopg2
def measure_connection_time(n=100):
"""測量建立 N 個連線的平均時間。"""
times = []
for _ in range(n):
start = time.perf_counter()
conn = psycopg2.connect(
host="localhost", port=5432,
dbname="myapp", user="app_user", password="secret"
)
elapsed = time.perf_counter() - start
times.append(elapsed)
conn.close()
avg = sum(times) / len(times)
print(f"平均連線時間: {avg*1000:.2f} ms")
print(f"最快: {min(times)*1000:.2f} ms")
print(f"最慢: {max(times)*1000:.2f} ms")
measure_connection_time()
# 典型輸出（本機，無 SSL）：
# 平均連線時間: 5.23 ms
# 最快: 3.12 ms
# 最慢: 15.67 ms
# 如果加上 SSL：
# 平均連線時間: 25.89 ms
# 跨網路（不同 AZ）：
# 平均連線時間: 50-100 ms
PostgreSQL 的連線限制
-- 查看最大連線數
SHOW max_connections;
-- 預設通常是 100
-- 查看目前的連線使用情況
SELECT
count(*) AS total_connections,
count(*) FILTER (WHERE state = 'active') AS active,
count(*) FILTER (WHERE state = 'idle') AS idle,
count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_tx,
count(*) FILTER (WHERE wait_event_type = 'Lock') AS waiting_for_lock
FROM pg_stat_activity
WHERE backend_type = 'client backend';
為什麼不能無限增加 max_connections？
每個 PostgreSQL 連線 = 一個 OS process
每個 process 約佔 5-10 MB 記憶體
100 連線 ≈ 1 GB RAM（可接受）
1000 連線 ≈ 10 GB RAM（很貴）
10000 連線 ≈ 100 GB RAM（不切實際）
更重要的是：太多 process 會導致 context switch 開銷暴增
通常 CPU 核心數 × 2-4 才是最佳的活躍連線數
PgBouncer — PostgreSQL 的外部連線池
什麼是 PgBouncer
PgBouncer 是一個輕量級的連線池代理，坐在應用程式和 PostgreSQL 之間：
應用程式 (1000 個連線)
↓
PgBouncer (維護 1000 個前端連線)
↓
PostgreSQL (只用 50 個後端連線)安裝與基本設定
# Ubuntu/Debian
sudo apt-get install pgbouncer
# macOS
brew install pgbouncer
;; /etc/pgbouncer/pgbouncer.ini
[databases]
; 資料庫映射：客戶端連的名稱 = 實際的 PostgreSQL 連線
myapp = host=127.0.0.1 port=5432 dbname=myapp
; 也可以指定連線參數
myapp_readonly = host=replica.internal port=5432 dbname=myapp
default_pool_size=20
[pgbouncer]
; 監聽設定
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
; === 連線池模式（最重要的設定）===
pool_mode = transaction
; session: 連線在整個 session 期間獨占（最保守，限制最少）
; transaction: 連線在 transaction 結束後歸還（推薦！）
; statement: 每條 SQL 執行後歸還（最激進，限制最多）
; === 連線池大小 ===
default_pool_size = 20 ; 每個 user/database 組合的預設連線數
min_pool_size = 5 ; 最小連線數（保持預熱）
reserve_pool_size = 5 ; 緊急備用連線數
reserve_pool_timeout = 3 ; 等待多久後使用備用連線
; === 連線限制 ===
max_client_conn = 1000 ; 最大客戶端連線數
max_db_connections = 50 ; 每個資料庫的最大後端連線數
; === 超時設定 ===
server_idle_timeout = 300 ; 後端連線閒置多久後關閉
client_idle_timeout = 0 ; 客戶端閒置超時（0=不限制）
query_timeout = 30 ; 單次查詢超時
query_wait_timeout = 120 ; 等待可用連線的超時
; === 監控 ===
admin_users = pgbouncer_admin
stats_users = pgbouncer_stats
log_connections = 0
log_disconnections = 0
# userlist.txt 格式
# "username" "password"
# 密碼可以是 MD5 hash
"app_user" "md5abc123def456..."
# 生成 MD5 密碼
echo -n "secretapp_user" | md5sum
# 前面加 md5 前綴
Pool Mode 詳解
=== Session Mode（最保守）===
客戶端取得連線後，直到斷開前都獨占這個後端連線。
優點：完全相容所有 PostgreSQL 功能
缺點：連線複用率低
適用：使用 PREPARE statement、SET 變數的應用
=== Transaction Mode（推薦）===
客戶端在 transaction 期間獨占連線，commit/rollback 後歸還。
優點：連線複用率高，大幅降低後端連線數
缺點：不能使用 session 層級的功能（PREPARE、SET、LISTEN/NOTIFY）
適用：大部分 Web 應用
=== Statement Mode（最激進）===
每條 SQL 語句執行完就歸還連線。
優點：連線複用率最高
缺點：不支援多語句 transaction！
適用：只有 autocommit 模式的簡單查詢
# Transaction mode 的限制和解決方案
# 問題：不能用 PREPARE（prepared statements）
# 解決：PgBouncer 1.21+ 支援 protocol-level prepared statements
# 或者在 pgbouncer.ini 設定：
# max_prepared_statements = 100
# 問題：不能用 SET local 以外的 SET
# 解決：把設定移到連線字串或 pgbouncer.ini 的 database 區段
# 問題：不能用 LISTEN/NOTIFY
# 解決：為需要 LISTEN 的連線建立直連 PostgreSQL 的獨立連線
監控 PgBouncer
-- 連接到 PgBouncer 的管理介面
-- psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer
-- 查看連線池狀態
SHOW POOLS;
-- database | user | cl_active | cl_waiting | sv_active | sv_idle | pool_mode
-- myapp | app_user | 45 | 0 | 8 | 12 | transaction
-- cl_active: 活躍的客戶端連線
-- cl_waiting: 等待可用後端連線的客戶端連線
-- sv_active: 活躍的後端（PostgreSQL）連線
-- sv_idle: 閒置的後端連線
-- 查看統計資訊
SHOW STATS;
-- database | total_xact_count | total_query_count | total_received | total_sent | avg_xact_time | avg_query_time
-- 查看每個客戶端連線的狀態
SHOW CLIENTS;
-- 查看每個後端連線的狀態
SHOW SERVERS;
# 用 Python 監控 PgBouncer
import psycopg2
def monitor_pgbouncer():
conn = psycopg2.connect(
host="127.0.0.1", port=6432,
dbname="pgbouncer", user="pgbouncer_stats"
)
conn.autocommit = True
with conn.cursor() as cur:
cur.execute("SHOW POOLS")
columns = [desc[0] for desc in cur.description]
for row in cur.fetchall():
pool = dict(zip(columns, row))
print(f"DB: {pool['database']}")
print(f" 客戶端 - 活躍: {pool['cl_active']}, 等待: {pool['cl_waiting']}")
print(f" 後端 - 活躍: {pool['sv_active']}, 閒置: {pool['sv_idle']}")
# 警告：如果有客戶端在等待
if int(pool.get('cl_waiting', 0)) > 0:
print(f" WARNING: {pool['cl_waiting']} 個客戶端在等待連線！")
conn.close()
HikariCP — JVM 的應用層連線池
為什麼選 HikariCP
HikariCP 是 JVM 生態系中效能最好的連線池，Spring Boot 2.0+ 的預設選擇。它的設計哲學是「極簡主義」——程式碼量少、鎖競爭少、記憶體佔用少。
Spring Boot 設定
# application.yml
spring:
datasource:
url: jdbc:postgresql://localhost:5432/myapp
username: app_user
password: secret
driver-class-name: org.postgresql.Driver
hikari:
# === 連線池大小 ===
maximum-pool-size: 20 # 最大連線數（含活躍+閒置）
minimum-idle: 5 # 最小閒置連線數
# 官方建議：minimum-idle = maximum-pool-size（固定大小池）
# 避免連線的建立和銷毀開銷
# === 超時設定 ===
connection-timeout: 30000 # 取得連線的等待超時（ms）
idle-timeout: 600000 # 閒置連線的最大存活時間（ms）
max-lifetime: 1800000 # 連線的最大生命週期（ms）
# max-lifetime 應該比資料庫的 wait_timeout 短
# === 驗證 ===
validation-timeout: 5000 # 連線驗證超時
# 不建議設 connectionTestQuery，用 JDBC4 的 isValid() 更高效
# === 監控 ===
pool-name: MyAppPool
register-mbeans: true # 透過 JMX 監控
核心參數調校
// 以程式碼方式設定 HikariCP
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://localhost:5432/myapp");
config.setUsername("app_user");
config.setPassword("secret");
// === 最重要的參數：maximum-pool-size ===
// 公式：connections = (core_count * 2) + effective_spindle_count
// 對 SSD 來說 spindle = 1
// 例如 4 核心 CPU：(4 * 2) + 1 = 9，通常設 10-20
config.setMaximumPoolSize(20);
config.setMinimumIdle(20); // 設成和 max 一樣 = 固定大小池
// === 超時設定 ===
config.setConnectionTimeout(30000); // 30 秒取不到連線就報錯
config.setIdleTimeout(600000); // 閒置 10 分鐘回收
config.setMaxLifetime(1800000); // 30 分鐘強制回收（避免 stale 連線）
// === 連線屬性（傳給 PostgreSQL）===
config.addDataSourceProperty("cachePrepStmts", "true");
config.addDataSourceProperty("prepStmtCacheSize", "250");
config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
HikariDataSource ds = new HikariDataSource(config);
maximum-pool-size 的計算
HikariCP 作者的建議公式：
pool_size = (core_count * 2) + effective_spindle_count
範例（4 核心 CPU + SSD）：
pool_size = (4 * 2) + 1 = 9
為什麼這麼小就夠了？
假設：
- 一個查詢的平均 RT（response time）= 10ms
- 其中 CPU 時間 = 2ms，等待 IO = 8ms
- 一個連線 1 秒內可以執行 100 個查詢
- 9 個連線 = 每秒 900 個查詢
如果你需要更高的 throughput，通常問題不在連線池大小，
而是查詢本身需要優化。
常見誤區：
- 把 pool_size 設成 100 甚至 500
- 結果：大量連線搶 CPU 和 IO，context switch 暴增
- 實測效能反而比 pool_size=20 差
HikariCP 監控
// 透過 Metrics 監控（Micrometer + Prometheus）
import io.micrometer.core.instrument.MeterRegistry;
// Spring Boot 自動配置，只需要加依賴
// implementation 'io.micrometer:micrometer-registry-prometheus'
// HikariCP 自動暴露的 Metrics：
// hikaricp_connections_active - 活躍連線數
// hikaricp_connections_idle - 閒置連線數
// hikaricp_connections_pending - 等待連線的請求數
// hikaricp_connections_total - 總連線數
// hikaricp_connections_timeout - 連線超時次數
// hikaricp_connections_creation - 連線建立耗時
// hikaricp_connections_usage - 連線使用耗時
# Prometheus 告警規則範例
"""
# hikari_alerts.yml
groups:
- name: hikari_connection_pool
rules:
- alert: HighConnectionUsage
expr: hikaricp_connections_active / hikaricp_connections_total > 0.8
for: 5m
labels:
severity: warning
annotations:
summary: "連線池使用率超過 80%"
- alert: ConnectionTimeout
expr: rate(hikaricp_connections_timeout_total[5m]) > 0
for: 1m
labels:
severity: critical
annotations:
summary: "出現連線超時！可能需要增加 pool size 或優化查詢"
- alert: ConnectionPending
expr: hikaricp_connections_pending > 5
for: 2m
labels:
severity: warning
annotations:
summary: "有超過 5 個請求在等待可用連線"
"""
Python 應用的連線池
# Python 生態系的連線池方案
# 1. psycopg2 的 SimpleConnectionPool / ThreadedConnectionPool
from psycopg2.pool import ThreadedConnectionPool
pool = ThreadedConnectionPool(
minconn=5,
maxconn=20,
host="localhost",
port=5432,
dbname="myapp",
user="app_user",
password="secret",
)
# 取得連線
conn = pool.getconn()
try:
with conn.cursor() as cur:
cur.execute("SELECT * FROM users WHERE id = %s", (1,))
result = cur.fetchone()
finally:
pool.putconn(conn) # 務必歸還！
# 2. SQLAlchemy 的內建連線池（推薦）
from sqlalchemy import create_engine
engine = create_engine(
"postgresql://app_user:secret@localhost:5432/myapp",
pool_size=20, # 常駐連線數
max_overflow=10, # 超過 pool_size 可以再建 10 個
pool_timeout=30, # 等待連線超時
pool_recycle=1800, # 30 分鐘回收
pool_pre_ping=True, # 使用前先 ping 確認連線有效
echo_pool="debug", # 除錯用
)
# 3. asyncpg（async 環境，效能最好）
import asyncpg
async def setup_pool():
pool = await asyncpg.create_pool(
host="localhost", port=5432,
database="myapp", user="app_user", password="secret",
min_size=5,
max_size=20,
max_inactive_connection_lifetime=300,
)
async with pool.acquire() as conn:
result = await conn.fetch("SELECT * FROM users WHERE id = $1", 1)
return pool
調校與除錯
常見問題：連線洩漏
# 連線洩漏：取了連線沒有歸還
# 症狀：活躍連線數持續上升，最終耗盡
# 在 SQLAlchemy 中偵測連線洩漏
from sqlalchemy import event
@event.listens_for(engine, "checkout")
def receive_checkout(dbapi_connection, connection_record, connection_proxy):
"""連線被取出時記錄。"""
import traceback
connection_record.info["checkout_stack"] = traceback.format_stack()
@event.listens_for(engine, "checkin")
def receive_checkin(dbapi_connection, connection_record):
"""連線被歸還時清除。"""
connection_record.info.pop("checkout_stack", None)
# 定期檢查未歸還的連線
def check_leaked_connections():
"""找出超過 5 分鐘未歸還的連線。"""
pool = engine.pool
print(f"Pool status: {pool.status()}")
print(f"Checked out: {pool.checkedout()}")
print(f"Checked in: {pool.checkedin()}")
print(f"Overflow: {pool.overflow()}")
連線池大小 vs 吞吐量的實驗
# 實驗：不同 pool size 下的吞吐量
import concurrent.futures
import time
from sqlalchemy import create_engine, text
def benchmark_pool_size(pool_size, num_requests=1000, concurrency=50):
"""測試特定 pool size 下的吞吐量。"""
engine = create_engine(
"postgresql://app_user:secret@localhost:5432/myapp",
pool_size=pool_size,
max_overflow=0,
pool_timeout=30,
)
def execute_query(_):
with engine.connect() as conn:
conn.execute(text("SELECT pg_sleep(0.01)")) # 模擬 10ms 查詢
conn.commit()
start = time.perf_counter()
with concurrent.futures.ThreadPoolExecutor(max_workers=concurrency) as executor:
list(executor.map(execute_query, range(num_requests)))
elapsed = time.perf_counter() - start
qps = num_requests / elapsed
print(f"Pool size: {pool_size:3d} | QPS: {qps:.0f} | Total time: {elapsed:.2f}s")
engine.dispose()
# 測試不同的 pool size
for size in [5, 10, 20, 50, 100, 200]:
benchmark_pool_size(size)
# 典型結果（4 核心 CPU）：
# Pool size: 5 | QPS: 450 | Total time: 2.22s
# Pool size: 10 | QPS: 890 | Total time: 1.12s
# Pool size: 20 | QPS: 1200 | Total time: 0.83s
# Pool size: 50 | QPS: 1150 | Total time: 0.87s ← 開始下降
# Pool size: 100 | QPS: 1050 | Total time: 0.95s ← 更慢了
# Pool size: 200 | QPS: 900 | Total time: 1.11s ← context switch 開銷
小結
連線池調校的核心心得：
- 一定要用連線池：沒有例外。裸連線在生產環境是不可接受的
- Pool size 不是越大越好：通常 CPU 核心數 x 2~4 就是最佳值
- PgBouncer 用 transaction mode：除非有特殊需求，否則 transaction mode 是最佳選擇
- 固定大小池比動態大小池穩定：min = max，避免連線建立/銷毀的抖動
- 設定 max-lifetime：避免 stale 連線，通常設 30 分鐘
- 監控是必需的：活躍連線數、等待數、超時次數，都要有告警
常見的架構搭配：
小型應用：
App → PostgreSQL（應用層連線池就夠）
中型應用：
App → PgBouncer → PostgreSQL（多個 App instance 共享）
大型應用：
App → 應用層連線池 → PgBouncer → PostgreSQL Primary
↘ PostgreSQL Replica（讀取分流）
延伸閱讀建議：
- PgBouncer 官方文件
- HikariCP Wiki – About Pool Sizing — 必讀！解釋為什麼 pool 不該太大
- SQLAlchemy Connection Pooling — Python 開發者必看
- pgcat — 新一代 PostgreSQL 連線池，支援 sharding