0

there!

I use PgBouncer with Sqlalchemy for getting connections.

For my understanding, I would like to clarify the following points:

  1. I use the sqlalchemy default pool (QueuePool) and take connections from my pgbouncer. After the transaction is completed, is the connection returned to the "lazy" connection storage on my side (sqlalchemy) or directly to the pgbouncer pool?

  2. If I use QueuePool with pool_size=5 parameters and create an engine for pgbouncer pool, does pgbouncer allocate these 5 connections at once, or are the connections given on demand?

  3. If I remove the connection pooling (using NullPool) and also create an engine for pgbouncer, does this mean that after exiting the transaction context, the connection is closed and a new one will be created already inside pgbouncer at the next request?

  4. Which of these approaches is more correct in the context of using sqlalchemy + pgbouncer?

With connections I work like this:

async with async_session() as connect:
      yield connect
      await connect.commit()
SmokyICE
  • 63
  • 1
  • 6

1 Answers1

0

I think you don't need in-app connection pool if you use pgbouncer, you can either use NullPool+pgbouncer of QueuePool + use_lifo=True flag to use LIFO Queue which should reuse old connections first. I am not entirely sure if using both pgbouncer + in-app connection pool would be beneficial or harmful though.

Here's a relevant documentation link: https://docs.sqlalchemy.org/en/20/core/pooling.html

Also for automatic transaction commit you could use sessionmaker.begin():

async with async_sessinomaker.begin() as session:
    ...
Doctor
  • 71
  • 1
  • 1
  • 1