Postgres connection pooling pitfalls

Connection poolers (PgBouncer, RDS Proxy, serverless Postgres) fix "too many connections" and create a new class of works on my laptop bugs. Most of them involve transactions, prepared statements, and which pool mode you're in.

Session vs transaction pooling

Session mode — one server connection per client for the whole client session. Safest; least multiplexing.

Transaction mode — server connection assigned only for the duration of a transaction. Between transactions, session state (prepared statements, SET, temp tables) is lost or shared wrongly.

Gotcha

ORMs that prepare statements implicitly + PgBouncer transaction pooling = prepared statement "foo" does not exist at 3am under load.

Symptoms under load

timeline

Intermittent `42P05` / missing prepared statement — pool mode mismatch.

`SET search_path` or timezone "randomly" wrong — session state leaked across clients.

Idle-in-transaction timeouts — long requests hold pool slots; everything else queues.

Planner surprises after deploy — new query shapes without updated stats (see [EXPLAIN](/blog/reading-postgres-explain-analyze)).

Practical defaults

App servers → session mode unless you truly understand transaction pooling.
Use PgBouncer pool_mode = session for ORMs; transaction mode for stateless micro-handlers only.
Size pools from Postgres max_connections, not "one pool entry per thread."

SKIP LOCKED workers still need sane pool sizing — a fast dequeue loop with 500 idle connections helps nobody.