如何高效更新PostgreSQL中100GB+的JSONB数据？

我需要更新PostgreSQL数据库中超过100GB的JSONB数据，数据结构如下：

{
  "attributes": [
    {
      "attribute": "foobar",
      "rules": [
        { "label": "rule1" },
        { "label": "rule2" }
      ]
    },
    {
      "attribute": "foobar2",
      "rules": [
        { "label": "rule1" },
        { "label": "rule2" }
      ]
    }
  ]
}

需求是遍历所有attributes数组元素，移除每个元素下rules数组中label值为rule2的对象。我编写了一段PL/pgSQL脚本处理，但执行速度极慢，求优化方案。

我的原脚本：

do $$
declare
  entry_record RECORD;
  new_jsonb jsonb;
  attributes_arr_length int;
  attribute_object_id int;
  target_rule_id_arr int[];
  rule_id int;

  begin
    for entry_record in (
      select
        mt.id,
        mt.attributes
      from
        my_table mt 
    )
    loop
      new_jsonb := mt.attributes;
      attributes_arr_length = jsonb_array_length(new_jsonb #> ('{attributes}')::text[]);
      continue when attributes_arr_length is null;
      
      for attribute_object_id in 0..(attributes_arr_length -1)
      loop
        select array(select arr.position - 1 from jsonb_array_elements(new_jsonb #> CONCAT('{attributes,', attribute_object_id, ',rules}')::text[])
        with ordinality arr(item_object, position)
        where item_object ->> 'label' = 'rule2'
        order by arr.position desc) into target_rule_id_arr;

        foreach rule_id in array target_rule_id_arr
        loop
          new_jsonb := jsonb_set(new_jsonb, CONCAT('{attributes,', attribute_object_id, ',rules}')::text[],
                                 (new_jsonb #> CONCAT('{attributes,', attribute_object_id, ',rules}')::text[]) - rule_id);
        end loop;

        update my_table
        set attributes = new_jsonb
        where id = entry_record.id;
      end loop;
    end loop;
  end$$;

优化方案 #

核心问题分析 #

原脚本的性能瓶颈在于多层逐行循环+频繁小更新：对每条记录、每个attribute、每个rule都做循环操作，且在attribute循环内就执行UPDATE，导致大量磁盘IO和事务开销，完全不适合100GB级别的数据处理。

优化后的脚本 #

改用SQL原生JSONB函数批量处理，减少循环和IO操作：

WITH updated_records AS (
    SELECT
        mt.id,
        jsonb_build_object(
            'attributes',
            jsonb_agg(
                jsonb_set(
                    attr.item,
                    '{rules}',
                    jsonb_agg(rule.item) FILTER (WHERE rule.item ->> 'label' != 'rule2')
                )
            )
        ) AS new_attributes
    FROM my_table mt
    CROSS JOIN jsonb_array_elements(mt.attributes -> 'attributes') AS attr(item)
    CROSS JOIN jsonb_array_elements(attr.item -> 'rules') AS rule(item)
    GROUP BY mt.id
)
UPDATE my_table mt
SET attributes = ur.new_attributes
FROM updated_records ur
WHERE mt.id = ur.id;

优化逻辑说明 #

1. 批量处理生成新结构：用CTE一次性处理所有记录，通过jsonb_array_elements展开attributes和rules数组，用FILTER直接过滤掉label='rule2'的规则，再通过jsonb_agg重新聚合数组，避免手动操作下标。
2. 单次批量更新：最后仅执行一次UPDATE操作，将处理后的JSONB批量写入，大幅减少磁盘IO次数。

额外优化建议 #

• 数据备份：操作前务必备份表数据，避免意外丢失。
• 分批处理：针对100GB级数据，建议按id范围分批执行，避免单次操作占用过多资源：

  WITH updated_records AS (
      SELECT
          mt.id,
          jsonb_build_object(
              'attributes',
              jsonb_agg(
                  jsonb_set(
                      attr.item,
                      '{rules}',
                      jsonb_agg(rule.item) FILTER (WHERE rule.item ->> 'label' != 'rule2')
                  )
              )
          ) AS new_attributes
      FROM my_table mt
      CROSS JOIN jsonb_array_elements(mt.attributes -> 'attributes') AS attr(item)
      CROSS JOIN jsonb_array_elements(attr.item -> 'rules') AS rule(item)
      WHERE mt.id > 上一批最大id -- 用id范围替代LIMIT/OFFSET更高效
      LIMIT 1000
      GROUP BY mt.id
  )
  UPDATE my_table mt
  SET attributes = ur.new_attributes
  FROM updated_records ur
  WHERE mt.id = ur.id;
  

• 索引利用：确保id字段为主键或有索引，让查询和更新能快速定位记录；若有其他过滤条件，可创建对应索引缩小扫描范围。

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