🧠 Connections Have Memory: A Step-by-Step Guide to Scaling Postgres with PgBouncer

How to serve more traffic with fewer database connections, and why sharing connections is easy until they start remembering who used them last.

TL;DR

• The goal: handle more traffic without Postgres drowning in connections. The tool: PgBouncer in transaction mode.
• The catch: a shared connection remembers what the last user left on it. In a multi-tenant app, that can mean one tenant's identity leaking into another's queries.
• The fix: apply tenant context at the start of every transaction with a SQLAlchemy event hook, so a transaction without its tenant can't exist.
• The proof: run your test suite through a real PgBouncer with a pool small enough to force sharing, before production ever sees it.
• The payoff: with cheap, safely scoped connections, independent queries can run concurrently with asyncio.TaskGroup.

The objective

Every web app eventually hits the same wall. Traffic grows, you add processes and replicas, and each one opens its own database connections. Postgres starts spending more effort managing connections than answering queries.

This post walks the path from "a new connection for every request" to "a few shared connections behind PgBouncer", one step at a time. Each step fixes something and uncovers the next problem. The examples use a hypothetical multi-tenant app built with FastAPI and async SQLAlchemy; all names are made up.

First, the five ideas the rest of the post builds on:

• Connection. A network session between your app and Postgres. Opening one costs a TCP connect, a TLS handshake (encryption setup) and authentication: often 10–20 ms before any query runs.
• Backend. Postgres serves each connection with its own operating-system process, called a backend. Backends use real memory, so a server can only hold so many.
• Transaction. A group of statements that succeed or fail together, ending in COMMIT or ROLLBACK. One connection runs many transactions over its life.
• Session setting. A named value stored on a connection, like myapp.tenant_id, set with set_config(name, value, ...). Depending on a flag, it lasts for the whole connection or only the current transaction.
• Row-level security (RLS). Postgres policies that filter which rows a query can see or change. Here, each policy compares a row's tenant_id with the session setting, so the database itself keeps tenants apart. (The NULL trap post covers how those policies can fail open.)

Step 1: Find out where the time goes

Before changing anything, measure. A trace records one request as a tree of timed steps called spans: "handled the request", and inside it "opened a connection", "ran this query". OpenTelemetry is the open standard for producing traces; tools like Jaeger draw them as a timeline.

from opentelemetry.instrumentation.fastapi import FastAPIInstrumentor
from opentelemetry.instrumentation.sqlalchemy import SQLAlchemyInstrumentor
 
FastAPIInstrumentor.instrument_app(app)
SQLAlchemyInstrumentor().instrument(engine=engine.sync_engine)

The SQLAlchemy instrumentation records spans for queries and for opening connections. That second part is the interesting one. A slow endpoint might look like this:

GET /reports/summary          ██████████████████████████  ~500 ms
  connect                     ██                          ~16 ms
  SELECT plan_level ...         █
  SELECT recent_items ...        ██
  SELECT totals ...                ███████████
  SELECT label_counts ...                     ████
  connect                                         ██      ~15 ms

Two connections per request, ~15 ms each, spent on handshakes before a single row is read. You won't find that by reading SQL. Measure first, so you fix the problem you have.

Notice the queries too: each one waits for the previous one to finish. Hold that thought until connections are cheap (Step 8).

Step 2: Stop opening connections on every request

The app was using SQLAlchemy's NullPool: open a fresh connection on every checkout, close it on return. That's the right choice for short-lived processes like serverless functions or CLI scripts. For a long-running API server, it pays for a handshake on every request.

A connection pool keeps connections open and lends them out. Your code checks out a connection, uses it, and returns it, and the next request reuses it. Think of a bike-share rack: you take a bike and bring it back, and nobody builds a new bike per ride.

from sqlalchemy.ext.asyncio import create_async_engine
 
