数据库连接池：PgBouncer、HikariCP 与应用层连接池

为什么连接池很重要

PostgreSQL 为每个连接创建一个 OS 进程（约 5MB RAM）。在 1000 个并发连接时，仅连接就需要 5GB——在运行任何查询之前。

问题

应用实例数：10
每实例线程数：50
所需连接数：500
PostgreSQL max_connections：通常 100-200
结果：负载下出现连接错误

PgBouncer（连接代理）

# /etc/pgbouncer/pgbouncer.ini

[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
listen_port = 6432
listen_addr = *

# 池模式选择：
# session：每个客户端会话一个服务器连接（与直连相同）
# transaction：每个事务一个连接（推荐用于 Web 应用）
# statement：每个语句一个连接（最激进，破坏某些功能）
pool_mode = transaction

# 连接限制
max_client_conn = 2000    # 到 PgBouncer 的总客户端连接数
default_pool_size = 25    # 每个数据库/用户的实际 PostgreSQL 连接数
min_pool_size = 10        # 始终就绪的连接
reserve_pool_size = 5     # 紧急备用连接

# 超时
server_idle_timeout = 600  # 10 分钟后关闭空闲服务器连接
client_idle_timeout = 0    # 不断开空闲客户端

# 认证
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

# 安装并运行
apt install pgbouncer
systemctl start pgbouncer

# 通过 PgBouncer 连接（端口 6432 而非 5432）
psql -h localhost -p 6432 -U myapp myapp

Node.js pg 连接池配置

import { Pool } from 'pg'

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,

  // 连接池大小
  max: 10,              // 最大连接数
  min: 2,              // 最小空闲连接数
  idleTimeoutMillis: 30_000,    // 30 秒后移除空闲连接
  connectionTimeoutMillis: 2_000, // 2 秒内无连接则失败

  // 健康检查
  allowExitOnIdle: false,
})

pool.on('error', (err) => {
  console.error('意外的连接池错误：', err)
})

// 正确的释放模式
async function query(sql: string, params?: any[]) {
  const client = await pool.connect()
  try {
    return await client.query(sql, params)
  } finally {
    client.release() // 始终释放，即使出错
  }
}

// 或直接使用 pool.query()（自动释放）
const result = await pool.query('SELECT * FROM users WHERE id = $1', [userId])

HikariCP（Java）

// application.yml (Spring Boot)
spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/myapp
    username: myapp
    password: secret
    hikari:
      maximum-pool-size: 20
      minimum-idle: 5
      idle-timeout: 600000       # 10 分钟
      connection-timeout: 30000  # 30 秒
      max-lifetime: 1800000      # 30 分钟
      pool-name: MyHikariPool
      connection-test-query: SELECT 1

连接池大小公式

池大小 = Tn × (Cm - 1) + 1

其中：
  Tn = 应用中的线程数
  Cm = 连接保持时间（计算时间 / I/O 时间）

示例：
  - 10 个应用线程
  - 查询总耗时 10ms，其中 5ms 等待数据库
  - Cm = 10/5 = 2
  - 池大小 = 10 × (2-1) + 1 = 11

但也要考虑：
  - 数据库服务器上的可用 CPU 核心数
  - 公式：(CPU 核心数 × 2) + 磁盘数
  - 对于 4 核 Postgres：4 × 2 + 1 = 9 个连接

监控连接池健康

// 监控连接池指标
setInterval(async () => {
  const { totalCount, idleCount, waitingCount } = pool
  
  console.log({
    total: totalCount,      // 活动 + 空闲连接数
    idle: idleCount,        // 等待被使用
    waiting: waitingCount,  // 等待连接的请求数
  })
  
  if (waitingCount > 0) {
    console.warn('连接池争用！增加池大小或优化查询')
  }
}, 10_000)

事务模式注意事项

PgBouncer 事务模式与以下功能不兼容：

• SET session_variable（在事务中使用 SET LOCAL）
• 预编译语句（使用 server_reset_query = DISCARD ALL）
• 咨询锁
• LISTEN/NOTIFY

对于这些功能，请使用会话模式或直接连接。