Hacker News Reader

This works wonderfully. If you have any long running queries, though, the PAUSE won't pause until they have finished.

I love pgbouncer, it is such a great tool.

this is interesting. So you have a weakest link kind of problem here.

Not really, new connections will block as it's pausing. But you won't be able to shut down Postgres until those long queries complete. Perhaps I was not super clear, but what I'm trying to say is that PAUSE is not instantaneous.

yeah what I'm saying is that you can only pause as fast as your slowest currently initiated query. So if you have a diverse set of query patterns, you could be waiting for a really small percentage of small queries to wrap up.

I understand now. Yes, this is true and should be taken into consideration during upgrades.

Most of our queries are fast, and the longer ones hit a read-only secondary instance. Cleanup jobs, though, can take hours to complete.

You can temporarily reduce query timeout to a smaller setting as part of the automated failover. The long running transactions will fail but you can minimize the window where you can't talk to postgres

Reply on news.ycombinator.com