PostgreSQL搭配Npgsql运行性能持续下降，仅重启客户端可恢复，如何排查？

按照影响优先级排序如下：

• 最高优先级：Npgsql 4.0.x版本连接重置逻辑未清理PostgreSQL后端进程的关系缓存（relcache）
  PostgreSQL每个连接对应的后端进程会独立维护元数据缓存，包括表结构、约束、索引等关系缓存和系统表缓存。你的每次测试都会删除并重建20张表，同一个连接复用次数越多，缓存中残留的无效旧表元数据条目就越多，后续执行DDL、DML操作时元数据查找的耗时会线性增长。
  而Npgsql 4.0.x版本的默认连接重置逻辑仅执行RESET ALL恢复会话参数，不会执行DISCARD ALL清理缓存，导致缓存持续膨胀。重启客户端进程会销毁所有旧连接对应的后端进程，新连接的缓存为空，速度自然恢复。
• 次优先级：单元测试schema重置逻辑的性能损耗放大
  你当前每次测试都删表重建的逻辑本身开销就很高，叠加缓存膨胀的问题后，耗时会被进一步放大。
• 低优先级：PostgreSQL 13.3 Windows版本的后端进程内存回收bug
  PostgreSQL 13.x的Windows版本存在少量已知的后端进程内存上下文回收不及时的问题，频繁的DDL操作会加剧这个问题。

• 执行测试15分钟待速度明显变慢后，找到活跃PostgreSQL后端进程的PID，执行以下SQL查看内存上下文占用：

SELECT name, sum(used_bytes) / 1024 as used_kb 
FROM pg_backend_memory_contexts 
WHERE pid = <目标进程PID> AND name IN ('Relcache', 'Syscache')
GROUP BY name;

如果两个缓存的总占用超过50MB，即可确认是缓存膨胀问题。

• 手动在复用了多次的连接上执行DISCARD ALL，再执行测试，如果耗时直接恢复到正常水平，即可100%确认根因。

按优先级从高到低：

1. 升级Npgsql驱动到4.1或更高支持.NET 4.8的版本，该版本开始默认在连接重置时执行DISCARD ALL，自动清理会话缓存，完全解决缓存膨胀问题。
2. 调整单元测试的重置逻辑：放弃删表重建的方案，改为测试启动时开启事务，测试执行完成后直接回滚事务，无需修改schema，整体测试耗时可以直接降到预期的10分钟以内。
3. 若无法升级驱动也无法调整测试逻辑，每次打开数据库连接后先手动执行一次DISCARD ALL语句，主动清理当前连接的缓存。
4. 临时缓解方案：在连接字符串中添加Connection Idle Lifetime=15参数，让闲置超过15秒的连接自动销毁，避免单个连接被复用过久。

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