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

Question

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.

Answer 1

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.

Answer 2

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:

session.begin()
session.execute(...)
session.rollback()

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

def A():
  session.begin()
  ...
  session.rollback()

def B():
  session.begin()
  try:
      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:

session.execute("BEGIN")
#...
session.execute("COMMIT")