database

PostgreSQL 连接池与 PgBouncer 生产配置

深入讲解 PostgreSQL 连接池原理与 PgBouncer 生产级配置。涵盖 session/transaction/statement 三种 pool mode 的行为差异、max_connections 与连接风暴的关系、PgBouncer 核心参数调优（listen_port、default_pool_size、server_lifetime）、连接池效果验证方法，以及 pg_stat_activity 多维监控手段。

Leeting Yan 7 分钟阅读 3010 字

PostgreSQL 连接开销远高于大多数数据库。连接建立涉及认证协商、SSL 握手、内存分配（shared_buffers 映射、后端进程 fork），短连接场景下这些成本会直接吃掉大量 CPU 与内存。PgBouncer 作为最轻量的中间层连接池，能以极小的资源代价，将数万级应用连接收敛到几十到几百个实际后端连接。

核心认知：连接池不是在"扩大"并发能力，而是在"复用"已建立的连接，消除连接建立/销毁的固定成本。

一、连接池基本原理

1.1 为什么需要连接池

PostgreSQL 是进程模型——每个连接对应一个独立的后端进程：

应用连接 → psql / libpq → PostgreSQL 后端进程（fork）→ 共享内存
                         ↑
                    每个连接约 1.5~5MB 私有内存
场景无连接池代价连接池收益
短连接 HTTP API每次请求新建连接，CPU 飙高连接复用，消除 fork 开销
连接数 > 500调度器压力、锁竞争收敛到可控后端连接数
微服务多实例单库总连接数爆炸PgBouncer 统一收敛
连接风暴max_connections 打满拒绝连接排队/限流保护

1.2 PgBouncer 三种 Pool Mode

PgBouncer 的核心差异在于"何时归还连接到池中"：

Mode绑定时机归还时机适用场景
session连接开始时客户端断开临时表、session 级参数、预备语句（Prepared Statement）
transaction事务开始时COMMIT / ROLLBACK通用 Web 场景（推荐默认）
statement每条语句前语句执行完只读报表、极短查询
session 模式：客户端连接 ←→ 后端连接 一对一绑死
transaction 模式：每个事务独立复用，BEGIN 时取连接
statement 模式：SELECT 1 执行完立刻归还

transaction 模式是生产首选，它平衡了复用率与兼容性，对大多数无临时表、无 SET SESSION 的场景完全适用。如果你使用了预备语句或 LISTEN/NOTIFY，则必须 session 模式。

二、max_connections 与连接风暴

2.1 max_connections 的真实约束

max_connections 不只是连接数上限，它直接关联 shared_buffers 之外的内存开销：

-- 计算当前每个连接的内存开销（近似值）
SELECT setting::int * (1024 * 1024) -- work_mem 单位字节
       + (SELECT setting::int FROM pg_settings WHERE name = 'maintenance_work_mem')::bigint
       as per_conn_approx_bytes
FROM pg_settings WHERE name = 'work_mem';

一个后端连接的典型内存结构：

区域来源参数默认大小说明
进程栈—8MBlinux 默认
work_memwork_mem4MB排序/哈希操作
maintenance_work_memmaintenance_work_mem64MBVACUUM/索引构建
共享缓存引用shared_buffers—只读引用

2.2 连接风暴的形成

100 个微服务实例 × 20 连接池大小 = 2000 连接 → 远超 max_connections=200

连接风暴的典型表现：

-- 连接打满时，新连接被拒绝
FATAL: sorry, too many clients already

-- 监控连接饱和状态
SELECT count(*) AS current_conn,
       (SELECT setting::int FROM pg_settings WHERE name='max_connections') AS max_conn
FROM pg_stat_activity;

2.3 连接层排队 vs 拒绝

PgBouncer 在全满时选择排队等待而非直接拒绝连接（可配 pool_mode=transaction + max_client_conn），这是它比应用内置池更适合做汇聚层的核心原因。

三、PgBouncer 配置实战

3.1 基础配置（pgbouncer.ini）

[databases]
; 映射应用请求的数据库名到真实 PostgreSQL 连接串
myapp = host=db.internal port=5432 dbname=myapp

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

; ---- 核心池参数 ----
default_pool_size = 20
max_client_conn = 10000
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 600
server_lifetime = 3600
server_connect_timeout = 15

; ---- 日志与监控 ----
log_connections = 1
log_disconnections = 1
stats_period = 60
admin_users = pgbouncer_admin
stats_users = pgbouncer_stats

