Add docs for using Psycopg 3 connection pooling by chrispy-snps · Pull Request #12540 · sqlalchemy/sqlalchemy

chrispy-snps · 2025-04-20T13:15:20Z

Updates documentation to describe how to use Psycopg 3 connection pooling in SQLAlchemy. This is a follow-up to discussion #12522.

Description

The Postgres > psycopg dialect documentation is updated. I used the Oracle connection pool docs as a reference to ensure consistent documentation style.

Checklist

A documentation / typographical / small typing error fix
- Good to go, no issue or tests are needed

…ssion sqlalchemy#12522) Signed-off-by: chrispy <[email protected]>

CaselIT

Thanks, nice addition.

Left a couple of suggestions

CaselIT · 2025-04-24T20:07:12Z

lib/sqlalchemy/dialects/postgresql/psycopg.py

+downsizing for unused connections), connection health pre-checks, and support
+for both synchronous and asynchronous code environments.
+
+To take advantage of ``psycopg``'s pool,


I'm not a fan of this section since we have the example just below, but let's wait mike's opinion on this

@CaselIT - this is patterned after the Oracle docs here. I am happy to make any changes requested.

I just meant the list part. Let's wait mike opinion on this

CaselIT · 2025-04-24T20:08:41Z

lib/sqlalchemy/dialects/postgresql/psycopg.py

+    the Psycopg 3 documentation for ``psycopg_pool.ConnectionPool``,
+    which provides more information on available configuration parameters,
+    logging, and more.
+


since the async version is not 100% the same it may make sense to provide the example example also in async

@CaselIT - I added an incremental example for async code. If you prefer a full example, I can do that too.

Signed-off-by: chrispy <[email protected]>

CaselIT · 2025-04-27T17:46:13Z

lib/sqlalchemy/dialects/postgresql/psycopg.py

    )

+Usage of ``psycopg_pool.AsyncConnectionPool`` is similar, but it requires the
+following ``psycopg.AsyncConnection`` subclass instead:


I think it make sense to mention also the use of the AsyncConnectionPool and async_creator

@CaselIT - good feedback. There were indeed enough differences that a more complete example was needed. It's there now.

Signed-off-by: chrispy <[email protected]>

chrispy-snps · 2025-05-03T15:15:19Z

@dvarrazzo - when I use the asynchronous code structure in this pull request (complete runnable example here) with logging enabled, I get the following warning:

venv/lib/python3.11/site-packages/psycopg_pool/pool_async.py:142: RuntimeWarning: opening the async pool AsyncConnectionPool in the constructor is deprecated and will not be supported anymore in a future release. Please use `await pool.open()`, or use the pool as context manager using: `async with AsyncConnectionPool(...) as pool: `...

How should the code change to resolve this? It is not clear to me.

dvarrazzo · 2025-05-04T11:40:36Z

As I have re-opened psycopg/psycopg#1046 maybe we want to wait for which shape it takes before merging this?

dvarrazzo · 2025-05-04T11:50:03Z

@dvarrazzo - when I use the asynchronous code structure in this pull request (complete runnable example here) with logging enabled, I get the following warning:
venv/lib/python3.11/site-packages/psycopg_pool/pool_async.py:142: RuntimeWarning: opening the async pool AsyncConnectionPool in the constructor is deprecated and will not be supported anymore in a future release. Please use `await pool.open()`, or use the pool as context manager using: `async with AsyncConnectionPool(...) as pool: `...
How should the code change to resolve this? It is not clear to me.

You must open the pool in closed state and use await pool.open() explicitly. For example:

async def main():
    mypool = psycopg_pool.AsyncConnectionPool(
        connection_class=MyAsyncConnection,
        conninfo=db_url.replace("+psycopg", ""),
        open=False,
    )
    await mypool.open()

This is documented here but maybe it's unclear that open=False is required in the c'tor.

chrispy-snps · 2025-05-04T12:59:54Z

@dvarrazzo - thanks! I updated the asynchronous example there to open the pool outside the constructor.

Signed-off-by: chrispy <[email protected]>

CaselIT · 2025-05-04T18:12:26Z

As I have re-opened psycopg/psycopg#1046 maybe we want to wait for which shape it takes before merging this?

