PostgreSQL批量数据增改优化求助：存在性校验致耗时过长

嘿，这个批量Upsert（插入更新）慢的问题我太熟了！PostgreSQL里单条循环处理肯定卡得飞起，得用原生的批量优化方案，给你几个从快到更快的思路：

这是最直接的优化，PostgreSQL 9.5+原生支持Upsert操作，完全不用自己写复杂的存在性判断逻辑。不过前提是你得先给id和name字段加个唯一约束——毕竟数据库得知道什么情况算“数据已存在”。

首先加约束（如果还没加的话）：

ALTER TABLE users ADD CONSTRAINT unique_id_name UNIQUE (id, name);

然后直接批量插入+更新，一次处理几十上百条甚至更多：

INSERT INTO users (id, name, email, age, updated_at)
VALUES
  (1, 'Alice', 'alice_new@example.com', 31, CURRENT_TIMESTAMP),
  (2, 'Bob', 'bob@example.com', 25, CURRENT_TIMESTAMP),
  -- 这里可以塞几百上千条数据
  (1000, 'Zoe', 'zoe_new@example.com', 29, CURRENT_TIMESTAMP)
ON CONFLICT (id, name)
DO UPDATE SET
  email = EXCLUDED.email,
  age = EXCLUDED.age,
  updated_at = CURRENT_TIMESTAMP;

这里的EXCLUDED是PostgreSQL的特殊关键字，指代那些本来要插入但因为冲突被排除的行数据。这种方式把所有操作放在一个SQL语句里，减少了网络往返和事务开销，比循环单条操作快得多。

如果你的数据量是几十万甚至上百万级，直接用VALUES批量还是有点慢——毕竟解析VALUES列表也有开销。这时候可以用COPY先把数据导入临时表，再和主表合并，效率会再上一个台阶。

步骤如下：

-- 创建临时表，结构和users完全一致（包括约束，也可以只保留必要字段）
CREATE TEMP TABLE temp_users (LIKE users INCLUDING ALL);

-- 用COPY导入数据（这是PostgreSQL最快的批量导入方式，比INSERT快N倍）
-- 如果你是从应用程序导入，可以用客户端的COPY接口（比如Python的psycopg2.copy_from）
COPY temp_users FROM '/path/to/your/big_data.csv' WITH (FORMAT csv, HEADER);

-- 把临时表的数据合并到users表
INSERT INTO users (id, name, email, age, updated_at)
SELECT id, name, email, age, CURRENT_TIMESTAMP FROM temp_users
ON CONFLICT (id, name)
DO UPDATE SET
  email = EXCLUDED.email,
  age = EXCLUDED.age,
  updated_at = CURRENT_TIMESTAMP;

-- 临时表会在会话结束后自动销毁，也可以手动删
DROP TABLE temp_users;

临时表的优势是导入时没有主表的并发压力，而且COPY是直接写磁盘的低开销操作，适合超大规模数据。

• 只更新必要字段：在DO UPDATE里加个判断，比如WHERE users.email != EXCLUDED.email，这样字段没变化就不会执行更新，减少IO操作。
• 大事务分批次：如果数据量特别大（比如千万级），别一次性塞进一个事务，分成每10万条一个批次，避免事务日志爆炸和锁表时间太长。
• 调整数据库配置：临时增大work_mem（让排序合并更快）、max_wal_size（减少 checkpoint 频率），这些配置可以在会话级别临时调整，不用改全局配置：

  SET work_mem = '64MB';
  SET max_wal_size = '4GB';
  

• 关闭自动提交：在客户端连接里关闭自动提交，把所有批量操作放在一个事务里，减少事务提交的开销。

• 不要在循环里单条执行SELECT EXISTS(...)再判断插入或更新——这会产生N次网络请求和N个小事务，慢到离谱。
• 不要用MERGE（PostgreSQL 15+才支持），除非你用的是最新版本，不然ON CONFLICT更成熟稳定。
• 确保(id, name)的唯一索引是高效的，不要建冗余索引，不然会拖慢插入和更新速度。

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