Current number of connections in use

[charsleysa]

We used to use pg and pg-pool which would expose the number of connections in use and the number available.

Our use case is that this would let us have a health endpoint that our load balancer could use to determine if the server is overloaded query-wise (cpu and request could could still be low but connections could be maxed out and queries taking too long for some reason).

Is there any way to do this?

[porsager]

Not straight forward, but I think it would be a great idea to expose that. I'll look into it!

[mdorda]

+1 for this feature! pg package has stats for totalCount, activeCount and idleCount (https://node-postgres.com/api/pool#properties) and it is super usefull for finding bottlenecks on heavily loaded backends.

[hsubra89]

Does it make sense to have a onconnect/ondisconnect callback? We could then trivially calculate totalCount and activeCount.

If onconnect is passed the underlying sql connection, then we could also potentially set some connection defaults per connection that could be useful such as set statement_timeout=??? or any other connection specific settings.

[porsager]

@hsubra89 statement_timeout can be set using connection options on init. postgres({ connection: { statement_timeout: ... } })

[porsager]

Ok, sorry for being slow on this, but it was actually pretty straight forward :) There's a bit more info in the pool I think is interesting, so I'm looking at the following api currently - What do you think of that?

// just getters that return the count
sql.connections.open // all open connections 
sql.connections.closed // all closed connections
sql.connections.connecting // all connections currently trying to connect
sql.connections.ended // connections explicitly ended but not closed yet (could have remaining queries and end timeout)
sql.connections.busy // connections with active queries
sql.connections.idle // connections open but not doing anyting
sql.connections.full // connections not available for pipelining (backpressure or active cursors)
sql.connections.reserved // connections in transactions or reserved
sql.connections.max // same as options.max

sql.connections.onchange = state => {
  // is fired any time a connection changes state, with the new state as the first parameter
}

[porsager]

Pushed a first stab at it here if you want to test : https://github.com/porsager/postgres/tree/connection-stats

[mdorda]

Thanks! It looks very promising. I would like to ask about a specific application. If I want to know how many requests are in the queue? That is, I assume a situation where busy will correspond more or less to open and idle will be around zero. Is there any way to find out the number of pending requests? Because that would be a great metric - how bad the situation is and if it's getting better or not.

To be more specific, I am interested in waitingCount of pg package: https://node-postgres.com/apis/pool#properties

Edit: I guess queries is what I am looking for? https://github.com/porsager/postgres/compare/master...connection-stats#diff-bfe9874d239014961b1ae4e89875a6155667db834a410aaaa2ebe3cf89820556R55 Just getter is missing?

[porsager]

Oh right.. We should get that in as well! Might be nice to include the difference in number of queries queued and number of queries executing. Number of queries executing isn't simply the max number of connections because we leverage pipelining, so it could be many more.

Perhaps keeping a count of finished queries would be interesting as well..

sql.queries.queued
sql.queries.executing
sql.queries.finished

[porsager]

Thank you.

Why would you need that?