I asked Jeremy this question via email. He was kind enough to reply so I am posting here.
Question ======= To give you some context, my question is specifically about how Sequel manages stale connections. I know you have done significant work around connection management in the 3.41.0 release. Our scenario is this: We were using 3.40.0 and noticed PG::Error: SSL connection has been closed unexpectedly errors. We figured out that PostgreSQL was closing connections and Sequel had cached these connections and was attempting to reuse them. We upgraded to to 3.41.0 and now use the connection_validation extension with the timeout set to -1, as stated in the release notes, so no more error. You also state in the release notes "*However, this can cause a substantial performance hit unless you are purposely using coarse connection checkouts via manual calls to Database#synchronize (for example, in a Rack middleware). Using coarse checkouts can greatly reduce the amount of concurrency that Sequel supports (for example, limiting the number of concurrent requests to the number of database connections), so this method is not without its tradeoffs*." This concerns me. How would you suggest we manage this without setting the connection_validation timeout to -1. I would prefer not to query the connection on every query into the DB. I am looking for a best practice suggestion here. Should we configure PostgreSQL to terminate connections after 60mins, so that Sequel will terminate (and hence control) the connection termination with the default time limit of 60mins? Answer ====== A more performant way to handle this is just to make sure that connections get used more often than the connection timeout. That can be done with a cron job and the :connection_handling=>:queue option. For example, with a 60 minute idle timeout, if you use the default of 4 connections in the pool, having a cron job that causes a query every 14 minutes should ensure that none of the connections idle out. The connection_validator extension is more for cases that can't be handled well by a cron job, or where you need to be extra sure and the performance hit is worth it for the transparent reconnection and general convenience. You could try setting the PostgreSQL connection idle timeout to over 60 minutes, and then the connection_validator default should work, only issuing a query if the connection hasn't been used in the last 60 minutes. -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To view this discussion on the web visit https://groups.google.com/d/msg/sequel-talk/-/dDA9Ka2xTS0J. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sequel-talk?hl=en.
