PgBouncer vs RDS Proxy for pgvector Connection Pooling

This page is a decision guide for choosing between PgBouncer and RDS Proxy as the server-side pooler in front of a pgvector ingestion pipeline, and for configuring the driver correctly once you pick one. It scopes the problem narrowly: given hundreds of ingestion coroutines that must fan into a bounded set of PostgreSQL backends, when to run PgBouncer in transaction mode, when to lean on managed RDS Proxy, and how each choice forces asyncpg and psycopg settings around server-side prepared statements and session state.

Up: Connection Pooling for pgvector Ingestion Pipelines

A driver-side pool caps one process’s connections; a server-side pooler multiplexes many processes onto a small pool of real PostgreSQL backends, which is the only way a fleet of ingestion workers stays under max_connections. The two dominant choices behave very differently: PgBouncer in pool_mode = transaction gives the highest fan-in but breaks server-side prepared statements and drops session-level SETs between transactions, while RDS Proxy trades some of that fan-in for managed failover, IAM auth, and less operational surface — at the cost of connection pinning that can quietly defeat pooling. Both sit behind the driver pool covered in connection pooling for pgvector ingestion pipelines, so the driver’s own settings must be adjusted to match whichever pooler you place in front.

Prerequisites #

• PostgreSQL 15+ with the pgvector extension 0.5+ installed, reachable either directly or through the pooler endpoint.
• A driver-side pool already sized per sizing asyncpg pools for embedding ingestion — the pooler changes the endpoint and statement settings, not the need to bound the driver.
• For PgBouncer: a host you operate (container or VM) and permission to run it between the app and PostgreSQL.
• For RDS Proxy: an Amazon RDS/Aurora PostgreSQL instance and a Secrets Manager secret or IAM auth configured for the proxy.
• An async ingestion loop whose concurrency is already bounded, per async processing with Python asyncio.

When to pick each #

Pick PgBouncer when you control the host, need the highest possible fan-in (thousands of clients onto tens of backends), and your workload is short transactional writes — exactly the shape of embedding ingestion. Pick RDS Proxy when you run on RDS/Aurora, want managed failover and IAM auth without operating a pooler, and can tolerate slightly lower fan-in and the pinning rules. Neither removes the driver-side pool; both change how the driver must handle prepared statements.

Step-by-step procedure #

1. Choose the multiplexing granularity #

Both poolers can multiplex at session or transaction granularity. Session pooling keeps a backend bound to a client for the whole connection — safe for prepared statements and SETs, but fan-in collapses to roughly the backend count, defeating the purpose. Transaction pooling returns the backend to the pool after every COMMIT, giving the fan-in you came for, at the cost that nothing may assume a stable backend across transactions. Ingestion should use transaction granularity; the rest of this procedure assumes it.

2a. Configure PgBouncer in transaction mode #

Set pool_mode = transaction and size default_pool_size to the number of real backends you want per user/database pair. Because a backend can change between transactions, server-side prepared statements from one transaction are invisible in the next.

# pgbouncer.ini
[databases]
vectors = host=db.internal port=5432 dbname=vectors

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
pool_mode = transaction
default_pool_size = 25          ; real backends per (user, db)
max_client_conn = 2000          ; clients the pooler will accept
server_reset_query =            ; empty in transaction mode

2b. Or point the driver at RDS Proxy #

RDS Proxy exposes a single endpoint; the driver connects to it exactly like a database, with IAM or Secrets Manager auth. RDS Proxy multiplexes at transaction granularity too, so the same prepared-statement rules apply — but it will pin a backend to a client (removing it from the pool) whenever it sees session-altering state it cannot safely reset, such as SET statements, advisory locks, or PREPARE.

# asyncpg connecting through the RDS Proxy endpoint
pool = await asyncpg.create_pool(
    host="proxy.embed.abc123.us-east-1.rds.amazonaws.com",
    port=5432,
    database="vectors",
    user="ingest",
    statement_cache_size=0,      # required behind transaction-mode pooling
    server_settings={"application_name": "embed-ingest"},
)

3. Disable server-side prepared statements in the driver #

This is the step that breaks pipelines silently. Under transaction-mode multiplexing, asyncpg’s implicit prepared-statement cache and psycopg’s auto-prepare will PREPARE on one backend and later EXECUTE on another, raising prepared statement "__asyncpg_stmt_1__" does not exist. Turn both off.

