psycopg 3 connection pool versus sqlalchemy connection pool #7478
Replies: 2 comments 8 replies
-
im not familiar with psycopg3's pool, but if pooling performance is super important to you, and your application runs with more than one Python process (very likely if performance matters), you'd be better off using pgbouncer. that is, the main "pool" issue is that Python processes that all have their own pool don't share the connections as efficiently as if you have high performance middleware handling it.
looking at https://www.psycopg.org/psycopg3/docs/advanced/pool.html it looks like psycopg3's pool is thankfully not implicit, you use a pool object explicitly, so out of the box, psycopg3's pool would not be used, so no.
sure, you would set up for NullPool in your create_engine and then write a creator function that uses the pool. Here is an example that is doing this identical thing for cx_Oracle's connection pool, you can use this as is, just replace the cx_Oracle functions for the psycopg3 ones: https://docs.sqlalchemy.org/en/14/dialects/oracle.html#using-cx-oracle-sessionpool that example is there because folks wanted to use that pool, I think cx_Oracle's pool might be tapping into some more enterprise-level Oracle server stuff (like middleware) that would make it worth it.
SQLAlchemy is designed around the assumptions used by this pool so for most predictable results, mostly in the area of what happens when connections are killed by the server and similar areas, you'd use the built-in pool. Looking at psycopg3's docs, looks like some pretty useful things are lacking:
ouch. that's a basic one. pgbouncer has this, SQLAlchemy has this for every DB (pool_pre_ping), im pretty sure Hibernate's pools have this, if psycopg3 wants to provide a connection pool it should be adding this.
SQLAlchemy's pool does not do this either but we have a pool_recycle option that helps a lot with this, though the pre-ping option prior is mostly all you need.
|
Beta Was this translation helpful? Give feedback.
-
|
I'm experiencing a similar issue. sqlalchemy_engine = create_async_engine(
url=settings.db_url,
async_creator=pool.getconn,
poolclass=NullPool,
)However, this code doesn’t work as expected. According to the API doc, I need to call Additionally, regarding the statement:
This appears to be outdated. psycopg now includes a |
Beta Was this translation helpful? Give feedback.
-
|
I am trying to adapt psycopg 3's Unfortunately the second transaction hangs instead of completing. Here's what I've got: import time
import logging
logging.basicConfig(level=logging.DEBUG, format="%(asctime)s %(levelname)s %(name)s: %(message)s")
logging.getLogger("sqlalchemy.pool").setLevel(logging.DEBUG)
import sqlalchemy
from psycopg_pool import ConnectionPool
db_url = "postgresql+psycopg://postgres:postgres@hostname:port/database"
# create a psycopg_pool connection pool
pool = ConnectionPool(
conninfo=db_url.replace("+psycopg", ""),
min_size=0,
max_size=1,
max_idle=5,
)
# create a SQLAlchemy engine using the psycopg_pool connection pool
engine = sqlalchemy.create_engine(
url=db_url,
poolclass=sqlalchemy.pool.NullPool, # disable SQLAlchemy's default connection pool
creator=pool.getconn, # use the psycopg_pool connection pool instead
)
# generate some "activity"
def do_stuff():
with engine.connect() as connection:
result = connection.execute(sqlalchemy.text("SELECT now()"))
print(f"Current time: {result.fetchone()[0]}")
do_stuff()
do_stuff()
# now go idle
# (and wait for max_idle timeout to release connection)
time.sleep(15)I expected the Thanks! |
Beta Was this translation helpful? Give feedback.
-
|
please see the event mentioned here #7478 (reply in thread) |
Beta Was this translation helpful? Give feedback.
-
|
@CaselIT - thanks! I did look at those posts, and it seems they provide the solution given @edwardzjl's positive reaction. However, I was unable to understand how to craft a solution from them. If that means I'm not ready to be using these mechanisms in my own code, I accept that too. :) Perhaps I'll disable connection pooling for now, then revisit this topic when my knowledge of SQLAlchemy has increased. |
Beta Was this translation helpful? Give feedback.
-
|
@edwardzjl - did you get psycopg 3's |
Beta Was this translation helpful? Give feedback.
-
|
No I switched to pgbouncer instead. |
Beta Was this translation helpful? Give feedback.
Uh oh!
There was an error while loading. Please reload this page.
-
With more advanced features coming in the psycopg 3 connection pool, I'm wondering:
Interested to hear comments from @dvarrazzo as well. Thanks
Beta Was this translation helpful? Give feedback.
All reactions