Re: Recommendations for PGBouncer interacting with HikariCP

> On Aug 23, 2019, at 1:39 PM, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:
>
> On Wed, 21 Aug 2019 at 21:03, Joseph Hammerman <jhammerman(at)squarespace(dot)com> wrote:
> Hi pgsql-jdbc email distribution list,
>
> We have recently added PGBouncer into our stack. We have done this in part, to protect the database. Our Java userbase is accustomed to using HikariCP / Hibernate for connection pooling. They would prefer to continue to use the connection pooler, in order to not have GC churn on connection spin up and tear down (in addition to additional latency introduced by having to perform the three-way handshake more often).
>
>> Is there any published material / does anyone have any knowledge of how these tools should interact or not interact?
>
> JDBC should work fine with pgbouncer.

Most things do, although there are some caveats. For example if you have anything attached to your server session *outside* of a txn
(say, server-side prepared statements or session vars)
and you configure your pgbouncer for transaction pooling, you can end up creating some extremely difficult to understand behaviors.

https://pgbouncer.github.io/faq.html#how-to-use-prepared-statements-with-transaction-pooling

So you do have to be a little bit careful.

>
>> HikariCP wants to keep many long running idle_in_transaction connections open, which saturates the backend connection pool.
>
> That's strange why is it keeping them in transaction ? One would think once you closed (returned the connection) it should commit it.

My personal experience working with JDBC and HikariCP is that 99.9% of the time, "idle in transaction" means that some programmer somewhere
actually did not close their resource. I did end up finding numerous bugs, both in my $WORK's code as well as in Java libraries we use, although to the
best of my memory the roots of problems we found were never in either PgJDBC nor HikariCP.

To diagnose these nasty issues, we took a pretty extreme method: on every checkout from the HikariCP pool, we generate a UUID and
print a tagged stack trace to a file. We then set `application_name` to "app_name-$UUID".

When our DBAs found an "idle in txn" connection that we did not know where it came from, we would immediately have the app and could
then look up the stack trace of the last allocation site using the application_name in pg_stat_activity.

From there it was usually straightforward, if painful, to diagnose the problem: "Oh I opened a Hibernate session and handed it off to a Reactive
actor that threw an exception but it never got handled and so the Connection was never closed and the whole thing got leaked. Whoops. But look at how *fast* Reactive is!!!"

Hope that gives you some ideas.