Two Pools Are Better Than One: Splitting Our PostgreSQL Connections

Every now and then our API would just stop responding. Not crashes, not errors. Just silence. Requests would hang, then timeout. Turned out our background jobs were the culprit.

Here's what happened and how we fixed it.

What's a Connection Pool Anyway?

PostgreSQL can handle a bunch of simultaneous connections, but each one costs memory and CPU. Opening a new connection from scratch is also slow. TCP handshake, authentication, session setup. Nobody wants that on every request.

So you use a connection pool. Your app pre-opens a set of connections and reuse them. Your app says "I need a connection", the pool hands you one, you do your query, you give it back. Easy.

But here's the thing. Pools have limits. And if you only have, say, 20 connections, and 15 of them are busy doing something slow, your API requests have to wait.

The Problem We Hit

Our services shared a single pool for everything:

• User clicks a button → API request → needs a connection → runs a quick query → done
• Background job starts → connection → runs for 30 seconds doing an export → done

These are fundamentally different workloads. API requests need to be fast. If a user has to wait more than a few seconds, they're gonna reload, hammer the button again, and make things worse. Background jobs don't have that constraint. An export taking 60 seconds is fine.

But here's the thing about pools: they don't know the difference. A long-running background query sits on a connection just the same as a quick one. Under normal load this was fine. But when exports ran during peak traffic, we'd run out of connections. API requests would start queuing up, waiting, timing out. The worst part is it was completely silent. No errors, just waiting.

The Fix: Separate Pools for Separate Concerns

The solution was obvious once we saw the issue: stop mixing workloads. We gave each service two connection pools:

Pool Config

API pool    - HTTP requests         - 15 + 20 overflow - 5s timeout
Worker pool - Background jobs       - 30 + 50 overflow - 60s timeout

The API pool is small and aggressive. 5 second timeout means if a request cannot get a connection quickly, it fails fast. The user sees an error and can retry, which is better than waiting 30 seconds for a timeout.

The worker pool is larger and relaxed. 60 seconds gives long-running jobs like exports and vector indexing time to complete without rushing.

Now when an export is running and eating connections from the worker pool, the API pool is completely untouched. User requests never wait.

How It Works in Practice

Service A (src/core/database.py)

Two async engines, two session getters:

# API requests: fast, fail-fast
async_api_engine → AsyncAPISessionLocal → get_api_db()

# Background jobs: chill, longer timeout
async_worker_engine → AsyncWorkerSessionLocal → get_worker_db()

Every FastAPI route uses get_api_db() as a Depends(). Future Celery tasks will use get_worker_db(). When the app shuts down, shutdown_db() cleans up both engines.

_API_POOL_SIZE = 15, _API_MAX_OVERFLOW = 20, _API_POOL_TIMEOUT = 5
_WORKER_POOL_SIZE = 30, _WORKER_MAX_OVERFLOW = 50, _WORKER_POOL_TIMEOUT = 60

Service B (src/sys/database.py)

This one has both sync and async. Four engines total:

# Sync, for FastAPI Depends()
engine → Session(engine) → get_db_session()
worker_engine → Session(worker_engine) → get_worker_db_session()

# Async, for connector routes
async_session → async_api_engine
async_worker_session → async_worker_engine

Service C (common/db/session.py)

api_engine → get_api_session()     # MCP tool calls
worker_engine → get_worker_session()  # background indexing, cleanup

get_api_session() is aliased as get_session() so existing MCP code doesn't need changes.

One Gotcha: PgBouncer in Transaction Mode

If you're running PgBouncer in transaction mode (which is common for connection pooling at scale), there's a footgun: prepared statements don't work. asyncpg tries to cache query plans per connection, but in transaction mode, connections are recycled between transactions and the cache becomes invalid.

Easy fix. Disable the cache when PgBouncer is in play:

async_connect_args["statement_cache_size"] = 0

We check for PGBOUNCER_TRANSACTION_MODE=true in the environment and set this automatically.

Writing Background Tasks Going Forward

If you're adding a new background job, use the worker pool, not the API pool:

Service A:

from src.core.database import get_worker_db

async def my_background_task():
    async with get_worker_db() as db:
        await db.execute(...)

Service B:

from src.sys.database import get_worker_db_session

def my_background_task():
    with get_worker_db_session() as db:
        db.execute(...)

Service C:

from common.db.session import get_worker_session

def my_background_task():
    with get_worker_session() as session:
        session.execute(...)

If you're modifying existing Celery tasks, those currently use the sync SessionLocal. When the Redis queue layer lands, we'll migrate them to use get_worker_db() too.

What Actually Changed

• API requests stopped timing out during exports
• Background jobs can run as long as they need without affecting users
• Pool recycle every 30 minutes (Services A & B) or 60 minutes (Service C) prevents stale connections from accumulating
• pool_pre_ping=True validates connections before handing them out, so dead connections get recycled before they cause problems

The implementation was straightforward. Just another engine, another session getter. The operational impact was immediate.