PostgreSQL并行运行upsert查询半小时后耗时陡增问题咨询

云上老周

2026-8-28

可能成因

表与索引膨胀：带ON CONFLICT DO UPDATE的Upsert操作会持续产生死元组，运行前半小时数据基数小、死元组占比低，查询效率正常；随着死元组不断堆积，在autovacuum未及时触发清理的情况下，冲突校验、数据更新需要扫描的无效数据量持续上涨，IO开销会陡增。这类问题不会产生锁阻塞，因此在pg_stat_activity中看不到等待中的查询。
WAL写入与Checkpoint尖刺：50个并发连接持续批量写入时，前期WAL写入量小，大部分写请求可以命中内存缓存；运行半小时左右WAL堆积量达到Checkpoint触发阈值，后台开始批量刷脏页到磁盘，磁盘IO被占满后所有写入请求的延迟都会显著升高。
B树索引页分裂：Upsert依赖(id, source)的唯一索引做冲突判断，随着数据量持续增长，B树索引频繁发生页分裂，索引物理存储的连续性被破坏，每次冲突校验需要读取更多随机存储的索引页，延迟逐步抬升。
连接数过载：PostgreSQL的最佳写入并发数通常为服务器CPU核心数的1.5~2倍，50个并行写入连接如果部署在CPU核心数不足16核的数据库实例上，会产生大量上下文切换与WAL写锁竞争，随着运行时间拉长、缓存命中率下降，延迟会持续升高。

优化建议

第一步先定位根因，执行以下SQL做排查：
1. 查表与索引的死元组占比，确认是否存在膨胀：
```
SELECT 
  schemaname, relname, 
  n_dead_tup, n_live_tup, 
  round(n_dead_tup::numeric/(case when n_live_tup=0 then 1 else n_live_tup end)*100,2) as dead_tuple_ratio
FROM pg_stat_user_tables 
WHERE relname = 'table_name';
```
死元组占比超过10%就会明显影响写入性能。
2. 查看Checkpoint统计，确认是否存在刷盘尖刺：
```
SELECT checkpoints_timed, checkpoints_req, checkpoint_write_time, buffers_checkpoint FROM pg_stat_bgwriter;
```
如果checkpoints_req占总Checkpoint比例超过20%、checkpoint_write_time持续走高，就说明Checkpoint配置不合理。
3. 开启track_io_timing参数后，通过pg_stat_statements查看对应Upsert语句的blk_read_time和blk_write_time，如果这两个值占SQL总耗时的90%以上，即可确认瓶颈在IO层。
针对膨胀问题的处理：
- 调低高频写入表的autovacuum触发阈值，默认配置需要死元组达到总行数20%才触发清理，对高频Upsert表来说阈值太高：
```
ALTER TABLE schema_name.table_name SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 500,
  autovacuum_vacuum_cost_delay = 2
);
```
  调整后死元组达到「500 + 总行数*2%」就会触发清理，同时降低vacuum的成本延迟，加快清理速度。
- 如果当前表已经出现严重膨胀，在业务低峰期执行VACUUM FULL schema_name.table_name重建表和索引回收空间，注意该操作会持有排他锁，禁止在业务高峰执行。

针对WAL与Checkpoint的优化：
调整数据库实例的参数配置，拉长Checkpoint周期，避免集中刷盘的IO尖刺：

# 根据磁盘性能调整，SSD/NVMe设备可以设到32GB以上
max_wal_size = 16GB
min_wal_size = 4GB
# 让Checkpoint刷盘动作分散在整个周期内，避免尖刺
checkpoint_completion_target = 0.9
wal_buffers = 16MB
# 开启WAL异步提交，注意极端宕机情况下会丢失最多1s的写入数据，根据业务容忍度开启
# synchronous_commit = off

有条件的话将WAL日志目录挂载到独立的NVMe SSD分区，和数据盘分离，降低WAL写入的IO竞争。

写入逻辑优化：
- 调整并发与批量大小：压测验证最优配置，通常单批次写入500~1000行时Upsert吞吐最高，写入并发数不要超过数据库CPU核心数的2倍，过高的并发只会增加竞争开销，不会提升写入速度。
- 保持当前更新逻辑，不要更新id、source这类属于唯一索引的字段，否则会带来额外的索引维护开销。
- 业务允许的前提下，适当攒批后再提交，减少事务提交触发的WAL刷盘次数。
索引优化：
- 清理目标表上所有未使用的冗余索引，每多一个索引，Upsert时就需要多维护一份索引数据，写入延迟会线性升高。
- 定期在业务低峰期执行REINDEX INDEX CONCURRENTLY 索引名重建碎片化严重的索引，消除页分裂带来的性能损耗。

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