PostgreSQL JDBC批量插入出现10倍性能波动的原因排查

性能波动来自PostgreSQL外键校验环节的服务端执行计划缓存切换，结合代码中的长事务写法，导致执行计划在两个效率差10倍以上的路径间跳转：

• 代码中setAutoCommit(false)后全程未调用commit()，全部1.6万余条插入运行在同一个长事务内。写入B表时，每条记录的两个外键字段BA、BB都会触发参照完整性触发器，查询A表是否存在对应主键值，校验通过才允许写入。
• PostgreSQL的参照完整性触发器基于SPI（服务端编程接口）执行校验SQL，SPI自带计划缓存逻辑：默认前5次执行会根据传入参数生成适配的自定义计划，之后会周期性评估通用计划（与入参无关的预生成计划）的估算成本，若通用计划成本更低就切换使用，否则回退到自定义计划。
• 高速档位：自定义计划能正确识别外键等值查询的特征，选择A表主键索引做等值匹配，单次校验时间复杂度为O(1)，对应观测到的4~5万行/秒的性能。
• 低速档位：通用计划做成本估算时，不会感知当前长事务内未提交的A表数据分布，错误判定顺序扫描A表的成本低于主键索引扫描，选择全表扫描路径。随着事务内A表写入的行数不断增加，单次顺序扫描的开销线性上涨，最终整体性能比高速档低10倍以上。
• 单外键场景无波动的原因是：该场景下通用计划的成本估算始终判定主键索引扫描更优，不会切换到全表扫描路径，因此性能稳定。

• 缩短事务长度：每执行完若干批次插入后显式调用connection.commit()，控制单事务内的写入行数。即使触发顺序扫描，因为单事务内A表数据量小，扫描开销极低，不会出现明显性能下跌。
• 配置外键延迟校验：将外键校验时机推迟到事务提交时批量执行，避免逐行校验触发的计划切换，同时减少重复校验开销：

ALTER TABLE B ALTER CONSTRAINT FK_BA_A DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE B ALTER CONSTRAINT FK_BB_A DEFERRABLE INITIALLY DEFERRED;

• 强制使用自定义计划：会话级别修改PostgreSQL参数，禁用通用计划，从根源避免计划选错问题：

try (var statement = connection.createStatement()) {
    statement.execute("SET plan_cache_mode = force_custom_plan");
}

配置后性能会稳定在高速档水平。

内容的提问来源于stack exchange，提问作者Marcin Król