engine = create_async_engine(
    settings.database_url,
    pool_size=10,          # connections kept open per process
    max_overflow=10,       # extra connections allowed during bursts
    pool_pre_ping=True,    # check a connection is alive before handing it out
    pool_recycle=1800,     # replace connections older than 30 min
)

The connect spans disappear. But two things come with it.

Shared connections remember things. A connection now serves request A for tenant A, goes back to the pool, then serves request B for tenant B. If tenant A's session setting is still on it, request B runs as tenant A. No error, wrong data. The usual first fix is to wipe connections on the way back into the pool (a reset event listener running RESET ALL, which restores every setting to its default). Keep that problem in mind; it comes back bigger in a moment.

The connection count still grows with your deployment. Each process has its own pool, so Postgres sees:

(pool_size + max_overflow) × processes × replicas

With 20 connections per process, 4 processes per container and 5 containers, that's up to 400 backends, most of them idle and all of them holding memory. It has to fit inside Postgres max_connections, with room for migrations and admin sessions. Scale out once more and you're over the limit.

That's the wall. An in-process pool shares connections within a process. To share them across processes, you need something in between.

Step 3: Put PgBouncer in front of Postgres

PgBouncer is a lightweight proxy. Your apps connect to PgBouncer instead of Postgres; PgBouncer keeps a small number of real connections to Postgres and hands them out as needed. Hundreds of client connections can share a few dozen backends.

How much sharing you get depends on the pool mode:

• Session mode gives a client one backend until it disconnects. Safe, but it barely helps, because your app's pools hold their connections open all day.
• Transaction mode gives a client a backend only for the length of one transaction, then hands it to someone else. A backend is busy only while a query is actually running.

Transaction mode is the objective. It's also where the "connections remember things" problem stops being an edge case: your app no longer owns a backend for a request, or even between two transactions in the same request. Anything stored on the session, rather than inside the transaction, is now shared with strangers.

So before switching production, find out what breaks.

Step 4: Try it in tests first

The safest place to discover what transaction mode breaks is your own test suite. Add an opt-in test lane (the same suite, run against a different setup) that starts PgBouncer in a container and points every database fixture at it:

