Re: Connection hike

On Wed, 2024-03-13 at 18:14 +0530, Rajesh Kumar wrote:
> There were connections hike and I used to check blocking sessions and long
> running queries using pg_stat_activity.
>
> My manager gave me less rating during performance meeting, because he says
> even a kid can do this. He said my job is to analyse the cause of connection hike. 
>
> Any idea how to analyse?

I assume that you mean "hang", not "hike".

You can diagnose locked sessions fron the "wait_event" and "wait_event_type" in
"pg_stat_activity". For the process ID of a blocked session, you can call the
"pg_blocking_pids()" function to get the process IDs of the sessions blocking it.

You can look at "pg_locks" to see on which object the lock is (a lock waiting
for a transaction is waiting for a row lock).

That's about all you can analyze in the database. To figure out which statements
took the locks that block others, you'd have to debug the application.

Yours,
Laurenz Albe