PgBouncer：3 种 pool_mode 实操，跟 Django/Rails 的坑

起因

PostgreSQL 每个 client connection 是一个进程，几 MB RAM。
应用每 worker 一个 connection × 几十 worker × 几个服务 → 几千连接 →
PG 默认 max_connections=100 撑不住，加到 1000 也吃几 GB RAM。

PgBouncer 在应用和 PG 之间做 connection pool：

[app worker × 100]   ←──── 100 cheap connection ────→  [PgBouncer]
                                                          ↓
                                                  [10 actual PG conn]
                                                          ↓
                                                       [Postgres]

PG 真正打开 10 connection 即可，PgBouncer 复用给 100 个 app worker。

pool_mode 三选

# pgbouncer.ini
pool_mode = session | transaction | statement

session mode

client 一直占有 PG conn 直到断开。
跟没 pgbouncer 几乎一样（只是 connection 数仍上限）。
基本无意义，跳过。

transaction mode（默认 + 最常用）

client 一个 transaction 内独占一个 PG conn，transaction 结束归还。

client A: BEGIN  → 拿 conn1
client A: SELECT
client A: UPDATE
client A: COMMIT → 还 conn1
client B: BEGIN  → 拿 conn1
...

效率高，但有限制：
- ❌ session 状态（SET、prepared statement、cursor 等）不跨 transaction
- ❌ LISTEN/NOTIFY（pub/sub）
- ❌ 临时表（声明周期是 session）

statement mode

每个 SQL 语句独立 conn。
最高复用，但禁止 transaction（不能 BEGIN/COMMIT）。
极少用，特殊只读 / 简单 OLTP 场景。

典型配置

# pgbouncer.ini
[databases]
mydb = host=pg.example.com port=5432 dbname=mydb pool_size=20

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = transaction
default_pool_size = 20            # 每 db × user 组合 20 个 PG conn
max_client_conn = 1000            # 接受最多 1000 个 client conn
reserve_pool_size = 5
server_idle_timeout = 600

# 重要：让 prepared statement 在 transaction mode 工作
max_prepared_statements = 100      # pgbouncer 1.21+

userlist.txt

"appuser" "SCRAM-SHA-256$4096:..."

scram-sha-256 hash，从 PG 查：

SELECT rolname, rolpassword FROM pg_authid;

客户端连接

应用连 pgbouncer 地址，不是 PG：

# Django settings
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'HOST': 'pgbouncer.example.com',
        'PORT': 6432,
        'NAME': 'mydb',
        'USER': 'appuser',
        'PASSWORD': '...',
        'CONN_MAX_AGE': 0,        # 不用应用层 connection pool（让 pgbouncer 管）
    }
}

CONN_MAX_AGE=0 是关键：让 Django 每请求重新拿 conn（实际是从 pgbouncer 池）。
CONN_MAX_AGE > 0 会让 app worker 长期持 connection → pgbouncer 无用。

Django / Rails 与 transaction mode 的坑

prepared statement

# psycopg2 默认 prepared statement
queryset = MyModel.objects.filter(x=1)

psycopg2 用 server-side prepared statement 提速。
但 transaction mode 跨 transaction conn 切换 → prepared statement 在
另一 conn 没准备 → 报错。

解决：

option A：禁用 prepared statement

# Django + psycopg2
DATABASES['default']['OPTIONS'] = {
    'options': '-c default_transaction_isolation=read_committed',
}
# psycopg3：
DATABASES['default']['OPTIONS'] = {
    'prepare_threshold': None,
}

option B：pgbouncer 1.21+ 支持 prepared statement caching：

max_prepared_statements = 100

我建议 option B（pgbouncer 新版本 + 不改应用）。

SET / 临时表 / cursor

业务代码避免：

# bad（transaction mode 不持久）
cursor.execute("SET application_name = 'myapp'")
# 下个 query 可能换 conn → SET 丢

# bad
cursor.execute("CREATE TEMP TABLE ...")
# 临时表跨 transaction 没了

# bad
LISTEN / NOTIFY  → 直接不能用

需要 SET / 临时表 → 包在同 transaction 内，或者直连 PG 不走 pgbouncer。

pool_size 怎么定

经验：

PG 总 max_connections：保留给所有 client 之和
pgbouncer default_pool_size：少于 PG max_connections / N（N = client app 数）

例：PG max_connections=100，2 app server，每 app 配 30 pool_size →
2×30=60 < 100，留 40 给 admin / monitoring。

pool_size 太小 → app 等 conn timeout。
pool_size 太大 → PG 进程多 RAM 涨。

经验值：CPU 核数 × 2-4。32 vCPU PG → pool_size 64-128 / app。

监控

SHOW POOLS;（连 pgbouncer 跑）：

database | user      | cl_active | cl_waiting | sv_active | sv_idle
mydb     | appuser   | 50        | 0          | 18        | 2

cl_waiting > 0 → 客户端在等 conn，pool_size 不够
sv_active / pool_size 接近 1 → 长期满负载

prometheus pgbouncer_exporter 抓 → Grafana panel。

透明部署 vs sidecar

部署方式：

每 app server 一个 pgbouncer：sidecar，本地连
集中 pgbouncer cluster：多 app 共用
PG container 内嵌

我倾向 1（sidecar）：

app 连 localhost:6432 几乎 0 网络开销
pgbouncer 进程独立 crash 不影响 PG
配置分散但简单

与 pgcat / odyssey 对比

pgcat（Rust）：现代 PG pooler，原生支持 read replica 路由 + sharding
odyssey（Yandex）：高性能 multi-threaded pooler
pgbouncer：单线程 C，事实标准，稳

90% 场景 pgbouncer 够。需要 read replica 路由 / sharding → pgcat。

实战：解决 too many connections

我们一个 Django app + Celery + 30 worker × 4 instance = 120 conn 需求。
PG max_connections=200 撑得住但内存吃紧。

加 pgbouncer：

pool_size = 25
max_client_conn = 200
pool_mode = transaction

实际 PG conn 数：25 × 1（pgbouncer）= 25。
app 仍能并发 200 个 logical conn（pgbouncer 排队复用）。

PG RAM 从 8 GB → 2 GB。预算节省。

踩过的坑

CONN_MAX_AGE > 0：以为加 pgbouncer 就万事大吉，结果 app 仍持
长 conn → pgbouncer 无效。改 0。
scram-sha-256 vs md5：PG 14+ 默认 scram，pgbouncer 1.20+ 才好
支持。老版本配 md5。
prepared statement 误报：奇怪的 prepared statement "_pg_q1" does not exist → transaction mode + prepared 不兼容。pgbouncer
1.21 + prepare cache 或者禁 prepared。
transaction 中调存储过程：某些 PG 存过程内 COMMIT 触发 →
pgbouncer 状态错乱。pool_mode statement 或者重构。
pgbouncer 重启 = 断所有 client：升级 pgbouncer 谨慎。优雅做法
多个 pgbouncer + LB rolling restart。