排序落盘后团队把work_mem从 4MB 全局调到 256MB。单条报表快了BI 高峰数据库却被 OOM Killer 终止。直接答案是work_mem约束的是一次 Sort、Hash 等操作的基础预算不是一条查询、更不是整个实例的内存上限多个操作、并行参与者与并发查询会叠加Hash 还会乘hash_mem_multiplier。work_mem是许多 Sort、Hash 等操作各自的基础预算。多个节点、Hash 倍数、并行参与者和并发连接会继续相乘允许受控落盘通常比全局追求零临时文件更安全。先用压力模型代替推荐值单个查询的执行内存压力 ≈ Σ(同时活跃 Sort 的各自预算) Σ(同时活跃 Hash 的 work_mem × hash_mem_multiplier) 再按实际并行参与者及其执行节点放大 实例峰值 ≈ 上述查询内存 × 同时执行的重查询数 shared_buffers backend/连接基础内存 maintenance/autovacuum/扩展等其他内存这不是上界公式节点生命周期未必重叠executor 还有不完全受work_mem约束的结构实际结构也可能提前 spillleader 与 worker 分工随计划变化。它的用途是识别风险因子不能拿来承诺“最多占多少”。它足以否定max_connections × work_mem或“每条查询最多 256MB”这两种简单口径。PostgreSQL 文档明确指出一个复杂查询可能有多个 Sort/Hash 操作并发会话可能同时使用各自预算。Hash 操作的上限还会乘hash_mem_multiplier。源码证明Sort 与 Hash 拿的是不同操作预算PostgreSQL 18.6 中两条关键源码路径把配置映射成 executor 状态src/backend/utils/sort/tuplesort.c的tuplesort_begin_common()为一次排序创建独立的TupleSort main、TupleSort sortMemoryContext并把该调用收到的workMem换算后写入allowedMem。排序数据超过预算后tuplesort 转为临时 runs 和外部归并这对应 EXPLAIN 的Sort Method、Memory、Disk与 temp blocks。src/backend/executor/nodeHash.c的get_hash_memory_limit()直接计算work_mem × hash_mem_multiplier × 1024。这对应 Hash 节点的Memory Usage、Batches和Disk Usage增大预算可能减少 batch但预算是每个相关 Hash 操作的不是整条计划共享一个固定块。SET LOCAL work_mem ├─ Sort → tuplesort_begin_common(workMem) → allowedMem → 内存排序或临时 runs └─ Hash → get_hash_memory_limit() → work_mem × hash_mem_multiplier → 内存表或 batches EXPLAIN (ANALYZE, BUFFERS) └─ Sort Memory/Disk、Hash Memory/Batches/Disk、temp read/writeSQL 是 PostgreSQL executor 的正式入口本文要证明的是服务端算子预算与实例内存放大不存在能比 SQL EXPLAIN 更直接的 Java 入口因此不添加与结论无关的 JDBC 包装代码。一个查询同时触发 HashAggregate 与 Sort在资源受控的测试库运行数据规模按机器调整DROPTABLEIFEXISTSmem_demo;CREATETABLEmem_demoASSELECTgASid,md5(g::text)ASk,g%100000ASgroup_id,random()ASvFROMgenerate_series(1,3000000)ASg;ANALYZEmem_demo;低内存实验使用事务级设置避免污染连接后续请求BEGIN;SETLOCALwork_mem4MB;SETLOCALmax_parallel_workers_per_gather0;EXPLAIN(ANALYZE,BUFFERS,SETTINGS)SELECTgroup_id,count(*)AScnt,avg(v)FROMmem_demoGROUPBYgroup_idORDERBYcntDESC,group_id;ROLLBACK;记录执行计划中的Sort Method、Memory、DiskHashAggregate 的 Batches、Memory Usage、Disk Usagetemp read/write总执行时间与 shared buffers。再提高当前事务预算BEGIN;SETLOCALwork_mem256MB;SETLOCALmax_parallel_workers_per_gather0;EXPLAIN(ANALYZE,BUFFERS,SETTINGS)SELECTgroup_id,count(*)AScnt,avg(v)FROMmem_demoGROUPBYgroup_idORDERBYcntDESC,group_id;ROLLBACK;高预算可能减少 Hash batches 和 Sort 落盘单查询更快但它只证明这条查询在单并发、禁用并行时受益不能支持全局修改。数据是否已缓存、实际 group 数和 planner 选择都会影响结果。同一条计划为什么能消费多份预算一个执行计划可能同时或交叠存在多个 Sort例如窗口函数、Merge Join 与最终 ORDER BYHash Join 的 build hash tableHashAggregateMemoize、Materialize、去重等其他状态子查询与 CTE 内各自的操作并行 worker 中重复执行的节点。work_mem不是预先为每个连接一次性保留的固定块这解释了空闲连接不等于都占满work_mem。但高并发计划同时达到预算时总量会急剧上升。Hash 为什么还要乘一次SHOWwork_mem;SHOWhash_mem_multiplier;Hash-based 操作可使用work_mem × hash_mem_multiplier。如果work_mem 256MB且 multiplier 为 2一个 Hash 节点的基准上限思路已接近 512MB同一查询再有第二个 Hash 与 Sort就不是“256MB 查询”。增大 multiplier 能减少 batch也会放大峰值。它适合在确定 Hash spill 是主要瓶颈且总量可控时局部验证不应与work_mem同时盲目上调。并行查询如何继续放大EXPLAIN(ANALYZE,BUFFERS,SETTINGS)SELECTgroup_id,count(*)FROMmem_demoGROUPBYgroup_id;查看Workers Planned与Workers Launched。并行查询使用的资源可能远多于非并行计划因为每个 worker 是独立进程某些操作在多个参与者中各自分配内存leader 也可能参与执行。Workers Planned 4不保证实际启动 4 个实际资源竞争会影响 launched 数。容量压测要以Workers Launched、真实并发和进程 RSS 为证据不能只把配置上限代入公式后声称精确。官方文档给出的容量含义更直接并行查询中的work_mem等资源限制通常逐 worker 生效4 个 worker 加 leader 的资源消耗可能接近非并行查询的 5 倍但并非每个节点都会在每个参与者中同时达到预算。并行维护命令的maintenance_work_mem又是整条命令共享不能把查询规则机械套到CREATE INDEX或VACUUM。把单查询收益升级为并发证据单会话 EXPLAIN 只能证明一次执行。要评估全局修改需在有明确内存限制的隔离实例中增加并发先从较小预算开始。示例work_mem-case.sqlBEGIN;SETLOCALwork_mem32MB;SETLOCALmax_parallel_workers_per_gather0;SELECTcount(*)FROM(SELECTgroup_id,count(*)AScnt,avg(v)FROMmem_demoGROUPBYgroup_idORDERBYcntDESC,group_id)ASreport_result;ROLLBACK;pgbench-n-c4-j4-T60-fwork_mem-case.sql target_database每一级并发同时采集数据库/容器 RSS、swap/OOM、临时文件增量、吞吐和 p95/p99若内存逼近隔离环境上限或开始 swap立即停止不继续把-c放大。该命令仅用于有资源上限的隔离压测禁止直接指向生产库。为什么“临时文件为零”不是优化目标SELECTdatname,temp_files,pg_size_pretty(temp_bytes)AStemp_bytesFROMpg_stat_databaseORDERBYtemp_bytesDESC;该视图是数据库级累计统计不能直接归因到某条 SQL且统计可能有刷新延迟或重置。可结合log_temp_files、pg_stat_statements和 EXPLAIN 定位具体工作负载。临时文件是受控退化路径。一个每天运行一次的报表 spill 10GB可能比 100 个并发报表各自多拿数百 MB 更安全。真正目标是业务吞吐、尾延迟、内存水位与临时盘容量共同满足而不是把temp_bytes清零。还要区分“慢但可完成”和“内存把实例杀掉”前者影响单任务后者会让所有连接中断并触发恢复。用 MemoryContext 证据补足进程 RSS操作系统 RSS 能证明进程当前占用但不能告诉你是哪一个 PostgreSQL 执行上下文在增长。PostgreSQL 18 可请求目标 backend 把 MemoryContext 树写入服务端日志SELECTpg_log_backend_memory_contexts(target_pid);target_pid应来自已经确认的测试会话或问题查询 PID不能照抄占位符。该函数会向目标 backend 发信号默认需要较高权限输出进入服务端日志而不是 SQL 结果因此还要控制日志访问和敏感信息范围。建议在隔离压测中按时间线采样T0查询启动前记录实例/容器内存与 PID T1Hash/Sort 进入高水位时请求 MemoryContext 日志 T2查询结束后再次记录 RSS、temp I/O 与上下文释放MemoryContext 日志可以定位 executor、tuple sort、hash 等上下文的分配方向但它是一个时点快照不能直接相加为整条查询历史峰值也不能替代操作系统和容器总量监控。先修计划再给内存Hash/Sort 过大有时是基数估算错误造成的。推荐顺序找第一处 estimated/actual rows 分叉修统计、过滤和 Join 顺序问题。减少无用返回列和中间行尽早过滤。验证索引是否能提供过滤或排序避免本不必要的全量 Sort。对确定受益的角色、事务或作业局部提高work_mem。同时限制 BI 并发与并行度再做混合负载压测。若第一处 estimated/actual rows 已明显分叉先按第 9 篇SQL 和索引没变计划为什么突然慢一百倍修正统计与计划给错误计划更多内存只会让错误路径跑得更昂贵。局部示例BEGIN;SETLOCALwork_mem64MB;SETLOCALhash_mem_multiplier1.5;-- 已验证且受并发控制的报表查询COMMIT;若经连接池执行必须保证事务边界可靠使用 session 级SET后忘记 RESET可能把高预算泄漏给后续用户。不能漏算的其他内存池实例容量还包括shared_buffers每个 backend 的基础状态、缓存和查询上下文maintenance_work_mem对 CREATE INDEX、VACUUM 等维护操作autovacuum worker 的维护内存边界WAL、锁表、连接认证、扩展与语言运行时操作系统页缓存及其他进程。因此“物理内存减 shared_buffers剩余全分给 work_mem”没有为峰值抖动、内核和维护任务留下安全余量。容器环境还要以 cgroup limit 而非宿主机总内存为边界。生产灰度与停止条件变更前记录查询 p50/p95/p99、规划和执行时间、temp bytes、进程/容器 RSS、swap、OOM 事件、并发数、workers、业务吞吐。灰度单位优先是单个报表角色或作业队列而非全实例。先限制并发为 1—2逐级增加每级覆盖完整报表周期。验收要求报表 SLA 改善同时数据库内存高水位、OLTP 延迟和临时盘都在预算内。出现以下任一信号立即停止扩大可用内存逼近预设安全线或开始持续 swappostmaster/backend 被 OOM killer 处理OLTP p99 或错误率超过阈值临时盘仍增长而内存也显著升高并发稍增就出现非线性抖动。恢复原work_mem可以限制后续分配但不能撤销当前已运行查询占用也不能消除 OOM 后恢复影响。必要时先停止新报表进入再等待或按业务可重试性取消明确的重查询。证据边界证据能证明不能证明高 work_mem 单查询更快该查询减少 spill 后受益全局高并发安全temp bytes 很高数据库产生大量临时 I/O唯一 SQL 和根因Sort Memory 为 200MB该 Sort 节点使用量整条查询总内存workers 为 4计划/执行使用并行参与者每个 worker 都吃满同样内存OOM 后调回配置后续预算恢复当前查询和实例影响已回滚面试表达主线work_mem是 Sort/Hash 等操作级预算一条查询可有多个操作Hash 再乘hash_mem_multiplier并行 worker 与并发连接继续放大。调优要看 EXPLAIN 的 Memory、Disk、Batches 与 temp I/O优先局部设置和并发治理可控 spill 通常比全局大内存安全。实验清理DROPTABLEIFEXISTSmem_demo;官方资料PostgreSQL 18Resource ConsumptionPostgreSQL 18Using EXPLAINPostgreSQL 18How Parallel Query WorksPostgreSQL 18Runtime StatisticsPostgreSQL 18Server Signaling and Memory Context LoggingPostgreSQL 18.6 源码tuplesort.c / tuplesort_begin_common()PostgreSQL 18.6 源码nodeHash.c / get_hash_memory_limit()PostgreSQL 18.6 源码标签 REL_18_6 
长河君 · 2026/9/3 19:48:01 · 阅读 1.2k PostgreSQL 内存调优实战（第 12 篇）：work_mem 只调大 64 倍，峰值为什么远不止 64 倍
排序落盘后团队把work_mem从 4MB 全局调到 256MB。单条报表快了BI 高峰数据库却被 OOM Killer 终止。直接答案是work_mem约束的是一次 Sort、Hash 等操作的基础预算不是一条查询、更不是整个实例的内存上限多个操作、并行参与者与并发查询会叠加Hash 还会乘hash_mem_multiplier。work_mem是许多 Sort、Hash 等操作各自的基础预算。多个节点、Hash 倍数、并行参与者和并发连接会继续相乘允许受控落盘通常比全局追求零临时文件更安全。先用压力模型代替推荐值单个查询的执行内存压力 ≈ Σ(同时活跃 Sort 的各自预算) Σ(同时活跃 Hash 的 work_mem × hash_mem_multiplier) 再按实际并行参与者及其执行节点放大 实例峰值 ≈ 上述查询内存 × 同时执行的重查询数 shared_buffers backend/连接基础内存 maintenance/autovacuum/扩展等其他内存这不是上界公式节点生命周期未必重叠executor 还有不完全受work_mem约束的结构实际结构也可能提前 spillleader 与 worker 分工随计划变化。它的用途是识别风险因子不能拿来承诺“最多占多少”。它足以否定max_connections × work_mem或“每条查询最多 256MB”这两种简单口径。PostgreSQL 文档明确指出一个复杂查询可能有多个 Sort/Hash 操作并发会话可能同时使用各自预算。Hash 操作的上限还会乘hash_mem_multiplier。源码证明Sort 与 Hash 拿的是不同操作预算PostgreSQL 18.6 中两条关键源码路径把配置映射成 executor 状态src/backend/utils/sort/tuplesort.c的tuplesort_begin_common()为一次排序创建独立的TupleSort main、TupleSort sortMemoryContext并把该调用收到的workMem换算后写入allowedMem。排序数据超过预算后tuplesort 转为临时 runs 和外部归并这对应 EXPLAIN 的Sort Method、Memory、Disk与 temp blocks。src/backend/executor/nodeHash.c的get_hash_memory_limit()直接计算work_mem × hash_mem_multiplier × 1024。这对应 Hash 节点的Memory Usage、Batches和Disk Usage增大预算可能减少 batch但预算是每个相关 Hash 操作的不是整条计划共享一个固定块。SET LOCAL work_mem ├─ Sort → tuplesort_begin_common(workMem) → allowedMem → 内存排序或临时 runs └─ Hash → get_hash_memory_limit() → work_mem × hash_mem_multiplier → 内存表或 batches EXPLAIN (ANALYZE, BUFFERS) └─ Sort Memory/Disk、Hash Memory/Batches/Disk、temp read/writeSQL 是 PostgreSQL executor 的正式入口本文要证明的是服务端算子预算与实例内存放大不存在能比 SQL EXPLAIN 更直接的 Java 入口因此不添加与结论无关的 JDBC 包装代码。一个查询同时触发 HashAggregate 与 Sort在资源受控的测试库运行数据规模按机器调整DROPTABLEIFEXISTSmem_demo;CREATETABLEmem_demoASSELECTgASid,md5(g::text)ASk,g%100000ASgroup_id,random()ASvFROMgenerate_series(1,3000000)ASg;ANALYZEmem_demo;低内存实验使用事务级设置避免污染连接后续请求BEGIN;SETLOCALwork_mem4MB;SETLOCALmax_parallel_workers_per_gather0;EXPLAIN(ANALYZE,BUFFERS,SETTINGS)SELECTgroup_id,count(*)AScnt,avg(v)FROMmem_demoGROUPBYgroup_idORDERBYcntDESC,group_id;ROLLBACK;记录执行计划中的Sort Method、Memory、DiskHashAggregate 的 Batches、Memory Usage、Disk Usagetemp read/write总执行时间与 shared buffers。再提高当前事务预算BEGIN;SETLOCALwork_mem256MB;SETLOCALmax_parallel_workers_per_gather0;EXPLAIN(ANALYZE,BUFFERS,SETTINGS)SELECTgroup_id,count(*)AScnt,avg(v)FROMmem_demoGROUPBYgroup_idORDERBYcntDESC,group_id;ROLLBACK;高预算可能减少 Hash batches 和 Sort 落盘单查询更快但它只证明这条查询在单并发、禁用并行时受益不能支持全局修改。数据是否已缓存、实际 group 数和 planner 选择都会影响结果。同一条计划为什么能消费多份预算一个执行计划可能同时或交叠存在多个 Sort例如窗口函数、Merge Join 与最终 ORDER BYHash Join 的 build hash tableHashAggregateMemoize、Materialize、去重等其他状态子查询与 CTE 内各自的操作并行 worker 中重复执行的节点。work_mem不是预先为每个连接一次性保留的固定块这解释了空闲连接不等于都占满work_mem。但高并发计划同时达到预算时总量会急剧上升。Hash 为什么还要乘一次SHOWwork_mem;SHOWhash_mem_multiplier;Hash-based 操作可使用work_mem × hash_mem_multiplier。如果work_mem 256MB且 multiplier 为 2一个 Hash 节点的基准上限思路已接近 512MB同一查询再有第二个 Hash 与 Sort就不是“256MB 查询”。增大 multiplier 能减少 batch也会放大峰值。它适合在确定 Hash spill 是主要瓶颈且总量可控时局部验证不应与work_mem同时盲目上调。并行查询如何继续放大EXPLAIN(ANALYZE,BUFFERS,SETTINGS)SELECTgroup_id,count(*)FROMmem_demoGROUPBYgroup_id;查看Workers Planned与Workers Launched。并行查询使用的资源可能远多于非并行计划因为每个 worker 是独立进程某些操作在多个参与者中各自分配内存leader 也可能参与执行。Workers Planned 4不保证实际启动 4 个实际资源竞争会影响 launched 数。容量压测要以Workers Launched、真实并发和进程 RSS 为证据不能只把配置上限代入公式后声称精确。官方文档给出的容量含义更直接并行查询中的work_mem等资源限制通常逐 worker 生效4 个 worker 加 leader 的资源消耗可能接近非并行查询的 5 倍但并非每个节点都会在每个参与者中同时达到预算。并行维护命令的maintenance_work_mem又是整条命令共享不能把查询规则机械套到CREATE INDEX或VACUUM。把单查询收益升级为并发证据单会话 EXPLAIN 只能证明一次执行。要评估全局修改需在有明确内存限制的隔离实例中增加并发先从较小预算开始。示例work_mem-case.sqlBEGIN;SETLOCALwork_mem32MB;SETLOCALmax_parallel_workers_per_gather0;SELECTcount(*)FROM(SELECTgroup_id,count(*)AScnt,avg(v)FROMmem_demoGROUPBYgroup_idORDERBYcntDESC,group_id)ASreport_result;ROLLBACK;pgbench-n-c4-j4-T60-fwork_mem-case.sql target_database每一级并发同时采集数据库/容器 RSS、swap/OOM、临时文件增量、吞吐和 p95/p99若内存逼近隔离环境上限或开始 swap立即停止不继续把-c放大。该命令仅用于有资源上限的隔离压测禁止直接指向生产库。为什么“临时文件为零”不是优化目标SELECTdatname,temp_files,pg_size_pretty(temp_bytes)AStemp_bytesFROMpg_stat_databaseORDERBYtemp_bytesDESC;该视图是数据库级累计统计不能直接归因到某条 SQL且统计可能有刷新延迟或重置。可结合log_temp_files、pg_stat_statements和 EXPLAIN 定位具体工作负载。临时文件是受控退化路径。一个每天运行一次的报表 spill 10GB可能比 100 个并发报表各自多拿数百 MB 更安全。真正目标是业务吞吐、尾延迟、内存水位与临时盘容量共同满足而不是把temp_bytes清零。还要区分“慢但可完成”和“内存把实例杀掉”前者影响单任务后者会让所有连接中断并触发恢复。用 MemoryContext 证据补足进程 RSS操作系统 RSS 能证明进程当前占用但不能告诉你是哪一个 PostgreSQL 执行上下文在增长。PostgreSQL 18 可请求目标 backend 把 MemoryContext 树写入服务端日志SELECTpg_log_backend_memory_contexts(target_pid);target_pid应来自已经确认的测试会话或问题查询 PID不能照抄占位符。该函数会向目标 backend 发信号默认需要较高权限输出进入服务端日志而不是 SQL 结果因此还要控制日志访问和敏感信息范围。建议在隔离压测中按时间线采样T0查询启动前记录实例/容器内存与 PID T1Hash/Sort 进入高水位时请求 MemoryContext 日志 T2查询结束后再次记录 RSS、temp I/O 与上下文释放MemoryContext 日志可以定位 executor、tuple sort、hash 等上下文的分配方向但它是一个时点快照不能直接相加为整条查询历史峰值也不能替代操作系统和容器总量监控。先修计划再给内存Hash/Sort 过大有时是基数估算错误造成的。推荐顺序找第一处 estimated/actual rows 分叉修统计、过滤和 Join 顺序问题。减少无用返回列和中间行尽早过滤。验证索引是否能提供过滤或排序避免本不必要的全量 Sort。对确定受益的角色、事务或作业局部提高work_mem。同时限制 BI 并发与并行度再做混合负载压测。若第一处 estimated/actual rows 已明显分叉先按第 9 篇SQL 和索引没变计划为什么突然慢一百倍修正统计与计划给错误计划更多内存只会让错误路径跑得更昂贵。局部示例BEGIN;SETLOCALwork_mem64MB;SETLOCALhash_mem_multiplier1.5;-- 已验证且受并发控制的报表查询COMMIT;若经连接池执行必须保证事务边界可靠使用 session 级SET后忘记 RESET可能把高预算泄漏给后续用户。不能漏算的其他内存池实例容量还包括shared_buffers每个 backend 的基础状态、缓存和查询上下文maintenance_work_mem对 CREATE INDEX、VACUUM 等维护操作autovacuum worker 的维护内存边界WAL、锁表、连接认证、扩展与语言运行时操作系统页缓存及其他进程。因此“物理内存减 shared_buffers剩余全分给 work_mem”没有为峰值抖动、内核和维护任务留下安全余量。容器环境还要以 cgroup limit 而非宿主机总内存为边界。生产灰度与停止条件变更前记录查询 p50/p95/p99、规划和执行时间、temp bytes、进程/容器 RSS、swap、OOM 事件、并发数、workers、业务吞吐。灰度单位优先是单个报表角色或作业队列而非全实例。先限制并发为 1—2逐级增加每级覆盖完整报表周期。验收要求报表 SLA 改善同时数据库内存高水位、OLTP 延迟和临时盘都在预算内。出现以下任一信号立即停止扩大可用内存逼近预设安全线或开始持续 swappostmaster/backend 被 OOM killer 处理OLTP p99 或错误率超过阈值临时盘仍增长而内存也显著升高并发稍增就出现非线性抖动。恢复原work_mem可以限制后续分配但不能撤销当前已运行查询占用也不能消除 OOM 后恢复影响。必要时先停止新报表进入再等待或按业务可重试性取消明确的重查询。证据边界证据能证明不能证明高 work_mem 单查询更快该查询减少 spill 后受益全局高并发安全temp bytes 很高数据库产生大量临时 I/O唯一 SQL 和根因Sort Memory 为 200MB该 Sort 节点使用量整条查询总内存workers 为 4计划/执行使用并行参与者每个 worker 都吃满同样内存OOM 后调回配置后续预算恢复当前查询和实例影响已回滚面试表达主线work_mem是 Sort/Hash 等操作级预算一条查询可有多个操作Hash 再乘hash_mem_multiplier并行 worker 与并发连接继续放大。调优要看 EXPLAIN 的 Memory、Disk、Batches 与 temp I/O优先局部设置和并发治理可控 spill 通常比全局大内存安全。实验清理DROPTABLEIFEXISTSmem_demo;官方资料PostgreSQL 18Resource ConsumptionPostgreSQL 18Using EXPLAINPostgreSQL 18How Parallel Query WorksPostgreSQL 18Runtime StatisticsPostgreSQL 18Server Signaling and Memory Context LoggingPostgreSQL 18.6 源码tuplesort.c / tuplesort_begin_common()PostgreSQL 18.6 源码nodeHash.c / get_hash_memory_limit()PostgreSQL 18.6 源码标签 REL_18_6