kingster 11 min read

SQL 与查询优化（PostgreSQL 篇）· 第七期

SQL 与查询优化（PostgreSQL 篇）· 第七期

查询缓存、连接池与中间件优化

从第一期到第六期，我们的焦点一直围绕单实例 PostgreSQL：执行计划、索引设计、连接算法、统计信息、物化视图与分区表，以及锁与并发控制。但是，当到达了几百万乃至上亿行数据之后，应用程序不仅查询复杂，连接数也可能成千上万。
本期我们跳出数据库内核，进入中间件层，探讨查询缓存、连接池和分布式中间件如何进一步提升系统的极限。

一、连接池 – 高并发的前提

1.1 为什么需要连接池？

在 PostgreSQL 中，每一个新的客户端连接都会在服务器端 fork() 一个新的进程（Backend Process）。一个空闲的数据库连接，即使没有执行任何查询，也会占用大约 5-10 MB 的内存。

然而，对于现代的 Web 应用和无服务器架构，通常不需要上百个全时的数据库连接。一个典型的请求可能只持续几毫秒来执行一个查询，然后释放连接。如果每次请求都重新打开连接，断开后立刻关闭，下一轮请求再次创建……这会引入显著的连接建立/销毁开销。

高并发下会产生两种典型的“连接风暴”现象：
现象机制检测手段连接风暴连接数超 max_connections 阈值时，新连接请求直接被拒绝ERROR: too many clients already向连接风暴"升级"空闲连接数过多（超过 tcp_keepalives_idle 阈值），系统不断尝试维持活着，且触发了 TCP keepalive 探针风暴tcpdump 观察到大量极小的探针包，同时 pg_stat_activity 里 idle 连接数极高查看当前连接状态，以评估是否需要连接池：

SELECT state, count(*) FROM pg_stat_activity GROUP BY state;
SELECT name, setting FROM pg_settings WHERE name = 'max_connections';

如果输出中的 idle 状态连接占据了绝大多数，那么，这些空闲连接正在浪费大量的服务器内存资源。

1.2 PgBouncer – 轻量级专用连接池核心

为了解决“连接数爆炸”的问题，标准的解决方案是在客户端和数据库之间引入一个连接池中间件。PgBouncer 就是最著名的专用连接池。

其工作原理类似这样：应用程序连接到 PgBouncer；PgBouncer 维护一个到后端 PostgreSQL 的真实连接的池（池中包含一定数量已建立的连接）；当一个请求到达时，PgBouncer 从池中找出一个空闲的真实连接分配给该请求；请求完成后，真实连接被归还到池中。

PgBouncer 提供三种连接池模式：
模式工作原理适用场景限制会话池（Session）连接生命周期 = 客户端会话全程（默认模式）使用临时表、SET 会话变量、LISTEN/NOTIFY、PREPARE 语句、WITH HOLD 游标性能收益最小事务池（Transaction）连接生命周期 = 单个事务。事务结束（COMMIT/ROLLBACK）后归还连接短事务为主的 OLTP 系统（推荐模式）不支持会话级特性（临时表、SET）语句池（Statement）连接生命周期 = 单条 SQL 语句执行结束后归还连接大量短查询场景不支持多语句事务配置示例 (pgbouncer.ini)：

