Connection Pooling: PgBouncer, RDS Proxy & Serverless

Q: What is database connection pooling and why does Postgres need it?

Connection pooling is keeping a small, warm set of database connections and lending them to many clients, instead of letting every client open its own. Postgres needs it more than most databases because of its architecture: every connection is a full operating-system process, holding roughly 1 to 10 MB of memory and a slot in the process table, and participating in scheduler and lock-manager coordination. That design gives excellent isolation, but it means connections are expensive to open (a full process fork plus authentication, tens of milliseconds) and expensive to keep (memory plus coordination overhead that grows faster than linearly). A serious server is comfortable with a few hundred direct connections, and its true concurrent query capacity is closer to a few multiples of its core count — everything beyond that is idle processes making the scheduler slower. Meanwhile the demand side multiplies: every app instance brings its own ORM pool, every background worker its own connections, every serverless function potentially one more. Twenty instances with a default pool of 20 is 400 connections before any burst. A pooler collapses that multiplication: thousands of client connections in, a few dozen server connections out, with clients queueing briefly for a lease instead of the database thrashing. The result is counterintuitive but consistent: fewer connections, higher throughput, calmer p99s.

Q: What is the difference between session, transaction, and statement pooling in PgBouncer?

The mode decides when a server connection is returned to the pool, which decides both how much multiplexing you get and what application patterns break. Session mode assigns a server connection for the client's entire session — safest, fully compatible with every Postgres feature, and weakest, because an idle client still hogs a server connection; you gain little beyond faster connection setup. Transaction mode returns the connection after each transaction — this is where the 10-to-100x multiplexing lives, because most applications hold connections idle between transactions (a request that computes for 80 ms and queries for 8 ms occupies a server connection only for the 8). The cost: anything that assumes session continuity breaks, since consecutive transactions may run on different server connections. That means session-level SET commands (leaking settings to unrelated clients), advisory locks held across transactions, LISTEN/NOTIFY subscriptions, and protocol-level prepared statements — though modern PgBouncer can track prepared statements across connections via max_prepared_statements, and most ORMs ship pooler-compatible modes. Statement mode returns the connection after every single statement, which breaks multi-statement transactions entirely and exists for special read-only workloads. The production default is transaction mode after a codebase audit for the four session-state patterns; the audit is an afternoon, and skipping it converts the migration into a series of intermittent night-time incidents.

Q: Why does raising max_connections make Postgres slower?

Because max_connections is a protective ceiling, not a capacity setting, and raising it converts a loud error into silent degradation. Each allowed connection is a potential process consuming memory (work_mem is per-operation, per-connection — 500 connections each entitled to a few work_mem allocations can theoretically demand more RAM than the machine has), and each active process joins scheduler queues, snapshot computation, and lock-manager coordination whose costs grow with the number of participants, not with useful work. Past a few hundred connections on typical hardware, the database spends an increasing share of its time context-switching between processes rather than executing queries: throughput flattens, then falls, while p99 latency climbs — the classic symptom is a busier-feeling database at lower CPU efficiency. We have watched a max_connections raise from 100 to 5,000 triple p99 latency while throughput dropped, with the box at 40% CPU. The error the raise was meant to silence — "sorry, too many clients already" — was the honest signal that connection demand exceeded what the machine should hold; the correct response is multiplexing that demand through a pooler so the database sees only a small working set. The rule that survives production: max_connections stays modest (a few hundred), the pooler absorbs the fleet, and any urge to raise the ceiling is treated as a symptom of missing pooling, a connection leak, or idle-in-transaction sessions.

Q: How should I size a connection pool?

Smaller than intuition says, on both sides. The server-side pool (pooler to database) should start at cores times two to four: a 16-core database rarely benefits from more than 50 or 60 active server connections, because queries beyond the hardware's true concurrency just queue inside the database at higher cost than queueing outside it. Brief waits in the pooler are cheaper than scheduler thrash in Postgres — cutting an oversized default_pool_size from 200 to 40 has raised throughput by a third on unchanged hardware in our audits. Disk-bound workloads can run somewhat wider (waiting on I/O parks processes cheaply); CPU-bound OLTP should stay near the low end. The client-side arithmetic is about the multiplication: fleet size times per-instance pool must stay under the pooler's client capacity comfortably, and if there is no pooler, under the database's comfort line — which is the number nobody computes until the incident. Per-instance pools of 5 to 10 serve typical API services; serverless functions hold exactly 1. Then measure rather than believe: pool checkout wait time (rising waits mean the pool is too small or queries too slow), database-side active versus idle connection counts (idle-heavy means pools are too big), and p99 latency as you tune. The failure mode in both directions is invisible without those numbers — too small shows up as artificial queueing, too big as mysterious latency with idle CPU.

Q: How do I connect AWS Lambda to Postgres without exhausting connections?

