PostgreSQL 13含jsonb字段的表执行查询时出现内存溢出问题

问题场景 #

PostgreSQL 13版本环境中，对包含jsonb字段的表执行查询时触发内存不足问题，系统OOM killer直接终止PostgreSQL主进程，导致所有连接被强制断开，报错信息如下：

警告：因其他服务器进程崩溃，正在终止当前连接；详情：postmaster已命令当前进程回滚事务并退出，其他进程异常退出可能损坏共享内存；提示：稍候可重连数据库并重试命令；服务器意外关闭连接，处理请求时异常终止，连接丢失重置失败。

问题根因 #

• 查询逻辑冗余严重：对dummy表执行了两次全表扫描，每次扫描都全量展开嵌套的jsonb数组，生成的临时结果集再基于claseq、linseq做等值关联，当表数据量较大、jsonb数组元素较多时，临时结果集会膨胀到数倍原表大小，占满物理内存触发OOM。
• 重复计算浪费资源：两次子查询的jsonb数组展开逻辑几乎完全一致，额外占用大量CPU和内存资源。

优化方案 #

1. 重构查询逻辑（核心优化） #

删除冗余的二次扫描和关联逻辑，只需扫描一次dummy表即可完成所有字段提取，大幅降低内存占用：

SELECT 
    (t.data -> 'claimno'::text) AS claimno,
    (t.data ->> 'claseq'::text) AS claseq,
    (t.response ->> 'clientcode'::text) AS clientname,
    to_timestamp((((t.response ->> 'timestamp'::text))::numeric)::double precision) AS batchdate,
    (o.value ->> 'linseq'::text) AS linseq,
    (o.value ->> 'deny_proc_code'::text) AS deny_proc_code,
    (o.value ->> 'allow_proc_code'::text) AS allow_proc_code,
    (k.value ->> 'action'::text) AS predictions,
    ((k.value ->> 'score'::text))::numeric AS score,
    (k.value ->> 'deleted'::text) AS deleted,
    (k.value ->> 'review'::text) AS review
FROM dummy t,
LATERAL jsonb_array_elements((t.response -> 'lines'::text)) o(value),
LATERAL jsonb_array_elements(
    CASE
        WHEN jsonb_typeof(o.value -> 'flags'::text) = 'array' THEN o.value -> 'flags'::text
        ELSE '{"key": "team_q:"}'::jsonb
    END
) k(value);

2. 数据库参数调优 #

• 限制单查询内存上限：调整work_mem参数，避免单查询无限制占用内存，可先在会话级别测试效果：

  SET work_mem = '64MB';
  

  确认稳定后可修改postgresql.conf配置文件永久生效。
• 调整共享缓冲区：将shared_buffers设置为物理内存的25%~40%，提升缓存命中率减少内存颠簸。

3. 存储层优化 #

• 对高频查询的jsonb提取字段创建生成列和B树索引，避免重复解析jsonb：

  ALTER TABLE dummy ADD COLUMN claseq text GENERATED ALWAYS AS (data ->> 'claseq') STORED;
  CREATE INDEX idx_dummy_claseq ON dummy(claseq);
  

• 对包含数组的jsonb字段创建GIN索引，加快数组展开和查询效率。

4. 系统层面优化 #

• 调整PostgreSQL进程的OOM评分，降低被系统优先杀死的概率：

  echo -1000 > /proc/$(pidof postmaster)/oom_score_adj
  

• 配置合理大小的交换分区，避免物理内存耗尽时直接终止核心进程。

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