PgBouncer 连接池治理实战：从切到事务池后 search_path 失效到会话状态与池容量双线收敛

dbaai

PgBouncer 连接池治理实战：从切到事务池后 search_path 失效到会话状态与池容量双线收敛

一、具体的问题

一套电商库，PostgreSQL 14，应用侧 96 个 Pod，每个 JVM 的连接池配 20，理论上峰值能开到 1920 条连接。数据库 max_connections = 2000。每隔两三周就会撞一次：

2. FATAL: sorry, too many clients already

复制代码

翻 pg_stat_activity 看，1900 多条连接里 87% 是 idle，真正干活的不到 200。这就是典型的"连接被池占着当摆设"。于是按标准做法在应用和数据库之间插了一层 PgBouncer 1.21，pool_mode 从默认的 session 改成 transaction。

效果立竿见影：打到 PostgreSQL 的后端连接从 1900 降到 96 条，too many clients 再没出现过。

但上线第二天开始，冒出来三种"偶发"报错，而且都很邪门：

2. -- 报表框架，每天两三次
3. ERROR:  relation "orders_2026" does not exist
5. -- 夜间对账作业
6. ERROR:  prepared statement "s1" does not exist
8. -- 幂等服务，一天一次，重启才好
9. ERROR:  deadlock detected  /  锁一直拿不到，第二天谁都释放不了

复制代码

同时 SHOW POOLS 里的 cl_waiting 一度飙到 300 多，maxwait 爬到 40 秒，超过 query_wait_timeout 之后应用直接收到错误。

连接数降了 95%，故障率反而涨了。问题不在池本身，而在于：会话被切成了一段一段，而应用代码从头到尾都假设这条会话是自己的。

二、核心原理

1. 事务池到底"池"掉了什么

pool_mode 三个取值，差别只在后端连接什么时候被收回：

• session：客户端连上就独占一条后端连接，直到断开。默认值，兼容一切。
  
• transaction：一条后端连接只在一次事务期间归你，COMMIT／ROLLBACK 一完成立刻回池，交给下一个客户端。
  
• statement：一条语句一收回，多语句事务直接不允许。
  

所以 transaction 模式省连接的道理很朴素：应用在两次事务之间等待的那段时间，后端连接已经去服务别人了，贵的地方是"空闲"。

代价同样朴素：两次事务之间不保证是同一条后端连接，也不保证同一个后端进程。

2. 官方对照表里标着 Never 的那几项

PgBouncer 官网 features 页有一张"SQL feature map for pooling modes"对照表，事务池下明确标 Never 的，就是绝对不能用的：

• SET / RESET：Never。这是杀伤面最大的一条。事务结束时你 SET search_path 的效果留在后端上，谁也不知道下一个客户端是谁，于是要么你的设置丢了，要么你的设置污染了别人。
  
• LISTEN：Never（NOTIFY 本身能用，但没人听）。
  
• WITH HOLD 游标：Never，因为它必须活过事务。
  
• 会话级 advisory lock：Never。事务级的 pg_advisory_xact_lock 才可以。
  
• SQL 级 PREPARE / DEALLOCATE：Never。
  
• PRESERVE ROWS / DELETE ROWS 的临时表：Never；ON COMMIT DROP 的临时表可以。
  

有个例外值得记：启动参数里的 client_encoding、DateStyle、IntervalStyle、Timezone、standard_conforming_strings、application_name 这六项，PgBouncer 会主动跟踪并保证客户端看到的始终一致。1.26.0 起默认还跟踪 search_path 和 default_transaction_read_only。但这是新版才有的待遇，跑 1.21／1.22 的环境不能指望它。

3. 池大小和句柄数不是一回事

几个默认值必须先记住，因为默认值都很保守：

• max_client_conn 默认 100：PgBouncer 能接多少个客户端。很多环境直接踩在这个默认值上，第 101 个应用连接就进不来。
  
• default_pool_size 默认 20：每个"数据库＋用户"组合能开多少条后端连接。
  
• min_pool_size 默认 0、reserve_pool_size 默认 0：两个都关着，突发流量只能干等。
  
• query_wait_timeout 默认 120 秒：等不到后端连接就报错，不是无限排队。
  

真实压在 PostgreSQL 上的连接数是 池数 × default_pool_size，跟 max_client_conn 没关系。文件句柄要按官方公式留：max_client_conn + (pool_size × 数据库数 × 用户数)，ulimit -n 得比这个再高一截。

4. 为什么 idle in transaction 是池的头号杀手

一条 BEGIN 之后代码抛了异常、没回滚，这个事务就一直挂着。事务池模式下，它占着一条后端连接不放。这种连接积累十几个，池就见底，其余客户端全在 cl_waiting 里排队。

两边都有护栏，但默认都是关的：PG 侧 idle_in_transaction_session_timeout 默认 0，池侧 idle_transaction_timeout 默认 0。

三、实例参考（动手步骤）

步骤 0：先取证，别急着改配置

