Postgresql – How to best use connection pooling in SQLAlchethe for PgBouncer transaction-level pooling

postgresqlpython

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.

Best Answer

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.

Related Solutions

Postgresql – Span single transaction over several sequential psql sessions

Maybe it would be easiest to run a psql in the background, with it set to execute stdin, and connect its stdin to a named pipe. Then you can continually push data into that pipe, and finally push "end; \quit". Something like:

#!/bin/sh

psql_pipe=/tmp/psql$$
mkfifo -m 600 $psql_pipe
psql < $psql_pipe &
exec 3>$psql_pipe
psql_pid=$!

echo "> Started psql (pid=$psql_pid) reading from $psql_pipe"
trap '
  kill $psql_pid
  rm -f $psql_pipe
' EXIT

echo "begin;" >&3

echo "select now();" >&3

sleep 2

echo "select now();" >&3

sleep 2

echo "end; \quit" >&3

wait $psql_pid

Note that you can't simply do echo "sql" >$psql_pipe since the EOF would be transmitted to psql, which would then exit early-- the shell script has to keep its fd open.

Postgresql – Could too many idle connections affect PostgreSQL 9.2 performance

I seriously doubt idle connections are your issue. PostgreSQL is very capable of handling idle sessions since they take almost no resources. Active sessions on the other hand are much more likely the cause of slowness.

See https://wiki.postgresql.org/wiki/Number_Of_Database_Connections for reference on the number of database connections.

Best Answer

Related Solutions

Postgresql – Span single transaction over several sequential psql sessions

Postgresql – Could too many idle connections affect PostgreSQL 9.2 performance

Related Question