Skip to main content

AsyncPG Connection Pools: Setup and Configuration

Opening a new PostgreSQL connection takes 10–100 ms (TCP handshake, TLS negotiation, authentication). If every request opens a fresh connection, you lose 50% of your response time to setup and teardown overhead. Connection pooling maintains a reservoir of warm, authenticated connections ready to reuse. Asyncpg's built-in pool reduces per-request latency by 70–90% and lets you serve 10× more concurrent users on the same hardware.

This guide shows you how to create and configure pools, tune sizes for your workload, and integrate them into a real application. You'll learn the difference between min/max pool sizes and when to adjust them.

What is a Connection Pool?​

A pool is a collection of open, idle connections waiting to serve requests. When your code needs a connection, the pool gives you one (or creates a new one if all are busy, up to a limit). When you're done, you return it—the connection closes its transaction but stays open. This reuse eliminates the handshake overhead on each request.

Pools have three key parameters:

  • min_size: How many connections to open at startup and maintain as idle.
  • max_size: The maximum total connections (both in-use and idle). Beyond this, new requests queue.
  • max_queries: Optional; recycle a connection after N queries to prevent stale state.

For a typical API serving 100 concurrent requests:

  • Min size 10, max size 50 handles bursts without over-allocating.
  • Min size 1, max size 100 works but creates connections slowly on startup.
  • Min size 50, max size 50 (fixed) is fine if you know your peak.

Creating a Pool​

Here's the simplest approach. Create a pool at application startup and reuse it for all requests:

import asyncio
import asyncpg

async def create_pool():
    """Create and return an asyncpg connection pool."""
    pool = await asyncpg.create_pool(
        'postgresql://postgres:password@localhost/postgres',
        min_size=10,
        max_size=50
    )
    return pool

async def main():
    pool = await create_pool()
    
    # Get a connection from the pool
    async with pool.acquire() as conn:
        version = await conn.fetchval('SELECT version()')
        print(f"Server: {version}")
    
    # Return the connection to the pool (happens automatically)
    # The connection is now available for reuse
    
    await pool.close()

asyncio.run(main())

Key steps:

  1. asyncpg.create_pool() opens min_size connections and returns a pool object.
  2. pool.acquire() gets a connection. If all are in-use, waits for one to return.
  3. async with automatically releases the connection when the block exits.
  4. pool.close() shuts down all pooled connections gracefully.

The first request takes ~100 ms (opening min_size connections). Subsequent requests reuse existing connections in ~1 ms.

Pool Acquisition with a Context Manager​

Best practice: always use async with pool.acquire() as conn:. The context manager guarantees the connection returns to the pool even if an exception occurs:

import asyncio
import asyncpg

async def query_with_pool(pool, user_id: int):
    """Fetch a user by ID from the pool."""
    async with pool.acquire() as conn:
        user = await conn.fetchrow(
            'SELECT id, name, email FROM users WHERE id = $1',
            user_id
        )
        if user:
            print(f"Found: {user['name']} ({user['email']})")
        else:
            print(f"User {user_id} not found")

async def main():
    pool = await asyncpg.create_pool(
        'postgresql://postgres:password@localhost/postgres',
        min_size=5,
        max_size=20
    )
    
    try:
        # Simulate concurrent requests
        await asyncio.gather(
            query_with_pool(pool, 1),
            query_with_pool(pool, 2),
            query_with_pool(pool, 3)
        )
    finally:
        await pool.close()

asyncio.run(main())

If fetchrow() raises an exception, the async with block still releases the connection. Without it, the connection would leak and eventually exhaust the pool.

Tuning Pool Size​

Pool size depends on your workload. Use these heuristics:

WorkloadMinMaxRationale
Light API (10 req/s)210Minimal idle connections needed
Medium API (100 req/s)1030Handles burst, avoids queueing
High-traffic API (1000 req/s)20100More concurrency, less queueing
Data pipeline (sequential tasks)55Fixed pool, all tasks use same connections

A practical approach: start with min_size = (avg_concurrent_requests / 2) and max_size = (peak_concurrent_requests * 1.5). Monitor your app; if the pool is always at max_size, increase it. If it never exceeds min_size, lower min to save memory.

Monitoring Pool Usage​

Asyncpg pools expose statistics. Use them to tune sizes:

import asyncio
import asyncpg
import time