; ---- pool_mode ----
pool_mode = transaction
参数推荐值含义
default_pool_size20~40每个数据库的用户对维度的池大小
max_client_conn10000PgBouncer 接受的最大客户端连接
reserve_pool_size5~10紧急预留连接
reserve_pool_timeout3s排队超时才启用预留连接
server_idle_timeout600空闲后端连接保持时间
server_lifetime3600单个后端连接最大存活时间，防雪崩
server_connect_timeout15后端连接建立超时

3.2 userlist.txt 用户列表

"myapp_user" "SCRAM-SHA-256$4096:..." "myapp"

或用 auth_query 让 PgBouncer 向 PostgreSQL 查询密码（推荐，避免密码文件同步）：

auth_user = pgbouncer_auth
auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1

-- 在 PostgreSQL 中创建专用认证角色（最小权限）
CREATE ROLE pgbouncer_auth LOGIN PASSWORD 'secure_pass';
GRANT SELECT ON pg_shadow TO pgbouncer_auth;

3.3 连接数计算公式的对照

总后端连接数 = sum(每个 db × user 的 pool_size)

示例：
  - 数据库 myapp，用户 app_user，pool_size=25
  - 数据库 analytics，用户 report_user，pool_size=10
  - 总后端连接上限 = 25 + 10 = 35

关键原则：default_pool_size 乘以 db/user 组合数，应低于 max_connections 的 70%。

3.4 pool_mode 切换对比实验

-- 在 transaction 模式下，每个 COMMIT 后连接被复用
BEGIN;
INSERT INTO logs (msg) VALUES ('tx1');
COMMIT;  -- 连接归还到池

-- 同一连接可能被其他客户端复用执行
BEGIN;
INSERT INTO logs (msg) VALUES ('tx2');
COMMIT;

在 session 模式测试：

-- session 模式：SET 与临时表跨请求保持
SET SESSION app.tenant_id = 't-123';
CREATE TEMP TABLE tmp_items (id int);
-- 客户端断开前，后端连接始终属于该客户端

四、验证连接池效果

4.1 PgBouncer SHOW 命令

-- 登录 PgBouncer 管理控制台
psql -h localhost -p 6432 pgbouncer -U pgbouncer_admin

SHOW POOLS;
-- 关键列：cl_active（活跃客户端）, sv_active（活跃服务端）,
--         cl_waiting（等待连接的客户端）, sv_idle（空闲后端）

SHOW DATABASES;
-- 各数据库的连接池状态与限制

SHOW STATS;
-- 吞吐量指标（requests, query_time, etc.）

4.2 连接复用率监控

-- 在 PgBouncer admin 中
SHOW STATS_TOTALS;
-- total_xact_count / total_server_count = 每个后端连接的复用次数

复用率 = transaction 总数 / server 连接分配次数
        = 100000 / 5000 = 20x

高复用率说明连接池在高效工作

4.3 pg_stat_activity 视角

-- PostgreSQL 端查看实际连接数（经过 PgBouncer 收敛后应减少）
SELECT usename, application_name, count(*)
FROM pg_stat_activity
WHERE application_name LIKE 'pgbouncer%'
GROUP BY usename, application_name
ORDER BY count(*) DESC;

-- 对比应用直连时的连接数
SELECT usename, application_name, count(*)
FROM pg_stat_activity
GROUP BY usename, application_name
ORDER BY count(*) DESC;

4.4 排队与超时分析

-- PgBouncer SHOW POOLS 中，cl_waiting > 0 说明连接不足
SHOW POOLS;
-- 出现 cl_waiting 常态化 → 增大 default_pool_size 或 reserve_pool_size

五、pg_stat_activity 多维监控

5.1 连接池层的连接画像

-- 按 state 分组监控后端连接
SELECT state, count(*) AS cnt
FROM pg_stat_activity
WHERE datname = 'myapp'
GROUP BY state;

-- active: 正在执行查询
-- idle: 空闲等待
-- idle in transaction: 事务已开启但无活动（危险）

5.2 连接池性能指标清单

-- 1. 连接使用率（直连 PostgreSQL 时）
SELECT round(100.0 * count(*) /
       (SELECT setting::int FROM pg_settings WHERE name='max_connections'), 1)
       AS conn_usage_pct
FROM pg_stat_activity;

-- 2. 长事务（阻塞死元组回收与连接复用）
SELECT pid, usename, now() - xact_start AS duration, state, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL AND now() - xact_start > interval '30 seconds';

