前言
pgbouncer 作为一款优秀的连接池工具，经常与 PostgreSQL 搭配，其主要目的是为了收敛连接数，由于多进程架构，对于海量连接的情况下，操作系统光是调度这些进程都十分费力，其次还有内存的消耗，粗略计算一下，每个后端进程在创建之后啥也不做的情况下，大概会消耗 3~4MB 的内存，因此连接数一多也会占据很可观的内存，更不用考虑那些 relcache/PLpgSQL/plan 等等。
那么 pgbouncer 作为 PostgreSQL 的好帮手，又有哪些注意事项，以及坑呢？简单聊两句。
连接池模式
首先必须提及的是，pgbouncer 支持的三种模式
会话模式：会话结束之后，连接返还给 pgbouncer 事务模式：事务结束之后，连接返还给 pgbouncer 语句模式：语句结束之后，连接返还给 pgbouncer
| eature | Session pooling | Transaction pooling |
|---|---|---|
| Startup parameters 1 | Yes | Yes |
| SET/RESET | Yes | Never |
| LISTEN | Yes | Never |
| NOTIFY | Yes | Yes |
| WITHOUT HOLD CURSOR | Yes | Yes |
| WITH HOLD CURSOR | Yes | Never |
| Protocol-level prepared plans | Yes | Yes 2 |
| PREPARE / DEALLOCATE | Yes | Never |
| ON COMMIT DROP temp tables | Yes | Yes |
| PRESERVE/DELETE ROWS temp tables | Yes | Never |
| Cached plan reset | Yes | Yes |
| LOAD statement | Yes | Never |
| Session-level advisory locks | Yes | Never |
可以看到，会话模式支持项是最多的，但是这使得它减少数据库连接的作用荡然无存，反而网络多了一跳，有点脱裤子放屁的味道，而事务模式可以收敛连接数，但是在 1.21 之前，事务模式不支持 prepare statement，也就意味着 1.21 之前，一旦代码中使用了 extend protocal，那么必须得使用会话模式。所以，1.21 算是一个比较大的里程碑。
但是事务模式，顾名思义，不能使用一些基于会话的行为，因为事务提交就返还给连接池了。比如 SET/LOAD 之类，WITH HOLD CURSOR 当然也无法使用 (指定该游标在创建它的事务提交之后还能被继续使用)。
语句模式用得较少，此处就不再过多描述了。
Most aggressive method. This is transaction pooling with a twist: Multi-statement transactions are disallowed. This is meant to enforce “autocommit” mode on the client, mostly targeted at PL/Proxy.
search_path
使用事务模式，前提一定要是：事务是 stateless 的，举个栗子，假如你的业务行为流程是这样的
数据库中很多个模式，比如典型的多租户场景 多个模式下有很多个同名的表，业务经常需要通过 set seach_path to xxx 的形式，指定访问某个模式下的表
如果是这样的行为，那么事务模式就无法满足你的需求了，不难理解，set search_path 之后，事务结束便还给了连接池，下一个应用连接拿到的连接可能已经是一个完全不同的 search_path 了，要解决的话，得将相关的行为放在一个事务之中，总而言之，还是那句话，stateless
很好验证，首先不通过 pb 的情况下连接数据库，建个表：
postgres=# create table public.test1(id int,info text);
CREATE TABLE
postgres=# insert into public.test1 values(1,'postgresql niubility');
INSERT 0 1
然后通过 pb，执行一下 search_path
[postgres@mypg ~]$ psql -h localhost -p 6432 -d postgres -U u1
Password for user u1:
psql (16.1)
Type "help" for help.
postgres=> set search_path to 'xiongcc';
SET
postgres=>
然后再起一个连接，也通过 pb
[postgres@mypg ~]$ psql -h localhost -p 6432 -d postgres -U u1
Password for user u1:
psql (16.1)
Type "help" for help.
postgres=> select * from test1;
ERROR: relation "test1" does not exist
LINE 1: select * from test1;
^
postgres=> show search_path ;
search_path
-------------
xiongcc
(1 row)
显然，由于前一个会话设置了 search_path，新来的连接就有可能拿到这个会话，进而提示找不到表。关于这个问题，其实官网一直也在讨论：https://github.com/pgbouncer/pgbouncer/pull/73#issuecomment-143570901，反正最后这个 patch 也没有合并进去，感兴趣的童鞋可以看看他们的讨论。
单点故障
其次是单点故障的问题，由于 pgbouncer 的单进程架构，单机上限吞吐量大概在 5W 左右，一般来说，单个 pgBouncer 最多可以处理 10,000 个连接。一次可以有 1,000 个左右处于活动状态。其次假如你的连接池挂了怎么办？业务不就中断了，一个土办法是起一个定时任务，每隔 10 秒探测一下，挂了就拉起来... 但是实在是很不优雅。
对于这个问题，我们可以使用 SO_REUSEPORT，使得多进程或者多线程创建多个绑定同一个ip:port 的socket，提高服务器的接收链接的并发能力，那么比如你绑定了三个，也就意味着可以挂俩
类似地，你还可以基于此，实现负载均衡，故障转移 (需要配合 VIP)，丐版的读写分离，比如
[databases]
pgbench_rw = dbname=pgbench host=127.0.0.1 user=pgbench
pgbench_ro = dbname=pgbench host=192.168.152.130 port=5432 user=pgbench
当你的业务通过连接 pgbench_rw，那么可读可写，如果是只读业务，连接 pgbench_ro (写业务会提示 cannot execute xxxx in a read-only transaction)。
另外，实践中，一般也会使用 HAPROXY + pgbouncer，pgbouncer 中很早就加入了 HAproxyProtocol 支持，透传应用 IP。
如何获取真实地址
这个需求也很常见，遇到慢查询，需要溯源，找到矛头，但是由于有了连接池，数据库中看到的都是连接池过来的连接。为此，pgbouncer 提供了 application_name_add_host 参数，通过在 pg_stat_activity.application_name 字段中，添加了额外的 IP 和地址，比如
postgres=# select application_name from pg_stat_activity where state = 'active';
application_name
--------------------------------
pgbench - 172.30.215.174:54996
psql
(2 rows)
这样就很方便了，当然你在 pgbouncer 自身的日志中，或者登录控制台，show clients，也能找到相关信息，但是这就显得比较麻烦了。另外，PostgreSQL 自身日志中，也会记录，如果是 csvlog 的话，默认就会记录，如果配置的是 stderr，那么你需要配置额外的 log_line_prefix 为 %a。
2024-03-19 21:58:49.482 CST,"u1","mydb",32390,"localhost:58678",65f99995.7e86,265071,"idle in transaction",2024-03-19 21:56:37 CST,3/124497,1152068,LOG,00000,"statement: INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (91, 4, 718112, -2360, CURRENT_TIMESTAMP);",,,,,,,,,"pgbench - 172.30.215.174:54996","client backend",,0
可以看到，最后的 pgbench - 172.30.215.174:54996 便是来源。
小结
以上种种，便是使用 pgbouncer 中需要格外当心的一些坑了，虽然 pgbouncer 还有着各种各样的问题，比如无法限制某个用户针对特定数据库的连接上限，但是瑕不掩瑜，在海量连接的情况下，pgbouncer 依旧是你的第一选择。
下一期，让我们聊一聊另一款优秀的连接池，奥德赛——odyssey。
参考
https://www.pgbouncer.org/features.html
https://www.enterprisedb.com/postgres-tutorials/pgbouncer-setup-extended-architecture-use-cases-and-leveraging-soreuseport
推荐阅读
Feel free to contact me
微信公众号：PostgreSQL学徒 Github：https://github.com/xiongcccc 微信：_xiongcc 知乎：xiongcc 墨天轮：https://www.modb.pro/u/3958
