Connection pooling

A connection pool keeps a small set of open database connections and lends them to requests, so each request skips the expensive setup and the database never runs more work at once than it can handle. Borrowing is the easy part. The hard parts are the size, where the pool lives (in every app process or in a proxy such as PgBouncer or RDS Proxy), and making sure a slow database can’t turn the pool into a queue that takes your service down.

Picture it first: the clinic and its doctors

A clinic has eight consulting rooms. In the first version, every patient brings their own doctor, who must be signed in and badge-checked before a five-minute consultation; the paperwork takes longer than the visit. On a busy morning 200 doctors crowd into eight rooms and every patient is seen more slowly.

In the second version the clinic hires eight doctors once. Patients take a token and wait in the lobby; when a doctor is free, the next token goes in. If the wait passes 20 minutes, reception books you for later rather than letting the lobby fill up to the street.

Map it back: doctors are connections, rooms are the database’s CPU cores, the sign-in and badge check are TCP, TLS, authentication and a new process, the token counter is the pool’s queue, and “come back later” is the checkout timeout. A doctor who stays in a room through a two-hour lunch with a patient’s file open is a session left idle in transaction.

Why a connection is expensive

A process or a thread on the server. PostgreSQL uses a “process per user” model: a supervisor process, the postmaster, spawns a new backend process for every connection, and that backend serves only that client. MySQL uses a thread per connection by default (thread_handling reads one-thread-per-connection on MySQL 8.4) and keeps a thread cache to avoid creating threads; its manual warns that thread creation, per-thread stack space and scheduling overhead grow with the number of connections.

Six round trips before the first query. Following PostgreSQL’s message flow for a TLS connection with password authentication:

1. TCP handshake.
2. SSLRequest, answered with a single byte: S for yes.
3. TLS 1.3 handshake.
4. StartupMessage; the server replies that it wants SCRAM.
5. SCRAM client-first message; server-first reply.
6. SCRAM proof; the server replies AuthenticationOk and ReadyForQuery.

Only then can the query go, as round trip seven. With app and database in one zone at about 1 ms per round trip that is about 6 ms of pure setup, before the work at each end: the server forks a process, and the client runs SCRAM’s key derivation, 4,096 hash iterations under PostgreSQL 18’s default scram_iterations. Put the database in another region at 60 ms and setup costs 360 ms. Andres Freund’s analysis of PostgreSQL connection costs ranks them “TLS, latency, and Postgres costs, in that order”.

Memory. Each backend has private memory: under 2 MiB per connection in many workloads with huge pages configured, by Freund’s measurements, which is fine for hundreds. The bigger risk is query memory: work_mem (4 MB by default) applies per sort or hash operation, so 500 connections each running a report with two hash joins at 64 MB could ask for 500 × 2 × 64 MB = 64 GB at once (query execution plans).

max_connections is a ceiling, not a dial

PostgreSQL’s max_connections is typically 100 out of the box, can only change at server start, and sizes shared memory and other resources directly. A few slots are held back for superusers, so ordinary roles hit the wall a little early. On PostgreSQL 18 with defaults, the 98th application connection failed with SQLSTATE 53300:

FATAL:  remaining connection slots are reserved for roles with the SUPERUSER attribute

Raising the limit to 5,000 treats the symptom and worsens the disease, for three reasons:

• Cores don’t grow with connections. A 16-core server runs 16 queries at any instant. With 400 queries in flight, 384 wait inside the database, holding memory, locks and snapshots, and throughput doesn’t rise.
• Idle connections aren’t free. Freund showed that the setting itself costs little, but many established connections do: with thousands of idle connections, a single active connection ran more than 2× slower. The cause was snapshot building (GetSnapshotData), which every transaction does and whose cost grew with the number of connections. Work that landed in PostgreSQL 14 targeted exactly this, but an established connection is still never entirely free.
• Context switches. With a process per connection, more connections mean more switching between processes and colder CPU caches.

Treat max_connections as a safety ceiling: the sum of every pool that can connect, plus admin, replication and monitoring slots, plus headroom. Control the actual concurrency with pools.

What a pool does

A pool owns N open connections. Code borrows one, runs its queries, and returns it, usually when it calls close() on what is really a wrapper. If all N are busy, the caller waits in a queue; if the wait exceeds the checkout timeout, it gets an error instead of a connection. Behind the scenes the pool tests idle connections, retires old ones and replaces broken ones.

That gives you two wins. Amortised setup: the six round trips happen once per connection, not per request. And, more important, admission control: the pool size caps how much work the database gets at once, and the queue in front is where you apply backpressure. Creating a new pool, or a new connection, per request throws both away; it’s the improper instantiation anti-pattern.