; pgbouncer.ini for the test lane
[databases]
myapp = host=db port=5432 dbname=myapp
 
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
pool_mode = transaction
; deliberately tiny (PgBouncer doesn't allow comments after a value)
default_pool_size = 2

Two settings make this test honest:

1. A pool of 2. With a big pool, each test connection tends to get its own backend, and the tests pass for the wrong reason. A tiny pool forces sharing on almost every transaction.
2. No superuser. Superusers bypass RLS, so a green run as superuser proves nothing about tenancy. Connect as the same restricted role your app uses.

Run the suite through it, and two kinds of failure show up.

Tenant context goes missing or goes wrong. The app set the tenant once per request, at session scope. Under transaction mode, the next transaction can land on a backend that never saw that setting, and RLS returns no rows. Worse, it can land on a backend that still carries another test's tenant. The pool reset from Step 2 doesn't help: it runs when your app's pool gets a connection back, but PgBouncer swaps the backend underneath after every transaction.

Queries fail intermittently with an error about a prepared statement that doesn't exist. More on that in Step 6.

Step 5: Make every transaction carry its tenant

The first idea most people try is to set the tenant at transaction scope instead (set_config(..., true), the same as SET LOCAL). It disappears when the transaction ends, so it can't leak to the next client.

It breaks on code that commits in the middle of a request: save something, commit, then read something else. The tenant was set in the first transaction; the second one starts without it. RLS doesn't raise an error when the tenant is missing. It quietly filters out every row, and the page comes back empty.

You could ask every developer to re-set the tenant after every commit. In a codebase with hundreds of handlers, someone will forget. The rule needs to live in one place:

Every transaction starts with its tenant context. Not every request, not every connection. Every transaction.

Three SQLAlchemy features make that enforceable:

• Autobegin. In SQLAlchemy 2.0 you rarely write BEGIN. The first statement on a connection starts a transaction automatically, and so does the first statement after a commit().
• The begin event. SQLAlchemy lets you register event listeners, functions it calls at specific moments. The begin event fires whenever a transaction starts, including those automatic ones.
• Execution options. A dictionary SQLAlchemy keeps on the Connection you checked out. A natural place to record "who this connection is acting for".

Store the tenant in execution options; apply it in the begin event:

from contextlib import asynccontextmanager
from dataclasses import dataclass
 
import sqlalchemy as sa
from sqlalchemy import event
from sqlalchemy.ext.asyncio import AsyncConnection, AsyncEngine
 
 
@dataclass(frozen=True, slots=True)
class DbIdentity:
    """Who a transaction acts for. Empty strings mean 'unset' (fail closed)."""
    role: str = ""
    tenant_id: str = ""
    user_id: str = ""
 
 
SERVICE = DbIdentity(role="system")
IDENTITY_KEY = "db_identity"
 
_APPLY_SQL = (
    "SELECT set_config('myapp.role', %s, true),"
    "       set_config('myapp.tenant_id', %s, true),"
    "       set_config('myapp.user_id', %s, true)"
)
 
 
def apply_identity_on_begin(sync_engine: sa.Engine, default: DbIdentity | None = None) -> None:
    """Apply identity settings at the start of every transaction."""
 
    @event.listens_for(sync_engine, "begin")
    def _apply(conn: sa.Connection) -> None:
        ctx = conn.get_execution_options().get(IDENTITY_KEY, default)
        if ctx is None:
            return  # nobody scoped this connection: stay unscoped (RLS returns nothing)
        # Plain driver-level SQL, no statement compilation. (SQLAlchemy doesn't
        # autobegin a second transaction from inside the begin event.)
        conn.exec_driver_sql(_APPLY_SQL, (ctx.role, ctx.tenant_id, ctx.user_id))
 
 
async def bind_identity(conn: AsyncConnection, ctx: DbIdentity) -> None:
    """Bind ctx to this checkout: applies to this and every later transaction."""
    await conn.execution_options(**{IDENTITY_KEY: ctx})
    if conn.in_transaction():
        # The option only applies from the *next* begin; cover the open one too.
        await conn.exec_driver_sql(_APPLY_SQL, (ctx.role, ctx.tenant_id, ctx.user_id))

Each setting is transaction-scoped (true as the last argument), so nothing outlives the transaction and nothing can leak through a shared backend. Events are synchronous, so for an async engine you install the hook on engine.sync_engine; async SQLAlchemy runs it inside a greenlet (a lightweight coroutine it uses to bridge sync and async code), so running SQL there is safe.

A FastAPI dependency binds the tenant once per request:

async def get_db(user = Depends(current_user)):
    async with engine.connect() as conn:
        await bind_identity(conn, DbIdentity(tenant_id=user.tenant_id, user_id=user.id))
        yield conn

Route handlers don't change. They call conn.execute(...) and conn.commit() as before. After a commit, the next query autobegins a new transaction, the begin event fires, and the tenant is applied again, on whichever backend PgBouncer hands out.

A nice side effect: if nothing survives a transaction, there's nothing to wipe. The pool reset hook from Step 2 can be deleted. The best fixes remove code.

Two details that decide whether it's safe

Store the context on the checkout, not the pooled connection. Connection.info looks like a convenient place, but it belongs to the underlying pooled connection and outlives your checkout, so the next request inherits your tenant. execution_options on the Connection you checked out live only as long as that checkout.

Don't use a bare engine.begin() for tenant-scoped work. It starts the transaction before you can attach the identity, so the hook fires with nothing to apply and the block runs unscoped, silently. Offer a helper instead, and forbid engine.begin() in application code with a test or lint rule:

@asynccontextmanager
async def scoped_transaction(engine: AsyncEngine, ctx: DbIdentity):
    async with engine.connect() as conn:
        await bind_identity(conn, ctx)
        async with conn.begin():
            yield conn

Step 6: Fix prepared statements

The second failure from Step 4 looks like this:

ERROR: prepared statement "_pg3_0" does not exist

A prepared statement is a query Postgres has parsed and planned once, then stored by name so later runs skip that work. It's stored on one specific backend. psycopg 3 prepares a statement automatically after running it 5 times (prepare_threshold). Under transaction mode, the sixth run can land on a different backend that has never seen it. It fails only sometimes, which makes it hard to reproduce.

Two ways out:

• Turn it off in the driver: set prepare_threshold=None in the connect args. Prove it with a test that runs one statement 7 times and asserts pg_prepared_statements stays empty.
• Let PgBouncer handle it: PgBouncer 1.21+ can track prepared statements and replay them onto whichever backend it hands out. It needs max_prepared_statements above 0, which wasn't the default before 1.24. On a managed service, check with SHOW CONFIG.

One surprise worth knowing: tools like sqlcommenter add a trace ID comment to every query so database logs link back to traces. That makes every query's text unique, and prepared-statement caches key on the exact text, so nothing is ever reused. Test this under the pooler rather than trusting defaults.

Step 7: Go green, then go live

Run the PgBouncer lane again. Besides the two failures above, a lane like this tends to flush out smaller things a direct connection never shows:

• Test helpers that hand-roll their own connections with session-level settings. Those settings leak onto shared backends and break unrelated tests. Replace them with the real connection helpers.
• Assertions that only hold on a fresh backend. A setting nobody has set is undefined on a new backend but '' on a reused one. Treat both as "unset", in your tests and in your RLS policies.

When the lane is green, roll out in order:

1. Make RLS policies treat '' and unset the same, and fail closed. Give migrations and scripts an explicit system identity.
2. Install the begin hook on every engine (API, background jobs, scripts) and switch to transaction-scoped settings. This is safe even before PgBouncer arrives.
3. Fix prepared statements, on the client or the pooler.
4. Point one service at PgBouncer in transaction mode. Watch it. Then the next.

Running the suite twice costs CI time. If the lane can't run on every pull request, run it nightly, so it keeps guarding the invariant after the migration is done.

Step 8: Spend the headroom

Connections are now cheap on both sides: the app checks one out of its pool in about a millisecond, and PgBouncer keeps Postgres from paying for a backend per client. That unlocks something the old setup made expensive: running independent queries at the same time.

Go back to the trace from Step 1. The queries ran one after another, but do they need to? Draw what depends on what:

plan_level ──┬──> recent items
             ├──> totals
             └──> label counts

plan_level decides what the caller may see, so it goes first. The other three don't depend on each other. They ran in sequence only because the code was written top to bottom. Draw the dependency graph before you parallelise anything.

Python's asyncio runs many tasks on one thread: while one task waits on the network, another runs. Queries spend most of their time waiting, so they overlap well. There's one constraint: a connection runs one query at a time, so each concurrent query needs its own connection. Before pooling, that meant a handshake per query. Now it's nearly free, and the begin hook from Step 5 scopes each connection to the tenant on its own, whichever backend PgBouncer hands it.

asyncio.TaskGroup (Python 3.11+) starts several tasks and waits for all of them before the block exits:

import asyncio
 
@asynccontextmanager
async def scoped_connection(ctx: DbIdentity):
    async with engine.connect() as conn:
        await bind_identity(conn, ctx)
        yield conn
 
 
async def _with_conn(ctx, fn, *args):
    # Each concurrent query gets its own pooled connection, scoped to the tenant.
    async with scoped_connection(ctx) as conn:
        return await fn(conn, *args)
 
 
async def get_summary(ctx: DbIdentity) -> Summary:
    plan = await _with_conn(ctx, load_plan_level)    # gates the rest
 
    async with asyncio.TaskGroup() as tg:
        recent = tg.create_task(_with_conn(ctx, load_recent_items, plan))
        totals = tg.create_task(_with_conn(ctx, load_totals, plan))
        labels = tg.create_task(_with_conn(ctx, load_label_counts, plan))
 
    return Summary(recent.result(), totals.result(), labels.result())

The cheap queries now overlap the expensive one instead of queueing behind it. Database time drops from the sum of the queries to roughly the slowest one.

Why TaskGroup and not asyncio.gather? If one task fails, gather raises the error but lets the others keep running, still holding connections and doing work nobody will read. Under load, one failing dependency can drain your pool with orphaned queries. TaskGroup cancels the remaining tasks and waits for them to stop, so their connections go back to the pool. (It raises an ExceptionGroup, one exception that bundles every failure, so unwrap errors your framework treats specially, like FastAPI's HTTPException, with except*.)

One last bit of arithmetic: a request that runs three queries in parallel holds three connections at once, so size the app's pool for that.

Working with an AI agent

Agents are good at the plumbing here: test containers, engine config, finding every call site. They're weaker at judging whether a change is safe, because that depends on tenancy rules that are easy to miss. Let the agent do the inventory and typing; keep the reasoning yourself.

Ask for an inventory before any change:

Find every place this codebase sets Postgres session settings (set_config, SET, SET LOCAL, connect events, pool hooks). Count route handlers that commit before the request ends. Report numbers and file paths. Don't change code.

State the invariant, not the implementation:

Invariant: every database transaction starts with the tenant context of the request that owns the connection, including transactions that autobegin after a mid-request commit. Constraints: no changes to route handlers; context is never stored on anything that outlives a pool checkout. Propose two implementations with trade-offs.

If the plan uses Connection.info or a bare engine.begin(), push back with the reason. Both show up often in generated code.

Then let it build the test lane, and read every failure yourself:

Add an opt-in pytest lane that starts PgBouncer in a container with pool_mode=transaction and default_pool_size=2, authenticates the restricted test role (never the superuser), and points all database fixtures at it. Run the suite both ways and summarise failures that only happen through the pooler.

Finally, write the rules down in CLAUDE.md or a project skill so future sessions keep them:

## Database access
- API server engines are pooled; serverless functions and scripts use NullPool.
- Scoped work: `scoped_transaction(engine, ctx)` or a request dependency. Never `engine.begin()`.
- Tenant context lives in `execution_options`, never `Connection.info`.
- Never set myapp.* settings at session scope.
- Concurrent queries: one connection per task, under asyncio.TaskGroup, never bare gather.

Checklist

• Traces include connection spans, so you can see connection cost.
• Long-lived servers use a pool; short-lived processes use NullPool.
• Total connections fit within max_connections, or PgBouncer sits in front.
• Tenant context is applied per transaction by a hook, stored in execution_options.
• engine.begin() is banned for tenant-scoped work.
• RLS policies treat '' and unset the same, and fail closed.
• Prepared statements are handled on the client or the pooler, and tested.
• A test lane runs through a real PgBouncer with a tiny pool and a non-superuser role.
• Independent queries run concurrently, one scoped connection each, under TaskGroup.

Takeaways

Measure before you scale. Connection setup is invisible in SQL and obvious in a trace.

Sharing is the whole point of pooling, and the whole risk. Whatever you leave on a shared connection, the next user gets.

Under transaction pooling, the transaction is the only thing you own. Put everything that matters inside it.

Make the safe path the only path. A hook that runs on every transaction beats a rule every developer has to remember.

Test with the real thing, at its most hostile. A pool of 2 finds in minutes what production would find at 3 a.m.

Cheap connections are a budget, so spend it. Independent queries can run side by side once each one has its own safely scoped connection.

When a fix depends on everyone remembering something, look for the one place where you can make forgetting impossible.