Connection Pooling for Python Apps: PgBouncer and Async Pools

“Just add PgBouncer in front of it” is the reflexive answer to almost any Postgres connection problem, and it’s right often enough that nobody stops to ask which of PgBouncer’s three pooling modes they just enabled, or whether their ORM’s own connection handling is now fighting the pooler instead of cooperating with it. Then a migration adds a prepared statement, or a session sets a search_path, and something that worked in every test breaks in production with an error that doesn’t obviously point back at the pooler at all.

The actual problem underneath “too many connections” is that a Postgres connection is expensive — each one forks a full backend process with its own memory overhead — while a modern Python web app can easily want thousands of concurrent logical database operations. Pooling exists to reconcile those two facts, but the specific mode you choose determines exactly what session-level behavior your application is allowed to rely on. Get the mode and the pool sizing wrong together, and you trade one failure mode (too many connections) for a subtler one (silently broken prepared statements, or workers queued for a connection that’s already sitting idle two mode-settings away).

You’ll learn:

• Why a raw Postgres connection is expensive enough to need pooling in the first place
• The real difference between PgBouncer’s session, transaction, and statement pooling modes
• Specifically what breaks under transaction mode, and why
• How to size a pool correctly instead of guessing at a round number
• How async Python frameworks change the pool-sizing math compared to sync ones
• How to configure PgBouncer for FastAPI and Django without fighting the framework’s own connection handling
• How to actually diagnose a “too many connections” error instead of just raising the limit

Table of Contents

1. Why Postgres Connections Are Expensive
2. PgBouncer’s Three Pooling Modes
3. What Breaks Under Transaction Mode
4. Sizing a Pool Correctly
5. Async Python and Pool Sizing
6. Configuring PgBouncer for FastAPI and Django
7. Diagnosing “Too Many Connections”
8. PgBouncer Alternatives Worth Knowing
9. Common Pitfalls

Why Postgres Connections Are Expensive

Postgres uses a process-per-connection model — every new connection forks a dedicated backend process, complete with its own memory for query execution, sort buffers, and cached catalog lookups. That’s simple and robust, but it means each connection costs real, non-trivial memory (commonly several megabytes per idle connection, more under active query load) and real CPU to fork in the first place. max_connections in postgresql.conf defaults to 100 specifically because the server doesn’t scale gracefully past a few hundred connections — pushing it into the thousands to match application concurrency directly usually degrades overall throughput rather than improving it, because the server spends more time context-switching between backend processes than doing query work.

Meanwhile, a Python web app under real traffic can have hundreds or thousands of concurrent requests, each nominally wanting a database connection. Connecting a browser tab’s worth of traffic 1:1 to Postgres backend processes doesn’t scale — that mismatch, not any specific bug, is the entire reason connection pooling exists. A pooler sits between the application and Postgres, holds a small, fixed number of real backend connections open, and multiplexes many more logical client connections onto that small pool.

A driver-level pool (SQLAlchemy’s QueuePool, asyncpg.create_pool(), Django’s CONN_MAX_AGE) solves a narrower version of the same problem: it reuses connections within one application process, which helps a single-process script but does nothing for the case that actually causes “too many connections” in production — dozens of independently-scaled application pods or worker processes, each running its own driver-level pool, all connecting to the same Postgres instance at once. A hundred pods each opening twenty driver-level connections is two thousand real Postgres backends, regardless of how well each individual pod’s pool is tuned. An external pooler like PgBouncer sits below all of those pods as a shared layer, which is the only place the total connection count across the whole fleet can actually be bounded.

PgBouncer’s Three Pooling Modes

PgBouncer is the de facto standard external pooler for Postgres, and its behavior is defined almost entirely by one setting: pool_mode.

Session pooling — a client keeps the same server connection for the entire lifetime of its session, released only on disconnect. This is functionally identical to connecting directly to Postgres from the application’s point of view; every session-level feature (prepared statements, session variables, advisory locks, LISTEN/NOTIFY) works exactly as expected. The tradeoff is that it doesn’t actually solve the connection-count problem much — a long-lived client still holds a real Postgres connection for as long as it’s connected, which is the exact thing pooling was supposed to fix.

Transaction pooling — a server connection is assigned to a client only for the duration of a single transaction, then immediately returned to the pool the moment that transaction commits or rolls back. This is the mode that actually delivers the scaling benefit: hundreds of idle client connections can share a handful of real server connections, because each one only holds a server connection for the brief window it’s actually running a transaction. It’s also, by a wide margin, the most commonly deployed mode in production for exactly this reason.

Statement pooling — the most aggressive mode, releasing the server connection after every single statement, even within a transaction. It’s rarely used in practice because it doesn’t support multi-statement transactions at all — a BEGIN in one statement and a COMMIT in the next could land on two different server connections, which breaks transactional semantics outright. Most real deployments never touch this mode.

; pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

That configuration accepts up to a thousand simultaneous client connections from the application, while ever opening only 20 real connections to Postgres — the ratio that makes pooling worth deploying in the first place.

What Breaks Under Transaction Mode

