How to best use connection pooling in SQLAlchemy for PgBouncer transaction-level pooling?

Using SQLAlchemy to query a PostgreSQL database behind PgBouncer, using transaction-level pooling.

What is the best pattern to use for this kind of set up? Should I have one-engine-per-process, using a ConnectionPool, or should I create an engine per-request, and use NullPool for each one of them? Is there a different pattern altogether that I should be using?

Thanks very much! Let me know if more information is needed and I'll update ASAP.

with PGBouncer, you'd probably want to just stick with NullPool. In that case you may be able to share a single Engine across subprocesses since no socket connections will be carried over the subprocess boundary. But you can't share anything referring to a Connection object, like a Session with an active transaction, over this boundary. You definitely wouldn't want to do "engine-per-request" though, an Engine is an expensive object that accumulates a lot of information about a particular database URL the first time it sees it.

StackExchange Database Administrators Q#36828, answer score: 12