Treat Lambda as the pathological database client it is: thousands of short-lived, independently scaling instances, each wanting a connection for 50 milliseconds of work. Left alone, a concurrency spike of 1,000 functions is 1,000 connection attempts — the exact shape max_connections exists to refuse. The pattern that works has three parts. First, per-function connection count of exactly 1: a pool inside a Lambda is a trap, because the pool multiplies by concurrency (a pool of 10 at 500 concurrent executions is 5,000 demanded connections); initialize a single connection outside the handler so warm invocations reuse it. Second, a transaction-mode pooler between the fleet and the database — RDS Proxy on AWS (managed, IAM-auth-capable, failover-aware, priced around $11 per vCPU per month), or the platform pooler if you are on Supabase (Supavisor) or Neon, where pooled versus direct is just a different connection string. The pooler collapses 1,000 nominal clients into a few dozen warm server connections. Third, timeouts everywhere: connect timeouts so spikes fail fast rather than pile up, and statement timeouts so one slow query cannot occupy the pool. The complementary architectural move: much Lambda work touching the database is queue-shaped anyway — routing writes through a queue with a small worker fleet caps database concurrency by design instead of by hope.

Q: What is idle-in-transaction and why is it dangerous?

An idle-in-transaction session is a connection that has opened a transaction (BEGIN) and is doing nothing — the application is off computing, calling an external API, or waiting on a user, while the database holds the transaction open. It is the most expensive kind of idle. The session pins its server connection in every pooling mode (the pooler cannot reclaim a connection mid-transaction), so it silently shrinks your effective pool; a handful of handlers that call a 3-second payment API inside an open transaction can hold half the pool hostage at peak. Worse, an open transaction pins the database's vacuum horizon: Postgres cannot clean up row versions that the old transaction might still need, so long idle-in-transaction sessions cause table and index bloat that degrades everyone's queries, not just the offender's. And any locks the transaction took are held for the full duration, turning a slow external call into lock queues for unrelated requests. The fixes, in order: restructure the code so external calls happen outside transactions — do the slow work first, then open a short transaction to write results (with idempotency protection for retries); set idle_in_transaction_session_timeout as the backstop so the database kills offenders (start around 30-60 seconds for OLTP); and monitor pg_stat_activity for idle-in-transaction sessions as a standing alert, because new offenders arrive with every feature that touches an external service.

Q: Do I need PgBouncer if my ORM already has a connection pool?

Almost certainly yes, once you run more than a couple of instances — because the ORM pool and the external pooler solve different halves of the problem. An application-side pool (HikariCP, Prisma, SQLAlchemy, ActiveRecord) polices one process: it reuses connections across requests within that instance and caps that instance's demand. What it cannot do is see the fleet. Every instance brings its own pool, every deployment doubles instances briefly during rollout, autoscaling adds more, background workers bring theirs — and the database receives the sum. Twenty instances with a conservative pool of 10 is 200 connections; a scale-out event or a second service and you are past the comfort line, with each individual pool correctly configured. The external pooler (PgBouncer, RDS Proxy, platform poolers) polices the aggregate: the whole fleet's client connections multiplex onto a small server-side working set, and fleet growth changes queueing at the pooler rather than pressure on the database. The two compose naturally — modest per-instance pools for request-level reuse, one transaction-mode pooler in front of the database for fleet-level multiplexing. One configuration note when stacking: keep the ORM pool small (5-10) and let the pooler do the heavy multiplexing, and make sure ORM-level prepared-statement settings match the pooler's mode. The single-instance monolith with stable concurrency is the one honest case where the ORM pool alone suffices.

Q: When is connection pooling not enough?

When the constraint is work, not connections. A pooler multiplexes access to the database's capacity; it does not add any. The tell-tale signals that you have outgrown what pooling can fix: pool checkout wait times climbing while the database's active connections sit at the sized maximum and CPU or I/O is saturated — the pool is correctly full of genuinely busy connections; queries that are slow even in isolation (that is an indexing and query-plan problem wearing a connection costume — fix the missing index before buying hardware); a working set that no longer fits memory, showing up as read latency the pool cannot influence; or write volume beyond what one primary sustains. Each has its own escalation path, and pooling buys you the calm to take it deliberately: slow queries go to query and index tuning first, since the majority of "we need a bigger database" incidents die there; read-heavy workloads go to replicas with their own pools, and heavy analytics gets routed away from OLTP entirely; big tables with retention pain go to partitioning; and only genuine write-volume or working-set exhaustion justifies sharding, which is an architecture project rather than a configuration change. The diagnostic discipline: with a pooler in place and sized, the pool metrics themselves become your capacity gauge — sustained pool-full with busy connections is a true capacity signal, while pool-full with idle-in-transaction sessions is still an application bug. Without the pooler, both look identical: connection errors.