PostgreSQL13摄入对象与事件日志的存储过程随时间变慢排查咨询

1. 常见踩坑点及临时表相关影响 #

• 分区表路由开销上涨：如果使用声明式分区且未做路由优化，随着分区数量增多，每次插入/更新扫描分区元数据的耗时会线性上升。如果分区键计算用到非immutable函数、存在隐式类型转换，会导致分区裁剪失效，每次都扫描全部分区。
• 统计信息过时：PostgreSQL默认自动统计信息收集的触发阈值是表数据变动超过10%，高吞吐写入场景下新分区的统计信息无法及时更新，会导致执行计划逐渐劣化，原本走索引的操作变为全表扫描。
• 索引/表膨胀：持续的写入、更新操作如果没有足够的vacuum资源回收死元组，会导致表和索引的碎片化快速升高，IO访问成本指数级上升。
• 长事务/锁堆积：如果存储过程存在未正确提交的长事务，会持续持有锁导致后续操作的锁等待时间不断变长。
• 临时表确实会导致这类性能退化：如果存储过程每次执行创建会话级临时表但不主动清理，会话存续期间临时表会持续占用内存/临时表空间；如果临时表未手动更新统计信息，会导致执行计划选错；使用全局临时表时多线程并发访问的锁冲突也会随并发量上升越来越严重。

2. 排查所需补充信息 #

你提到的执行命令、完整存储过程、包含分区规则和索引定义的表结构都是必备材料，还需要补充以下信息：

• 变慢语句执行EXPLAIN ANALYZE的完整输出
• 分区表当前的分区总数、每个分区的大致数据量
• PostgreSQL核心参数配置：work_mem、maintenance_work_mem、autovacuum相关参数、max_connections、partition_pruning开关状态
• 性能劣化时段慢查询日志中的等待事件统计
• 对应分区表、索引的膨胀率查询结果

3. .NET + Npgsql技术栈常见坑点 #

• 连接池泄露：代码中如果未将NpgsqlConnection放在using块中主动释放，会导致连接池资源被占满，后续请求需要等待连接释放，等待时长随运行时间持续上涨。
• 执行计划未复用：未使用参数化查询，或者未开启Npgsql的自动语句准备功能，每次执行都需要重新解析SQL生成执行计划，高并发下开销会持续累积。
• 连接池配置不合理：Npgsql默认连接池上限为100，如果多线程并发数远高于连接池上限，会出现大量连接等待，耗时随并发量上涨。
• 批量写入效率低：如果未使用Npgsql的Copy接口做批量插入，而是循环单条写入，或者批次大小设置不合理，吞吐量会随数据量上升快速下降。
• Windows平台特有问题：Windows默认TCP参数未调优时，大量短连接会导致TIME_WAIT状态端口占满，新连接无法建立，耗时飙升。

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