PostgreSQL批量插入为何在200-300次迭代后性能骤降？

操作系统层面排查 #

• 磁盘IO瓶颈验证：执行iostat -x 1实时观测磁盘指标，重点关注%util（磁盘使用率）、await（IO请求平均等待时间）、svctm（IO服务时间）。若%util长期高于80%或await突然飙升，说明磁盘IO过载——哪怕unlogged表数据量不大，脏页刷写、WAL日志落盘也会触发阻塞。
• 内存资源检查：用free -m或vmstat查看内存与swap使用情况，若si（swap读）、so（swap写）列持续非0，说明PostgreSQL内存不足，频繁换页会直接拖慢性能。
• 系统负载排查：通过top/htop确认CPU使用率是否异常，是否有其他进程（如备份、定时任务）抢占CPU或IO资源。

PostgreSQL内部状态排查 #

• 后台进程与等待事件：执行SELECT * FROM pg_stat_activity，重点看wait_event_type和wait_event列：
  • 若出现IO类等待（如WalWrite、DataFileWrite），说明IO操作阻塞了插入；
  • 若出现Lock类等待，需排查是否存在未释放的表锁/行锁。
• WAL日志状态检查：
  • 执行SELECT pg_current_wal_lsn(), pg_wal_lsn_diff(pg_current_wal_lsn(), pg_last_wal_replay_lsn())，若差值持续增大，说明WAL写入或归档速度跟不上，导致插入等待；
  • 查询pg_stat_writer视图，若buffers_backend占比过高，说明后台刷脏页进程跟不上，前端插入请求被迫等待脏页刷盘。
• 连接与会话统计：执行SELECT count(*) FROM pg_stat_activity WHERE state = 'active'，确认活跃连接数是否接近max_connections上限，导致连接排队；同时检查是否有大量闲置会话占用资源。
• 表与索引状态：查询pg_stat_user_tables，查看n_dead_tup（死元组数量）——即使是unlogged表，若存在隐性的更新/删除操作（比如触发器），死元组过多会触发频繁自动vacuum，拖慢插入；另外确认unlogged表是否有索引，索引维护的开销也可能随时间积累。

应用与JDBC层面排查 #

• 连接池状态验证：检查Spring使用的连接池（如HikariCP）指标，查看activeConnections、idleConnections是否持续增长到最大值，若存在连接泄漏，会导致新插入请求等待可用连接。
• CopyManager使用检查：确认是否每次批量插入都重复创建CopyManager实例，是否正确关闭CopyIn流——资源未正确释放会导致连接/IO资源泄漏，积累到一定程度后引发性能下降。
• 批量数据一致性验证：排查前后批次的数据量是否一致，是否存在某几批数据突然变大的情况，单批数据量激增会直接导致耗时上升。

其他排查点 #

• PostgreSQL日志分析：开启log_min_duration_statement = 100（记录耗时超100ms的语句），查看高耗时插入语句的日志，是否伴随checkpoint触发、WAL归档延迟等提示信息。
• 文件系统优化：用mount命令检查磁盘是否开启atime，开启atime会导致每次文件访问都写入元数据，添加noatime挂载参数可减少不必要的IO。
• 定时任务排查：确认性能下降的时间点是否有定时任务触发（如自动备份、vacuum full、统计信息收集），这类任务会抢占系统资源，导致插入性能骤降。

内容的提问来源于stack exchange，提问作者Виктор