Transaction mode’s speed comes directly from the thing that makes it dangerous: because a client’s server connection can change between transactions, anything that depends on server-side session state surviving across transactions silently breaks or behaves inconsistently.

Prepared statements. PREPARE, and any ORM or driver feature built on top of it (Django’s persistent connections interact here, and asyncpg prepares statements by default), assumes the statement stays prepared on the same backend it was prepared on. Under transaction mode, the next transaction might land on a completely different backend that never saw that PREPARE — resulting in “prepared statement does not exist” errors that appear intermittent and load-dependent, because they depend on which backend the pool happens to hand back.

Session-level SET statements. SET search_path = tenant_42 outside of a transaction persists on the server connection, not on the client’s logical session — the next client to receive that same server connection back from the pool inherits whatever the previous client last set, unless it was explicitly reset. This is a genuine, silent multi-tenant data leak risk if search_path (or any session-scoped setting) is used for tenant isolation. SET LOCAL inside a transaction is scoped to that transaction and resets automatically at commit, which is the safe version of the same idea under transaction mode.

Advisory locks. pg_advisory_lock() held outside a transaction persists on the connection until explicitly released — but under transaction mode, that connection may be handed to a completely different client the moment your transaction ends, so the lock either releases earlier than intended or, worse, appears to be held by an unrelated later client. Use pg_advisory_xact_lock() instead, which is scoped to the transaction and always releases cleanly at commit or rollback.

LISTEN/NOTIFY. A LISTEN registered on one backend is meaningless once that connection is returned to the pool and handed to someone else — transaction mode is fundamentally incompatible with long-lived listeners, because there’s no guarantee the same backend, or even the same client, keeps that subscription alive.

Temporary tables. CREATE TEMP TABLE is scoped to the session (the physical backend connection), not the client’s logical connection — a temp table created in one transaction may not exist, or may unexpectedly still exist with stale data, on whatever backend the next transaction happens to land on.

The unifying rule: under transaction pooling, treat every connection as stateless between transactions. Anything that needs to survive across statements has to be scoped explicitly inside a single transaction (SET LOCAL, pg_advisory_xact_lock) or avoided entirely (long-lived LISTEN, session-level prepared statements) — this is the practical core of pgbouncer fastapi and pgbouncer django setups going wrong in ways that look random until you know to look for exactly this.

Sizing a Pool Correctly

The instinct to set default_pool_size to match expected concurrent requests is wrong, and it’s wrong for a specific, well-known reason: PostgreSQL’s own guidance (echoed from the PgBouncer project and multiple production postmortems) is that the optimal pool size is usually far smaller than intuition suggests, because a database connection spends most of its “busy” time waiting on I/O, not consuming CPU — and a small pool with a short queue often outperforms a large pool with contention, because Postgres itself has a limited number of CPU cores to actually execute queries with.

A commonly cited starting formula, adapted from PostgreSQL performance guidance, is:

pool_size = ((core_count * 2) + effective_spindle_count)

For a modern server with SSD-backed storage (effectively treating spindle count as low), that lands a reasonable starting pool size for a single database in the range of (cores * 2) + 1, then adjusted upward only after measuring actual queue wait time, not guessed upward preemptively. The real tuning loop is: set a conservative pool size, monitor PgBouncer’s SHOW POOLS output for clients waiting on a connection, and only increase the pool if wait time is real and sustained rather than an artifact of one slow query holding a connection too long.

SHOW POOLS;
-- cl_waiting column: clients currently queued for a server connection

A nonzero, persistently growing cl_waiting under normal load is the actual signal to increase pool size or investigate slow queries holding connections too long — not a hunch, and not matching the pool size to max_client_conn.

Async Python and Pool Sizing

Sync frameworks (classic Django views, Flask with a threaded WSGI server) map roughly one worker process or thread to one in-flight request, so the connection math is close to workers * pool_size_per_worker. Async frameworks change this fundamentally: a single async worker can hold hundreds of concurrent logical requests in flight, each potentially wanting a database connection at the same moment, all multiplexed onto far fewer OS threads.

This means an async app’s application-level connection pool (asyncpg.create_pool(), or SQLAlchemy’s async engine pool) needs to be sized independently of how many concurrent requests the ASGI server can hold — and it should sit well below max_client_conn on PgBouncer, since PgBouncer is a second layer of pooling on top of it, not a replacement for it.

import asyncpg

pool = await asyncpg.create_pool(
    dsn="postgresql://user:pass@pgbouncer-host:6432/mydb",
    min_size=5,
    max_size=20,
    statement_cache_size=0,  # required under PgBouncer transaction mode
)

statement_cache_size=0 is not optional under transaction-mode PgBouncer — asyncpg prepares and caches statements per physical connection by default, which is exactly the behavior that breaks when the underlying backend can change between calls. Disabling it trades some per-query overhead for correctness under pooling; skipping this step is the single most common cause of “works locally, breaks under load” asyncpg errors against PgBouncer.

Configuring PgBouncer for FastAPI and Django

