work_mem: 这是一个陷阱！

work_mem: 这是一个陷阱摘要本文探讨了一个 PostgreSQL 内存问题尽管work_mem仅设置为 2 MB但某个查询却消耗了 2 TB 的 RAM。根本原因在于 PostgreSQL 的内存上下文系统该系统只在查询执行结束时释放内存而不是在执行过程中释放导致所有work_mem分配都在单个上下文内累积直到执行完成。原文链接我的朋友 Henrietta Dombrovskaya 在 Telegram 上联系了我。她的生产集群刚刚被 OOM killer 杀死消耗了 2 TB 的 RAM。work_mem设置为 2 MB。有些不对劲。Hetty 和我一样喜欢玩弄巨型硬件。2 TB 的 RAM 在她的世界里并不罕见。但在高峰时段因单个查询导致整个集群崩溃与凌晨 3 点的故障是完全不同的问题。当 OOM killer 在最糟糕的时刻来袭时你需要快速找到答案。一个重要的细节下面展示的内存日志并非来自生产事故。Hetty 在另一台服务器上重现了这一行为来调查。那一次她在 OOM killer 发威之前停止了查询。生产集群就没那么幸运了。我想直接指出这类问题靠一个好的网络关系比靠好的搜索引擎解决得更快。Hetty 是一位出色的 Postgres 专家。我们一起研究了这个问题的成因。我把它写下来是因为你也会遇到而且 Postgres 内存管理的行为确实令人惊讶。拯救危机的工具在我们深入探讨为什么之前让我向你介绍一个在这次对话之前我并不知道存在的函数pg_log_backend_memory_contexts。传入一个 PID。Postgres 会将该后端的完整内存上下文树转储到日志中。每个分配、每个上下文、包括大小和块数量。selectpg_log_backend_memory_contexts(299392);然后查看你的日志。我有点尴尬之前不知道这个函数。二十年使用 Postgres我每周都能学到新东西。这就是我喜欢这个数据库的原因。该函数在 Postgres 14 中引入。如果你想了解更多请参阅 Luka 的博客文章。我们看到的内容以下是日志输出中的相关行level: 4; ExecutorState: 234954896 total in 40 blocks; 14989952 free (524059 chunks); 219964944 used level: 5; HashTableContext: 339738680 total in 47 blocks; 4716320 free (84 chunks); 335022360 usedExecutorState 约 235 MBHashTableContext 约 340 MB。work_mem为 2 MB。该后端的总计Grand total: 586713672 bytes in 409 blocks; 20823696 free (527202 chunks); 565889976 used在重现服务器上单个后端约 557 MB。生产集群有 2 TB 的 RAMOOM killer 不得不介入。ExecutorState 中的 524,059 个块立即引起了我们的注意。但是每个 DBA 都知道 work_mem任何经验丰富的 DBA 都知道work_mem不是每个查询的内存。正如 Postgres 文档 所述work_mem是每个哈希或排序操作可以使用的内存量。一个查询可以有很多这样的操作。加上并行 worker数量会快速增加。所以 2 MB 乘以很多哈希、很多排序、很多 worker已经可以让你陷入麻烦。但 2 TB那是一个完全不同的数量级。即使有惊人的并行 worker 数量和操作数量数学也不应该得出这个结果。一定有其他原因。为什么 PostgreSQL 在这里忽略了 work_mem简短回答它没有忽略。它只是不能控制一切。work_mem限制了每个哈希或排序操作分配的内存。但真正的罪魁祸首是内存只在整个操作结束时释放而不是在执行过程中释放。这是设计如此。Postgres 内存上下文系统的设计理念是一次性释放整个上下文比跟踪单个分配更快、更可靠。来源于源代码 README内存上下文相对于直接使用 malloc/free 的主要优势在于可以轻松释放整个内存上下文的内容而无需请求释放其中的每个块。ExecutorState是查询开始时创建的内存上下文。执行器需要的所有内容都放在其中。它在查询完成时销毁而不是之前。HashTableContext是ExecutorState的子级。它保存哈希表数据桶、条目所有这些。哈希连接的整个生命周期都存活。在结束时一次性释放。524,059 个块是罪魁祸首回到那些块。查询是一个select调用了一个 plpgsql 函数该函数内部执行了一个copy操作然后与另一个表连接结果。完全有效的 Postgres。但用 Hetty 的话来说能做某事并不意味着你应该做。开发者已经学会了很好地使用函数。但随后他们做了一个非常面向对象的事情他们在连接中把那个函数当作另一个表来使用。一个典型的重构错误。Hetty 实际上将在 Nordic PGDay 2026 上讨论这种 SQL 结构化陷阱。从 Postgres 的角度看这是一个巨大的操作。一个从开始到结束的单一 ExecutorState 上下文。这 524,059 个块中的每一个都使用了最多work_mem大小的内存。而且因为它们都生活在同一个 ExecutorState 上下文中直到整个操作完成之前它们都不会被释放。操作从未完成。OOM killer 确保了这一点。这就是解释 2 TB 的组合不仅仅是哈希和排序的数量而是每个work_mem块都累积在一个上下文内而这个上下文在完成之前不会释放任何东西。诚实的脚注当我们询问社区时答案大致是没有人完全理解 Postgres 内存行为的方方面面。源代码中的内存上下文 README 是最接近权威文档的。如果你想要深入了解值得一读。你能防止这种情况吗你无法对每个后端的 Postgres 内存设置硬上限。没有这样的设置。Postgres 会获取它需要的内存。你能做的是修复你的统计数据。如果计划器低估了行数它会在何时溢出到磁盘方面做出糟糕的决定。运行ANALYZE检查pg_stats和pg_statistic。如果你有具有相关值的列这违反了更高范式本身是一种建模问题使用CREATE STATISTICS来捕获相关性。使用ALTER TABLE ... ALTER COLUMN ... SET STATISTICS增加每列目标只在列值相互独立时有所帮助。修复查询。消耗 2 TB RAM 的查询是一个糟糕的查询。就是这样。但有时你继承的是糟糕的查询。在这种情况下……使用查询超时。你无法限制内存但可以使用statement_timeout终止运行时间过长的查询。使用pg_log_backend_memory_contexts监控。既然你知道它的存在就使用它。当某些东西看起来不对时调用该函数。你会在 OOM 杀死你的服务器之前知道发生了什么。没有任何硬件可以弥补错误的查询这是 Hetty 在我们对话结束时说的她说得有道理。公平地说写那个函数的人可能没有预料到这种行为。大多数开发者都不会。根本原因是一个写得糟糕的查询消耗了远多于预期的内存。Postgres 在执行结束时才释放内存的底层行为是设计如此而不是 bug。理解为什么Postgres 这样做并不能修复糟糕的查询。但它确实可以帮助你向应用团队解释为什么他们的简单select在高峰时段导致了生产故障。有时这就足以让查询被重写。