2. # 直连数据库，看连接都在干什么
3. psql "host=10.0.2.10 port=5432 dbname=app" -c "
4. select count(*) total,
5.        count(*) filter (where state='idle')          as idle,
6.        count(*) filter (where state='idle in transaction') as idle_in_tx,
7.        count(*) filter (where state='active')        as active
8. from pg_stat_activity where datname='app';"
10. # 看池：cl_active / cl_waiting / sv_active / sv_idle 四列
11. psql -h 127.0.0.1 -p 6432 -U pgb_admin pgbouncer -c "SHOW POOLS;" -c "SHOW STATS;"

复制代码

判据只有两条：idle 占比超过 80%，说明上池有意义；cl_waiting 长期大于 0 且 maxwait 在涨，说明池太小或者有长事务占坑。这两个判断分别决定"要不要池"和"池开多大"。

步骤 1：按算出来的数写配置

2. [databases]
3. app = host=10.0.2.10 port=5432 dbname=app pool_size=40
5. [pgbouncer]
6. listen_addr = 0.0.0.0
7. listen_port = 6432
8. auth_type = scram-sha-256
9. auth_file = /etc/pgbouncer/userlist.txt
10. admin_users = pgb_admin
11. stats_users = pgb_stats
13. pool_mode = transaction
14. max_client_conn = 3000
15. default_pool_size = 40
16. min_pool_size = 10
17. reserve_pool_size = 10
18. reserve_pool_timeout = 3
20. server_idle_timeout = 300
21. server_lifetime = 3600
22. query_wait_timeout = 30
23. idle_transaction_timeout = 60
24. max_prepared_statements = 200
25. ignore_startup_parameters = extra_float_digits

复制代码

40 条后端连接对 16 核的库是合适量级（经验值：最热的池按核数的 2 到 4 倍起步）。句柄需求是 3000 + 40，systemd 里配 LimitNOFILE=65535。

注意 query_wait_timeout 从 120 秒收到 30 秒是故意的：与其让用户等两分钟再报错，不如快速失败，让上游限流兜住。

步骤 2：把会话状态从应用里挖出来

2. grep -rnE "SET[[:space:]]+(search_path|timezone|statement_timeout|application_name)|pg_advisory_lock|LISTEN |CREATE TEMP|PREPARE " \
3.   --include=*.java --include=*.py --include=*.go --include=*.xml src/ | tee /tmp/session_state.txt
4. wc -l /tmp/session_state.txt

复制代码

挖出来的每一条都要有归属处置，四条路：

2. -- 1) search_path / statement_timeout 这类，改成角色级，永久生效
3. ALTER ROLE app_rw SET search_path TO biz, public;
4. ALTER ROLE app_rw SET statement_timeout TO '5s';
6. -- 2) 必须按连接设置又不能用角色级的，用 connect_query 在建连时执行
7. --    [pgbouncer] 段加：connect_query = SET timezone TO 'Asia/Shanghai'
9. -- 3) 会话级 advisory lock 换成事务级，事务结束自动释放
10. --    原：select pg_advisory_lock(hashtext('order-8831'));
11. --    改：select pg_advisory_xact_lock(hashtext('order-8831'));
13. -- 4) 报表、DDL 迁移、pg_dump、逻辑复制 → 走 5432 直连，不过池

复制代码

第 4 条最容易被忽略。pg_dump 内部会执行一串 SET，事务池下这些设置不生效，导出来的东西可能和预期不一致。这些工具天生要独占会话，必须直连。

步骤 3：灰度切流，一次一个服务

改配置用 RELOAD，不用重启，也不掉连接：

2. psql -h 127.0.0.1 -p 6432 -U pgb_admin pgbouncer -c "RELOAD;"
3. psql -h 127.0.0.1 -p 6432 -U pgb_admin pgbouncer -c "SHOW CONFIG;" \
4.   | grep -E "pool_mode|max_prepared|query_wait|idle_transaction"

复制代码

然后只把一个边缘服务的连接串指向 6432，观察满 24 小时，看应用错误率曲线，再看 PgBouncer 日志有没有 pooling 相关的报错。干净了再切下一个。全部切完之前，PG 的 max_connections 不要降，那是回滚的唯一退路。

步骤 4：压测验证池够不够，而不是先加大客户端上限

2. # 200 并发的事务型压测，直接打到 6432
3. pgbench -h 127.0.0.1 -p 6432 -U app_rw -c 200 -j 8 -T 300 -S app

复制代码

压测同时另开一个窗口反复看 SHOW POOLS;。如果 cl_waiting 长期不为 0、maxwait 一路上爬，先查是不是有慢事务或 idle in transaction 占着连接，再考虑调 default_pool_size。千万别用加大 max_client_conn 来"解决"排队，那只是把队列从 PgBouncer 挪到 PG。

步骤 5：把四个指标挂上监控