works for me! Or we can even have two example depending on the pool version.

chrispy-snps · 2025-05-04T21:20:57Z

@CaselIT, @dvarrazzo - I am fine with waiting for the psycogp_pool 3.3.0 release, then releasing this documentation update with the more user-friendly example (noting the required psycogp_pool version).

dvarrazzo · 2025-05-04T22:20:54Z

As I have re-opened psycopg/psycopg#1046 maybe we want to wait for which shape it takes before merging this?

works for me! Or we can even have two example depending on the pool version.

I doubt that psycopg pool 3.3 will bring any difference that anyone would prefer to use the older version. I think that, because asking to subclass the connection is pretty awkward, I think you can limit the documentation to the easier 3.3.

If someone needs to use an older version, I am adding the subclassing example to our docs too, so I don't think you need to be responsible of presenting it. I will link you the updated docs for review as soon as the branch I am preparing is tested.

chrispy-snps · 2025-05-04T23:35:41Z

@dvarrazzo - for completeness, I will also keep both the < 3.3 and >= 3.3 asynchronous examples here as a reference.

CaselIT · 2025-05-05T18:03:28Z

@dvarrazzo - for completeness, I will also keep both the < 3.3 and >= 3.3 asynchronous examples here as a reference.

I doubt that psycopg pool 3.3 will bring any difference that anyone would prefer to use the older version. I think that, because asking to subclass the connection is pretty awkward, I think you can limit the documentation to the easier 3.3.

makes sense. At worst we could link to that discussion comment from the documentation simply stating "In older version of psycopg see the example at [...]"

CaselIT · 2025-10-20T07:30:09Z

since this was merged in psycopg I think we could merge here too. I'll do that this week

sqla-tester

OK, this is sqla-tester setting up my work on behalf of CaselIT to try to get revision 8abbcb6 of this pull request into gerrit so we can run tests and reviews and stuff

sqla-tester · 2025-10-24T21:08:32Z

New Gerrit review created for change 8abbcb6: https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/6261

sqla-tester

Michael Bayer (zzzeek) wrote:

this section is awkward at the moment since it does not introduce the concept very well in the context of what pooling is already present as well as that there are other pooling options.

I think a generic section in the pooling docs for "using vendor specific connection pools", illustrating the general idea of NullPool and creator on SQLAlchemy's side, then a few examples of psycopg and oracle pooling, is best. we dont need to re-document psycopg's pool

View this in Gerrit at https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/6261

sqla-tester · 2025-10-25T20:29:19Z

lib/sqlalchemy/dialects/postgresql/psycopg.py

    dialect shares most of its behavior with the ``psycopg2`` dialect.
    Further documentation is available there.

+Connection Pooling


Michael Bayer (zzzeek) wrote:

is this section about "using psycopg's connection pooling" ? it should be named like that

because really if you want to use a good pooling mechanism with postgresql you use pgbouncer

additionally, this whole concept of using a vendor specific pool applies to other drivers too like oracle. Shouldn't we just include in the "pooling" section a chapter on "using vendor-specific pooling implementations" ?

View this in Gerrit at https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/6261

Federico Caselli (CaselIT) wrote:

Done

View this in Gerrit at https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/6261

sqla-tester · 2025-10-25T20:29:19Z

lib/sqlalchemy/dialects/postgresql/psycopg.py

+Connection Pooling
+------------------
+
+Applications with multiple concurrent users should use connection pooling. A


Michael Bayer (zzzeek) wrote:

users or processes?

What kind of connection pooling? SQLAlchemy uses connection pooling by default already so why do we need to mention this? ah, this is for psycopg's specific feature. most people asking about this are looking for pgbouncer, so this section needs to be a lot more specific from the start that it's about people who specifically want to use this particular pool.

This intro should start: "Users may opt to use psycopg's provided pooling facilities as a replacement for SQLAlchemy's connection pooling. psycopg's pool provides for similar features as SQLAlchemy's pool with the addition of automatic downsizing of unused connections (our pool does all those other things already).

View this in Gerrit at https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/6261

Federico Caselli (CaselIT) wrote:

Done

