Psycopg Connection Pool

3.3.0 · active · verified Sun Apr 05

psycopg-pool provides efficient connection pooling implementations for Psycopg 3, the PostgreSQL adapter for Python. It includes both synchronous (`ConnectionPool`) and asynchronous (`AsyncConnectionPool`) pools, designed to manage a limited number of PostgreSQL connections, reduce connection overhead, and optimize resource usage in multi-threaded or async applications. The current version is 3.3.0, and it follows an independent release cycle from the main `psycopg` package.

Warnings

breaking The default value for the `open` parameter in `ConnectionPool` is currently `True`, but this will likely change to `False` in future releases. For `AsyncConnectionPool`, opening in the constructor will become an error. Explicitly set `open=True` if you rely on the pool opening immediately, or manage it with `pool.open()` and `pool.close()` or as a context manager.
Fix: For `ConnectionPool`, explicitly pass `open=True` to the constructor, or wrap pool creation in a `with` statement. For `AsyncConnectionPool`, always use `async with AsyncConnectionPool(...) as pool:` or call `await pool.open()`.
gotcha Unlike `psycopg2`, using `with connection:` in `psycopg-pool` (via `with pool.connection() as conn:`) manages the entire connection's lifecycle within the `with` block, including returning it to the pool and implicitly handling transaction commit/rollback. In `psycopg2`, `with connection:` only managed the transaction.
Fix: Be aware of the different behavior when migrating from `psycopg2`. For transaction-only blocks in `psycopg-pool`, use `with conn.transaction():`.
gotcha For integration with SQLAlchemy, `psycopg-pool` versions prior to 3.3.0 might behave unexpectedly if `conn.close()` is called, as it would actually close the connection instead of returning it to the pool. SQLAlchemy expects `close()` to return the connection to the pool.
Fix: Upgrade to `psycopg-pool` 3.3.0 or later and set `close_returns=True` in the `ConnectionPool` constructor when integrating with SQLAlchemy.
gotcha The `psycopg-pool` library has its own versioning and release cycle, which is separate from the main `psycopg` package. This is important for managing dependencies and understanding compatibility.
Fix: When specifying dependencies, refer to the specific `psycopg-pool` version rather than assuming it aligns with the `psycopg` version.
gotcha Setting both `min_size` and `max_size` to 0 in `ConnectionPool` previously resulted in a hang. Since version 3.0.3, this now correctly raises a `ValueError`.
Fix: Ensure `min_size` is at least 1, or that `max_size` is greater than or equal to `min_size` (unless you explicitly intend a null pool with specific `max_size` throttling).

Install

pip install psycopg-pool Install standalone
pip install "psycopg[pool]" Install with psycopg extra

Imports

ConnectionPool
```
from psycopg_pool import ConnectionPool
```

AsyncConnectionPool

from psycopg_pool import AsyncConnectionPool

SimpleConnectionPool
This is from Psycopg2 and is not compatible with Psycopg 3 or psycopg-pool.

Quickstart

This quickstart demonstrates how to initialize a `ConnectionPool` using a connection string, obtain a connection using a context manager, execute a simple query, and ensure proper connection handling (returning to pool, transaction management). It also highlights how the pool manages connections for multiple requests.

import os
from psycopg_pool import ConnectionPool

# Get connection string from environment variable for security
DB_URL = os.environ.get('DATABASE_URL', 'postgresql://user:password@host:port/dbname')

# Create a connection pool as a context manager
# The pool is opened and closed automatically
with ConnectionPool(DB_URL, min_size=1, max_size=5) as pool:
    print("Connection pool created.")

    # Get a connection from the pool as a context manager
    with pool.connection() as conn:
        # The connection context handles transaction commit/rollback
        # and returns the connection to the pool.
        with conn.cursor() as cur:
            cur.execute("SELECT 1 + 1")
            result = cur.fetchone()
            print(f"Result: {result[0]}")

    # Example of getting multiple connections (will block if pool exhausted)
    print("Attempting to get another connection...")
    with pool.connection() as conn2:
        with conn2.cursor() as cur2:
            cur2.execute("SELECT 'Hello from conn2'")
            result2 = cur2.fetchone()
            print(f"Result from conn2: {result2[0]}")

print("Pool is closed after exiting the 'with' block.")

view raw JSON →