Heroku平台Postgres 15.7升级后频繁出现内存不足问题求助

问题背景 #

我们在Heroku上运行Rails应用，使用Postgres 15.7（对应Heroku Postgres standard-2套餐）。升级前使用Postgres 12.x搭配standard-0套餐，当时虽存在大量复杂慢查询，但未出现异常。升级到15.7后，频繁触发内存不足(OOM)错误，具体示例如下：

ActiveRecord::StatementInvalid: PG::OutOfMemory: ERROR:  out of memory
DETAIL:  Failed on request of size 21 in memory context "CachedPlan".

ActiveRecord::StatementInvalid: PG::OutOfMemory: ERROR:  out of memory
DETAIL:  Failed on request of size 292 in memory context "CacheMemoryContext".

ActiveRecord::StatementInvalid: PG::OutOfMemory: ERROR:  out of memory
DETAIL:  Failed on request of size 27336 in memory context "MessageContext".

错误不仅出现在大型查询中，当有其他查询并行运行时，简单查询也常触发OOM。自动备份/快照失败率达40%，拉取数据库到本地也会失败，只有重启Heroku dynos（短暂停机降低负载）才能成功。

套餐升级后内存从4GB提升到8GB，我们尝试过调整work_mem等配置参数（调高、调低均试过，目前已恢复默认），这是Heroku支持团队的建议，但他们无法提供进一步帮助。

我们知道优化查询是根本方案，但短期内没有足够资源推进，希望找到其他临时解决方法。升级前一切正常，说明存在其他可调整的优化空间。

补充背景：我们收集客户反馈并生成图表展示，过往月份数据用物化视图获取并计算过滤，当日数据从数据库实时查询。

当前数据库配置信息 #

pg:settings输出 #

auto-explain:                       false
auto-explain.log-analyze:           false
auto-explain.log-buffers:           false
auto-explain.log-format:            text
auto-explain.log-min-duration:      -1
auto-explain.log-nested-statements: false
auto-explain.log-triggers:          false
auto-explain.log-verbose:           false
log-connections:                    true
log-lock-waits:                     true
log-min-duration-statement:         2000
log-min-error-statement:            error
log-statement:                      ddl
pgbouncer-default-pool-size:        300
pgbouncer-max-client-conn:          10000
pgbouncer-max-db-connections:       300
track-functions:                    none

pg:info输出 #

Plan:                  Standard 2
Status:                Available
Data Size:             13.6 GB / 256 GB (5.32%)
Tables:                60
PG Version:            15.7
Connections:           31/400
Connection Pooling:    Available
Credentials:           1
Fork/Follow:           Available
Rollback:              earliest from 2024-08-23 09:23 UTC
Created:               2024-04-15 13:54 
Region:                eu
Data Encryption:       In Use
Continuous Protection: On
Enhanced Certificates: Off
Upgradable Extensions: Yes
Maintenance:           not required
Maintenance window:    Thursdays 19:00 to 23:00 UTC

下一步排查方向 #

1. 调整计划缓存相关参数 #

Postgres 12到15的计划缓存机制有变化，OOM报错涉及CachedPlan等上下文，建议：

• 将plan_cache_mode设为force_custom_plan，避免复用可能适配旧数据分布的缓存计划（尤其物化视图数据更新后，旧计划易引发内存暴涨）
• 调低max_plans_per_query，限制单查询的缓存计划数量，防止缓存堆积膨胀

2. 优化连接池与并发设置 #

当前连接数虽仅31/400，但pgbouncer配置的default-pool-size=300可能导致瞬间并发过高：

• 降低pgbouncer的default-pool-size到50-100区间，避免同时发起过多查询耗尽内存
• 检查max_worker_processes和max_parallel_workers_per_gather，Postgres 15默认并行度更高，过多并行查询会叠加内存消耗，可适当调低并行度参数

3. 分析内存上下文实时占用 #

使用超级权限执行以下命令，定位内存异常占用的上下文及关联查询：

-- 查看目标内存上下文的占用情况
SELECT * FROM pg_memory_contexts 
WHERE name IN ('CachedPlan', 'CacheMemoryContext', 'MessageContext') 
ORDER BY size DESC;

-- 结合活动查询分析关联关系
SELECT pid, query, state FROM pg_stat_activity WHERE pid IN (
  SELECT pid FROM pg_memory_contexts 
  WHERE name IN ('CachedPlan', 'CacheMemoryContext', 'MessageContext')
);

4. 调整共享内存与内存分配参数 #

Postgres 15对共享内存分配逻辑有调整，虽内存升级到8GB，但参数可能未适配：

• 调整shared_buffers到2.5-3GB（不超过物理内存的1/3），优化缓存效率
• 全局调低work_mem到2MB（每个排序/哈希操作都会分配该内存，并发时叠加消耗巨大），仅给特定复杂查询单独设置SET work_mem = '8MB';
• 临时调低maintenance_work_mem到64MB，降低备份时的内存消耗

5. 优化物化视图刷新机制 #

物化视图刷新可能触发大量内存消耗：

• 避开业务高峰时段刷新物化视图
• 若物化视图有唯一索引，使用REFRESH MATERIALIZED VIEW CONCURRENTLY刷新，减少锁和内存占用
• 拆分大物化视图为多个小视图，分散计算压力

6. 临时缓解措施 #

• 备份操作避开业务高峰，手动触发时先暂停非核心服务降低负载
• 开启auto_explain并设置log_min_duration_statement=1000，记录慢查询执行计划（不要开启log_analyze避免额外内存消耗），后续逐步优化
• 定期清理失效缓存：执行SELECT pg_stat_reset();和SELECT pg_clear_plan_cache();（Postgres 12+支持）

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