如何加速PostgreSQL中数百万条JSONB字段的批量更新？

核心优化思路：从逐条更新转为批量操作 #

逐条执行UPDATE的开销极大（每条SQL都要经历网络传输、SQL解析、事务处理等环节），数百万条记录必然耗时很久，核心优化方向是把所有操作放在数据库端批量完成。

步骤1：将CSV中的用户ID导入临时表 #

先创建临时表存储需要更新的用户ID，再用PostgreSQL的COPY命令快速导入CSV数据（比Python逐条插入快几个数量级）：

-- 创建临时表（会话结束后自动销毁，无需手动清理）
CREATE TEMP TABLE target_user_ids (id INT PRIMARY KEY);

-- 直接从CSV导入数据（假设CSV仅含id列，路径为'/path/to/your/user_ids.csv'）
COPY target_user_ids(id) FROM '/path/to/your/user_ids.csv' WITH (FORMAT csv, HEADER);

如果用Python执行，推荐用psycopg2的copy_from方法避免文件权限问题：

import psycopg2

conn = psycopg2.connect("your_connection_string")
cur = conn.cursor()

with open('/path/to/your/user_ids.csv', 'r') as f:
    # 跳过CSV表头（如果有表头的话）
    next(f)
    cur.copy_from(f, 'target_user_ids', columns=('id',))

conn.commit()
cur.close()
conn.close()

步骤2：批量更新JSONB字段 #

用单条UPDATE语句关联临时表，一次性更新所有匹配的用户记录。这里可以用JSONB的||操作符简化更新逻辑（效果和嵌套jsonb_set完全一致：存在的键直接覆盖，不存在的键自动添加），比嵌套jsonb_set更简洁高效：

UPDATE "User" u
SET preferences = u.preferences || '{"setting1": true, "setting2": true}'::jsonb
FROM target_user_ids t
WHERE u.id = t.id;

如果必须使用jsonb_set（比如需要处理更复杂的嵌套路径），可以写成：

UPDATE "User" u
SET preferences = jsonb_set(
    jsonb_set(u.preferences, '{setting1}', 'true'::jsonb, true),
    '{setting2}', 'true'::jsonb, true
)
FROM target_user_ids t
WHERE u.id = t.id;

步骤3：分批次处理（可选，针对超大规模数据） #

如果数百万条记录一次性更新导致事务过大（WAL日志暴涨、锁表时间过长），可以分批次更新，每次处理1-10万条：

-- 循环执行以下语句，每次OFFSET增加10000，直到返回0 rows updated
WITH batch AS (
    SELECT id FROM target_user_ids ORDER BY id LIMIT 10000 OFFSET 0
)
UPDATE "User" u
SET preferences = u.preferences || '{"setting1": true, "setting2": true}'::jsonb
FROM batch b
WHERE u.id = b.id;

可以用Python循环自动处理批次，直到更新行数为0即可停止。

额外性能调优 #

• 调整数据库参数：临时调高以下参数（更新完成后可恢复默认值）
  • work_mem：增大内存分配，提升JOIN和排序性能（比如设为64MB）
  • checkpoint_timeout：延长检查点间隔，减少磁盘写入频率（比如设为30min）
  • wal_buffers：增大WAL缓冲区，减少WAL刷盘次数（比如设为16MB）
• 确保索引高效："User"表的id字段必须是主键或有唯一索引（通常默认已满足），临时表target_user_ids的id设主键也能大幅加速JOIN操作。

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