async def stress_test(pool, num_requests: int):
    """Simulate concurrent requests and monitor pool stats."""
    
    async def dummy_query(query_id):
        async with pool.acquire() as conn:
            await asyncio.sleep(0.1)  # Simulate 100 ms query
            await conn.fetchval('SELECT 1')
    
    # Fire requests concurrently
    start = time.time()
    tasks = [dummy_query(i) for i in range(num_requests)]
    await asyncio.gather(*tasks)
    elapsed = time.time() - start
    
    # Print pool statistics
    print(f"\nPool stats after {num_requests} concurrent requests ({elapsed:.2f}s):")
    print(f"  Size: {pool.get_size()} connections")
    print(f"  Idle: {pool.get_idle_size()} waiting in pool")
    print(f"  Min: {pool._minsize}, Max: {pool._maxsize}")

async def main():
    pool = await asyncpg.create_pool(
        'postgresql://postgres:password@localhost/postgres',
        min_size=5,
        max_size=30
    )
    
    try:
        await stress_test(pool, 50)
    finally:
        await pool.close()

asyncio.run(main())

Sample output:

Pool stats after 50 concurrent requests (0.52s):
  Size: 30 connections
  Idle: 2 waiting in pool
  Min: 5, Max: 30

The pool scaled to 30 connections (hit max_size) because 50 requests arrived faster than 30 connections could serve them. Consider increasing max_size if this happens under normal load.

Connection Timeout and Acquisition Timeout​

By default, pool acquisition waits indefinitely. For production, set timeouts to prevent queue hangs:

import asyncio
import asyncpg

async def acquire_with_timeout(pool):
    """Get a connection with a 5-second timeout."""
    try:
        # If no connection becomes available in 5 seconds, raise TimeoutError
        async with pool.acquire(timeout=5.0) as conn:
            result = await conn.fetchval('SELECT 1')
            print(f"Query succeeded: {result}")
    except asyncpg.TooManyConnectionsError:
        print("Pool exhausted, request queued too long")

async def main():
    pool = await asyncpg.create_pool(
        'postgresql://postgres:password@localhost/postgres',
        min_size=2,
        max_size=5,
        max_queries=10000
    )
    
    try:
        await acquire_with_timeout(pool)
    finally:
        await pool.close()

asyncio.run(main())

A timeout of 5 seconds is reasonable for most APIs. If requests regularly timeout, your pool is too small or your queries are too slow. Profile and adjust.

Pool Lifecycle in a Web App​

In a FastAPI app, create the pool at startup and close it at shutdown:

from fastapi import FastAPI
import asyncpg

app = FastAPI()
pool = None

@app.on_event('startup')
async def startup():
    """Initialize the connection pool on app startup."""
    global pool
    pool = await asyncpg.create_pool(
        'postgresql://postgres:password@localhost/postgres',
        min_size=10,
        max_size=50
    )

@app.on_event('shutdown')
async def shutdown():
    """Close the pool on app shutdown."""
    await pool.close()

@app.get('/users/{user_id}')
async def get_user(user_id: int):
    """Fetch a user using a pooled connection."""
    async with pool.acquire() as conn:
        user = await conn.fetchrow(
            'SELECT id, name FROM users WHERE id = $1',
            user_id
        )
    return user

The pool is created once, shared across all requests, and closed gracefully when the app stops. This pattern scales to thousands of concurrent requests.

Key Takeaways​

  • Connection pools eliminate 70–90% of connection overhead by reusing open connections.
  • Create a pool once at app startup; reuse it for all requests.
  • Use async with pool.acquire() as conn: to guarantee connections return to the pool.
  • Tune min_size and max_size based on your workload. Start with min = peak/5, max = peak.
  • Monitor pool statistics to catch exhaustion and adjust sizes.
  • Set acquisition timeouts to prevent infinite queues.

Frequently Asked Questions​

What happens if the pool runs out of connections?​

If all max_size connections are in-use, new requests queue and wait. If you set timeout=X, they raise asyncio.TimeoutError after X seconds. Increase max_size or reduce query duration.

How many idle connections should I maintain?​

The min_size should be high enough that 99% of requests get an idle connection without waiting. For a typical API, min_size = peak_concurrency / 4 to 10 works well. Too low causes queueing; too high wastes RAM (~4 MB per connection).

Can I have multiple pools in one app?​

Yes, but avoid it. One pool per database is standard. If you connect to multiple databases, create one pool per database and manage their lifecycles together.

Does the pool reconnect automatically if the database goes down?​

Asyncpg will raise exceptions on in-flight queries. The pool doesn't auto-reconnect; it's your app's responsibility to handle errors and close/recreate the pool. In production, use a reverse proxy or connection pooler (PgBouncer) for automatic failover.

Further Reading​