App-side pools: HikariCP

The usual Java pool is HikariCP; most languages have an equivalent, and the settings map across. Its defaults, and what to do with them:

# HikariCP, per application instance (Java properties don't allow trailing comments)
maximumPoolSize=10
minimumIdle=10
# wait at most 1 s for a free connection, then fail fast
connectionTimeout=1000
# 29 minutes: below a 30-minute proxy idle limit
maxLifetime=1740000
leakDetectionThreshold=20000

One trap is pool-locking: if every thread holds one connection and waits for a second, the pool deadlocks. HikariCP’s wiki gives the minimum safe size as threads × (connections per thread − 1) + 1, so 8 threads needing 3 each need 17. Better: never hold two connections from one pool.

The multiplication problem

An app-side pool lives inside one process. At dinner peak a food delivery app runs 200 pods of its orders service, each with a pool of 20: 200 × 20 = 4,000 connections, 40 times PostgreSQL’s default limit. Autoscaling makes it worse, because pods are added exactly when the database is busiest. Shrinking every pool to 1 or 2 leaves each pod unable to absorb a burst, and the total still grows with every pod you add. The fix is a pooler shared by all the pods.

PgBouncer and transaction pooling

PgBouncer is a lightweight proxy that speaks PostgreSQL’s protocol. Apps connect to it as if it were the database, at about 2 kB of memory per client connection, and it keeps a pool of real server connections for each user and database pair (default_pool_size 20; max_client_conn defaults to 100, so raise it). Its pool_mode decides when a server connection goes back to the pool:

Transaction mode solves the multiplication problem because most web connections sit idle between transactions. The price: a client’s next transaction may run on a different server connection, so anything that lives in the session rather than the transaction breaks. PgBouncer’s own compatibility table lists these as never working in transaction mode:

• SET and RESET: the setting stays on whichever server ran it and leaks to the next client.
• LISTEN, cursors declared WITH HOLD, and SQL-level PREPARE / DEALLOCATE.
• Temp tables with ON COMMIT PRESERVE ROWS or DELETE ROWS, and the LOAD statement.
• Session-level advisory locks such as pg_advisory_lock.

Several things do work: NOTIFY, ON COMMIT DROP temp tables, startup parameters such as TimeZone and application_name (PgBouncer tracks them), and the protocol-level prepared statements most drivers use, supported since PgBouncer 1.21 through max_prepared_statements and on by default (200 per connection) since 1.24.

The usual rewrites: SET LOCAL inside the transaction instead of SET; per-role defaults with ALTER ROLE … SET, which apply when a server connection logs in as that role and so survive transaction pooling; transaction-level advisory locks (pg_advisory_xact_lock); and a separate session-mode pool, or a direct connection, for LISTEN, migrations and long admin sessions.

RDS Proxy and serverless connection storms

Serverless functions make the multiplication problem acute. AWS Lambda gives every concurrent request its own execution environment, so 1,000 concurrent invocations can mean 1,000 connections, opened during the very burst that made it scale out. Open the connection outside the handler so a warm environment reuses it, keep one per environment, and put a pooler between the functions and the database (serverless architecture).

Amazon RDS Proxy is a managed one. Like PgBouncer in transaction mode, it multiplexes a client’s transactions over different database connections. Unlike PgBouncer, when it sees session state it doesn’t let it leak: it pins that client to one database connection until the client disconnects, which is safe but quietly removes the benefit. For PostgreSQL, SET, SQL-level prepared statements, temporary tables, cursors, LISTEN, session advisory locks, even nextval, and any statement over 16 KB all pin; so does a pool library that resets connections with DISCARD ALL. Watch the DatabaseConnectionsCurrentlySessionPinned metric, and move identical SETs into the proxy’s initialization query.

Sizing the pool with Little’s law

Little’s law says the average number of items in a system is the arrival rate times the average time each spends inside: L = λ × W. For a pool, that is busy connections = queries per second × average hold time:

• 5,000 queries/s × 4 ms = 20 busy connections on average.
• Add headroom for bursts and slow outliers, say 1.5×: about 30 server connections, in total, across every app instance. With PgBouncer in front, that is its pool size. Without one, 30 connections spread over 200 pods is 0.15 per pod, which no app-side pool can be: that is why app-side pools alone stop working at scale.

