PostgreSQL连接池优化实战：从PgBouncer到Pgpool-II的6种生产模式
数据库
FATAL: sorry, too many connections——凌晨3点的报警短信
凌晨3点，你的手机被报警短信轰炸：FATAL: sorry, too many connections already。50个微服务实例，每个实例20个连接，PostgreSQL的max_connections只有200。连接风暴（Connection Storm）让数据库直接拒绝新连接，整个系统雪崩。2026年，PostgreSQL连接池优化 是高并发系统的生命线——从PgBouncer的三种池化模式到Pgpool-II的负载均衡，从Odyssey的高性能架构到连接数容量规划，选对方案能让单机支撑10万并发。
本文将从PostgreSQL连接模型出发，带你完成6种生产级连接池模式，从配置到监控，从避坑到高级优化，全链路实战。
PostgreSQL连接池核心概念
| 概念 | 说明 |
|---|---|
| 连接池（Connection Pool） | 复用数据库连接，避免频繁创建/销毁连接的开销 |
| PgBouncer | 轻量级PostgreSQL连接池，支持Session/Transaction/Statement三种模式 |
| 事务级池化（Transaction Pooling） | 事务结束后释放连接回池，最大复用率 |
| 会话级池化（Session Pooling） | 客户端断开才释放连接，兼容性最好 |
| 语句级池化（Statement Pooling） | 每条SQL后释放连接，复用率最高但兼容性最差 |
| Pgpool-II | PostgreSQL中间件，支持连接池、负载均衡、读写分离 |
| Odyssey | Yandex开发的高性能PostgreSQL连接池，支持多数据库多用户 |
| max_connections | PostgreSQL最大连接数，每个连接是一个OS进程，约10MB内存 |
PostgreSQL进程模型与连接开销
PostgreSQL Process-Per-Connection 模型：
┌─────────────┐ ┌──────────────┐
│ Application │────→│ Postmaster │ (主进程，监听端口)
└─────────────┘ └──────┬───────┘
│ fork()
┌──────▼───────┐
│ Backend │ (每个连接一个进程)
│ Process │ ≈ 10MB 内存
│ (PG_CONN_1) │ ≈ 1个OS进程
└──────────────┘
┌──────────────┐
│ Backend │
│ Process │ 1000连接 ≈ 10GB内存
│ (PG_CONN_2) │ 上下文切换开销巨大
└──────────────┘
...N个
连接池介入后：
┌─────────────┐ ┌──────────────┐ ┌──────────────┐
│ App (1000) │────→│ Pooler (20) │────→│ PostgreSQL │
│ 连接 │ │ 复用连接 │ │ 真实连接 │
└─────────────┘ └──────────────┘ └──────────────┘
问题分析：PostgreSQL连接池优化的5大挑战
- 连接风暴：微服务启动/重启时大量实例同时建连，瞬间打满
max_connections，数据库拒绝所有新连接 - 连接泄漏：应用未正确释放连接，空闲连接堆积，池耗尽后新请求阻塞
- 模式选择困难：PgBouncer三种模式各有取舍，Session模式浪费连接，Transaction模式不兼容SET/PREPARE，Statement模式兼容性最差
- 容量规划缺失：没有公式计算连接数上限，凭经验配置导致资源浪费或连接不足
- 监控盲区：缺少连接池指标监控，连接耗尽、排队超时等问题发现太晚
分步实操：6种生产级连接池模式
模式1：PgBouncer Session模式配置
Session模式最接近原生PostgreSQL行为，兼容性最好，适合需要会话状态（SET变量、PREPARE语句、临时表）的场景。
; /etc/pgbouncer/pgbouncer.ini — Session模式配置
[databases]
; 生产数据库连接
myapp_db = host=10.0.1.100 port=5432 dbname=myapp_production
; 只读副本
myapp_readonly = host=10.0.1.101 port=5432 dbname=myapp_production
[pgbouncer]
; 监听地址和端口
listen_addr = 0.0.0.0
listen_port = 6432
; Session模式：客户端断开才释放连接
pool_mode = session
; 池大小：每个数据库/用户对的最大服务端连接数
; Session模式下，此值必须 >= 客户端并发数
default_pool_size = 50
; 最小池大小，预热连接
min_pool_size = 10
; 预留连接数，供管理员紧急连接
reserve_pool_size = 5
reserve_pool_timeout = 3
; 空闲连接超时（秒），回收长时间空闲的服务端连接
idle_timeout = 600
; 服务端连接最大存活时间（秒），防止长连接问题
server_lifetime = 3600
; 客户端空闲超时（秒）
client_idle_timeout = 0
; 服务端登录超时（秒）
server_login_retry = 3
; 连接等待超时（秒），池满时客户端等待时间
query_timeout = 30
query_wait_timeout = 30
; 认证方式
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
; 管理界面
admin_users = pgadmin
stats_users = pgmonitor
; 日志
log_connections = 0
log_disconnections = 0
log_pooler_errors = 1
verbose = 0
# 创建认证文件
echo '"myapp_user" "SCRAM-SHA-256$4096:...hash..."' > /etc/pgbouncer/userlist.txt
# 启动PgBouncer
pgbouncer -d /etc/pgbouncer/pgbouncer.ini
# 验证Session模式
psql -h 127.0.0.1 -p 6432 -U myapp_user myapp_db -c "SHOW pool_mode;"
# Python应用连接PgBouncer（Session模式）
import psycopg2
from contextlib import contextmanager
POOL_CONFIG = {
"host": "10.0.1.50",
"port": 6432,
"database": "myapp_db",
"user": "myapp_user",
"password": "secure_password",
"connect_timeout": 5,
"options": "-c search_path=public"
}
@contextmanager
def get_connection():
conn = psycopg2.connect(**POOL_CONFIG)
try:
yield conn
conn.commit()
except Exception:
conn.rollback()
raise
finally:
conn.close()
with get_connection() as conn:
cur = conn.cursor()
cur.execute("SET LOCAL work_mem = '256MB'")
cur.execute("SELECT * FROM orders WHERE user_id = %s", (12345,))
print(cur.fetchall())
模式2：PgBouncer Transaction模式——高并发首选
Transaction模式在事务结束后立即释放连接回池，是高并发场景的最佳选择。1000个客户端可能只需要20个真实数据库连接。
; /etc/pgbouncer/pgbouncer.ini — Transaction模式配置
[databases]
myapp_db = host=10.0.1.100 port=5432 dbname=myapp_production
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
; Transaction模式：事务结束即释放连接
pool_mode = transaction
; 池大小：Transaction模式下可以远小于客户端数
; 经验公式：pool_size = ((core_count * 2) + effective_spindle_count)
; 16核 + SSD → 约 40-50 连接
default_pool_size = 40
min_pool_size = 10
reserve_pool_size = 5
reserve_pool_timeout = 3
; 事务内空闲超时（秒），防止事务内长时间不操作
idle_transaction_timeout = 60
; 空闲连接超时
idle_timeout = 600
; 服务端连接最大存活时间
server_lifetime = 1800
; 服务端检查间隔（秒），检测服务端连接是否存活
server_check_query = SELECT 1
server_check_delay = 30
; 关键：事务模式下的等待超时
query_timeout = 30
query_wait_timeout = 60
; 客户端连接上限
max_client_conn = 2000
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
admin_users = pgadmin
stats_users = pgmonitor
# Transaction模式下的最佳实践：显式事务控制
import psycopg2
from psycopg2 import pool
threaded_pool = pool.ThreadedConnectionPool(
minconn=5,
maxconn=20,
host="10.0.1.50",
port=6432,
database="myapp_db",
user="myapp_user",
password="secure_password"
)
def transfer_funds(from_id, to_id, amount):
conn = threaded_pool.getconn()
try:
cur = conn.cursor()
cur.execute("BEGIN")
cur.execute(
"UPDATE accounts SET balance = balance - %s WHERE id = %s",
(amount, from_id)
)
cur.execute(
"UPDATE accounts SET balance = balance + %s WHERE id = %s",
(amount, to_id)
)
cur.execute("COMMIT")
except Exception as e:
cur.execute("ROLLBACK")
raise e
finally:
threaded_pool.putconn(conn)
def get_user_orders(user_id):
conn = threaded_pool.getconn()
try:
cur = conn.cursor()
cur.execute(
"SELECT id, status, total FROM orders WHERE user_id = %s",
(user_id,)
)
return cur.fetchall()
finally:
threaded_pool.putconn(conn)
-- Transaction模式下不能使用的功能清单
-- ❌ SET语句（事务结束会被重置）
SET work_mem = '256MB'; -- 事务结束丢失
-- ❌ PREPARE语句
PREPARE get_user AS SELECT * FROM users WHERE id = $1; -- 事务结束丢失
-- ❌ 临时表
CREATE TEMP TABLE tmp_cache AS SELECT * FROM products; -- 事务结束丢失
-- ❌ 通知/监听
LISTEN channel_name; -- 无法跨事务接收通知
-- ✅ 正确做法：在事务内使用SET LOCAL
BEGIN;
SET LOCAL work_mem = '256MB';
SELECT * FROM large_table ORDER BY data;
COMMIT; -- work_mem只在此事务内生效
模式3：Pgpool-II——负载均衡与读写分离
Pgpool-II不仅是连接池，更是PostgreSQL中间件，支持读写分离、负载均衡、自动故障切换。
; /etc/pgpool-II/pgpool.conf — Pgpool-II配置
; 监听配置
listen_addresses = '*'
port = 9999
unix_socket_directories = '/var/run/pgpool'
; 后端节点配置
backend_hostname0 = '10.0.1.100'
backend_port0 = 5432
backend_weight0 = 1
backend_flag0 = 'ALLOW_TO_FAILBACK'
backend_application_name0 = 'primary'
backend_hostname1 = '10.0.1.101'
backend_port1 = 5432
backend_weight1 = 1
backend_flag1 = 'ALLOW_TO_FAILBACK'
backend_application_name1 = 'replica1'
backend_hostname2 = '10.0.1.102'
backend_port2 = 5432
backend_weight2 = 1
backend_flag2 = 'ALLOW_TO_FAILBACK'
backend_application_name2 = 'replica2'
; 连接池配置
connection_cache = on
num_init_children = 50
max_pool = 4
child_life_time = 300
connection_life_time = 0
child_max_connections = 0
; 负载均衡
load_balance_mode = on
; SELECT查询自动分发到副本
; 写操作自动路由到主节点
; 读写分离
; 默认：主节点处理写，副本处理读
; 可通过hint精确控制
; 健康检查
health_check_period = 10
health_check_timeout = 5
health_check_user = 'pgpool_health'
health_check_password = 'health_check_pass'
health_check_max_retries = 3
; 故障切换
failover_command = '/etc/pgpool-II/failover.sh %d %h %p %D %H %P %m %M'
failback_command = '/etc/pgpool-II/failback.sh %d %h %p %D %H %P'
follow_master_command = '/etc/pgpool-II/follow_master.sh %d %h %p %D %H %P'
; 自动故障切换
auto_failback = off
failover_on_backend_error = off
; 日志
log_connections = on
log_disconnections = on
log_statement = off
log_per_node_statement = off
# Pgpool-II读写分离验证
# 写操作 → 主节点
psql -h 127.0.0.1 -p 9999 -U myapp_user myapp_production -c \
"INSERT INTO orders (user_id, status) VALUES (1, 'pending');"
# 读操作 → 自动负载均衡到副本
psql -h 127.0.0.1 -p 9999 -U myapp_user myapp_production -c \
"SELECT * FROM orders WHERE user_id = 1;"
# 使用hint强制路由到主节点
psql -h 127.0.0.1 -p 9999 -U myapp_user myapp_production -c \
"/*NO_LOAD_BALANCE*/ SELECT * FROM orders WHERE user_id = 1;"
# 查看节点状态
psql -h 127.0.0.1 -p 9999 -U pgpool_admin -c "SHOW pool_nodes;"
# Python应用使用Pgpool-II
import psycopg2
def get_pgpool_connection(read_only=False):
conn = psycopg2.connect(
host="10.0.1.50",
port=9999,
database="myapp_production",
user="myapp_user",
password="secure_password"
)
if read_only:
conn.set_session(readonly=True, autocommit=True)
return conn
# 写操作
write_conn = get_pgpool_connection(read_only=False)
cur = write_conn.cursor()
cur.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
write_conn.commit()
write_conn.close()
# 读操作（自动负载均衡）
read_conn = get_pgpool_connection(read_only=True)
cur = read_conn.cursor()
cur.execute("SELECT * FROM accounts WHERE id = 1")
print(cur.fetchall())
read_conn.close()
模式4：Odyssey——高性能连接池
Odyssey由Yandex开发，专为高吞吐场景设计，支持多数据库多用户路由，比PgBouncer性能更高。
; /etc/odyssey/odyssey.conf — Odyssey配置
storage myapp_production {
type remote
host 10.0.1.100
port 5432
}
storage myapp_replica {
type remote
host 10.0.1.101
port 5432
}
database default {
storage default
pool default {
pool_mode session
pool_size 0
}
}
database myapp_db {
storage myapp_production
pool myapp_pool {
pool_mode transaction
pool_size 50
pool_timeout 5000
; 空闲超时（毫秒）
idle_timeout 600000
; 服务端连接生命周期
server_lifetime 1800000
; 自动预备语句支持
discard_idle 600000
}
}
database myapp_readonly {
storage myapp_replica
pool readonly_pool {
pool_mode transaction
pool_size 30
pool_timeout 5000
}
}
; 用户路由规则
user myapp_user {
authentication scramsha256
password "SCRAM-SHA-256$4096:...hash..."
; 默认路由到主库
database default {
pool myapp_pool
}
; 只读路由到副本
database myapp_readonly {
pool readonly_pool
}
}
; 监听配置
listen {
host 0.0.0.0
port 6432
}
; 日志
log_format "%p %t %l [%c %s] %m\n"
log_debug 0
log_config 0
; 统计
stats_interval 60
# 启动Odyssey
odyssey /etc/odyssey/odyssey.conf
# 连接测试
psql -h 127.0.0.1 -p 6432 -U myapp_user myapp_db -c "SELECT version();"
# 只读连接
psql -h 127.0.0.1 -p 6432 -U myapp_user myapp_readonly -c "SELECT pg_is_in_recovery();"
# Python应用使用Odyssey
import psycopg2
from psycopg2 import pool
class OdysseyConnectionManager:
def __init__(self):
self.write_pool = pool.ThreadedConnectionPool(
minconn=2, maxconn=10,
host="10.0.1.50", port=6432,
database="myapp_db",
user="myapp_user", password="secure_password"
)
self.read_pool = pool.ThreadedConnectionPool(
minconn=2, maxconn=20,
host="10.0.1.50", port=6432,
database="myapp_readonly",
user="myapp_user", password="secure_password"
)
def get_write_conn(self):
return self.write_pool.getconn()
def get_read_conn(self):
return self.read_pool.getconn()
def put_write_conn(self, conn):
self.write_pool.putconn(conn)
def put_read_conn(self, conn):
self.read_pool.putconn(conn)
manager = OdysseyConnectionManager()
# 写操作
conn = manager.get_write_conn()
try:
cur = conn.cursor()
cur.execute("INSERT INTO events (type, data) VALUES (%s, %s)", ("click", "{}"))
conn.commit()
finally:
manager.put_write_conn(conn)
# 读操作
conn = manager.get_read_conn()
try:
cur = conn.cursor()
cur.execute("SELECT COUNT(*) FROM events")
print(cur.fetchone())
finally:
manager.put_read_conn(conn)
模式5：连接数容量规划公式
容量规划是连接池优化的基础，没有公式就是盲人摸象。
连接数容量规划公式：
1. PostgreSQL最大连接数规划：
max_connections = (CPU核心数 × 2) + 有效磁盘数
示例：16核CPU + SSD (等效4个磁盘)
max_connections = (16 × 2) + 4 = 36
生产建议：max_connections = 100（留余量），通过连接池控制真实连接
2. PgBouncer连接池大小：
pool_size = CPU核心数 × 2 + 有效磁盘数
示例：16核 + SSD
pool_size = 16 × 2 + 4 = 36 → 取40
3. 应用侧连接池大小（每个实例）：
app_pool_size = pool_size / 实例数
示例：40个总连接 / 10个实例 = 4个/实例
4. 客户端最大连接数：
max_client_conn = 实例数 × app_pool_size × 1.5
示例：10 × 4 × 1.5 = 60
5. 内存估算：
PostgreSQL总内存 ≈ max_connections × 10MB + shared_buffers
示例：100 × 10MB + 4GB = 5GB
-- 查看当前连接数分布
SELECT
datname AS database,
usename AS username,
state,
COUNT(*) AS connection_count,
AVG(EXTRACT(EPOCH FROM (now() - query_start))) AS avg_query_duration_sec
FROM pg_stat_activity
WHERE datname IS NOT NULL
GROUP BY datname, usename, state
ORDER BY connection_count DESC;
-- 查看连接数历史趋势（需要pg_stat_statements）
SELECT
EXTRACT(HOUR FROM query_start) AS hour,
COUNT(*) AS query_count
FROM pg_stat_activity
WHERE query_start > now() - interval '24 hours'
GROUP BY hour
ORDER BY hour;
-- 检查连接配置
SHOW max_connections;
SHOW superuser_reserved_connections;
-- 计算活跃连接占比
SELECT
state,
COUNT(*) AS count,
ROUND(COUNT(*)::numeric / (SELECT COUNT(*) FROM pg_stat_activity WHERE datname IS NOT NULL) * 100, 2) AS percentage
FROM pg_stat_activity
WHERE datname IS NOT NULL
GROUP BY state
ORDER BY count DESC;
# 连接数容量规划工具
def calculate_pool_config(cpu_cores, disk_type, instance_count, workload="mixed"):
effective_spindles = {
"hdd_1": 1, "hdd_4": 4, "hdd_8": 8,
"ssd": 4, "nvme": 8
}.get(disk_type, 4)
pg_max_connections = (cpu_cores * 2) + effective_spindles
pg_max_connections = max(pg_max_connections, 20)
pool_size = (cpu_cores * 2) + effective_spindles
if workload == "read_heavy":
pool_size = int(pool_size * 1.2)
elif workload == "write_heavy":
pool_size = int(pool_size * 0.8)
app_pool_per_instance = max(pool_size // instance_count, 2)
max_client_conn = int(instance_count * app_pool_per_instance * 1.5)
shared_buffers_gb = max(cpu_cores * 0.5, 2)
total_memory_gb = pg_max_connections * 0.01 + shared_buffers_gb
return {
"cpu_cores": cpu_cores,
"disk_type": disk_type,
"instance_count": instance_count,
"workload": workload,
"pg_max_connections": min(pg_max_connections + 20, 200),
"pgbouncer_pool_size": pool_size,
"pgbouncer_min_pool_size": max(pool_size // 4, 5),
"app_pool_per_instance": app_pool_per_instance,
"max_client_conn": max_client_conn,
"shared_buffers_gb": shared_buffers_gb,
"estimated_total_memory_gb": round(total_memory_gb, 1),
"pool_mode": "transaction" if workload in ("read_heavy", "mixed") else "session"
}
config = calculate_pool_config(
cpu_cores=16,
disk_type="ssd",
instance_count=10,
workload="mixed"
)
for key, value in config.items():
print(f"{key}: {value}")
模式6：生产监控与告警配置
没有监控的连接池就是定时炸弹，完善的监控和告警是生产部署的底线。
# prometheus-pgbouncer-exporter 配置
# /etc/prometheus/pgbouncer_exporter.yml
global:
scrape_interval: 15s
scrape_configs:
- job_name: 'pgbouncer'
static_configs:
- targets: ['10.0.1.50:9127']
metrics_path: /metrics
params:
pgbouncer_addr: ['10.0.1.50:6432']
# Prometheus告警规则
# /etc/prometheus/rules/pgbouncer_alerts.yml
groups:
- name: pgbouncer_alerts
rules:
- alert: PgbouncerPoolExhausted
expr: pgbouncer_pools_server_active >= pgbouncer_pools_server_max
for: 2m
labels:
severity: critical
annotations:
summary: "PgBouncer连接池耗尽"
description: "数据库 {{ $labels.database }} 的连接池已满，活跃连接数达到上限"
- alert: PgbouncerClientWaitQueue
expr: pgbouncer_pools_client_active > pgbouncer_pools_server_active * 2
for: 5m
labels:
severity: warning
annotations:
summary: "PgBouncer客户端等待队列过长"
description: "数据库 {{ $labels.database }} 客户端等待数是服务端的2倍以上"
- alert: PgbouncerHighWaitTime
expr: pgbouncer_pools_client_wait_time_seconds > 5
for: 1m
labels:
severity: warning
annotations:
summary: "PgBouncer等待时间过长"
description: "数据库 {{ $labels.database }} 等待时间超过5秒"
- alert: PostgreSQLTooManyConnections
expr: pg_stat_activity_count > 80
for: 5m
labels:
severity: warning
annotations:
summary: "PostgreSQL连接数过高"
description: "当前活跃连接数 {{ $value }}，接近 max_connections 上限"
-- PgBouncer管理命令
-- 通过psql连接到PgBouncer管理数据库
-- psql -h 127.0.0.1 -p 6432 -U pgadmin pgbouncer
-- 查看连接池状态
SHOW POOLS;
-- 查看客户端列表
SHOW CLIENTS;
-- 查看服务端列表
SHOW SERVERS;
-- 查看数据库列表
SHOW DATABASES;
-- 查看统计信息
SHOW STATS;
-- 查看活跃查询
SHOW ACTIVE_SOCKETS;
-- 强制释放空闲连接
PAUSE myapp_db;
RESUME myapp_db;
-- 关闭指定数据库的所有连接
KILL myapp_db;
#!/bin/bash
# /etc/pgbouncer/health_check.sh — PgBouncer健康检查脚本
PGBOUNCER_HOST="127.0.0.1"
PGBOUNCER_PORT="6432"
PGBOUNCER_ADMIN_USER="pgadmin"
ALERT_WEBHOOK="https://hooks.slack.com/services/xxx"
check_pool_status() {
local result=$(psql -h $PGBOUNCER_HOST -p $PGBOUNCER_PORT \
-U $PGBOUNCER_ADMIN_USER pgbouncer -c "SHOW POOLS;" -t 2>/dev/null)
if [ -z "$result" ]; then
send_alert "PgBouncer无法连接，可能已宕机"
return 1
fi
echo "$result" | while read line; do
active=$(echo "$line" | awk '{print $5}')
max_conn=$(echo "$line" | awk '{print $7}')
if [ -n "$active" ] && [ -n "$max_conn" ] && [ "$active" -ge "$max_conn" ]; then
send_alert "PgBouncer连接池耗尽: active=$active, max=$max_conn"
fi
done
}
check_postgresql_connections() {
local conn_count=$(psql -h 10.0.1.100 -p 5432 -U monitor -d myapp_production \
-t -c "SELECT COUNT(*) FROM pg_stat_activity WHERE datname = 'myapp_production';" 2>/dev/null)
conn_count=$(echo $conn_count | tr -d ' ')
if [ "$conn_count" -gt 80 ]; then
send_alert "PostgreSQL连接数过高: $conn_count / 100"
fi
}
send_alert() {
local message="$1"
curl -s -X POST "$ALERT_WEBHOOK" \
-H "Content-Type: application/json" \
-d "{\"text\": \"[ALERT] $message\"}" > /dev/null
echo "$(date): $message" >> /var/log/pgbouncer_health.log
}
check_pool_status
check_postgresql_connections
避坑指南：5个常见陷阱
陷阱1：Transaction模式下使用SET语句
-- ❌ 错误：SET在事务结束后丢失
SET work_mem = '256MB';
SELECT * FROM large_table ORDER BY data;
-- 下一个查询 work_mem 恢复默认值
-- ✅ 正确：使用SET LOCAL在事务内生效
BEGIN;
SET LOCAL work_mem = '256MB';
SELECT * FROM large_table ORDER BY data;
COMMIT;
陷阱2：应用侧连接池与PgBouncer双重池化
# ❌ 错误：应用侧HikariCP大连接池 + PgBouncer
# HikariCP: maximumPoolSize=50
# PgBouncer: default_pool_size=40
# 结果：50个客户端连接争抢40个服务端连接，大量等待
# ✅ 正确：应用侧小连接池 + PgBouncer
# HikariCP: maximumPoolSize=5（每实例）
# PgBouncer: default_pool_size=40
# 10个实例 × 5连接 = 50客户端连接，40个服务端连接足够
# HikariCP配置（配合PgBouncer Transaction模式）
spring.datasource.hikari.maximum-pool-size=5
spring.datasource.hikari.minimum-idle=2
spring.datasource.hikari.connection-timeout=3000
spring.datasource.hikari.idle-timeout=600000
spring.datasource.hikari.max-lifetime=1800000
spring.datasource.hikari.connection-test-query=SELECT 1
陷阱3：PgBouncer server_lifetime过长
; ❌ 错误：server_lifetime过长，服务端连接可能关联过期会话状态
server_lifetime = 86400
; ✅ 正确：合理设置server_lifetime，定期刷新连接
server_lifetime = 1800
; 配合server_check_query定期检查连接健康
server_check_query = SELECT 1
server_check_delay = 30
陷阱4：忽略superuser_reserved_connections
-- ❌ 错误：max_connections=100，没有预留超级用户连接
-- 当连接耗尽时，连管理员都无法登录
-- ✅ 正确：预留超级用户连接
ALTER SYSTEM SET superuser_reserved_connections = 3;
SELECT pg_reload_conf();
-- 紧急情况下使用超级用户连接
psql -h 10.0.1.100 -U postgres -d myapp_production -c \
"SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename = 'idle_app' AND state = 'idle';"
陷阱5：Pgpool-II健康检查用户权限不足
-- ❌ 错误：使用普通用户做健康检查
-- CREATE USER health_check WITH PASSWORD 'pass';
-- ✅ 正确：赋予健康检查用户足够权限
CREATE USER pgpool_health WITH PASSWORD 'health_check_pass' LOGIN;
GRANT pg_monitor TO pgpool_health;
-- 或最小权限
GRANT CONNECT ON DATABASE myapp_production TO pgpool_health;
错误排查表
| 错误信息 | 原因 | 解决方案 |
|---|---|---|
FATAL: sorry, too many connections already |
连接数超过max_connections |
增加连接池、降低default_pool_size、检查连接泄漏 |
FATAL: remaining connection slots are reserved for non-replication superuser connections |
普通用户连接数达到上限 | 增大max_connections或使用超级用户清理空闲连接 |
FATAL: no pg_hba.conf entry for host |
客户端IP不在白名单 | 在pg_hba.conf中添加客户端IP或连接池服务器IP |
FATAL: password authentication failed for user |
认证信息错误 | 检查PgBouncer的userlist.txt或auth_query配置 |
ERROR: connection not open |
PgBouncer无法连接到PostgreSQL | 检查后端数据库连接配置、网络、PostgreSQL是否运行 |
ERROR: query_wait_timeout |
客户端等待连接超时 | 增大query_wait_timeout或default_pool_size |
ERROR: idle_transaction_timeout |
事务内空闲时间过长 | 检查应用是否有长事务未提交，增大idle_transaction_timeout |
ERROR: server_login_retry failed |
PgBouncer重连PostgreSQL失败 | 检查PostgreSQL是否可达、pg_hba.conf是否允许连接 |
ERROR: prepared statement "s1" does not exist |
Transaction模式下PREPARE语句丢失 | 改用Session模式或使用prepared_statement_cache_size=0 |
ERROR: cannot drop temporary table because it is being used |
Transaction模式下临时表跨事务使用 | 改用Session模式或使用永久表替代临时表 |
高级优化
1. PgBouncer auth_query动态认证
; /etc/pgbouncer/pgbouncer.ini — 动态认证配置
[databases]
myapp_db = host=10.0.1.100 port=5432 dbname=myapp_production auth_query="SELECT usename, passwd FROM pg_shadow WHERE usename=$1"
[pgbouncer]
auth_type = md5
; 不需要维护userlist.txt，直接查询PostgreSQL获取密码
; 适合用户频繁变更的场景
-- 创建只读用户供auth_query使用
CREATE USER pgbouncer_auth WITH PASSWORD 'auth_query_pass' LOGIN;
GRANT pg_monitor TO pgbouncer_auth;
-- 配置pg_hba.conf允许pgbouncer_auth连接
-- host myapp_production pgbouncer_auth 10.0.1.50/32 md5
2. 多级连接池架构
多级连接池架构设计：
┌──────────────┐ ┌──────────────┐ ┌──────────────┐
│ App实例1 │ │ App实例2 │ │ App实例3 │
│ HikariCP(5) │ │ HikariCP(5) │ │ HikariCP(5) │
└──────┬───────┘ └──────┬───────┘ └──────┬───────┘
│ │ │
└────────┬────────┴────────┬────────┘
│ │
┌────────▼────────┐ ┌─────▼──────────┐
│ PgBouncer │ │ PgBouncer │
│ (写-Session) │ │ (读-Transaction)│
│ pool_size=20 │ │ pool_size=40 │
│ :6432 │ │ :6433 │
└────────┬────────┘ └──────┬──────────┘
│ │
┌────────▼────────┐ ┌─────▼──────────┐
│ PostgreSQL │ │ PostgreSQL │
│ Primary │ │ Replica ×2 │
│ max_conn=100 │ │ max_conn=100 │
└─────────────────┘ └─────────────────┘
关键设计：
- 写操作：Session模式（兼容PREPARE/SET）
- 读操作：Transaction模式（最大复用率）
- 应用侧小池 + PgBouncer大池
- 读写分离在连接池层完成
3. 连接池自动扩缩容
# 基于负载的连接池自动扩缩容
import psycopg2
import time
import logging
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger("pool_autoscaler")
class PoolAutoScaler:
def __init__(self, pgbouncer_host, pgbouncer_port, admin_user, admin_db="pgbouncer"):
self.conn_params = {
"host": pgbouncer_host,
"port": pgbouncer_port,
"user": admin_user,
"database": admin_db
}
self.min_pool = 10
self.max_pool = 100
self.scale_up_threshold = 0.8
self.scale_down_threshold = 0.3
def get_pool_stats(self):
conn = psycopg2.connect(**self.conn_params)
cur = conn.cursor()
cur.execute("SHOW POOLS;")
pools = cur.fetchall()
cur.execute("SHOW DATABASES;")
databases = cur.fetchall()
conn.close()
return pools, databases
def calculate_utilization(self, active, total):
if total == 0:
return 0
return active / total
def scale_pool(self, database, new_size):
new_size = max(self.min_pool, min(self.max_pool, new_size))
conn = psycopg2.connect(**self.conn_params)
cur = conn.cursor()
cur.execute(f"SET default_pool_size = {new_size} FOR {database};")
conn.close()
logger.info(f"Pool scaled: {database} → {new_size}")
def run(self):
pools, databases = self.get_pool_stats()
for pool in pools:
database = pool[0]
active = pool[4] if len(pool) > 4 else 0
total = pool[6] if len(pool) > 6 else 1
utilization = self.calculate_utilization(active, total)
if utilization > self.scale_up_threshold:
new_size = int(total * 1.5)
self.scale_pool(database, new_size)
elif utilization < self.scale_down_threshold:
new_size = int(total * 0.7)
self.scale_pool(database, new_size)
if __name__ == "__main__":
scaler = PoolAutoScaler("10.0.1.50", 6432, "pgadmin")
while True:
try:
scaler.run()
except Exception as e:
logger.error(f"Auto-scaler error: {e}")
time.sleep(60)
连接池方案对比
| 特性 | PgBouncer | Pgpool-II | Odyssey | PostgreSQL内置 | SQLAlchemy Pool |
|---|---|---|---|---|---|
| 池化模式 | Session/Transaction/Statement | Session/Transaction | Session/Transaction | 无 | Session |
| 读写分离 | ❌ 需外部配置 | ✅ 内置 | ✅ 配置路由 | ❌ | ❌ |
| 负载均衡 | ❌ | ✅ 内置 | ✅ 配置路由 | ❌ | ❌ |
| 故障切换 | ❌ | ✅ 内置 | ❌ | ❌ | ❌ |
| 性能 | 极高（C语言） | 高（C语言） | 极高（C语言） | 无 | 中（Python层） |
| 多数据库路由 | ✅ | ✅ | ✅ | ❌ | ❌ |
| 内存占用 | 极低（~2MB） | 中等 | 低 | N/A | 低 |
| 部署复杂度 | 低 | 高 | 中 | 无 | 无 |
| 适用场景 | 大多数场景 | 需要读写分离+负载均衡 | 超高并发+多租户 | 开发/测试 | 单应用Python项目 |
| PREPARE支持 | Session模式 | ✅ | ✅ | ✅ | ✅ |
| 推荐度 | ⭐⭐⭐⭐⭐ | ⭐⭐⭐⭐ | ⭐⭐⭐⭐ | ⭐⭐ | ⭐⭐⭐ |
总结
PostgreSQL连接池优化核心要点：1) 理解Process-Per-Connection模型的开销，每个连接≈10MB内存；2) 高并发场景首选PgBouncer Transaction模式，1000客户端只需20-40个真实连接；3) 需要读写分离+负载均衡选Pgpool-II；4) 超高并发+多租户选Odyssey；5) 连接数公式：
pool_size = CPU核心数 × 2 + 有效磁盘数；6) 应用侧小池+PgBouncer大池，避免双重池化；7) 生产必须有监控告警，连接池耗尽是P0级故障；8) 预留superuser_reserved_connections，防止连接耗尽后无法登录。
推荐工具
本站提供浏览器本地工具，免注册即可试用 →
#PostgreSQL#连接池#PgBouncer#Odyssey#性能优化#2026#数据库