PostgreSQL 核心原理：如何利用多核 CPU 加速大数据量扫描（并行查询）

在现代数据库系统中，硬件资源的充分利用是提升性能的关键。PostgreSQL 自 9.6 版本起引入了并行查询（Parallel Query）功能，使得单条 SQL 查询能够利用多个 CPU 核心协同工作，显著加速对大规模数据的扫描、连接与聚合操作。这一机制尤其适用于数据仓库、报表分析、ETL 等 OLAP 场景。

本文将从设计思想、执行模型、支持的操作类型、关键参数、调优策略、限制条件及实战案例七个维度，全面深入剖析 PostgreSQL 并行查询的核心原理与应用方法。

一、并行查询概述

1.1 为什么需要并行查询？

传统 PostgreSQL 查询由单个“后端进程”（backend process）顺序执行。当面对数十亿行的大表时，即使 I/O 被操作系统缓存或 SSD 加速，CPU 成为瓶颈——因为解析元组、计算表达式、执行过滤条件等操作均需消耗 CPU 周期。

而现代服务器普遍配备 16 核、32 核甚至更多 CPU 核心。若仅用一个核心处理查询，其余核心闲置，资源利用率极低。

并行查询的目标是：

• 将可分解的查询任务拆分为多个子任务；
• 分配给多个工作进程（worker processes）并行执行；
• 由主进程（leader）汇总结果；
• 在不改变 SQL 语义的前提下，实现近线性的性能提升。

1.2 核心理念

• 透明性：用户无需修改 SQL，优化器自动决定是否启用并行。
• 安全性：仅对“并行安全”（parallel-safe）的操作启用，避免数据竞争。
• 可控性：通过参数精细控制并行度，防止资源耗尽。
• 最小侵入：在现有执行器架构上扩展，保持兼容性。

1.3 支持的并行操作类型

并非所有查询操作都能并行。PostgreSQL 逐步扩展了支持范围：

典型支持场景：

1. 并行顺序扫描（Parallel Seq Scan）
   大表全表扫描，按数据块范围划分给多个 Worker。

2. 并行聚合（Parallel Aggregate）

   • Worker 执行局部聚合（Partial Aggregate）；
   • Leader 执行最终聚合（Finalize Aggregate）；
   • 适用于COUNT,SUM,AVG等可分解聚合函数。

3. 并行连接（Parallel Join）

   • 支持 Hash Join、Nested Loop；
   • 通常一方表并行扫描，另一方广播或分区。

4. 并行索引扫描
   对大索引进行分段扫描（需索引支持并行遍历）。

5. 分区表并行扫描
   每个分区可由独立 Worker 扫描，天然适合并行。

1.4 并行查询的核心价值

PostgreSQL 的并行查询是一项成熟且高效的性能优化技术，其核心价值在于：

• 自动化：优化器智能决策，无需改写 SQL；
• 可扩展：随 CPU 核数增加而提升吞吐；
• 精细化控制：通过参数与表级设置灵活调整；
• 安全可靠：严格限制于只读、并行安全的操作。

然而，并行并非银弹。DBA 需理解其原理、限制与成本模型，在合适的场景（大表、复杂聚合、低并发）下启用，并通过监控与测试验证效果。

1.5 常用命令参考

-- 查看当前并行参数SHOWmax_parallel_workers_per_gather;SHOWmin_parallel_table_scan_size;-- 会话级启用并行（测试用）SETmax_parallel_workers_per_gather=4;-- 为表指定并行度ALTERTABLElarge_tableSET(parallel_workers=6);-- 查看表统计信息（含 reltuples）SELECTschemaname,tablename,n_tup_ins,n_tup_updFROMpg_stat_user_tablesWHEREtablename='large_table';

二、并行查询的执行模型

PostgreSQL 的并行查询采用“Leader-Worker” 模型：

2.1 进程角色

• Leader 进程：

  • 即原始查询会话对应的后端进程；
  • 负责查询规划、启动 Worker、收集结果、执行非并行部分（如最终聚合）；
  • 可选择是否参与实际数据扫描（由parallel_leader_participation控制）。

• Worker 进程：

  • 由 Leader 动态创建的后台工作进程（Background Worker）；
  • 执行分配给它的子计划（如扫描某段数据块）；
  • 通过共享内存队列（shm_mq）将结果元组发送给 Leader。

2.2 通信机制

• 使用共享内存消息队列（Shared Memory Message Queue, shm_mq）实现 Leader 与 Worker 之间的高效通信。
• 每个 Worker 有两个队列：
  • 一个用于返回元组；
  • 一个用于上报错误信息。
• 避免频繁的进程间 IPC 开销，提升吞吐。

2.3 执行计划中的标识：Gather 节点

当查询使用并行时，执行计划中会出现Gather或Gather Merge节点：

• Gather：Worker 返回无序结果，Leader 直接收集；
• Gather Merge：Worker 返回已排序结果，Leader 执行多路归并（用于ORDER BY场景）。

例如：

Gather Workers Planned: 4 -> Parallel Seq Scan on large_table Filter: (status = 'active')

