Pros and Cons of SQLAlchemy Pool with connection poolers like PGBouncer/Odyssey

[funkindy]

Hi!

I am using SA connection pools with standalone poolers all the time, and everything works fine. But does it really make sense?

Is there some best practices/caveats or benchmarks for when to use SA pool vs when to just use NullPool with standalone pooler?

I see @zzzeek is pointing out that NullPool is good to start, especially for debugging, but what about general use in production? Maybe someone can share an experience?

Thank you.

[zzzeek]

i dont have real world experience w/ PGBouncer outside of local testing, I dont know if I'd leave NullPool when using this tool, unless I measured additional latency that could be resolved by adding a second QueuePool layer. I'd be concerned about differing "reset" behaviors for connections that stay in QueuePool vs. those that come recycled from PGBouncer. I'd probably write a lot of test suites to really nail down everything going on.