优化PostgreSQL循环更新存储过程：批量更新实现难题求解

核心问题分析 #

你的DO块性能瓶颈在于循环执行10万次单条UPDATE，每次UPDATE都会触发事务日志写入、行锁和磁盘IO，累积开销极大。批量更新时单ID仅能处理一个path，是因为没有将同一ID的所有修改操作聚合为一次UPDATE。

解决方案：按ID聚合修改，单次UPDATE完成所有JSONB操作 #

我们可以将同一个article_id对应的所有需要修改的path聚合，通过reduce函数（PostgreSQL 13+）或自定义聚合函数，一次性完成该ID的所有JSONB修改，从而将10万次UPDATE缩减为仅需执行与唯一article_id数量相等的UPDATE。

方案1：PostgreSQL 13+版本（使用reduce函数）

WITH target_paths AS (
    WITH cte1 AS (
        SELECT a.id, 
               sg.value::jsonb AS study_group, 
               sg.ordinality - 1 AS study_group_num
        FROM t1 a
        CROSS JOIN LATERAL (
            SELECT *
            FROM jsonb_array_elements_text(a.document -> 'studyGroups' -> 'studyGroups')
            WITH ORDINALITY oclass_id(value)
        ) AS sg
    ),
    cte2 AS (
        SELECT osg.id,
               osg.study_group_num,
               pv.value::jsonb ->> 'parameterName' AS parameter_name,
               (pv.value::jsonb ->> 'termNotFound')::text::bool AS term_not_found,
               pv.ordinality - 1 AS measured_parameter_num
        FROM cte1 osg
        CROSS JOIN LATERAL (
            SELECT *
            FROM jsonb_array_elements_text(osg.study_group -> 'measuredParameters' -> 'parameterValues')
            WITH ORDINALITY oclass_id(value)
        ) AS pv
    )
    SELECT mp.id,
           '{studyGroups,studyGroups,' ||
           mp.study_group_num ||
           ',measuredParameters,parameterValues,' ||
           mp.measured_parameter_num || ',termNotFound}'::text[] AS path
    FROM (
        SELECT m.id,
               m.parameter_name,
               m.term_not_found,
               m.study_group_num,
               m.measured_parameter_num
        FROM cte2 m
    ) mp
    LEFT JOIN tab i ON i.name = mp.parameter_name
    WHERE (NOT mp.term_not_found OR mp.term_not_found IS NULL)
      AND i.id IS NULL
),
aggregated_changes AS (
    SELECT id, array_agg(path) AS paths
    FROM target_paths
    GROUP BY id
)
UPDATE t1 a
SET document = reduce(
    ac.paths,
    a.document,
    (doc jsonb, p text[]) -> jsonb_set(doc, p, 'true'::jsonb)
)
FROM aggregated_changes ac
WHERE a.id = ac.id;

方案2：PostgreSQL 12及以下版本（自定义聚合函数）

如果你的PostgreSQL版本低于13，需要先创建自定义聚合函数来累积JSONB修改：

-- 定义单个jsonb_set应用函数
CREATE OR REPLACE FUNCTION apply_jsonb_set(doc jsonb, path text[])
RETURNS jsonb AS $$
BEGIN
    RETURN jsonb_set(doc, path, 'true'::jsonb);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 创建聚合函数，用于累积应用多个jsonb_set
CREATE AGGREGATE jsonb_apply_sets(jsonb, text[]) (
    SFUNC = apply_jsonb_set,
    STYPE = jsonb
);

然后执行批量更新：

WITH target_paths AS (
    -- 同方案1中的target_paths CTE内容
),
aggregated_changes AS (
    SELECT tp.id, jsonb_apply_sets(a.document, tp.path) AS updated_doc
    FROM target_paths tp
    JOIN t1 a ON tp.id = a.id
    GROUP BY tp.id, a.document
)
UPDATE t1 a
SET document = ac.updated_doc
FROM aggregated_changes ac
WHERE a.id = ac.id;

性能优化说明 #

1. 减少UPDATE次数：从10万次单条UPDATE变为仅针对唯一article_id的批量UPDATE，大幅降低事务日志、磁盘IO和锁竞争的开销。
2. 内存中完成多路径修改：通过聚合函数将同一ID的所有修改在内存中完成后一次性写入，避免多次磁盘交互。
3. 可选分批更新：若article_id数量仍很大，可在aggregated_changes中加入LIMIT和OFFSET分批执行，避免一次性锁定过多行。

验证建议 #

执行UPDATE前，可先通过SELECT验证修改结果是否正确：

SELECT a.id, 
       reduce(ac.paths, a.document, (doc jsonb, p text[]) -> jsonb_set(doc, p, 'true'::jsonb)) AS updated_doc
FROM aggregated_changes ac
JOIN t1 a ON a.id = ac.id
LIMIT 10;

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