如何高效更新PostgreSQL中的1000万条记录？

1. 修复第一种语句的报错问题，保留高性能 #

第一种语句报错的核心原因是：当accounts中存在inf_accounts无匹配的行时，子查询返回NULL，若account_status字段带有NOT NULL约束就会触发错误。只需添加匹配判断，就能在保留原语句性能的同时解决报错：

UPDATE accounts acc
SET account_status = (SELECT account_status FROM inf_accounts inf WHERE acc.account_number = inf.account_number)
WHERE EXISTS (SELECT 1 FROM inf_accounts inf WHERE acc.account_number = inf.account_number);

该语句仅更新inf_accounts中有对应记录的行，避免了NULL赋值导致的约束冲突，同时继承原语句的高效执行逻辑。

2. 优化第二种JOIN方式的性能 #

第二种语句性能极差的大概率原因是缺少合适的索引。确保两张表的account_number字段都建立索引：

-- 给accounts表的account_number加索引
CREATE 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);

若account_number是主键则无需额外创建（主键本身即为索引）。索引会大幅提升JOIN阶段的匹配速度，显著缩短更新耗时。

3. 分批更新（超大规模数据场景） #

若单次更新千万级数据会导致锁表时间过长、影响业务，可采用分批更新的方式，每次处理部分数据：

-- 每次更新10万条，循环执行直到更新行数为0
WITH batch AS (
    SELECT acc.account_number
    FROM accounts acc
    JOIN inf_accounts inf ON acc.account_number = inf.account_number
    LIMIT 100000
)
UPDATE accounts acc
SET account_status = (SELECT account_status FROM inf_accounts inf WHERE acc.account_number = inf.account_number)
WHERE acc.account_number IN (SELECT account_number FROM batch);

这种方式能缩小单次锁表范围，降低对业务的影响，同时避免一次性更新带来的内存、IO压力。

4. 替换更新（全量更新极端场景） #

若每日需更新的是全量数据，或大部分数据都需要修改，可采用新建表再替换的方式，速度远快于逐行更新：

-- 创建临时表，合并两张表的最新数据
CREATE TABLE accounts_new AS
SELECT 
    acc.*,
    COALESCE(inf.account_status, acc.account_status) AS account_status
FROM accounts acc
LEFT JOIN inf_accounts inf ON acc.account_number = inf.account_number;

-- 替换原表（操作前务必备份原表）
DROP TABLE accounts;
ALTER TABLE accounts_new RENAME TO accounts;

-- 重建索引与约束
CREATE INDEX idx_accounts_account_number ON accounts(account_number);
-- 按需重建主键、NOT NULL等约束

该方案需在业务低峰期执行，且必须做好数据备份，避免意外数据丢失。

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