The trap is the word hold. A connection is held from checkout to return, not just while SQL runs: network round trips, the app’s work between statements, and anything else the request does inside a transaction all count. A handler that borrows at the top and returns at the bottom holds the connection for its full 50 ms even if its SQL takes 5 ms, so at 1,000 requests/s it needs 50 connections instead of 5. Borrow late, return early, and never call another service while holding a connection. This is also why an N+1 query pattern exhausts pools: it multiplies hold time.

Then cross-check against the hardware. HikariCP’s wiki quotes a starting point from the PostgreSQL project: connections = (core_count × 2) + effective_spindle_count, with spindles counting as zero when the working set is cached, so about 32 for a 16-core server with hot data. It is a starting point for load tests, not a law: Freund found that adding 1 ms of network delay and 1 ms of client processing moved peak throughput from about 48 clients to about 3,000, because each connection then sat idle most of the time. If Little’s law asks for far more than the core formula, shorten the hold time first.

A smaller pool often gives lower latency. In the Oracle demonstration the HikariCP wiki describes, cutting a pool from 2,048 connections to 96, with no other change, took response times from about 100 ms to about 2 ms. The queueing moves out of the database, where waiting work fights for CPU, locks and cache, into the pool, where it costs only time and a timeout can bound it.

Give mixed workloads separate pools: in a shared one, a burst of 30-second reports starves 3 ms checkout queries. HikariCP’s wiki suggests one pool for long-running jobs and one for real-time queries, the same idea as bulkheads.

When pools fail

Pool exhaustion cascades. Take one pod at dinner peak: 500 requests/s, each running one query, a pool of 20, 200 request threads, and HikariCP’s default 30-second connectionTimeout. Normally each query holds a connection for 4 ms, so the pool runs about 2 busy. Then a missing index or a lock makes queries take 400 ms:

The arithmetic: at 400 ms per query the pool serves at most 20 ÷ 0.4 = 50 requests/s, so 450 a second join the queue, and the 200 threads are all blocked within 200 ÷ 450 ≈ 0.44 s. The defences are a short checkout timeout so requests fail fast with a 503, a statement_timeout so slow queries give their connections back, a circuit breaker in front of the database, health checks that don’t need a database connection, and retries with backoff and jitter so clients don’t start a retry storm.

Idle in transaction. Code opens a transaction, runs an UPDATE, then calls a payment API that takes 20 seconds. The connection sits in state idle in transaction, holding its row locks and a snapshot. The PostgreSQL manual warns that an open transaction also stops vacuum from removing dead rows, so the table bloats (vacuum and bloat). Set idle_in_transaction_session_timeout, which terminates such sessions, and never do network I/O inside a transaction.

Leaks. A path that borrows and never returns, typically on an exception, shrinks the pool by one each time until every request times out. Use try-with-resources or a context manager, and turn on leak detection.

Failover churn. After a failover, every pooled connection still points at the old primary and fails on its next use, and client DNS caches may keep resolving the old address. Pools must evict broken connections quickly, and maxLifetime bounds how long any connection stays on one host. With a pooler you re-point PgBouncer once instead of restarting 200 pods, which is how Notion cut over during its re-shard (sharding a relational database).

Connection storms. A full redeploy or a database restart makes every pod reconnect at once: 200 × 20 = 4,000 forks and handshakes in a few seconds, when the database is coldest. Roll deploys gradually; with a pooler in front, only its 40 server connections need rebuilding.

Timeouts at every layer

Each layer should give up before the layer above it does, so the innermost failure is the one that surfaces:

The PostgreSQL manual advises against setting statement_timeout in postgresql.conf, because it hits every session. Set it per role instead, which also survives transaction pooling:

ALTER ROLE orders_app SET statement_timeout = '2s';
ALTER ROLE orders_app SET lock_timeout = '1s';
ALTER ROLE orders_app SET idle_in_transaction_session_timeout = '15s';

Leave idle_session_timeout off for pooled roles: idle connections are the point of a pool, and the manual warns that poolers may not react well to the server closing them.

Replicas and shards

Give each target its own pool. A primary pool and a replica pool should be sized separately, from the write and read rates each actually serves; reads that must see the user’s own latest write go to the primary pool (data replication).

Sharding multiplies connections again, because every app instance or pooler needs a pool per shard: 200 pods × 16 shards × 5 connections is 16,000 connections. Size each shard’s pool from that shard’s own query rate. Notion’s re-shard hit this wall: about 100 PgBouncer instances, each allowed 6 connections per shard, already meant 600 per database before the new shard map multiplied them.

What to watch