-- 3. 等待事件（定位 IO 或锁瓶颈）
SELECT wait_event_type, wait_event, count(*)
FROM pg_stat_activity
WHERE wait_event IS NOT NULL
GROUP BY wait_event_type, wait_event
ORDER BY count(*) DESC;

5.3 Prometheus postgres_exporter 连接池指标

# prometheus 告警规则：连接饱和度
groups:
  - name: postgres-connections
    rules:
      - alert: PostgresConnectionSaturation
        expr: pg_stat_database_numbackends > (pg_settings_max_connections * 0.7)
        for: 5m
        labels: { severity: warning }

常见问题（FAQ）

应用已有 HikariCP（或类似连接池），还需要 PgBouncer 吗？

两种池是互补的：HikariCP 管理应用 ↔ PgBouncer 的连接复用；PgBouncer 管理PgBouncer ↔ PostgreSQL 的连接复用。在多服务/多实例场景下，PgBouncer 是防止后端连接数爆炸的统一汇聚层。

transaction pool_mode 会丢失 session 状态吗？

是的。SET SESSION、临时表、LISTEN、PREPARE 在 transaction 模式下跨事务不保留。如有这些需求，应切换为 session 模式或改用 transaction 模式下的显式 PREPARE 替代方案。

server_lifetime 设多合适？

server_lifetime 强制回收旧连接，避免长时间运行的后端进程出现内存泄漏或状态漂移。常规设 3600 秒（1 小时），高稳定性环境可设 7200；内存敏感环境（如 Heroku）可设 600。

如何从直连切到 PgBouncer？

  1. 将 PgBouncer 部署到与数据库同机房
  2. 应用连接串端口从 5432 切到 6432
  3. 在 PgBouncer 开启池模式 transaction
  4. 逐步迁移应用实例（灰度），同时监控 SHOW POOLS 的 cl_waiting
  5. 验证后关闭应用旧的内置连接池最大值限制

相关阅读

延伸阅读

完整示例（一键复制）

以下是 PgBouncer 生产配置与监控验证的完整 SQL 与配置汇总：

-- ========== PostgreSQL 端：连接认证与监控 ==========

-- 1. 创建 PgBouncer 认证专用角色
CREATE ROLE pgbouncer_auth LOGIN PASSWORD 'secure_pass';
GRANT SELECT ON pg_shadow TO pgbouncer_auth;

-- 2. 查看当前连接分布
SELECT usename, application_name, count(*),
       string_agg(DISTINCT state, ', ') AS states
FROM pg_stat_activity
WHERE datname = current_database()
GROUP BY usename, application_name
ORDER BY count(*) DESC;

-- 3. 连接使用率
SELECT round(100.0 * count(*) /
       (SELECT setting::int FROM pg_settings WHERE name='max_connections'), 1)
       AS conn_usage_pct
FROM pg_stat_activity;

-- 4. 长事务诊断（连接复用的敌人）
SELECT pid, usename, now() - xact_start AS duration,
       state, left(query, 80) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND now() - xact_start > interval '30 seconds'
ORDER BY xact_start;

-- 5. 等待事件分析
SELECT wait_event_type, wait_event, count(*) AS cnt
FROM pg_stat_activity
WHERE wait_event IS NOT NULL
GROUP BY wait_event_type, wait_event
ORDER BY cnt DESC;

-- ========== PgBouncer 控制台命令 ==========
-- psql -h localhost -p 6432 pgbouncer -U pgbouncer_admin

SHOW POOLS;
SHOW DATABASES;
SHOW STATS;
SHOW STATS_TOTALS;
SHOW CONFIG;
RELOAD;  -- 热重载配置

; ========== pgbouncer.ini 生产模板 ==========
[databases]
myapp = host=db.internal port=5432 dbname=myapp

[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
auth_user = pgbouncer_auth
auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1

default_pool_size = 20
max_client_conn = 10000
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 600
server_lifetime = 3600
server_connect_timeout = 15

log_connections = 1
log_disconnections = 1
stats_period = 60
admin_users = pgbouncer_admin
stats_users = pgbouncer_stats

pool_mode = transaction

继续阅读

探索更多技术文章

浏览归档，发现更多关于系统设计、工具链和工程实践的内容。

全部文章 返回首页

「database」更多文章

  1. Supabase 平台与 PostgreSQL 边缘函数实践
  2. PostgreSQL 事件触发器与审计日志实现
  3. Kubernetes 上 PostgreSQL 运维与 CloudNativePG 实战