如何不使用jsonb_set修改PostgreSQL的jsonb变量？

在PL/pgSQL循环中更新JSONB变量时，每次更新行都会执行带jsonb_set的SQL操作。由于同一行可能被更新数千次，且JSONB字段数据量可达260KB，当前存储过程批量更新数千条记录耗时20分钟，极端场景甚至4小时，推测jsonb_set是性能瓶颈。改用PLV8后耗时缩短至数分钟，但希望仅用PL/pgSQL解决问题。

原PL/pgSQL存储过程代码：

CREATE OR REPLACE PROCEDURE "public"."bulk_price_import_cat_mat"()
 AS $BODY$
DECLARE
    rec RECORD;
    cpt INT := 0;
BEGIN
          FOR rec IN
                    select * from feuil1,
                (
                        SELECT 
                                id,
                                key AS m,
                                obj_ordinality - 1 AS rank,
                                (obj->>'plu')::VARCHAR AS plu,
                                (obj->>'price')::numeric AS price
                        FROM 
                                (select id, price from shop_config where config_target = 'product' and price_type = 'category_matrix' and shop_id in (SELECT id FROM shop WHERE client_id = 'e44c0df8-c5c1-4c84-92de-792a3e7ffd40')) sc,
                                jsonb_each(sc.price) AS kv,
                                jsonb_array_elements(kv.value) WITH ORDINALITY AS obj(obj, obj_ordinality)
                            
                                
                )    mat
                    
                where feuil1.plu = mat.plu and feuil1.computed_plu = false
        LOOP
            IF MOD(cpt, 10000) = 0 THEN
                COMMIT; -- Commit every 10000 iterations
                RAISE NOTICE 'shop_config matrix price::commit %', cpt;
            END IF;
                        
                        update shop_config  
                        set price = jsonb_set(
                                                price::jsonb, 
                                                concat('{', rec.m, ',' , rec.rank, ', price}')::text[],
                                                 rec.prix::jsonb)
                                                 where id = rec.id;
                        cpt := cpt + 1;
        END LOOP;
    RAISE NOTICE 'Update into shop_config done for all plu %', cpt;
END;
$BODY$
  LANGUAGE plpgsql

核心思路是减少单条记录的更新次数，将同一行的多次jsonb_set合并为一次操作，避免重复读取和写入整个JSONB字段。

1. 按shop_config.id分组聚合更新操作 #

先把所有需要更新的(m, rank, price)按id分组，一次性构建完整的更新后的JSONB对象，再执行单次更新。

2. 内存中批量修改JSONB对象 #

在PL/pgSQL中，先将同一id的所有更新项存入数组，然后在内存中对JSONB对象进行多次修改，最后一次性写入数据库，避免多次IO开销。

优化后的存储过程代码 #

CREATE OR REPLACE PROCEDURE "public"."bulk_price_import_cat_mat_optimized"()
 AS $BODY$
DECLARE
    id_batch RECORD;
    update_item JSONB;
    new_price JSONB;
BEGIN
    -- 按id分组，收集每个id对应的所有更新项
    FOR id_batch IN
        SELECT 
            mat.id,
            json_agg(json_build_object('m', mat.m, 'rank', mat.rank, 'prix', feuil1.prix)) AS updates
        FROM feuil1
        JOIN (
            SELECT 
                id,
                key AS m,
                obj_ordinality - 1 AS rank,
                (obj->>'plu')::VARCHAR AS plu,
                (obj->>'price')::numeric AS price
            FROM 
                (select id, price from shop_config where config_target = 'product' and price_type = 'category_matrix' and shop_id in (SELECT id FROM shop WHERE client_id = 'e44c0df8-c5c1-4c84-92de-792a3e7ffd40')) sc,
                jsonb_each(sc.price) AS kv,
                jsonb_array_elements(kv.value) WITH ORDINALITY AS obj(obj, obj_ordinality)
        ) mat ON feuil1.plu = mat.plu 
        WHERE feuil1.computed_plu = false
        GROUP BY mat.id
    LOOP
        -- 获取原JSONB字段到内存
        SELECT price INTO new_price FROM shop_config WHERE id = id_batch.id;
        
        -- 批量应用所有更新到内存中的JSONB对象
        FOREACH update_item IN ARRAY id_batch.updates::JSONB[]
        LOOP
            new_price := jsonb_set(
                new_price,
                concat('{', (update_item->>'m'), ',', (update_item->>'rank'), ', price}')::text[],
                (update_item->>'prix')::JSONB
            );
        END LOOP;
        
        -- 执行单次更新操作
        UPDATE shop_config 
        SET price = new_price 
        WHERE id = id_batch.id;
        
        -- 每处理100个id提交一次事务
        IF MOD(id_batch.id::INT, 100) = 0 THEN
            COMMIT;
            RAISE NOTICE 'Committed updates for id %', id_batch.id;
        END IF;
    END LOOP;
    
    COMMIT;
    RAISE NOTICE 'All updates completed';
END;
$BODY$
LANGUAGE plpgsql;

额外优化建议 #

• 调整提交批次：根据实际数据量调整提交间隔，避免过于频繁的提交或过大的事务
• 优化索引：确保shop_config.id为主键，feuil1.plu和mat.plu添加索引，提升JOIN和查询速度
• 减少类型转换：如果shop_config.price本身就是JSONB类型，去掉price::jsonb转换，减少不必要的开销
• 尝试jsonb_path_set（PostgreSQL 12+）：若使用PostgreSQL 12及以上版本，可尝试用jsonb_path_set批量处理路径更新，但实际效果可能不如内存循环修改

内容的提问来源于stack exchange，提问作者Ben Rhouma Zied