PostgreSQL连接数过多怎么办？连接管理与连接池优化教程

PostgreSQL 有个很坑的特性：每个连接对应一个独立的后端进程，连接不是"轻量"的。它默认的 max_connections 只有 100，一旦应用连接没管好——连接池没配、代码里漏关连接、突发的并发请求——很快就会撞上 FATAL: sorry, too many clients already，然后所有新连接被拒，服务看起来"数据库挂了"。更糟的是，很多人遇到这问题第一反应是直接把 max_connections 调大，结果内存被几百个进程吃光，反而把数据库拖垮。

这篇文章要解决的就是 PostgreSQL 连接管理的完整思路：为什么 PG 的连接这么"贵"、max_connections 该不该调、怎么用连接池（PgBouncer 等）把后面的真实连接数压下来、以及怎么定位"到底是谁占着连接不放"。读者读完应该能判断自己的库是被连接池问题、慢查询问题还是代码泄漏问题拖垮的，并知道对应的处置方法。

文章先讲 PG 的连接模型和它为什么不能简单粗暴地加大连接数，再给出一套连接数计算和参数配置方法，然后重点讲 PgBouncer 的部署和模式选择，最后讲怎么排查连接泄漏和长事务。文中的数值都是经验量级，实际以压测和监控为准。

一、PostgreSQL 的连接为什么"贵"

MySQL 的连接模型是"一个线程一个连接"，相对轻；PostgreSQL 是一个连接一个后端进程（fork 模型），每个连接都要占用独立的内存和进程资源。这带来两个直接后果：

其一，内存开销大。每个后端进程都有各自的私有内存（如 work_mem 可能被分配多份，temp_buffers、缓存等），几百个连接叠加起来，内存消耗非常可观。work_mem 默认 4MB，如果有 200 个连接同时执行含排序或哈希的复杂查询，理论峰值可能就是 200 × 4MB 甚至更高（复杂查询可能用到多份 work_mem）。

其二，上下文切换成本高。进程比线程重，大量连接频繁切换，CPU 会被调度开销吃走。

所以 PostgreSQL 社区的主流建议是：不要在数据库层堆连接数，而应在应用和数据库之间加一层连接池，用少量真实连接服务大量应用请求。这也是为什么 PgBouncer、pgpool-II 这类连接池工具在 PG 世界里如此重要。

二、max_connections 该不该调

max_connections 是允许的最大并发连接数，默认 100。先看现状：

-- 当前设置
SHOW max_connections;
-- 当前实际连接数
SELECT count(*) FROM pg_stat_activity;
-- 按用户/数据库/客户端统计连接
SELECT usename, datname, client_addr, count(*)
FROM pg_stat_activity
GROUP BY 1,2,3
ORDER BY 4 DESC;
-- 各状态连接数（idle 太多说明连接池没生效）
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

要不要调大 max_connections？判断标准是内存。粗略估算所需内存：

总内存需求 ≈ max_connections × (每连接基础开销 + work_mem 峰值)
每连接基础开销经验值：几 MB（含进程本身和各种缓冲区）

假设每连接基础开销 5MB、work_mem 4MB，200 个连接大约需要 200 × 9MB ≈ 1.8GB，再加上 shared_buffers（通常设物理内存的 25%）。所以在一台 8G 的机器上，max_connections 调到 200~300 已是上限，再往上就得掂量内存。

正确姿势：max_connections 保持在一个合理值（比如 100~200），把"高并发"交给连接池解决，而不是靠加大数据库连接数硬扛。把 max_connections 设成 1000，看似能接更多，实际上会被内存和进程调度反噬。

# postgresql.conf，谨慎调整
max_connections = 200            # 经验值，按内存定
shared_buffers = 2GB             # 通常物理内存的 25%
work_mem = 8MB                   # 会话级，别设太大，高并发时会翻倍

修改后需要重启 PostgreSQL 生效：

# RHEL/CentOS/Rocky（路径随版本，PG 通常自带 systemd 服务）
sudo systemctl restart postgresql-16
# Debian/Ubuntu
sudo systemctl restart postgresql

# 查看当前生效值
sudo -u postgres psql -c 'SHOW max_connections;'