必须落的：cl_waiting、maxwait、sv_active / pool_size（饱和度）、SHOW STATS 里的 avg_xact_time。外加 PG 侧的 idle in transaction 计数。

告警线：cl_waiting > 0 持续 1 分钟、maxwait > 5 秒、饱和度常驻 80% 以上。这三条任意一条报出来，都是池配置或事务边界的问题，不是"再加点连接"能解决的。

治理前后对比

指标	治理前	治理后
PG 后端连接数	1900（峰值）	96
max_connections	2000	200
idle 连接占比	87%	11%
too many clients	每 2 到 3 周一次	0
cl_waiting	最高 300+	常态 0，峰值 12
maxwait	40 秒	0.4 秒
search_path 报错	每天 2 到 3 次	0
预处理语句报错	每夜 1 次	0
幂等锁卡死	每天 1 次	0
数据库侧内存余量	紧（2000 连接）	宽松（200 连接）

连接数从 1900 降到 96，shared_buffers 才有空间往上加。这一层收益通常比池本身更大。

四、实操检查清单

• [ ] 用 pg_stat_activity 统计 idle／idle in transaction／active 三个状态占比，idle 超 80% 才上池
  
• [ ] 数清业务侧的"数据库＋用户"组合数，池总量按组合数 × default_pool_size 算，别按 max_client_conn 算
  
• [ ] max_client_conn 改掉默认的 100，同步检查 LimitNOFILE 是否大于 max_client_conn + 池总量
  
• [ ] 全仓搜索 SET search_path、SET timezone、SET statement_timeout，逐条改成 ALTER ROLE ... SET 或 connect_query
  
• [ ] 全仓搜索 pg_advisory_lock，全部换成 pg_advisory_xact_lock
  
• [ ] 全仓搜索 CREATE TEMP、WITH HOLD、LISTEN，命中项一律改走直连端口
  
• [ ] JDBC 侧确认 max_prepared_statements 非 0，或显式设 prepareThreshold=0 关闭命名预处理
  
• [ ] pg_dump、DDL 迁移、逻辑复制、报表导出，全部配直连连接串（不经 6432）
  
• [ ] 两边的 idle in transaction 超时都要设上，PG 侧和池侧各一道
  
• [ ] 压测按真实并发打 6432 端口，以 cl_waiting 和 maxwait 判断池是否够
  
• [ ] 灰度按服务逐个切，每个观察 24 小时，全部切完后再降 PG 的 max_connections
  
• [ ] 监控至少覆盖 cl_waiting、maxwait、饱和度、avg_xact_time，并配告警
  

几个容易踩的坑

一、把 max_client_conn 当成后端连接上限。它只决定"能接多少客户端"，真正打到 PostgreSQL 的是池大小，两者差着两个数量级。

二、以为 SET 只是"偶尔失效"。官方对照表里标的是 Never，不是"有时"。报表那句话 relation does not exist 就是死在这里：框架启动时 SET search_path，第一条事务结束后设置就没了。升级到 1.26.0 以上会默认跟踪 search_path，但别把升级当成整改方案。

三、分不清 SQL 级 PREPARE 和协议级预处理语句。前者在事务池下永远不可用；后者从 1.21 起只要 max_prepared_statements 非 0 就能用（默认是 0，即关闭）。看到 prepared statement "s1" does not exist，先查这个参数，不要直接下"PgBouncer 不支持预处理"的结论。

四、max_prepared_statements 设得越大越好。它是"每条客户端连接最多跟踪多少条语句"，200 条乘以 3000 个客户端，是要吃内存的。

五、指望池帮你把会话洗干净。server_reset_query 默认是 DISCARD ALL，但只在 session 模式下执行；transaction 模式的默认值是空。想让事务模式也重置，得开 server_reset_query_always，代价是每条事务结束多一次往返。所以正确的思路是"应用不要留下会话状态"，不是"让池帮忙擦"。

六、min_pool_size 和 reserve_pool_size 都留默认 0。平时看不出问题，突发时所有客户端一起排队。另外 min_pool_size 只在该池指定了 forced user、或已有客户端连着时才真正执行。

七、完全不管 idle in transaction。两边的超时默认都是 0，等于没护栏。一个忘记回滚的事务能占着后端连接十几分钟，把池吃干，而你在应用日志里只看到"连接超时"。

八、用会话级 advisory lock 做分布式幂等。事务池下这把锁跟着后端连接走，可能被下一个客户端继承，也可能永远释放不掉。换成 pg_advisory_xact_lock，事务结束自动释放。

九、全部服务切完当天就把 PG 的 max_connections 从 2000 降到 200。降是对的，但要留一周观察期。真出问题回滚时，PgBouncer 会把积压的客户端一股脑放回 PG，而 PG 已经接不住了。

十、只看 PgBouncer 的日志。事务池的故障大部分不产生池侧错误，而是以"业务偶发失败"出现在应用日志里。定位路径是：应用报错 → 找出那条语句 → 对照 feature map 看它在事务池下的状态。