如何并行批量更新PostgreSQL海量行数据，避免IO超时？

针对你的场景（3亿条message记录，单核心执行UPDATE超时），以下是最大化加速更新的具体方案：

1. 分批更新（核心优化，避免大事务与IO过载） #

大规模单事务更新会导致锁表、WAL日志爆炸、IO资源耗尽，必须拆分成分批小事务执行：

方式一：PL/pgSQL循环分批 #

创建一个存储过程，按message的主键或user_pgid分段更新，每次处理10万-100万条（根据服务器IO能力调整）：

DO $$
DECLARE
  batch_size INT := 100000;
  done BOOLEAN := FALSE;
BEGIN
  WHILE NOT done LOOP
    UPDATE message m
    SET user_id = u.id
    FROM "user" u
    WHERE u.user_pgid = m.user_pgid
      -- 只更新未处理的记录（假设user_id初始为NULL或特定值）
      AND m.user_id IS NULL
    LIMIT batch_size;
    
    IF FOUND THEN
      COMMIT;
    ELSE
      done := TRUE;
    END IF;
  END LOOP;
END $$;

方式二：Shell脚本循环执行 #

如果不想用PL/pgSQL，也可以用shell脚本配合psql分批：

#!/bin/bash
BATCH_SIZE=100000
while true; do
  psql -d your_db -c "
    UPDATE message m
    SET user_id = u.id
    FROM \"user\" u
    WHERE u.user_pgid = m.user_pgid
      AND m.user_id IS NULL
    LIMIT $BATCH_SIZE;
  "
  if [ $? -ne 0 ]; then
    break
  fi
  # 可选：添加短暂延迟，避免IO打满
  sleep 1
done

2. 确保连接字段有高效索引 #

原语句的连接条件是u.user_pgid = m.user_pgid，必须给这两个字段创建索引，否则会触发全表扫描：

-- 给user表的user_pgid建索引（如果不存在）
CREATE INDEX IF NOT EXISTS idx_user_user_pgid ON "user"(user_pgid);
-- 给message表的user_pgid建索引（如果不存在）
CREATE INDEX IF NOT EXISTS idx_message_user_pgid ON message(user_pgid);

注意：建索引需要时间，建议在业务低峰期执行，或者提前完成。

3. 调整PostgreSQL配置参数，提升硬件利用率 #

根据服务器配置（CPU、内存、IO）调整以下参数（修改postgresql.conf后重启生效，或用SET LOCAL临时生效）：

• 增大work_mem：提升哈希连接、排序的内存阈值，避免磁盘临时文件：

  SET LOCAL work_mem = '64MB'; -- 根据内存情况调整，比如128MB/256MB
  

• 增大maintenance_work_mem：建索引或批量操作时的内存配额：

  SET LOCAL maintenance_work_mem = '2GB'; -- 服务器内存足够时设置
  

• 启用并行查询：让连接阶段利用多核心（PostgreSQL 10+支持）：

  SET LOCAL max_parallel_workers_per_gather = 8; -- 等于CPU核心数的1/2到1/4
  

• 优化WAL写入：减少checkpoint频率，避免IO瓶颈：

  SET LOCAL max_wal_size = '64GB'; -- 增大WAL日志上限
  SET LOCAL wal_buffers = '1GB'; -- 增大WAL缓冲区
  

• 临时关闭autovacuum：大规模更新会产生大量死元组，autovacuum会抢占资源，更新完成后再开启：

  ALTER SYSTEM SET autovacuum = off;
  SELECT pg_reload_conf();
  -- 更新完成后恢复
  ALTER SYSTEM SET autovacuum = on;
  SELECT pg_reload_conf();
  

4. 手动并行更新（利用多核心） #

如果服务器有多个CPU核心，可以手动按user_pgid的范围分片，开启多个会话同时执行更新：
比如将user_pgid分成4个区间，每个会话执行一个区间的更新：
会话1：

UPDATE message m
SET user_id = u.id
FROM "user" u
WHERE u.user_pgid = m.user_pgid
  AND m.user_id IS NULL
  AND m.user_pgid < 500000000000000000;
COMMIT;

会话2：

UPDATE message m
SET user_id = u.id
FROM "user" u
WHERE u.user_pgid = m.user_pgid
  AND m.user_id IS NULL
  AND m.user_pgid >= 500000000000000000 AND m.user_pgid < 1000000000000000000;
COMMIT;

以此类推，注意区间要覆盖所有user_pgid值，且不重叠。

5. 用CREATE TABLE AS SELECT替代UPDATE（最快，但需短暂锁表） #

如果业务允许短暂的只读或停机时间，重建表的方式比UPDATE快得多，因为可以利用并行查询，且不会产生死元组：

-- 1. 创建新表，直接写入关联后的结果
CREATE TABLE message_new AS
SELECT 
  m.id, -- 替换为message表的所有字段
  m.content,
  u.id AS user_id,
  m.user_pgid,
  -- 其他字段...
FROM message m
JOIN "user" u ON u.user_pgid = m.user_pgid;

-- 2. 重建新表的索引、约束、触发器
CREATE INDEX idx_message_new_user_pgid ON message_new(user_pgid);
ALTER TABLE message_new ADD PRIMARY KEY (id); -- 假设id是主键
-- 其他约束/触发器...

-- 3. 替换旧表（需业务低峰期执行）
BEGIN;
ALTER TABLE message RENAME TO message_old;
ALTER TABLE message_new RENAME TO message;
COMMIT;

-- 4. 验证数据无误后删除旧表
DROP TABLE message_old;

这个方法的速度通常是UPDATE的3-5倍，因为PostgreSQL可以并行扫描原表和关联表。

注意事项 #

• 执行前务必备份数据，避免操作失误导致数据丢失。
• 优先在测试环境验证方案，再到生产环境执行。
• 分批更新时，避免设置过大的batch_size，否则会导致单批次IO过载。

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