Good question.

PGBouncer isn't as useful if you have seriously long-running transactions. It can’t do much of anything with those. Sure, you can give it a pool of 1000 and your Postgres instance a pool of 100, but you’re just moving who is going to say, “sorry, the database can’t handle your request right now.”

It’s not a silver bullet.

If you have more connections than Postgres can handle on the hardware it’s on, but with a bit of buffer it’ll be able to burn them down: great.

If you have long-held connections with many transactions that PGBouncer can interleave: great.

If you have connections whose transactions are longer than a reasonable timeout, well, your optimization princess is in another castle.