PostgreSQL并行查询的行分配与内存使用技术问询

我们在AWS Aurora（兼容PostgreSQL）上开展两组并行查询实验，查询计划链路为：Subquery Scan -> WindowAgg -> Gather Merge -> Sort -> Parallel Bitmap Heap Scan -> Bitmap Index Scan

• Run 1：work_mem=4GB，max_parallel_workers_per_gather=8，含leader共9个worker，每个处理169万行数据
• Run 2：work_mem=4GB，max_parallel_workers_per_gather=4，含leader共5个worker，每个处理约300万行数据

问题1：PostgreSQL如何将行分配给worker与leader？是worker从共享队列拉取至耗尽，还是由Gather Node等中心节点分发？ #

PostgreSQL采用worker主动从共享队列拉取任务的模式，而非中心节点分发。核心逻辑在PostgreSQL 13+源码中：

• 数据分配的核心函数为gather_getnext（位于src/backend/executor/nodeGather.c，约190-250行），worker进程通过调用ParallelBitmapHeapScanNext（位于src/backend/executor/nodeBitmapHeapscan.c，约500-550行）从共享的位图扫描结果中抢占任务分片。
• 具体来说，Bitmap Index Scan生成的位图会被分割为多个区间，worker通过原子操作抢占未处理的区间，直到所有区间处理完毕，leader也会参与抢占剩余区间。

问题2：如何粗略预估内存使用量？是否可通过总行数*行宽+少量额外开销计算？以此设置足够高的work_mem避免磁盘排序？ #

可以通过总行数×平均行宽+固定额外开销粗略预估排序、哈希等操作的内存使用，以此调整work_mem避免磁盘溢出：

• 排序操作的内存预估逻辑参考src/backend/utils/sort/tuplesort.c：
  • 初始化函数tuplesort_begin_heap（约400-450行）会计算单条元组的存储宽度（包含元组头、数据本身），结合预估的元组数量计算所需内存。
  • 额外开销主要包括排序状态结构体、内存管理块等，约占总内存的5%-10%，可粗略按总行数×(行宽+24字节)计算（24字节为元组头固定开销）。
• 注意：work_mem是单操作的内存上限（如单个Sort节点），并行场景下每个worker的Sort节点都会独立使用work_mem额度，总内存消耗为worker数×work_mem，需结合实例空闲内存调整。

问题3：PostgreSQL如何智能分配18GB空闲内存给worker避免磁盘溢出？将4个worker的work_mem降至2GB时会触发磁盘排序。 #

PostgreSQL本身不会主动“智能分配”空闲内存给worker，work_mem是全局/会话级的固定配置，每个worker的Sort等操作最多使用work_mem额度：

• 当设置work_mem=4GB、4个worker时，总内存消耗为4×4GB=16GB，接近18GB空闲内存，此时每个worker的Sort操作能在内存中完成；若降至work_mem=2GB，每个worker仅能容纳约150万行数据（按行宽+开销计算），无法覆盖实际要处理的300万行，因此触发磁盘排序（外部归并排序）。
• 内存使用触发磁盘溢出的判断逻辑在tuplesort.c的tuplesort_heap_alloc（约1200-1250行）中，当内存使用超过work_mem时，会启动外部排序流程。
• 若要利用18GB空闲内存，可将work_mem设为(18GB ÷ worker数) × 0.9（预留10%内存避免OOM），例如4个worker时设为4GB，刚好满足300万行的内存需求。

关于max_parallel_workers_per_gather的调优经验 #

并行查询并非worker越多速度越快，调优需结合以下维度：

• 数据量与单worker处理成本：当单worker处理数据量过小（如Run1中169万行），worker间的调度、通信开销会抵消并行收益；若数据量过大，增加worker可分摊压力。
• CPU核心数：max_parallel_workers_per_gather不应超过实例可用CPU核心的70%，避免CPU竞争导致上下文切换激增。
• IO能力：若查询依赖大量磁盘IO（如Bitmap Heap Scan），过多worker会加剧IO竞争，此时应适当减少worker数量。
• 建议从CPU核心数÷2开始测试，逐步调整并监控pg_stat_activity中的wait_event_type，若出现大量IPC或IO等待，则说明worker数量过多。

内容的提问来源于stack exchange，提问作者Han Qi