PostgreSQL百万行大表复制字段更新无响应,如何判断任务是否正常运行
大表UPDATE任务运行状态确认方法及优化建议
确认任务是否正常运行的方法
- 查看活跃运行进程
新开数据库会话执行以下查询:
如果查询有返回结果,且SELECT pid, query, state, now() - query_start AS run_duration FROM pg_stat_activity WHERE query ILIKE '%UPDATE devicedata SET created_at%' AND state != 'idle';state字段值为active、run_duration持续递增,说明UPDATE任务正在正常运行。如果没有返回结果,或者state为idle in transaction,说明任务已经中断或异常终止。 - 校验数据更新进度
先查询表总预估行数(避免全表COUNT耗时过久):
再定期查询已更新的行数:SELECT reltuples::BIGINT AS total_rows FROM pg_class WHERE relname = 'devicedata';
对比已更新行数和总行数的比例,就能直观看到任务进度。SELECT COUNT(*) AS updated_rows FROM devicedata WHERE created_at IS NOT NULL; - 检查锁持有状态
执行以下查询确认UPDATE任务是否正常持有表锁,将<UPDATE进程pid>替换为上一步查询得到的pid值:
如果返回结果中SELECT locktype, mode, granted FROM pg_locks WHERE relation = 'devicedata'::regclass AND pid = <UPDATE进程pid>;granted为t,且持有RowExclusiveLock锁,说明任务正常持有锁在执行,没有被其他操作阻塞。
后续优化建议
全表UPDATE数百万行数据会长期锁表、占用大量事务日志资源,后续如果需要重新执行可以用分批更新的方式降低影响:
- 每次更新10000条,循环执行直到所有行更新完成:
UPDATE devicedata SET created_at = time_from_device - INTERVAL '1 hour' WHERE id IN (SELECT id FROM devicedata WHERE created_at IS NULL LIMIT 10000); - 所有历史数据更新完成后,再执行语句设置字段默认值:
ALTER TABLE devicedata ALTER COLUMN created_at SET DEFAULT now();
内容的提问来源于stack exchange,提问作者Santhosh
相关产品推荐
相关产品推荐

