Thanks Jeff. We have scripts in place now to capture the incoming rate of requests. Waiting on the crash to happen to see if it spikes up :)
Re: min_log_duration – we *do* see a good number of requests in the log that hit our cap (of 100ms). Just that nothing stands out when we have the issue. Whatever queries we do see slow down seem to be after we start the CPU spike, and so an effect and not a cause.
I think what you have is a vicious cycle: too many active connections leads to contention which leads to slow response which leads to piling up connections which leads to more contention. So the cause and the effect are the same thing as each other, you can't cleanly divide them.
We typically see about 500-700 active queries at a time – and that seems to match how high connection limit goes.
This is during normal times, or during the trouble?
We tried pg_bouncer, however, at session level pooling, it slowed down our applications (they maintain persistent connections once established, so any connection overhead slows them down),
I don't understand that. If the connections are persistent, why would they increase during the slow down?