[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction   # 事务池模式
default_pool_size = 20    # 每个数据库/用户的默认连接池大小
max_client_conn = 1000    # 允许的最大客户端连接数
listen_addr = *
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

PgBouncer 非常轻量级：无论有多少客户端连接，一个 PgBouncer 实例仅占用约 2-5 MB 的 RAM；每个连接的内存开销只有 2 KB 左右。纯连接池场景下，建议使用 PgBouncer，轻量简单，能够减少 97.5% 的连接数，并提升 53% 的吞吐量。

1.3 连接池最佳实战

设计容量估算公式：

pool_size = (max_connections - admin_connections) × buffer_factor × connection_multiplier / replication_factor

其中 connection_multiplier 取决于中间件架构：直连时代值为 1，引入 PgBouncer 后视压测情况可调整为 0.2~0.5。

Pgbouncer 的事务池模式能突破内存限制，把极大量的客户端连接收敛到少量的后端连接上。但使用事务池模式时，绝不能依赖会话级资源（即，不能在事务结束后还依赖临时数据存活）。这往往要求业务代码中所有 BEGIN … COMMIT ROI 级别的事务，都不应该依赖临时表和会话变量。若违反此限制，可能会看到一些后台报错（关于临时表或预备语句不存在）。

此外，也需要注意一些网络安全问题，尤其是在公网使用连接池时，务必配置 auth_file 中的用户密码，并建议使用 scram-sha-256 认证方式取代 md5。

二、Pgpool-II – 功能更丰富的中间件

Pgpool-II 是一个功能更丰富的中间件。相比于“单一职责”的 PgBouncer，Pgpool-II 还内置了自动读写分离、负载均衡、自动故障转移和查询缓存等功能。它运行在应用和数据库之间，类似一个 7 层（应用层）的负载均衡器，会完全解析 SQL 语句，然后决定将其转发到哪台 PostgreSQL 服务器（主库或从库）。

2.1 核心功能

功能实现方式适用场景连接池复用数据库连接类似 PgBouncer，但增加了一点开销读写分离SQL 解析识别 SELECT/INSERT/UPDATE/DELETE，写操作→主库，读操作→从库流量中读操作占比高的场景负载均衡加权轮询分发读请求到多个从库多从库场景下最大化读吞吐量自动故障转移配合流复制监控主从健康，主库故障时自动将从库提升为主库高可用架构需求内存查询缓存缓存 SELECT 结果到共享内存或 Memcached读多写少的重复查询场景

2.2 读写分离与负载均衡配置

典型的 Pgpool-II 部署在一主多从 PostgreSQL 流复制架构前端。

核心配置 (pgpool.conf)：

# 后端节点定义（0 为主库，1+ 为从库）
backend_hostname0 = '192.168.1.10'
backend_port0 = 5432
backend_weight0 = 1          # 权重（主库也可参与部分读负载）
backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = '192.168.1.11'
backend_port1 = 5432
backend_weight1 = 3          # 权重越高，读请求分配越多
backend_flag1 = 'ALWAYS_PRIMARY'   # 标识只读节点

# 负载均衡设置
load_balance_mode = on
master_slave_mode = on
master_slave_sub_mode = 'stream'   # 流复制模式

# 读写分离（SELECT 自动路由到从库）
black_function_list = 'nextval,setval'   # 这些函数会强制走主库

2.3 内存查询缓存机制

Pgpool-II 的内存查询缓存（In Memory Query Cache）可以将 SELECT 语句和它的结果集存储在共享内存或 Memcached 中。

不推荐缓存的 SELECT 包括这些：

  • 包含非 IMMUTABLE（不可变）函数（如 random()、now()、CURRENT_TIMESTAMP）的查询；
  • 访问临时表或未记录日志表的查询；
  • 结果集太大（超过 memqcache_maxcache 阈值）；
  • SELECT FOR SHARE/UPDATE；
  • 访问系统目录的查询。

从经验来看，如果系统更新（DML）频繁，过度的自动缓存失效可能会导致性能下降而非提升。因此，当缓存命中率低于 70% 时，建议禁用此功能。

如果确实有很强的重复查询需求，更安全的做法是采用业务层的缓存（如 Redis），或者使用数据库端的 物化视图（Materialized View），毕竟那些物化视图的刷新策略由开发人员显式控制，不会引发不可预测的缓存抖动。

三、读写分离架构 – 为读负载扩展

3.1 为什么需要读写分离？

对于读操作占比很高的场景（比如报表应用、BI 仪表盘），如果直接在主库上运行大查询，这些查询会消耗 CPU、内存和 I/O，拖慢主库处理事务性写操作的速度，导致整体系统响应变慢。

PostgreSQL 原生支持流复制（Streaming Replication），主库将预写日志（WAL）流式传输到一台或多台从库实时回放。这样就可以复制数据副本。应用程序可以将高开销的只读查询（SELECT）专门路由到这些从库上。

3.2 用 pgpool-II 实现读写分离

多数架构选择在客户端和后端之间部署 Pgpool-II 来实现读写分离：

  • pgpool-II 接收所有 SQL 请求并解析 SQL 类型；
  • INSERT / UPDATE / DELETE 直接路由到主库；
  • SELECT 默认路由到从库。某些特定的 SELECT，比如，调用 nextval()（会修改序列），依然会被标记并强制转到主库执行。

写入节点与只读节点权重决定读流量分配，而且 Pgpool-II 支持健康检查；如果某个从库宕机，它会自动将其从负载均衡池中剔除，从而使写入链路保持正常。

3.3 读写分离的注意事项 – 复制延迟

读写分离最需要关注的问题是 复制延迟（Replication Lag）。当事务在主库上提交后，需要经过流复制传输到从库并回放，从库才能读取到该数据。

针对复制延迟，有以下应对策略：
策略具体做法适用场景滞后消费关键读强制走主库，非关键读走从库强一致性要求高会话粘滞写入后一定时间内读请求一直路由主库用户修改自己的数据后立即浏览库内判断在从库上通过函数判断 pg_last_xact_replay_timestamp() 与主库的差距需要高精度的延迟感知只读事务使用 BEGIN READ ONLY; SET TRANSACTION READ ONLY; 强制从从库读取保证查询不会误写数据监控复制延迟：

-- 在从库上执行
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

如果复制延迟超过业务容忍度，需要排查网络、从库性能，或考虑使用同步流复制（会降低写入性能）。

3.4 中间件的局限性

pgpool-II 虽然是功能丰富的中间件，但使用时也需注意：

  • 连接池性能不如 PgBouncer：因其适合应对“即读即写”的综合型应用（包括解析 SQL 的行为），且一个连接对应一个 pgpool 进程，其内存开销（约 3-5 MB per child）大于 PgBouncer；
  • 纯连接池首选 PgBouncer：如果只需要连接池，PgBouncer 更轻量高效，配置也更简洁。将“代理 + 读写分离”这种更复杂的部署交给 Pgpool-II。

四、Citus – 真正的分布式 PostgreSQL

4.1 什么是 Citus？

Citus 是 PostgreSQL 的一个扩展，它将 PostgreSQL 转变为一个分布式的数据库：数据被自动按分片分布在一个服务器集群（称为 workers，即多个服务器节点）上。

4.2 分片与分布列 – 核心设计

在 Citus 中，每个表都需要指定一个 分布列（Distribution Column）。
分片策略原理适用场景哈希分片（Hash）DISTRIBUTE BY HASH(column) 将相同键值的行分配到同一分片按键等值查询为主（WHERE column = ?）范围分片（Range）DISTRIBUTE BY RANGE(column)，按列值范围划分日志、时序数据等范围查询场景Citus 针对不同的业务模型提供了分布列选择的特定指南：

多租户模型（SaaS 类）：
分布列应选择 tenant_id。所有表都按 tenant_id 分布，Citus 会将相同租户的数据放在同一个分片上（共置 Colocation）。这样，租户的查询可以直接在单节点上完成，无需分布式开销。

实时分析模型（高并发并行）：
分布列应选择 高基数（High Cardinality）列。避免选择偏斜分布的值（如只有少数几个枚举值的 status 列），以免数据集中到少数节点，造成某节点过热。

4.3 Citus 查询处理

Citus 的入口称为 协调器节点（Coordinator）。当协调器收到查询时，它依赖分布式查询规划器进行路径优化：

  • 找出所有相关的分片；
  • 规划最优的并行执行策略；
  • 向 Worker 分发「查询片段（fragment）」，并在最终合并部分结果以向客户端返回完整数据。

当 WHERE 条件中包含了分布列的精确匹配时，Citus 能够快速定位到单个分片，这种优化被称为 Fast Path——它让单键值的读写接近于单机数据库的低延迟。若是跨分片查询（如无分布列过滤的全表聚合），Citus 会采用 分布式执行 计划：各个 Worker 节点并行处理各自的分片，最后将局部结果汇集到 coordinator 合并。

4.4 配置与管理维护

操作命令/方法注意创建分布式表SELECT create_distributed_table('orders', 'customer_id')需在 coordinator 上执行多表共置SELECT mark_tables_colocated('orders', 'order_items')提升 JOIN 性能添加 WorkerSELECT * FROM master_add_node('192.168.1.12', 5432)需设置 citus.replication_model重新平衡分片SELECT rebalance_table_shards()在节点之间移动分片对于一些时间序列数据，Citus 也支持在分片内部再使用 PostgreSQL 原生分区（Partitioning）。这是实现定期归档（滚动删除）的最佳实践，因为在整个分布式架构中，它非常方便清理旧数据。

五、实战案例：全链路上云 – 用 pgpool-II + Citus 支撑 10 倍流量

5.1 背景

一个多租户电商 SaaS 平台，单 PostgreSQL 实例（RDS）在面对流量倍增后发生故障：

  • 每日写入订单量 1000 万行，数据库 CPU 持续 85% 以上；
  • 复杂运营报表查询占据了主库 40% 的资源；
  • 连接数峰值超过 2000，而 max_connections 仅为 800；
  • 出现大量客户端报错：“FATAL: sorry, too many clients already”。

5.2 架构改造

第一步：引入 PgBouncer 解决连接数瓶颈
短期方案：在原 RDS 实例前部署 PgBouncer，采用 事务池模式，default_pool_size = 80。

效果：后端实际连接保持在 ~70，但客户端可连接 1500+ 个并发会话。消除了因连接数超限导致的应用崩溃。

第二步：完成读写分离 + 负载均衡
中期方案：使用 Pgpool-II 在多个 RDS 只读副本前做 SQL 路由。架构调整：

  • 利用 AWS RDS 的流复制功能，新建 2 个只读副本（Read Replica）；
  • 在应用程序的 JDBC 字符串中，将目标改至 Pgpool-II 的监听端口（SELECT 默认被路由到副本）。

针对报表延迟容忍度高的业务（例如 BI 导出），通过 LOAD BALANCE 调度都指向只读节点，而一致性要求高的用户余额等读操作，通过 pgpool-II 配置文件中的 white_function_list 和 black_function_list 控制路由强制回主库。

第三步：Citus 分片攻克数据量爆炸
长期方案：扩展为 Citus 分布式架构：

  1. 确定分布列：使用 tenant_id 哈希分片，确保同租户的数据在物理节点上 共置（Colocated）；
  2. 对 orders 与其明细表 order_items 选择多表共置：避免跨节点 JOIN，提速 300% 以上；
  3. 对 events 日志表 ，为了提高写入吞吐量，采取 时间范围分片，并保留冷热数据分离管理。

5.3 优化结果

最终上线后，平台承受的 QPS 提升了 10 倍，P99 延迟从 1.2 秒 降至 180 毫秒 以内。高峰期的 CPU 使用率保持在 40% 以下。

六、总结与全文回顾

6.1 七期体系回顾

至此，七期《SQL 与查询优化》完成了从单机到分布式、从 SQL 编写到架构设计的完整闭环：

  • 第一期：读懂执行计划、索引入门、扫描方式与连接方法识别；
  • 第二期：窗口函数与 CTE 的高级应用与性能陷阱；
  • 第三期：连接优化、统计信息深度调优、扩展统计；
  • 第四期：物化视图、分区表与批量数据优化；
  • 第五期：优化器内部机制、代价参数与 pg_hint_plan；
  • 第六期：锁与并发控制、MVCC、autovacuum 调优；
  • 第七期：查询缓存、连接池与中间件（PgBouncer / pgpool-II / Citus）。

6.2 本期核心结论

  • 连接池是必备品：它为不仅降低新连接开销，也能节省内存、提升并发能力，尤其在无服务器架构或云环境里。默认值 max_connections 往往偏保守，但通过连接池我们能优雅地支撑上万路客户端。
  • 读写分离使扩展读负载非常经济：利用 pgpool-II，只需要添加只读副本，后端几乎不需大幅修改应用；但要关注复制延迟对数据一致性的影响。
  • 分布式 PG（Citus）突破最大性能瓶颈：Citus 可以把数据拆分跨多个节点给横向扩展带来质的飞越，适合多租户或实时分析的超大规模场景。最关键的优化是分布列的选择，这决定了 80% 的性能。
  • 选择中间件的三原则：
    仅需纯连接池 → PgBouncer；
  • 需连接池 + 读写分离 + HA → Pgpool-II；
  • 单机无法支撑数据量或写入吞吐 → Citus。


本系列到此暂告一段落。关于 PostgreSQL 的 SQL 优化与中间件扩展，远不止这些内容。但掌握七期所讲解的执行计划、统计信息、锁与中间件架构，已足以从容应对绝大多数的性能和架构挑战。

愿你的数据库，永远处于最快响应状态。

No comments yet

Continue reading