pgsql-31 连接池与连接管理

xingliuhua 收录于笔记

2025-09-25 约 1911 字预计阅读 4 分钟

1. 概述

连接管理是 PostgreSQL 高并发场景的关键，也是 PG 区别于 MySQL 的重要架构点。

核心问题：PostgreSQL 每个连接是一个独立的操作系统进程（不是线程）。这带来两个后果：

每个连接占用可观内存（几 MB 起）、建立/销毁成本高。
连接数一多，进程调度和内存开销急剧上升，几百个直连就可能拖垮数据库。

因此 PG 强烈依赖连接池来复用连接、限制并发。

对比 MySQL：MySQL 采用线程模型（一连接一线程，甚至线程池），单连接开销远小于 PG 的进程，所以 MySQL 能承受更多直连。这就是为什么"连接池对 PG 几乎是必需，对 MySQL 是优化项"——这是常考的架构差异。

2. 为什么不能一味调大 max_connections？

每个连接 ≈ 一个进程 ≈ 若干 MB 内存 + work_mem 潜在占用 + 上下文切换成本

max_connections = 1000 看似能扛更多并发，实则大量连接空闲也占资源，活跃时争抢 CPU/内存反而更慢。
经验：max_connections 设 100-300，真实并发靠连接池在前面复用。

关键洞察：数据库真正能并行干活的数量约等于 CPU核数 + 磁盘数，几十个足矣。成百上千的应用连接应通过连接池收敛成少量数据库连接。

3. PgBouncer（最常用的外部连接池）

PgBouncer 是轻量级连接池中间件，应用连它、它连数据库，在中间复用。

3.1 安装配置

sudo apt install pgbouncer

# /etc/pgbouncer/pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction          # 池化模式（见下）
max_client_conn = 1000           # 允许多少应用连接进来
default_pool_size = 25           # 实际到数据库的连接数（每个库）

应用改为连接 PgBouncer 的 6432 端口即可，无需改代码。

3.2 三种池化模式（面试重点）

模式	复用粒度	说明	适用
session	会话	客户端断开才归还连接	最安全，但复用率低
transaction	事务	每个事务结束就归还	最常用，复用率高
statement	语句	每条语句后归还	复用最高，但禁多语句事务

transaction 模式是绝大多数场景的选择：既大幅复用连接，又保持事务完整。

⚠️ 注意：transaction/statement 模式下不能使用会话级特性（如 SET、预备语句 prepared statement、LISTEN/NOTIFY、临时表），因为连接在事务间会被别的客户端复用。用了会出错——这是常见坑。

4. 应用层连接池

除了 PgBouncer，应用框架/驱动自带连接池（如 Go 的 pgxpool、Java 的 HikariCP、Python 的 SQLAlchemy pool）。两者可叠加使用。

4.1 Go 示例（pgxpool）

config, _ := pgxpool.ParseConfig("postgres://user:pwd@localhost:5432/mydb")
config.MaxConns = 20                        // 池最大连接
config.MinConns = 5                         // 保持的最小空闲连接
config.MaxConnLifetime = time.Hour         // 连接最长存活（定期重建避免老化）
config.MaxConnIdleTime = 30 * time.Minute  // 空闲超时回收
pool, _ := pgxpool.NewWithConfig(ctx, config)
defer pool.Close()

4.2 连接池参数怎么定？

MaxConns 不是越大越好：应用池总和不应超过数据库能高效处理的连接数。常用经验公式起点：连接数 ≈ (核数 × 2) + 磁盘数。
多实例部署时：所有应用实例的池大小之和 ≤ 数据库 max_connections（还要留余量给运维连接）。

5. 连接泄漏与排查

-- 查看当前连接数与状态分布
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;
-- active=执行中  idle=空闲  idle in transaction=开了事务没提交(危险!)

-- 揪出长时间 idle in transaction 的连接（连接泄漏/忘记提交）
SELECT pid, usename, now()-state_change AS idle_time, query
FROM pg_stat_activity
WHERE state = 'idle in transaction' AND now()-state_change > interval '5 min';

-- 自动清理：设置空闲事务超时（PG 会自动断开这类连接）
ALTER SYSTEM SET idle_in_transaction_session_timeout = '10min';

idle in transaction 是重点排查对象：它既占连接，又钉住 MVCC 快照阻止 VACUUM（见 VACUUM与表膨胀），危害极大。

6. 最佳实践

生产用 PgBouncer（transaction 模式）+ 应用层连接池叠加。
max_connections 保持 100-300，靠池复用。
应用池大小设合理，多实例总和不超库上限。
设 idle_in_transaction_session_timeout 防连接泄漏钉住快照。
transaction 模式下避免会话级特性（SET/prepared/临时表）。
监控 pg_stat_activity 的连接状态分布。

7. 面试高频问答

Q1：为什么 PostgreSQL 特别需要连接池？ PG 每个连接是一个独立进程，内存和调度开销大，连接数多会拖垮数据库；连接池把大量应用连接复用成少量数据库连接。MySQL 是线程模型开销小，对连接池依赖没这么强。

Q2：PgBouncer 有哪三种池化模式？怎么选？ session（会话级，最安全复用低）、transaction（事务级，最常用）、statement（语句级，复用最高但限制多）。绝大多数选 transaction。

Q3：transaction 模式有什么坑？ 连接在事务间会被别的客户端复用，因此不能依赖会话级状态：SET 参数、prepared statement、临时表、LISTEN/NOTIFY 都可能出错。

Q4：max_connections 是不是越大越好？ 不是。连接过多空闲占资源、活跃争抢 CPU 内存反而更慢。数据库真正并行能力约等于核数+磁盘数，应设中等值并靠连接池复用。

Q5：idle in transaction 状态有什么危害？ 它开着事务却不提交，既占用连接，又钉住 MVCC 旧快照阻止 VACUUM 回收死元组导致表膨胀。用 idle_in_transaction_session_timeout 自动清理。

8. 下一步

学习 WAL与检查点机制，理解持久化与崩溃恢复。

目录