风险提示：重启 PostgreSQL 会中断所有现有连接，业务会短暂不可用。生产环境务必在维护窗口操作，并确认应用能自动重连。回滚方式：备份 postgresql.conf，异常时改回并重启。能用 ALTER SYSTEM + reload 生效的参数尽量用 reload，减少停机。

三、用连接池解决根本问题

连接池的核心思想是：应用侧维护一个较小的连接池，复用连接处理请求；在数据库前面再放一层服务端连接池（PX 模式），让"应用连接数"和"数据库真实连接数"解耦。

应用侧连接池

无论是 Java 的 HikariCP、Python 的 psycopg 连接池、还是 Node 的 pg-pool，原则都一样：

• 池大小别设大。HikariCP 官方建议的一个经验公式：连接数 = CPU核心数 × 2 + 有效磁盘数。对一台 4 核的数据库，业务用连接池大小可能在 10 左右就够了，不是越大越好。连接太多反而增加数据库竞争。
• 设置连接超时和空闲回收，避免连接泄漏累积。
• 每个应用实例的池大小 × 实例数 = 数据库承受的总连接，全局要算总账。10 个应用实例、每个池 20，就是 200 个连接，已经到 max_connections 上限了。

# HikariCP 配置示例（application.yml）
spring:
  datasource:
    hikari:
      maximum-pool-size: 10          # 单实例最大连接，够用即可
      minimum-idle: 5
      connection-timeout: 30000      # 取连接超时 30s
      idle-timeout: 600000           # 空闲 10 分钟回收
      max-lifetime: 1800000          # 连接最长存活 30 分钟

服务端连接池：PgBouncer

PgBouncer 是最常用的 PostgreSQL 连接池中间件，它接收大量应用连接，复用少量到数据库的真实连接。三种模式差异很大：

绝大多数场景用 transaction 模式，它能在保证事务语义的前提下大幅降低真实连接数。但要注意它的限制：不能依赖会话级的 SET、不能跨事务用命名 prepared statement、不能用 LISTEN/NOTIFY 和会话级 advisory lock。

安装与配置：

# RHEL/CentOS/Rocky
sudo dnf install -y pgbouncer
# Debian/Ubuntu
sudo apt install -y 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 = 5000           ; 允许的最大客户端连接（应用侧）
default_pool_size = 20           ; 每个数据库/用户的 server 连接数上限
reserve_pool_size = 5            ; 保留连接
server_idle_timeout = 600
logfile = /var/log/pgbouncer/pgbouncer.log
admin_users = postgres

应用连接 PgBouncer 的 6432 端口，而不是直连 5432。这样 5000 个应用连接背后可能只有 20 个真实数据库连接，max_connections 压力骤降。

启用与重载：

sudo systemctl enable --now pgbouncer
# 修改配置后重载（PgBouncer 支持在线 reload，不中断）
sudo systemctl reload pgbouncer
# 或在控制台执行
psql -p 6432 -U postgres pgbouncer -c 'RELOAD;'

PgBouncer 管理控制台可查看池状态：

psql -p 6432 -U postgres pgbouncer -c 'SHOW POOLS;'
psql -p 6432 -U postgres pgbouncer -c 'SHOW STATS;'
psql -p 6432 -U postgres pgbouncer -c 'SHOW CLIENTS;'

四、连接被谁占着：定位连接泄漏和长事务

连接数打满时，最该做的是"抓现行"，看是谁占着连接不放。

-- 看每个连接的状态、持续时间和正在执行的语句
SELECT pid, usename, state,
       now() - state_change AS state_duration,
       now() - query_start  AS query_duration,
       wait_event_type, wait_event,
       left(query, 80) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY query_duration DESC;

-- 找出长时间 idle in transaction 的连接（非常危险）
SELECT pid, usename, state, now() - state_change AS idle_duration,
       left(query,80)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY idle_duration DESC;

-- 找出运行超过 5 分钟的查询（慢查询）
SELECT pid, now() - query_start AS dur, left(query,100)
FROM pg_stat_activity
WHERE state = 'active' AND now() - query_start > interval '5 minutes';

三类典型的"连接占着不放"：

• idle 连接过多：应用或连接池开了连接却不用（常见于连接池过大、或每个请求新建连接却不关）。说明该上连接池或调小池。
• idle in transaction 过多：事务开了却不提交也不回滚（代码里 BEGIN 后忘 COMMIT，或应用拿到连接后卡住）。这是最危险的，它还会阻塞 vacuum、导致表膨胀。
• active 且长时间运行：慢查询占着连接。要优化 SQL 或加超时。

