PostgreSQL 连接池与 PgBouncer 配置：事务池、会话池 与性能验证

本文面向高并发读写场景，目标是以 PgBouncer 降低连接建立与上下文切换成本，同时保证事务一致性与应用兼容性。所有参数、命令与现象均可在 Linux 环境直接验证。

## 适用版本与前提

• PostgreSQL 13–16（主从/高可用架构均可）。
• PgBouncer 1.18+（支持 `server_reset_query_always` 等配置）。
• 驱动：`libpq`、`psql` 或常见 ORM（需确认 prepared statements 与连接复用行为）。

## 连接池模式与适配

• 会话池（`pool_mode=session`）：客户端会话贯穿到后端连接，兼容 prepared statements 与临时表；并发能力受连接上限约束，适合需要会话状态的应用。
• 事务池（`pool_mode=transaction`）：每个事务结束即释放到池，显著提升并发连接复用率；不兼容跨事务的 prepared statements/会话状态（需禁用或改为语句级准备）。
• 语句池（`pool_mode=statement`）：每条语句级复用，限制多语句事务与游标；仅适用于非常受控的查询场景。

## 基础配置（pgbouncer.ini）

[databases]
app = host=127.0.0.1 port=5432 dbname=app user=app password=secret

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432

pool_mode = transaction
max_client_conn = 2000
default_pool_size = 100
min_pool_size = 20
reserve_pool_size = 20
reserve_pool_timeout = 5

server_reset_query = DISCARD ALL
server_reset_query_always = 1
ignore_startup_parameters = extra_float_digits

tcp_keepalive = 1
tcp_keepalive_idle = 300
tcp_keepalive_intvl = 30
tcp_keepalive_cnt = 5

说明与验证：

• `pool_mode=transaction` 搭配 `DISCARD ALL`，确保连接复用前清理会话状态。
• `default_pool_size` 依据数据库实例 `max_connections` 与可用 CPU/IO 能力设定，通常每实例 50–300 间。
• `reserve_pool_size` 用于突发流量缓冲，验证时可压测观察连接耗尽是否平稳退化。

## Prepared Statements 兼容性策略

• 在事务池模式：
• 禁用驱动的会话级 prepared（如 `pgbouncer.ini` 配置 `ignore_startup_parameters` 并在应用层关闭会话准备）。
• 改用语句级准备（ORM 支持“每次发送语句带参数”），避免跨事务状态。
• 在会话池模式：
• 允许会话级 prepared 与临时表，但并发受 `default_pool_size` 限制。
• 适合需要长会话与服务端游标场景。

验证示例（psql）：

-- 事务池下，跨事务 prepared 可能失效
PREPARE s AS SELECT now();
BEGIN; EXECUTE s; COMMIT;
-- 下一事务复用到不同服务器连接，会话状态清理导致 s 不存在

## 压测与观测

1. 连接建链成本对比（无池 vs 事务池）：

pgbench -h 127.0.0.1 -p 5432 -U app -c 200 -j 8 -T 60
pgbench -h 127.0.0.1 -p 6432 -U app -c 200 -j 8 -T 60

观察：`tps` 与 `latency`；事务池通常在高并发下获得更低的平均延迟与更高并发能力。

2. 连接占用与队列：

SELECT * FROM pgbouncer.pools; -- 需要开启管理数据库
SHOW STATS; -- pgbouncer 管理命令

3. 数据库端并发与等待：

SELECT state, count(*) FROM pg_stat_activity GROUP BY 1;
SELECT wait_event, count(*) FROM pg_stat_activity GROUP BY 1;

## 高可用与读写分离协同

• 在主从架构下，事务性写入/强一致读直连主库或通过池的写通道；只读查询可指向只读池。
• 故障切换时更新 PgBouncer 的后端指向（或配合 HAProxy/Keepalived 虚 IP），验证会话恢复与失败重试策略。

## 常见问题与排查

• 事务未结束导致连接占用：检查应用层是否显式 `COMMIT/ROLLBACK`，并关注长事务对可复用性的影响。
• 连接泄漏：业务异常未释放连接会造成池耗尽；结合应用 APM 与 `pgbouncer stats` 定位。
• 超时与队列增长：通过 `reserve_pool_size` 与应用层超时配置，避免排队时间过长导致级联故障。

## 结语

PgBouncer 在高并发场景下显著降低连接建立成本，但需根据业务对会话状态的需求选择合适的池模式，并在准备语句、事务边界与故障切换上进行严格验证与观测，方能稳定获益。