提升PostgreSQL可扩展性：实现PgBouncer连接池

PostgreSQL为每个客户端连接使用一个后端进程。这种模型可靠，但当Web应用打开成百上千个大部分空闲的连接时，成本会变得很高。

PgBouncer位于您的应用和PostgreSQL之间，维护一个较小的服务器连接池，让多个客户端重用这些连接。其好处是降低连接开销，并使数据库内存使用更可预测。

瓶颈：原生PostgreSQL连接开销

PostgreSQL采用每个连接一个专用进程的模型。虽然高度稳定并确保隔离，但这种架构在压力下会引入显著开销：

资源消耗： 每个新连接都需要服务器派生一个新的后端进程，消耗内存和CPU资源。成百上千个空闲连接不必要地占用RAM。
建立缓慢： 建立新连接涉及网络握手、认证和进程初始化，为应用请求（尤其是频繁打开和关闭连接的请求）增加可测量的延迟。
扩展限制： 这些资源需求对PostgreSQL服务器实际能处理的并发连接数施加了有效上限，超过后性能会崩溃。

介绍PgBouncer：轻量级代理

PgBouncer作为一个轻量级代理服务器，位于客户端应用和PostgreSQL数据库服务器之间。其核心功能是维护一组持久、固定数量的到PostgreSQL后端的开放连接，为临时应用客户端请求池化和重用这些连接。

这种方法带来两个关键好处：

降低开销： PostgreSQL服务器只看到PgBouncer维护的固定连接池，消除了为传入客户端请求而进行昂贵的每个连接派生进程的循环。
提高吞吐量： 通过重用已建立的连接，PgBouncer最小化认证和连接初始化时间，从而显著提高应用吞吐量并降低延迟。

理解PgBouncer池化模式

PgBouncer的效率很大程度上取决于所选的池化模式。PgBouncer提供三种基本模式，每种适用于不同的应用架构和并发需求。

1. 会话池化 (`pool_mode = session`)

会话池化是默认且最安全的模式。一旦客户端连接，PgBouncer会将一个池化服务器连接专用于该客户端，直到客户端断开连接。只有当客户端显式关闭其会话时，连接才会返回到池中。

使用场景： 严重依赖会话特定功能（例如，预处理语句、临时表、用于自定义变量的SET命令）的应用。
优点： 最安全，与所有PostgreSQL功能完全兼容。
缺点： 效率最低的池化，因为即使在客户端空闲期间也会持有连接。

2. 事务池化 (`pool_mode = transaction`)

事务池化通常推荐用于高流量Web应用，特别是那些使用无状态API的应用。服务器连接仅在单个事务（从BEGIN到COMMIT/ROLLBACK）期间专用于客户端。一旦事务完成，连接立即返回到池中，供其他等待的客户端重用。

使用场景： OLTP系统和微服务中常见的短、频繁事务。
优点： 高效利用服务器资源。
缺点： 要求应用仔细管理事务。会话级状态更改（例如，SET extra_float_digits = 3）会在事务之间丢失或泄漏给其他客户端。

事务池化警告

在事务池化中，避免会话状态，如临时表、会话级SET更改、会话级咨询锁和长期存在的预处理语句。PgBouncer会在客户端之间重置服务器连接，但事务池化仍需要应用兼容性测试。

3. 语句池化 (`pool_mode = statement`)

语句池化是最激进的模式。每次执行单个语句后，服务器连接都会返回到池中。这种模式有效地阻止了多语句事务的使用，并且限制性很强。

使用场景： 高度专业化的只读负载，其中事务被明确禁止或不必要。
优点： 最大化连接重用。
缺点： 破坏所有事务。 仅适用于保证不使用事务的环境。

PgBouncer设置和初始配置

1. 安装

PgBouncer通常在标准发行版仓库中可用：

# 在Debian/Ubuntu上
sudo apt update && sudo apt install pgbouncer

# 在RHEL/CentOS上
sudo dnf install pgbouncer

2. 配置文件

PgBouncer主要依赖两个配置文件，通常位于/etc/pgbouncer/：

pgbouncer.ini：主配置，定义数据库、池限制和操作模式。
userlist.txt：定义PgBouncer用于向PostgreSQL服务器认证的用户和密码。

3. 定义用户 (`userlist.txt`)

出于安全考虑，PgBouncer不直接读取PostgreSQL的pg_authid表。您必须手动定义它可以认证的用户。确保此文件是安全的（例如，由pgbouncer用户拥有并限制权限）。

"app_user" "md5<md5-hash>"
"admin_user" "another_hash"

在某些认证设置中可以使用明文密码，但在您的PgBouncer和PostgreSQL版本支持的情况下，优先使用哈希或更强的认证。对于传统的MD5认证，存储的值是md5加上密码和用户名的MD5哈希，而不仅仅是密码。

4. 配置 `pgbouncer.ini`

pgbouncer.ini文件定义了池化器的行为。以下是一个针对使用事务池化的常见Web应用设置的示例。

[databases]
# 客户端连接字符串定义：
# <数据库名> = host=<pg服务器IP> port=<pg端口> dbname=<数据库名> user=<pgbouncer认证用户>
myappdb = host=10.0.0.5 port=5432 dbname=productiondb user=pgbouncer_service

[pgbouncer]

; 监听配置
listen_addr = *
listen_port = 6432

; 认证配置
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

; 池化模式（根据应用需求设置）
pool_mode = transaction
; DISCARD ALL 是会话池化的默认重置查询。
; 在事务池化中，在依赖会话状态之前请仔细测试。
server_reset_query = DISCARD ALL

; 连接限制和大小
; 到PgBouncer的最大客户端连接总数
max_client_conn = 1000

; PgBouncer为每个数据库保持开放的最大连接数（池的大小）
default_pool_size = 20

; 整个池中所有数据库允许的最大连接数
max_db_connections = 100

; 当池耗尽时，保留这么多槽位
reserve_pool_size = 5

; 日志和管理
admin_users = postgres, admin_user
stats_users = postgres

监控和管理

PgBouncer暴露一个名为pgbouncer的伪数据库，允许管理员实时监控池化器的状态、统计信息和连接。您使用定义的admin_users之一连接到PgBouncer监听端口（例如，6432）。

psql -p 6432 -U admin_user pgbouncer

关键管理命令：

命令	描述	使用说明
`SHOW STATS;`	显示连接统计信息（请求、字节、总持续时间）。	用于性能分析。
`SHOW POOLS;`	显示所有已配置数据库的池状态。	监控`cl_active`、`sv_active`、`sv_idle`。
`SHOW CLIENTS;`	列出所有连接到PgBouncer的客户端连接。
`RELOAD;`	尝试在不中断连接的情况下重新加载配置。
`PAUSE;`	停止接受新查询，等待当前事务完成。	用于维护或升级PgBouncer之前。

扩展提示

放置： 将PgBouncer安装在应用服务器上或专用的、高度网络优化的机器上，以最小化应用和池化器之间的延迟。
池大小： default_pool_size应设置为一个合理的数字（通常为10-50），这通常远低于PostgreSQL服务器本身允许的连接数。过大的池大小会破坏池化的目的。
客户端限制： 使用max_client_conn防止连接风暴压垮PgBouncer本身。这充当了一个健壮的前端节流器。

要点

当您的应用有许多短生命周期或空闲连接时，PgBouncer最有帮助。选择您的应用能容忍的最低侵略性池化模式，保持PostgreSQL服务器池足够小以保护数据库，并在用户感到变慢之前通过SHOW POOLS;观察等待的客户端。