[Question] - Best way to check pool health?

[WoodyWoodsta]

Running our client in Kubernetes, we'd like to provide a readiness endpoint which can report whether or not it is ready to accept connections. This will be based on whether we know to have a healthy connection with the DB.

Is there a recommended way to check for the health of a pg pool? Is this as simple as checking to see if there is at least 1 connection in the pool, or would it require something different?

We'd prefer not to have to perform a query to make this check, but will resort to this approach if there is no reliable alternative.

[brianc]

Hmm...I'm not entirely clear on what you're looking for, but I'll do my best to give you some more information. You want a flag or something to check on the pool to see if the pool is able to connect to the DB? One way would be to manually instantiate and connect a client outside of the pool in some sort of startup section...wait for that client to connect, then disconnect it. Then you know the pool should be able to connect (provided you use the same connection info & the db doesn't go down between when the check connection was made & the pool was first asked to connect!). Otherwise you could do something like

async function onProcessStartup() {
  const pool = new Pool()
  await pool.query('SELECT 1;')
}

The startup handshake with postgres of creating a connection can take 10 to 100 times longer than executing a query on an already connected client, depending on your environment.

[WoodyWoodsta]

Thanks, that's useful. In the meantime we have actually gone with the select 1; approach.

What I was looking to find out is if pool.totalCount > 0 is an accurate indication that the pool is successfully connected to the DB, assuming that the DB will close connections if it is unhealthy.

[brianc]

Yeah pool.totalCount will definitely tell you how many clients are in the pool (both active & idle). If postgres becomes unavailable you're correct all the clients will be closed & removed from the pool immediately after they receive an error from their connection (either a network OR postgres error) while they're idle in the pool! You can still run into instances where your pool is fully drained (due to idleTimeoutMillis) & so it will be empty but your application might still be considered healthy.

[WoodyWoodsta]

You can still run into instances where your pool is fully drained (due to idleTimeoutMillis) & so it will be empty but your application might still be considered healthy.

Yes this has occurred to me!

So it seems their are two approaches:

1. Periodically query the db with a cheap query
2. Disable connection idle timeout and check connection count

Option 1 is probably the most robust while maintaining the ability to set pool sizes and idle timeouts as required for the client without flooding the DB with connections at scale.

[brianc]

Yeah definitely. 1 is probably the way to go. A cheap query is really cheap to execute both from postgres & node's perspective, so it should be minimal impact on the system.

You can technically let your connections never idle out...it doesn't hurt anything.... Having the idle timeout just kinda the "try turning it off and back on again" approach automatically built in, and since connections to postgres are a semi limited resource (unless you're running pg-bouncer or something) might as well not hog 'em if you're not usin' 'em. If your app stays under steady full load they will never timeout anyway.

[WoodyWoodsta]

We have both bursty and continuous clients so having an idle timeout is a default which feels the most responsible to me.

Cheap query every 10 seconds from even 1000 clients is pale in comparison to an actual workload! Thank you for your help!