PostgreSQL UPSERT性能问题：2400万数据插入更新耗时9-10小时

核心慢查询原因 #

1. 目标表体量过大：5.6亿行的目标表，ON CONFLICT冲突检查需频繁查询唯一索引，大索引的磁盘IO开销极高，每一行的冲突验证都要遍历巨量索引数据。
2. 全量更新触发大量索引维护：每次UPDATE会更新目标表的所有索引、外键约束，22列+额外字段的全量更新会产生海量索引写入操作，拖慢整体速度。
3. 源表过滤无有效索引：如果source_tbl的processed字段没有索引，WHERE processed=false会触发全表扫描，拖慢数据读取阶段。
4. 大事务与锁开销：一次性处理2400万条的大事务会占用大量数据库资源，且READ COMMITTED隔离级别下的行锁竞争会进一步降低效率。

针对性优化措施 #

1. 拆分小批量操作 #

避免一次性处理全量数据，将2400万条拆分为每次1万-10万条的小批次，减少大事务的资源占用与锁竞争。示例：

-- 循环分批处理，每次取10万条
WITH batch_data AS (
  SELECT col1, col2, ..., col22
  FROM source_tbl
  WHERE processed=false
  LIMIT 100000
)
INSERT INTO destination_tbl(col1, col2, ..., col22, processed, updated_at)
SELECT col1, col2, ..., col22, false, null
FROM batch_data
ON CONFLICT (unique_cols...)
DO UPDATE SET
  col1 = EXCLUDED.col1,
  ...,
  col22 = EXCLUDED.col22,
  processed = false,
  updated_at = now();

-- 重复执行直到source_tbl中processed=false的数据处理完成

2. 临时禁用非必要索引与约束 #

UPSERT期间仅保留ON CONFLICT所需的唯一索引，禁用其他非唯一索引、外键约束和触发器，减少索引维护开销。操作完成后务必恢复：

-- 禁用非唯一索引
ALTER INDEX idx_dest_non_unique1 DISABLE;
ALTER INDEX idx_dest_non_unique2 DISABLE;

-- 禁用外键触发器（如果有）
ALTER TABLE destination_tbl DISABLE TRIGGER ALL;

-- 执行UPSERT操作...

-- 恢复索引与触发器
ALTER INDEX idx_dest_non_unique1 ENABLE;
ALTER INDEX idx_dest_non_unique2 ENABLE;
ALTER TABLE destination_tbl ENABLE TRIGGER ALL;

3. 优化源表查询性能 #

给source_tbl的processed字段创建索引，加速过滤条件的查询：

CREATE INDEX idx_source_processed ON source_tbl(processed);

4. 调整数据库参数 #

临时调整内存与WAL相关参数，提升处理效率（需根据服务器配置调整，操作前备份配置）：

-- 提高维护内存，用于索引创建/维护
SET maintenance_work_mem = '1GB';
-- 提高工作内存，减少排序/哈希的磁盘IO
SET work_mem = '64MB';
-- 开启WAL压缩，减少日志写入量
SET wal_compression = on;

5. 拆分INSERT与UPDATE操作 #

将UPSERT拆分为“批量插入新数据”和“批量更新已有数据”两个独立操作，避免冲突检查的额外开销：

-- 第一步：插入目标表中不存在的行
INSERT INTO destination_tbl(col1, col2, ..., col22, processed, updated_at)
SELECT s.col1, s.col2, ..., s.col22, false, null
FROM source_tbl s
WHERE s.processed=false
AND NOT EXISTS (
  SELECT 1 FROM destination_tbl d 
  WHERE d.unique_cols1 = s.unique_cols1 
    AND d.unique_cols2 = s.unique_cols2
);

-- 第二步：更新目标表中已存在的行
UPDATE destination_tbl d
SET 
  col1 = s.col1,
  ...,
  col22 = s.col22,
  processed = false,
  updated_at = now()
FROM source_tbl s
WHERE d.unique_cols1 = s.unique_cols1 
  AND d.unique_cols2 = s.unique_cols2
  AND s.processed=false;

6. 更新目标表统计信息 #

确保PostgreSQL能生成最优执行计划，更新目标表的统计信息：

ANALYZE destination_tbl;

验证建议 #

• 先用1万条左右的小批量数据测试优化方案，对比耗时差异。
• 用EXPLAIN ANALYZE分析原查询的执行计划，定位是否存在全表扫描、索引扫描等瓶颈。

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