只要计划中出现Gather，即表示该子树启用了并行。

三、关键配置参数详解

并行查询的行为由多个 GUC 参数控制，按作用域可分为三类：

3.1 全局资源限制（需谨慎调整）

类比：max_worker_processes是银行总现金，max_parallel_workers是可用于取款的额度。

3.2 单查询并行度控制

此参数最常用，直接影响单查询的并行能力。

3.3 并行决策成本模型

优化器通过成本估算决定是否启用并行。以下参数影响决策阈值：

3.4 其他控制参数

四、并行度的确定规则

Worker 数量并非简单等于max_parallel_workers_per_gather，而是由以下因素共同决定：

1. 表/索引大小

   • 若表大小 <min_parallel_table_scan_size，不启用并行。
   • 否则，Worker 数 ≈log3(表大小 / min_parallel_table_scan_size) + 1，但不超过max_parallel_workers_per_gather。

   示例：

   • min_parallel_table_scan_size = 8MB
   • 表大小 8MB → 1 Worker
   • 24MB → 2 Workers
   • 72MB → 3 Workers
   • 216MB → 4 Workers

2. 系统资源限制

   • 实际启动 Worker 数 = MIN(规划数,max_parallel_workers_per_gather, 剩余可用max_parallel_workers)

3. 表级设置
   可通过ALTER TABLE ... SET (parallel_workers = N)为特定表指定并行度，覆盖全局设置。

五、并行查询的限制与不支持场景

尽管功能强大，并行查询仍有诸多限制：

5.1 查询类型限制

• 不支持写操作（INSERT/UPDATE/DELETE/MERGE）；
• 不支持CURSOR；
• 不支持FOR UPDATE/SHARE锁；
• 不支持SECURITY LABEL、RULES；
• 可序列化（Serializable）隔离级别下禁用；
• 客户端设置fetch_count（如 JDBC fetchSize）会禁用并行。

5.2 算子限制

• 窗口函数（Window Functions）整体不可并行（但部分子表达式可）；
• 有序集聚合（Ordered-set Aggregates）如PERCENTILE_CONT不支持；
• CTE（Common Table Expressions）默认物化且不并行（可用MATERIALIZED/NOT MATERIALIZED控制）；
• 外部数据包装器（FDW）默认不支持并行（需 FDW 自身实现）。

5.3 函数与表达式限制

• 若 WHERE/HAVING/SELECT 中包含非并行安全函数，整个查询无法并行。
• 函数的并行安全性由proparallel字段标记：
  • s= safe（默认）
  • r= restricted（仅在 Leader 安全）
  • u= unsafe（禁用并行）

可通过pg_proc查看：

SELECTproname,proparallelFROMpg_procWHEREproname='your_func';

5.4 资源与配置限制

• 所有 CPU 核心已饱和时，并行可能恶化性能；
• 高work_mem设置会导致内存爆炸（每个 Worker 独占work_mem）；
• I/O 密集型负载（未缓存）无法从并行中受益。

六、性能调优与监控

6.1 监控并行执行

• 使用EXPLAIN (ANALYZE, VERBOSE)查看：

  • Workers Planned：计划 Worker 数
  • Workers Launched：实际启动数
  • 各 Worker 的执行时间与行数

• 查询活跃 Worker：

  SELECTpid,backend_type,queryFROMpg_stat_activityWHEREbackend_typeLIKE'%parallel%';

6.2 调优建议

• 大表 OLAP 查询：适当提高max_parallel_workers_per_gather（如 4~8）；
• 高并发 OLTP：保持默认或设为 0，避免资源争抢；
• 内存充足：可适度增加work_mem，但注意总内存 ≈ max_parallel_workers * work_mem；
• 调整成本参数：若小表也想并行，降低parallel_setup_cost；
• 分区表 + 并行：天然契合，确保分区剪枝有效。

6.3 避免过度并行

• 并行并非总是更快。对于小表或简单过滤，启动开销可能超过收益；
• 通过对比EXPLAIN ANALYZE关闭/开启并行的结果，验证实际效果；
• 生产环境建议通过ALTER TABLE ... SET (parallel_workers = N)精细控制，而非全局调高。

七、实战案例

7.1 场景：分析 10 亿行订单表

-- 表结构CREATETABLEorders(id BIGSERIAL,user_idINT,amountNUMERIC,statusTEXT,created_atTIMESTAMP);-- 插入 10 亿行数据（略）

查询：统计总金额与订单数

EXPLAINANALYZESELECTCOUNT(*),SUM(amount)FROMordersWHEREstatus='completed';

优化步骤：

1. 确认表足够大（> 8MB），满足并行条件；
2. 设置并行度：

   SETmax_parallel_workers_per_gather=4;

3. 执行计划：

   Finalize Aggregate -> Gather Workers Planned: 4 -> Partial Aggregate -> Parallel Seq Scan on orders Filter: (status = 'completed'::text)

4. 性能对比：
   • 串行：120 秒
   • 4 Worker 并行：35 秒（加速比 ≈ 3.4x）

注：加速比受数据分布、CPU 核数、I/O 带宽等影响，通常低于理论值。