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.

2 Answers 2


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.


Set the Application Name

If you expect to be running many processes you need to know where they are connecting from. PGBouncer will make this invisible to pg_stat_activity. Solve this by carefully setting the application_name with the information you'll need:

# Sets the application name for this connection in the form of
#   application-name:user@host
prog = os.path.basename(sys.argv[0]) or 'desjob'
username = pwd.getpwuid (os.getuid ()).pw_name
hostname = socket.gethostname().split(".")[0]·
args.setdefault('connect_args', {'application_name': "%s:%s@%s" %
    (prog, username, hostname)})
args.setdefault('isolation_level', "AUTOCOMMIT")
engine = create_engine(url, **args)

Prefer Sessions

Use Sessions since requests from an Engine object can spawn and hold on to multiple connections. Connecting to Postgres is not very expensive, with PGBouncer it's even less so. I would always use NullPool so that the only connections you will see in Postgres are the connections that are actually being used.

from sqlalchemy.pool import Pool, NullPool
engine = create_engine(uri, poolclass=NullPool)

Eliminate Idle Transactions

If your intent is to use PGBouncer to scale then it is imperative that you avoid leaving transactions stuck open. To do this you need to turn autocommit on. This is not simple with SQLAlchemy...there are three places where something called "autocommit" can be set:

psycopg2 autocommit

conn = psycopg2.connect(uri)
conn.autocommit = True

Presumed to be unsafe unsafe because SQLAlchemy needs to know what's happening underneath.

Session autocommit

Session = sessionmaker(bind=engine, autocommit=True)
session = Session()

This requires careful, explicit handing:


Function calling and exception handing is exceedingly difficult because begin() and commit() cannot be nested:

def A():

def B():
      A() # error, already open

In this mode psycopg2 autocommit appears to be False (the default)

Engine autocommit

Setting the Engine isolation mode to "AUTOCOMMIT" when creating the engine establishes new default behavior that may not require changes to existing code.

engine = create_engine(uri, isolation_level="AUTOCOMMIT")

In this mode psycopg2 autocommit appears to be True

The major problem here is that the only way to guarantee that a block of code is wrapped in a transaction is to emit the statements manually:

  • God, I'm so happy I found this information! It literally saved me! Thank you so much for such an informative and comprehensive answer! I use FastAPI, SQLAlchemy (in async mode), PgBouncer and PostgreSQL. I had a problem with leaking database connections. it took me a long time to figure out what the issue was and how to fix it. everything looked ok in the code, but the graphana showed a steady trend of constantly increasing number of connections to the database.
    – zo0M
    Commented Aug 2, 2022 at 7:07
  • Our devops were complaining, i was uncomfortable. after i asked them to show info about connections of my service in PgBouncer, i saw that all of them in idlie status, your information gave me an understanding why and how to fix it. thank you so much again!
    – zo0M
    Commented Aug 2, 2022 at 7:07
  • @eradman What do you think is the most idiomatic way to do it in sqlalchemy 2.0 with no autocommit?
    – saolof
    Commented Nov 2, 2022 at 7:32
  • Time will tell what pitfalls there are to working with sqlalchemy 2.0. It will make my answer above obsolete. docs.sqlalchemy.org/en/14/changelog/migration_20.html
    – eradman
    Commented Nov 3, 2022 at 15:10
  • Here is some very important information about leaky connections and errors you may get when working with SQLA + FastAPI: github.com/MagicStack/asyncpg/issues/978
    – zo0M
    Commented Apr 3, 2023 at 11:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.