For FastAPI with SQLAlchemy’s async engine, point the engine at PgBouncer’s port (commonly 6432) rather than Postgres’s 5432 directly, and disable SQLAlchemy’s own statement caching for the same reason as asyncpg above:

from sqlalchemy.ext.asyncio import create_async_engine

engine = create_async_engine(
    "postgresql+asyncpg://user:pass@pgbouncer-host:6432/mydb",
    pool_size=10,
    max_overflow=5,
    connect_args={"statement_cache_size": 0},
)

For Django, CONN_MAX_AGE controls Django’s own persistent-connection behavior, and it needs to cooperate with PgBouncer rather than duplicate it — running Django’s connection persistence on top of transaction-mode PgBouncer means two independent pooling layers making decisions about the same connections. Setting CONN_MAX_AGE = 0 (the historical default) lets each request open and close its logical connection cleanly, leaving all the actual pooling work to PgBouncer underneath:

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "HOST": "pgbouncer-host",
        "PORT": "6432",
        "NAME": "mydb",
        "DISABLE_SERVER_SIDE_CURSORS": True,  # required under transaction mode
    }
}

DISABLE_SERVER_SIDE_CURSORS matters for the same class of reason as statement_cache_size=0 above — Django’s server-side cursor support for .iterator() assumes a stable backend connection across multiple fetch calls, which transaction mode doesn’t guarantee.

Diagnosing “Too Many Connections”

FATAL: too many connections for role or remaining connection slots are reserved almost never means the fix is raising max_connections. Work through this order instead:

1. Check pg_stat_activity for what’s actually holding connections open. SELECT state, count(*) FROM pg_stat_activity GROUP BY state; — a large idle in transaction count means application code is opening transactions and not closing them, which no amount of pooling fixes on its own.
2. Check whether PgBouncer is even in the path. It’s common to add PgBouncer for one service and have another service, a cron job, or an analytics tool connecting directly to Postgres’s port 5432 and bypassing the pooler entirely.
3. Check pool mode against actual usage. Session mode under high concurrency reintroduces the exact problem pooling was meant to solve — if connections aren’t being returned quickly, transaction mode combined with fixing long idle in transaction sessions is almost always the real fix.
4. Only then consider raising max_connections, and do it with awareness that each additional slot has a real memory cost on the Postgres server, not a purely configuration-level one.

Reading EXPLAIN ANALYZE output to find which specific queries are running long and holding connections open longer than they should is a natural next step here — see the guide to reading Postgres query plans for that half of the diagnosis.

PgBouncer Alternatives Worth Knowing

PgBouncer is the default recommendation because it’s mature, lightweight, and well-documented, but it’s single-threaded per instance (run multiple instances behind SO_REUSEPORT for multi-core scaling) and it’s worth knowing what else exists. Odyssey, built at Yandex, and PgCat, written in Rust, both offer multi-threaded pooling out of the box along with load-balancing across read replicas — genuinely useful if a single PgBouncer process is CPU-bound under heavy connection churn. Managed Postgres providers (RDS Proxy, Supabase’s built-in pooler, Neon’s pooler) increasingly bundle an equivalent of transaction-mode pooling directly into the platform, which is worth checking before standing up a self-managed PgBouncer instance — the same mode tradeoffs from this post still apply, just configured through the provider’s dashboard instead of a pgbouncer.ini file.

Common Pitfalls

Mistake: setting the PgBouncer pool size equal to max_client_conn. This defeats the purpose of pooling entirely — the whole benefit comes from a small server-side pool serving a much larger number of client connections. Solution: size the server pool from core count and measured wait time, independently of how many clients can connect.

Mistake: using transaction mode with unguarded session state. SET, advisory locks, and prepared statements all silently misbehave. Solution: use SET LOCAL and pg_advisory_xact_lock, and disable client-side statement caching.

Mistake: running two independent pooling layers without coordinating them. Django’s CONN_MAX_AGE or SQLAlchemy’s engine pool fighting PgBouncer’s own pool underneath it produces confusing, hard-to-reproduce connection exhaustion. Solution: keep the application-level pool modest and let PgBouncer do the heavy multiplexing.

Mistake: raising max_connections as a first response to connection errors. This treats the symptom and adds real memory pressure on the server. Solution: find out what’s actually holding connections via pg_stat_activity before touching the limit.

Mistake: forgetting that async connection pools and PgBouncer stack, not replace each other. Setting asyncpg’s pool max_size too high on top of an already-sized PgBouncer pool just moves the queueing point without fixing it. Solution: size the application pool modestly and confirm PgBouncer, not the app, is the layer actually absorbing connection bursts.

Wrapping Up

Connection pooling isn’t a single on/off decision — it’s a choice between three genuinely different contracts about what session state survives between statements, and transaction mode’s speed is inseparable from its statelessness. Getting this right means matching the pool mode to what your application actually depends on, sizing the pool from core count and measured wait time instead of a guessed round number, and treating async connection pools and PgBouncer as two cooperating layers rather than duplicates of each other.

Is your current pool size a number you measured, or a number that just felt safe?