Heroku Postgres设置work_mem后仍出现内存溢出错误咨询

可能的核心原因 #

• PostgreSQL的work_mem是单个排序/哈希操作的内存配额，而非全局或单连接的内存配额：如果一条查询包含多个排序节点，或是开启了并行查询，每个操作、每个并行worker进程都可以占用最高等于work_mem的内存。你将work_mem设置为4GB、8GB属于严重过高的配置，只要同时运行2~3条带排序的查询，加上shared_buffers、系统进程、后台维护进程的内存占用，很容易直接打满实例内存触发OOM，这也是你升级实例内存后仍然报错的核心诱因。
• 业务平稳运行时突然出现异常，大概率是数据量突破阈值或是统计信息过期导致执行计划劣化：原本可以走索引避免内存排序的查询，优化器错误选择了全表扫描后内存排序的路径，单查询内存占用成倍上涨。
• 数据库级的work_mem配置可能被更高优先级的配置覆盖：部分会话、自定义函数、用户级配置可能单独设置了更高的work_mem，全局修改不会生效。
• 临时表空间配置未解决底层问题：如果实例此前挂载的临时表空间卷存在IO瓶颈，即使将temp_tablespaces设置为pg_default，排序产生的临时数据刷盘速度跟不上写入速度，也会导致临时数据堆积在内存中无法释放，最终触发溢出。

排查修复建议 #

• 第一时间将work_mem下调到64MB~128MB的合理区间，避免持续OOM：work_mem不足时PostgreSQL会自动将排序数据落盘，仅会降低查询速度不会触发内存溢出，此前盲目上调work_mem是完全反向的错误操作。示例配置语句：

ALTER DATABASE xxxx SET work_mem = '128MB';

• 抓取报错的order by查询的执行计划，对比实际扫描行数和优化器预估值的差异，执行ANALYZE 对应表名更新相关表的统计信息，修复劣化的执行计划。
• 查询pg_stat_activity视图，确认报错瞬间的并发查询数量、并行查询开启情况，适当下调max_parallel_workers_per_gather参数降低并行查询的内存占用。
• 检查实例上是否存在非PostgreSQL的业务进程占用内存，排除其他进程的内存抢占问题。

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