处置手段：

-- 终止某个占用连接的后端进程（谨慎！会回滚该连接上的事务）
SELECT pg_terminate_backend(12345);

-- 或取消该连接正在执行的查询（更温和，只终止当前查询）
SELECT pg_cancel_backend(12345);

风险提示：pg_terminate_backend 会强制断开连接并回滚其事务，可能导致应用报错、事务丢失。只用于明确的"僵尸连接"或严重阻塞场景，且要先确认该连接不是关键业务正在执行的事务。更稳妥的做法是先 pg_cancel_backend 取消查询。回滚方式：无法"撤销"终止操作，但可通过修复应用逻辑、设置语句超时避免复发。

设置语句超时，从源头防止慢查询无限占连接：

# postgresql.conf
statement_timeout = 30s              # 单条语句最长执行时间
idle_in_transaction_session_timeout = 60s   # 空闲事务超时自动断开
lock_timeout = 10s                   # 等锁超时

五、监控与容量规划

-- 连接使用率告警阈值，建议 80%
SELECT
  (SELECT count(*) FROM pg_stat_activity) AS used,
  current_setting('max_connections')::int AS max_conn,
  round(100.0 * (SELECT count(*) FROM pg_stat_activity)
        / current_setting('max_connections')::int, 1) AS pct;

关注指标：

• 连接使用率：接近 80% 就要预警。
• idle 与 idle in transaction 数量：反映连接池质量。
• 活跃连接数：反映真实并发，用于规划池大小。
• 等待事件（wait_event_type）：看瓶颈在锁、IO 还是 CPU。

容量规划的顺序是：先加连接池，再优化慢查询和长事务，最后才考虑调 max_connections。把顺序搞反，就会陷入"调大连接数 → 内存吃紧 → 更慢 → 又调大"的恶性循环。

常见问题

Q：直接把 max_connections 从 100 调到 500 行不行？
短期能缓解"连接被拒"，但 500 个后端进程的内存开销很大，可能把机器压垮，而且连接太多会加剧锁竞争，性能反而下降。正解是上连接池，把真实连接数控制在几十到一两百。

Q：PgBouncer 用 transaction 模式，prepared statement 报错怎么办？
transaction 模式下 server 连接会在事务间被复用，命名 prepared statement 可能落到不同的 server 连接上而失效。解决方案：用协议级（非命名）prepared statement，或在 PgBouncer 1.21+ 开启 max_prepared_statements 支持。

Q：为什么 idle in transaction 这么危险？
它持有事务快照，会阻止 vacuum 清理死元组，导致表膨胀；还持有锁，可能阻塞其他事务。必须通过 idle_in_transaction_session_timeout 加应用层修复双管齐下。

Q：应用连接池设多大合适？
常用经验公式是 CPU 核心数 × 2 + 磁盘数，小规模业务单实例 10 左右往往就够。关键是算"池大小 × 实例数"的总账，别超过数据库能承受的量。

Q：重启 PostgreSQL 改参数，业务会断吗？
会。重启会断开所有连接。能在线的参数用 ALTER SYSTEM SET ... ; SELECT pg_reload_conf(); 生效，必须重启的放到维护窗口，并确认应用有重连机制。

总结

PostgreSQL 是"一连接一进程"模型，连接开销大，max_connections 默认 100，盲目调大只会把内存和 CPU 拖垮，正确做法是在数据库前加连接池。

PgBouncer 的 transaction 模式能在保证事务语义前提下把真实连接数从成百上千压到几十，是最常用的方案；代价是不支持会话级特性，要评估应用是否依赖。

排查连接打满时用 pg_stat_activity 抓现状，重点盯 idle in transaction 和长时间 active 连接，前者用 idle_in_transaction_session_timeout 兜底，后者用 statement_timeout 防慢查询永久占用。

应用侧连接池别设太大，牢记"单实例池大小 × 实例数 = 数据库总连接"，全局算账。

容量规划的顺序是：先加连接池，再优化慢查询和长事务，最后才考虑调 max_connections；任何重启、终止连接的操作都要评估影响、留好回滚。