# asyncpg: disable the statement cache entirely
pool = await asyncpg.create_pool(
    dsn="postgresql://ingest@pgbouncer.internal:6432/vectors",
    statement_cache_size=0,               # no server-side prepared stmts
    max_cacheable_statement_size=0,       # belt-and-suspenders
    server_settings={"application_name": "embed-ingest"},
)

# psycopg 3: never auto-prepare behind a transaction-mode pooler
import psycopg
conn = psycopg.connect(
    "host=pgbouncer.internal port=6432 dbname=vectors user=ingest",
    prepare_threshold=None,               # disable server-side prepare
)

4. Register the pgvector codec and avoid persistent session state #

The pgvector codec must still be registered per physical connection, and — critically — you must not rely on a SET persisting across transactions. If a session GUC like SET hnsw.ef_search matters for a query, issue it inside the same transaction as the query, or set it as a server_settings connection parameter so the pooler applies it to every backend. Registering the codec is a driver-local operation (it teaches the client how to encode the value) and is safe behind either pooler.

from pgvector.asyncpg import register_vector

async def _init(conn):
    await register_vector(conn)

# Apply per-query GUCs inside the transaction, not as a bare session SET:
async with pool.acquire() as conn:
    async with conn.transaction():
        await conn.execute("SET LOCAL hnsw.ef_search = 100")   # LOCAL = this txn only
        rows = await conn.fetch(
            "SELECT doc_id FROM doc_chunks ORDER BY embedding <#> $1 LIMIT 10",
            probe_vector,
        )

Parameter reference #

Verification #

Confirm the driver is not leaving server-side prepared statements behind, and that the pooler is actually multiplexing. First, prove prepared statements are off — behind a transaction pooler this query should return zero rows for the ingestion role between statements:

-- run against the backend; a transaction-mode pooler should show no lingering prepared stmts
SELECT name, statement FROM pg_prepared_statements;

For PgBouncer, connect to its admin console and inspect the pools directly — cl_active (clients) should far exceed sv_active (server backends), which is the fan-in working:

-- psql against the PgBouncer admin pseudo-database
SHOW POOLS;
-- columns: database, user, cl_active, cl_waiting, sv_active, sv_idle, pool_mode

For RDS Proxy, watch the DatabaseConnectionsCurrentlyBorrowed and DatabaseConnectionsCurrentlySessionPinned CloudWatch metrics: a rising pinned count means session state is defeating the pool and fan-in is collapsing toward the backend count.

Troubleshooting #

• prepared statement "…" does not exist. The driver is caching server-side prepared statements behind a transaction pooler that moved the EXECUTE to a different backend. Set statement_cache_size=0 (asyncpg) or prepare_threshold=None (psycopg 3), per step 3, and re-check pg_prepared_statements is empty between transactions.
• A session SET silently has no effect on later queries. In transaction mode the backend that ran the SET was returned to the pool, so the next query landed elsewhere. Use SET LOCAL inside the same transaction as the query (step 4), or push the value into server_settings so every backend gets it.
• RDS Proxy pinning defeats pooling — borrowed connections climb toward the backend count. Something triggered session pinning: a bare SET, a PREPARE, an advisory lock, or a LISTEN. Remove the session-scoping construct or scope it with SET LOCAL; watch DatabaseConnectionsCurrentlySessionPinned drop back toward zero.
• prepared statement already exists after a reconnect. A pooled physical connection was recycled onto a backend that still held the earlier statement. Disabling the statement cache (step 3) removes the whole class of failure; if you must keep it, use unique statement names and clear the cache on reconnect.
• Intermittent failures during a failover or DNS change. With PgBouncer, the app reconnected before the pooler’s upstream recovered — add retry-with-backoff around acquire, following async processing with Python asyncio. With RDS Proxy, ensure the driver resolves the proxy endpoint fresh (short DNS TTL) so it follows the promoted writer instead of caching the old IP.

Related #

• Connection Pooling for pgvector Ingestion Pipelines — the pooling stage this decision belongs to
• Sizing asyncpg pools for embedding ingestion — bounding the driver pool that sits in front of either pooler
• Async processing with Python asyncio — retry and backoff around acquire during failover
• Batch upsert patterns for pgvector — the transactional write shape that pools well behind a pooler
• Up: Connection Pooling for pgvector Ingestion Pipelines