Comment by welder
10 hours ago
Three real-world issues I've run into recently with PgBouncer + Postgres are:
1. pool exhaustion from idle connections inside open long-running transactions
2. SQLAlchemy's client-side pool using dead connections that PgBouncer had already killed, causing periodic request errors
3. Some tasks have to bypass PgBouncer when they use SET or prepared statements
I've already sharded large datasets at the application layer, but looks like PgDog solves the above problems for any future work?
#1 is a problem with the client's code, I don't know any easy workaround. Usually a long-running transaction means you're accidentally waiting on stuff like RPCs in the middle, or maybe doing something that doesn't really need to be in a xact.
#2, shouldn't the client<->PgBouncer connections stay open?
#3 is why I just use client-side pools instead of PgBouncer, but that gets annoying when you have a replicated service so you have to think about the sum of connections across all pools, so I get why people use PgBouncer.
SQLA async is a bit of a struggle with pgbouncer.
I had to disable application pooling as it was causing read only transactions I could couldnt pin down the cause.