View this in Gerrit at https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/6261

sqla-tester · 2025-10-25T22:04:05Z

Federico Caselli (CaselIT) wrote:

note: this is basically taken from the oracledb docs: https://docs.sqlalchemy.org/en/20/dialects/oracle.html#connection-pooling

Personally I think we could just omit the "what is a conn pool" and just go for the examples.

View this in Gerrit at https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/6261

sqla-tester · 2025-10-25T22:14:59Z

Michael Bayer (zzzeek) wrote:

yikes then i dont like the oracle section either :) had no idea we did it like that. that intro sentence is awful - we can leave both of these mostly intact, but take out that first paragraph (for both PG and Oracle) and name the sections "using psycopg connection pooling" / "using oracledb connection pooling"

View this in Gerrit at https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/6261

sqla-tester · 2025-10-28T21:24:34Z

Federico Caselli (CaselIT) wrote:

Done

View this in Gerrit at https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/6261

sqla-tester · 2025-10-29T15:38:47Z

Michael Bayer (zzzeek) wrote:

it's great. thanks

View this in Gerrit at https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/6261

sqla-tester · 2025-10-29T15:38:51Z

Gerrit review https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/6261 has been merged. Congratulations! :)

sqla-tester · 2025-10-29T15:38:56Z

Gerrit review https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/6262 has been merged. Congratulations! :)

Updates documentation to describe how to use Psycopg 3 connection pooling in SQLAlchemy. This is a follow-up to discussion #12522. References: #12522 Closes: #12540 Pull-request: #12540 Pull-request-sha: 8abbcb6 Change-Id: I11ded6f82852f354f8371051c4b68fd7bdd01997 (cherry picked from commit 6f2275d)

add docs for using Psycopg 3 connection pooling (see SQLAlchemy discu…

865a7d8

…ssion sqlalchemy#12522) Signed-off-by: chrispy <[email protected]>

CaselIT reviewed Apr 24, 2025

View reviewed changes

CaselIT requested a review from zzzeek April 24, 2025 20:09

add required subclass for async code

fa4f681

Signed-off-by: chrispy <[email protected]>

CaselIT reviewed Apr 27, 2025

View reviewed changes

add more complete async example

4c8b903

Signed-off-by: chrispy <[email protected]>

open AsyncConnectionPool outside constructor

8abbcb6

Signed-off-by: chrispy <[email protected]>

dvarrazzo mentioned this pull request May 8, 2025

Allow integration of psycopg pool with SQLAlchemy psycopg/psycopg#1067

Merged

CaselIT requested a review from sqla-tester October 24, 2025 21:08

sqla-tester reviewed Oct 24, 2025

View reviewed changes

sqla-tester reviewed Oct 25, 2025

View reviewed changes

sqlalchemy-bot closed this in 6f2275d Oct 29, 2025

Uh oh!

Conversation

chrispy-snps commented Apr 20, 2025

Description

Checklist

Uh oh!

CaselIT left a comment

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

chrispy-snps commented May 3, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

dvarrazzo commented May 4, 2025

Uh oh!

dvarrazzo commented May 4, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

chrispy-snps commented May 4, 2025

Uh oh!

CaselIT commented May 4, 2025

Uh oh!

chrispy-snps commented May 4, 2025

Uh oh!

dvarrazzo commented May 4, 2025

Uh oh!

chrispy-snps commented May 4, 2025

Uh oh!

CaselIT commented May 5, 2025

Uh oh!

CaselIT commented Oct 20, 2025

Uh oh!

sqla-tester left a comment

Choose a reason for hiding this comment

Uh oh!

sqla-tester commented Oct 24, 2025

Uh oh!

sqla-tester left a comment

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

sqla-tester commented Oct 25, 2025

Uh oh!

sqla-tester commented Oct 25, 2025

Uh oh!

sqla-tester commented Oct 28, 2025

Uh oh!

sqla-tester commented Oct 29, 2025

Uh oh!

sqla-tester commented Oct 29, 2025

Uh oh!

sqla-tester commented Oct 29, 2025

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

chrispy-snps commented May 3, 2025 •

edited

Loading

dvarrazzo commented May 4, 2025 •

edited

Loading