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?
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.