Psycopg Connection Pool

Common errors

• psycopg_pool.PoolTimeout: couldn't get a connection after X.X sec

  cause The application requested a connection from the pool, but all connections were in use, and no new connections could be created within the configured timeout or max_size limit, often due to connection leaks, long-running transactions, or an undersized pool.
  fix Ensure connections are always returned to the pool using context managers (`with pool.connection() as conn:`). Increase `max_size` if more concurrent connections are needed, consider shortening transaction times, and implement `pool.check()` or `pool.close()` followed by re-opening the pool in error handling for persistent issues.

• psycopg.InterfaceError: connection already closed

  cause The application attempted to use a database connection that had already been closed, typically due to idle timeouts enforced by the database server, network disruptions, or improper connection management, especially in multi-process environments.
  fix Utilize `psycopg-pool`'s connection context manager (`with pool.connection() as conn:`) to ensure proper connection lifecycle management. Configure `max_lifetime` in the pool to proactively recycle connections, and consider using the `check` callback or `pool.check()` to validate connection health upon retrieval.

• psycopg.OperationalError: fe_sendauth: no password supplied

  cause The PostgreSQL server rejected the connection attempt because the client did not provide a password, or the provided password was incorrect, or the `pg_hba.conf` on the server is not configured to allow the specified user and host to connect without a password.
  fix Ensure the `conninfo` string or `kwargs` passed to `ConnectionPool` or `AsyncConnectionPool` includes the correct `password` parameter. Verify the `user` and `password` credentials and confirm the PostgreSQL server's `pg_hba.conf` configuration allows authentication for the connecting user and host.

• ModuleNotFoundError: No module named 'psycopg_pool'

  cause The `psycopg_pool` package has not been installed or is not accessible in the current Python environment, as it is distributed separately from the main `psycopg` package.
  fix Install the package using pip: `pip install psycopg-pool` or, if you're installing `psycopg` and want the pool as an extra, `pip install "psycopg[pool]"`.

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).
• breaking `psycopg-pool` depends on `psycopg`, which requires the PostgreSQL client library (`libpq`) to be installed in the environment. If `libpq` or its development headers are missing, `psycopg` will fail to import with an `ImportError: no pq wrapper available`, making `psycopg-pool` unusable.
  Fix: Ensure that the PostgreSQL client library (`libpq`) and its development headers are installed in the environment before installing `psycopg-pool` or `psycopg`. For Debian/Ubuntu-based systems, this typically means running `apt-get install libpq-dev`. For other systems, consult the `psycopg` documentation for relevant installation instructions.
• breaking `psycopg`, a dependency of `psycopg-pool`, requires the PostgreSQL client library (`libpq`) to be installed in the environment. In minimal environments (e.g., Alpine Linux), this library might be missing, leading to an `ImportError: no pq wrapper available.` when importing `psycopg`.
  Fix: Ensure the PostgreSQL client library is installed in your environment. For Alpine Linux, install `postgresql-client` or `libpq` if available. For Debian/Ubuntu, install `libpq-dev`. Alternatively, use `psycopg[binary]` to install a pre-compiled wheel that bundles libpq.

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
  wrong:

  from psycopg2.pool import SimpleConnectionPool

  correct:
  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.")