From 6f2275d8051beb501af912d431672887baa26594 Mon Sep 17 00:00:00 2001 From: chrispy Date: Fri, 24 Oct 2025 17:08:31 -0400 Subject: [PATCH] Add docs for using Psycopg 3 connection pooling 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: https://github.com/sqlalchemy/sqlalchemy/pull/12540 Pull-request-sha: 8abbcb6e863deb6e0cf077d5fa780a2be5792931 Change-Id: I11ded6f82852f354f8371051c4b68fd7bdd01997 --- lib/sqlalchemy/dialects/oracle/oracledb.py | 8 +- lib/sqlalchemy/dialects/postgresql/psycopg.py | 76 +++++++++++++++++++ 2 files changed, 78 insertions(+), 6 deletions(-) diff --git a/lib/sqlalchemy/dialects/oracle/oracledb.py b/lib/sqlalchemy/dialects/oracle/oracledb.py index 1fbcabb6dd..9804cde295 100644 --- a/lib/sqlalchemy/dialects/oracle/oracledb.py +++ b/lib/sqlalchemy/dialects/oracle/oracledb.py @@ -236,12 +236,8 @@ Typically ``config_dir`` and ``wallet_location`` are the same directory, which is where the Oracle Autonomous Database wallet zip file was extracted. Note this directory should be protected. -Connection Pooling ------------------- - -Applications with multiple concurrent users should use connection pooling. A -minimal sized connection pool is also beneficial for long-running, single-user -applications that do not frequently use a connection. +Using python-oracledb Connection Pooling +---------------------------------------- The python-oracledb driver provides its own connection pool implementation that may be used in place of SQLAlchemy's pooling functionality. The driver pool diff --git a/lib/sqlalchemy/dialects/postgresql/psycopg.py b/lib/sqlalchemy/dialects/postgresql/psycopg.py index f525fe1831..c4da2ce9bc 100644 --- a/lib/sqlalchemy/dialects/postgresql/psycopg.py +++ b/lib/sqlalchemy/dialects/postgresql/psycopg.py @@ -59,6 +59,82 @@ The asyncio version of the dialect may also be specified explicitly using the dialect shares most of its behavior with the ``psycopg2`` dialect. Further documentation is available there. +Using psycopg Connection Pooling +-------------------------------- + +The ``psycopg`` driver provides its own connection pool implementation that +may be used in place of SQLAlchemy's pooling functionality. +This pool implementation provides support for fixed and dynamic pool sizes +(including automatic downsizing for unused connections), connection health +pre-checks, and support for both synchronous and asynchronous code +environments. + +Here is an example that uses the sync version of the pool, using +``psycopg_pool >= 3.3`` that introduces support for ``close_returns=True``:: + + import psycopg_pool + from sqlalchemy import create_engine + from sqlalchemy.pool import NullPool + + # Create a psycopg_pool connection pool + my_pool = psycopg_pool.ConnectionPool( + conninfo="postgresql://scott:tiger@localhost/test", + close_returns=True, # Return "closed" active connections to the pool + # ... other pool parameters as desired ... + ) + + # Create an engine that uses the connection pool to get a connection + engine = create_engine( + url="postgresql+psycopg://", # Only need the dialect now + poolclass=NullPool, # Disable SQLAlchemy's default connection pool + creator=my_pool.getconn, # Use Psycopg 3 connection pool to obtain connections + ) + +Similarly an the async example:: + + import psycopg_pool + from sqlalchemy.ext.asyncio import create_async_engine + from sqlalchemy.pool import NullPool + + + async def define_engine(): + # Create a psycopg_pool connection pool + my_pool = psycopg_pool.AsyncConnectionPool( + conninfo="postgresql://scott:tiger@localhost/test", + open=False, # See comment below + close_returns=True, # Return "closed" active connections to the pool + # ... other pool parameters as desired ... + ) + + # Must explicitly open AsyncConnectionPool outside constructor + # https://www.psycopg.org/psycopg3/docs/api/pool.html#psycopg_pool.AsyncConnectionPool + await my_pool.open() + + # Create an engine that uses the connection pool to get a connection + engine = create_async_engine( + url="postgresql+psycopg://", # Only need the dialect now + poolclass=NullPool, # Disable SQLAlchemy's default connection pool + async_creator=my_pool.getconn, # Use Psycopg 3 connection pool to obtain connections + ) + + return engine, my_pool + +The resulting engine may then be used normally. Internally, Psycopg 3 handles +connection pooling:: + + with engine.connect() as conn: + print(conn.scalar(text("select 42"))) + +.. seealso:: + + `Connection pools `_ - + the Psycopg 3 documentation for ``psycopg_pool.ConnectionPool``. + + `Example for older version of psycopg_pool + `_ - + An example about using the ``psycopg_pool<3.3`` that did not have the + ``close_returns``` parameter. + Using a different Cursor class ------------------------------ -- 2.47.3