Python Postgres - Should I manually close connections ?

[gaspardc-met]

What would you like help with?

Hello,

I have recently started using adbc_driver_postgresql and adbc-driver-manager along with pandas read_sql to query data from a Postgres database.
With SQLalchemy, everyone mentions the Connection Pool that should help with my case, but no trace of a Connection Pool in the ADBC docs.
When my python webapp starts, several different database requests are made, most to the same table.

I do not know what are the best practices or the most efficient way to handle ADBC connections.

• Should I use a context manager and close the connection after one use everytime ?
  This is the recommended use in the Pandas documentation (above 2.2.0)
  However, the dbapi.connect is long, always longer than the actual read_sql (see attached image for a profiling example), and is executed everytime.
  

  from adbc_driver_postgresql import dbapi
  
  with dbapi.connect(uri) as adbc_conn:
      return read_sql(query, adbc_conn)

• Should I just leave the connection open ?

• Should I cache the connection resource ?

I'm sure that like any engineering problem there are tradeoffs to all of these, but I do not have comparison points here apart from execution time

thanks !

Environment:

• python 3.11
• pandas == 2.2.2
• adbc_driver_postgresql==0.11.0
• adbc-driver-manager==0.11.0

[lidavidm]

You should reuse connections.

ADBC does not implement connection pooling. Use a dedicated connection pooling library on top of ADBC connections.

[gaspardc-met]

Hello,

Thanks for your reply. I couldn't find resources on this. Should I use psycopg for the connection pooling ?
Since ADBC does not implement connection pooling, maybe this could be documented somewhere as an example in the docs if it's the expected practice ?

[lidavidm]

I don't think in general DBAPI drivers are expected to implement connection pooling, so it's not really a consideration in the first place.

I'm not sure psycopg's pool will work with anything except psycopg. SQLAlchemy and others provide connection pools, but which one you want to use will depend on your application.

[lidavidm]

I think what we can do here is demo how to use a connection pool (like SQLAlchemy) with the ADBC drivers in the documentation.

[lidavidm]

There's now an example here: #1919

[gaspardc-met]

Very interesting, thank you
I would not have thought of doing this, so the doc is clearly beneficial 👍