PostgreSQL批量UPDATE语句偶发卡顿问题排查求助

问题描述 #

执行一条UPDATE语句更新12万行数据，有时能正常完成（耗时3-5秒），但经常出现卡顿。测试环境覆盖PostgreSQL 16的Docker镜像、服务器端数据库，执行方式包括查询控制台和Java代码，均表现出时快时慢的现象。

执行的UPDATE语句 #

update
    renamed_account.renamed_account
set
    level=0,
    chain_id=gen_random_uuid()
where
    old_number not in (
        select distinct new_number from renamed_account.renamed_account
    );

表结构与索引 #

CREATE TABLE IF NOT EXISTS renamed_account.renamed_account
(
    ID          bigserial PRIMARY key,
    OLD_NUMBER  varchar(40),
    NEW_NUMBER  varchar(40),
    CHAIN_ID    varchar(36),
    LEVEL       integer
);

CREATE INDEX old_number_idx ON renamed_account.renamed_account (OLD_NUMBER);
CREATE INDEX new_number_idx ON renamed_account.renamed_account (NEW_NUMBER);

卡顿原因分析 #

1. NOT IN子查询的性能波动：子查询中的DISTINCT会额外消耗计算资源，当数据分布变化或缓存失效时，可能触发全表扫描，导致执行时间不稳定；此外，NOT IN对NULL值的处理逻辑可能干扰执行计划选择。
2. 执行计划不稳定：PostgreSQL优化器会根据统计信息选择执行计划，如果表的统计信息过时，可能在索引扫描和全表扫描之间切换，引发性能波动。
3. 锁竞争：批量UPDATE需要获取大量行锁，若存在其他并发读写操作，锁等待会直接导致卡顿。
4. UUID生成阻塞：gen_random_uuid()依赖系统熵池，当熵值不足时，会等待熵源补充，造成随机的性能下降。

解决方案 #

1. 优化子查询逻辑 #

用NOT EXISTS替代NOT IN，避免DISTINCT的额外开销，同时让执行计划更稳定：

update renamed_account.renamed_account t1
set level=0, chain_id=gen_random_uuid()
where not exists (
    select 1 from renamed_account.renamed_account t2
    where t2.new_number = t1.old_number
);

也可以用LEFT JOIN的方式实现相同逻辑：

update renamed_account.renamed_account t1
set level=0, chain_id=gen_random_uuid()
left join renamed_account.renamed_account t2 on t1.old_number = t2.new_number
where t2.new_number is null;

2. 更新表统计信息 #

强制PostgreSQL获取最新的数据分布，帮助优化器选择更优执行计划：

ANALYZE renamed_account.renamed_account;

3. 优化UUID生成 #

如果是熵池不足导致的卡顿，可改用uuid-ossp扩展的uuid_generate_v4()（需先安装扩展）：

CREATE EXTENSION IF NOT EXISTS uuid-ossp;

update renamed_account.renamed_account t1
set level=0, chain_id=uuid_generate_v4()
where not exists (
    select 1 from renamed_account.renamed_account t2
    where t2.new_number = t1.old_number
);

Docker环境下可安装haveged工具补充熵源，避免UUID生成等待。

4. 分批更新 #

将大批次拆分为小批次，减少锁持有时间，降低冲突概率：

WITH batch AS (
    SELECT id FROM renamed_account.renamed_account
    WHERE not exists (
        select 1 from renamed_account.renamed_account t2
        where t2.new_number = renamed_account.old_number
    )
    LIMIT 10000
)
UPDATE renamed_account.renamed_account t1
SET level=0, chain_id=gen_random_uuid()
FROM batch
WHERE t1.id = batch.id;

重复执行该语句，直到没有行被更新。

5. 排查锁竞争 #

卡顿发生时，执行以下语句查看锁情况，确认是否存在并发操作冲突：

SELECT * FROM pg_locks WHERE relation = 'renamed_account.renamed_account'::regclass;

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