{"library":"psycopg-pool","title":"Psycopg Connection Pool","description":"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.","status":"active","version":"3.3.0","language":"en","source_language":"en","source_url":"https://github.com/psycopg/psycopg/tree/master/psycopg_pool","tags":["database","postgresql","psycopg","connection pool","asyncio","threading"],"install":[{"cmd":"pip install psycopg-pool","lang":"bash","label":"Install standalone"},{"cmd":"pip install \"psycopg[pool]\"","lang":"bash","label":"Install with psycopg extra"}],"dependencies":[{"reason":"Core PostgreSQL adapter for Python.","package":"psycopg","optional":false}],"imports":[{"symbol":"ConnectionPool","correct":"from psycopg_pool import ConnectionPool"},{"symbol":"AsyncConnectionPool","correct":"from psycopg_pool import AsyncConnectionPool"},{"note":"This is from Psycopg2 and is not compatible with Psycopg 3 or psycopg-pool.","wrong":"from psycopg2.pool import SimpleConnectionPool","symbol":"SimpleConnectionPool"}],"quickstart":{"code":"import os\nfrom psycopg_pool import ConnectionPool\n\n# Get connection string from environment variable for security\nDB_URL = os.environ.get('DATABASE_URL', 'postgresql://user:password@host:port/dbname')\n\n# Create a connection pool as a context manager\n# The pool is opened and closed automatically\nwith ConnectionPool(DB_URL, min_size=1, max_size=5) as pool:\n    print(\"Connection pool created.\")\n\n    # Get a connection from the pool as a context manager\n    with pool.connection() as conn:\n        # The connection context handles transaction commit/rollback\n        # and returns the connection to the pool.\n        with conn.cursor() as cur:\n            cur.execute(\"SELECT 1 + 1\")\n            result = cur.fetchone()\n            print(f\"Result: {result[0]}\")\n\n    # Example of getting multiple connections (will block if pool exhausted)\n    print(\"Attempting to get another connection...\")\n    with pool.connection() as conn2:\n        with conn2.cursor() as cur2:\n            cur2.execute(\"SELECT 'Hello from conn2'\")\n            result2 = cur2.fetchone()\n            print(f\"Result from conn2: {result2[0]}\")\n\nprint(\"Pool is closed after exiting the 'with' block.\")","lang":"python","description":"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."},"warnings":[{"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()`.","message":"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.","severity":"breaking","affected_versions":"All versions, explicitly warned since 3.2.0 for sync pools and 3.2.0 for async pools."},{"fix":"Be aware of the different behavior when migrating from `psycopg2`. For transaction-only blocks in `psycopg-pool`, use `with conn.transaction():`.","message":"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.","severity":"gotcha","affected_versions":"All versions of `psycopg-pool` (Psycopg 3)."},{"fix":"Upgrade to `psycopg-pool` 3.3.0 or later and set `close_returns=True` in the `ConnectionPool` constructor when integrating with SQLAlchemy.","message":"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.","severity":"gotcha","affected_versions":"<3.3.0"},{"fix":"When specifying dependencies, refer to the specific `psycopg-pool` version rather than assuming it aligns with the `psycopg` version.","message":"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.","severity":"gotcha","affected_versions":"All versions."},{"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).","message":"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`.","severity":"gotcha","affected_versions":"<3.0.3"}],"env_vars":null,"last_verified":"2026-04-05T00:00:00.000Z","next_check":"2026-07-04T00:00:00.000Z"}