如何优化PostgreSQL中千万级数据的INSERT与UPDATE操作

我有accounts和inf_accounts两张表，每天需要把inf_accounts里的数百万行数据同步到accounts。

当前同步流程 #

1. 插入accounts中不存在的记录：

INSERT INTO accounts SELECT * FROM inf_accounts ON CONFLICT DO NOTHING;

2. 更新account_status字段不一致的记录：

UPDATE accounts acc SET account_status=inf.account_status FROM inf_accounts inf WHERE acc.account_number=inf.account_number AND acc.account_status<>inf.account_status;

3. 清空inf_accounts：

DELETE FROM inf_accounts;

优化思路及遇到的问题 #

我想把流程改成：先执行UPDATE，删除inf_accounts中已经完成更新的记录，再插入剩余的新记录，避免原流程中INSERT对全量数据做判断。但自己写的DELETE语句性能极差，以下是两条DELETE语句的执行计划：

DELETE语句1执行计划 #

EXPLAIN DELETE FROM inf_accounts i USING accounts a WHERE a.account_number = i.account_number;

Delete on inf_accounts i  (cost=427341.04..950096.91 rows=0 width=0)
->  Hash Join  (cost=427341.04..950096.91 rows=9885646 width=12)
    Hash Cond: (a.account_number = i.account_number)
    ->  Seq Scan on accounts a  (cost=0.00..320884.95 rows=10035395 width=23)
    ->  Hash  (cost=245846.46..245846.46 rows=9885646 width=23)
          ->  Seq Scan on inf_accounts i  (cost=0.00..245846.46 rows=9885646 width=23)
JIT:
  Functions: 10
"  Options: Inlining true, Optimization true, Expressions true, Deforming true"

DELETE语句2执行计划 #

EXPLAIN DELETE FROM inf_accounts i WHERE i.account_number IN (SELECT i.account_number FROM inf_accounts i LEFT JOIN accounts a on a.account_number = i.account_number);

Delete on inf_accounts i  (cost=1141245.48..1817659.09 rows=0 width=0)
->  Hash Semi Join  (cost=1141245.48..1817659.09 rows=9885646 width=18)
    Hash Cond: (i.account_number = i_1.account_number)
    ->  Seq Scan on inf_accounts i  (cost=0.00..245846.46 rows=9885646 width=23)
    ->  Hash  (cost=950096.91..950096.91 rows=9885646 width=29)
          ->  Hash Right Join  (cost=427341.04..950096.91 rows=9885646 width=29)
                Hash Cond: (a.account_number = i_1.account_number)
                ->  Seq Scan on accounts a  (cost=0.00..320884.95 rows=10035395 width=23)
                ->  Hash  (cost=245846.46..245846.46 rows=9885646 width=23)
                      ->  Seq Scan on inf_accounts i_1  (cost=0.00..245846.46 rows=9885646 width=23)

请问有没有更优的实现方案，还是只能沿用原来先INSERT的流程？

解决方案 #

核心优化点：索引+联动操作+避免全表扫描 #

1. 添加必要索引
   从执行计划能看到两张表都在做全表扫描（Seq Scan），这是性能差的核心原因。给关联字段加BTREE索引：

   -- 给accounts的account_number加*唯一索引*（ON CONFLICT语法依赖唯一约束/索引）
   CREATE UNIQUE INDEX idx_accounts_account_number ON accounts(account_number);
   -- 给inf_accounts的account_number加普通索引
   CREATE INDEX idx_inf_accounts_account_number ON inf_accounts(account_number);
   

   索引建好后，后续的JOIN、WHERE操作都会走索引扫描，大幅降低IO开销。

2. 用CTE联动UPDATE和DELETE
   不要单独执行DELETE，而是在UPDATE时直接捕获已更新的记录ID，一次性完成更新+删除操作，避免重复关联大表：

   -- 用CTE捕获已更新的account_number，再删除inf_accounts中对应的记录
   WITH updated_accounts AS (
       UPDATE accounts acc
       SET account_status = inf.account_status
       FROM inf_accounts inf
       WHERE acc.account_number = inf.account_number 
         AND acc.account_status <> inf.account_status
       RETURNING acc.account_number
   )
   DELETE FROM inf_accounts i
   WHERE i.account_number IN (SELECT account_number FROM updated_accounts);
   

   这个方式只删除确实发生了更新的记录，而非所有在accounts中存在的记录，减少了DELETE的行数，同时避免重复扫描表。

3. 插入剩余新记录
   执行完上面的操作后，inf_accounts中剩下的都是accounts里没有的新记录，直接插入即可，无需冲突判断：

   INSERT INTO accounts SELECT * FROM inf_accounts;
   

4. 可选：分批次处理超大量数据
   如果数据量达到千万级以上，可拆成批次执行，避免单条语句占用过多数据库资源：

   -- 每次处理10000条，直到没有更新记录
   WHILE EXISTS (
       SELECT 1 FROM accounts acc
       JOIN inf_accounts inf ON acc.account_number = inf.account_number
       WHERE acc.account_status <> inf.account_status
   ) LOOP
       WITH updated_batch AS (
           UPDATE accounts acc
           SET account_status = inf.account_status
           FROM inf_accounts inf
           WHERE acc.account_number = inf.account_number 
             AND acc.account_status <> inf.account_status
           LIMIT 10000
           RETURNING acc.account_number
       )
       DELETE FROM inf_accounts i
       WHERE i.account_number IN (SELECT account_number FROM updated_batch);
   END LOOP;
   

对比原流程的优势 #

• 原流程的INSERT要对全量inf_accounts做冲突判断，优化后只插入新数据，节省了冲突检查的开销
• CTE联动操作避免了重复关联两张大表，减少了IO次数
• 索引从根本上解决了全表扫描的性能瓶颈

何时适合沿用原流程？ #

如果inf_accounts中每天的新数据占比极高（比如90%以上都是新记录），原流程的INSERT+UPDATE可能反而更简单——此时优化后的DELETE操作节省的开销，不如原流程简洁性带来的维护成本低。

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