• Pool metrics in the app: active, idle and pending (threads waiting for a connection), checkout wait time at p99, checkout timeouts, and new connections per second (churn).
• PgBouncer SHOW POOLS: cl_waiting (clients that sent a query but have no server yet) and maxwait, the oldest client’s wait. PgBouncer’s docs say it plainly: if maxwait climbs, the server is overloaded or the pool is too small.
• PostgreSQL pg_stat_activity by state: many idle connections mean oversized pools, any long idle in transaction is an app bug, and active well above the core count means queries are queueing inside the database.
• Limits and errors: connections against max_connections, the rate of 53300 errors, and for RDS Proxy the pinned-connection metric.

A quick look at the server side:

SELECT state, count(*) AS conns,
       max(now() - state_change) AS longest_in_state
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state;

Cheat sheet

How to say it in the interview

“A PostgreSQL connection is a forked process plus six round trips of TCP, TLS and SCRAM, so I never connect per request. Each instance gets a small fixed-size pool, and with a couple of hundred pods I’d put PgBouncer in transaction mode in front, so thousands of client connections share a few dozen server connections. Little’s law sizes it: 5,000 queries a second at 4 ms is 20 busy connections, about 30 with headroom, which matches twice the cores of a 16-core primary. A one-second checkout timeout and a two-second statement_timeout make a slow database fail fast instead of piling up.”

Why not just raise max_connections to 5,000? The server still has the same cores, so extra connections only queue inside it, each with its own process. Thousands of established connections also make every snapshot more expensive, so even useful queries slow down.

How big should the pool be? Queries/s × hold time, plus headroom, across all instances; compare with (cores × 2) + spindles and load-test around it. If you need far more, shorten hold times instead.

We switched PgBouncer to transaction mode and things broke. Why? Session state. A SET, a LISTEN, a session advisory lock or an SQL-level PREPARE lives on one server connection, and your next transaction may run on another. Use SET LOCAL, per-role settings, transaction-level advisory locks and protocol-level prepared statements, and send LISTEN and migrations through a session-mode pool.

How do you connect from Lambda? Open the connection outside the handler, keep one per environment, and put RDS Proxy or PgBouncer in between so a burst becomes cheap proxy connections, not new backends. Then check that session state isn’t pinning them.

What happens when the database slows down? Hold time rises, the pool drains and requests queue; with a 30-second checkout timeout, threads run out in under a second. A short checkout timeout, a statement timeout and a circuit breaker turn that into fast, visible errors.

Where to go next

• Database scaling: where pooling sits on the ladder, before caches and replicas.
• The N+1 query problem: the most common cause of long hold times.
• Query execution plans: making queries short enough that a small pool suffices, and work_mem.
• Timeouts, retries and backoff with jitter: choosing deadlines that nest.
• Serverless architecture: why functions need a proxy in front of a relational database.
• Improper instantiation: creating pools and clients per request.
• Backpressure and load shedding: the pool queue as admission control.
• Sharding a relational database: connection fan-out across shards and poolers.

References and further reading

1. PostgreSQL documentation: How Connections Are Establishedpostgresql.orgThe process-per-user model: the postmaster spawns a new backend process for every connection.
2. PostgreSQL documentation: Connections and Authenticationpostgresql.orgmax_connections (typically 100), resources sized from it, and the slots reserved for superusers.
3. PostgreSQL documentation: Client Connection Defaultspostgresql.orgstatement_timeout, lock_timeout, idle_in_transaction_session_timeout, transaction_timeout, and the warning about idle_session_timeout behind poolers.
4. PostgreSQL documentation: Message Flowpostgresql.orgSSLRequest, StartupMessage and the SASL exchange that happen before the first query.
5. Analyzing the Limits of Connection Scalability in Postgres (Andres Freund, Microsoft, 2020)techcommunity.microsoft.comPer-connection memory, idle connections slowing active ones through snapshot building, context switches, and how latency shifts the best connection count.
6. HikariCP README: configurationgithub.comDefaults for maximumPoolSize, minimumIdle, connectionTimeout, idleTimeout, maxLifetime, keepaliveTime and leakDetectionThreshold.
7. HikariCP wiki: About Pool Sizinggithub.comThe (core_count × 2) + effective_spindle_count starting point, the 2,048-to-96 connection demo, pool-locking arithmetic and separate pools.
8. PgBouncer: Featurespgbouncer.orgSession, transaction and statement pooling, the feature compatibility table, and about 2 kB per client connection.
9. PgBouncer: Configurationpgbouncer.orgdefault_pool_size, max_client_conn, max_prepared_statements, query_wait_timeout and server_lifetime.
10. Amazon RDS Proxy: Avoiding pinningdocs.aws.amazon.comTransaction-level multiplexing, what pins a session for PostgreSQL and MySQL, and the pinning metric.