Recommendations dealing with Auto-suspend (Dead Connections)

[cruzluna]

Hello, I am using neon database and it has an auto-suspend feature which causes a Terminating connection due to administrator command error. What is the best way to deal with dead connections? Prefer it to be concurrent safe and inexpensive. The error bubbles up when one of my API routes is hit and the connection is closed.

I don't think using a pool would be any benefit since the pool of connections will be killed by the database suspense.

This is not an error with PGX. I am inquiring about recommendations on handling dead connections. Thanks!

[jackc]

If that error only when the system has been idle for a while then I would expect the pool to handle it automatically. Connections that have been idle for > 1 second automatically do a ping on acquire. If the ping fails another connection is used (or created if necessary).

If that doesn't work, then the BeforeAcquire hook in pgxpool would allow you to manually test the connection, presumably with a ping, before usage.

Lastly, you can call Conn.Ping manually to ensure a connection is alive.