A database connection is not a cheap handle. Opening one means a TCP connection, usually a TLS handshake, authentication, and a server-side session, and the server then dedicates resources to it for as long as it lives. A connection pool keeps a small set of already-open connections and lends them out per unit of work. Interviewers probe pooling because it is where application code, database capacity and failure modes meet: pool exhaustion is one of the most common real production outages, and "why is the database falling over when we scaled the app to more pods?" is almost always a connection-count question. For a Backend or AI engineer the practical rule is that the database is the scarcest shared resource in the system, and the pool is the valve that decides how much of it any one process may use.
Cost of a brand-new connection (each request opens its own):
App Database server
|---- TCP SYN / SYN-ACK / ACK ---------->| 1.5 round trips
|---- TLS handshake (if enabled) ------->| 1-2 more round trips
|---- startup + authentication --------->| more round trips, password hashing
|<--- session ready --------------------| server allocates per-session state
|---- the actual query ----------------->|
|---- close ------------------------------>| teardown, TIME_WAIT on one side
Server-side, per connection (engine dependent):
PostgreSQL : one OS PROCESS per connection (a backend), own memory
MySQL : one THREAD per connection (thread cache softens the cost)
SQL Server : session on a worker thread from a shared scheduler poolThere are two separate costs. The first is latency: on every request you pay several network round trips before your query even starts, and the fresh TCP connection also starts in slow start (see the Connection Pooling and Keep-Alive page in the Computer Networks section for the transport-level side of this). The second is server capacity: in PostgreSQL each connection is a separate operating-system process, so thousands of mostly idle connections still cost memory and make the planner, lock manager and process scheduler do more work. That is why max_connections is a deliberately small number by default, and why "just raise the limit" is usually the wrong fix. A pool attacks both costs at once: it pays the setup cost once and it puts a hard ceiling on how many server-side sessions exist.
Little's Law: connections in use = arrival rate x time each one is held
N = lambda x W
Illustrative arithmetic (not a benchmark):
500 requests/s x 0.020 s held per request = 10 connections busy on average
500 requests/s x 0.400 s (slow call inside) = 200 connections busy on average
Total pressure on the database is the SUM over all app instances:
20 instances x pool_size 20 = 400 possible server connections
PostgreSQL max_connections = 100 -> the 6th instance already can't connectLittle's Law is the honest way to reason about size: the number of connections you actually need is the request rate times how long each request holds a connection, and the second factor is under your control. Notice what a 400 ms external call inside a connection does to the answer: it multiplies the connections you need by twenty without doing any database work. The opposite mistake is to set the pool large "to be safe". A database has a fixed number of CPU cores and disks; past the point where those are saturated, extra concurrent queries do not run faster, they just fight over CPU, locks and cache, so latency rises for everyone. A common starting heuristic (for example in the HikariCP documentation) is a small multiple of the database's core count, then adjust by measurement. Treat any specific number as a starting point to load-test, not a rule.
Application-side pool (HikariCP, SQLAlchemy, pgx, node-postgres):
[ pod 1: pool 10 ] --\
[ pod 2: pool 10 ] ---+--> PostgreSQL (sees 30 connections, and grows with pods)
[ pod 3: pool 10 ] --/
External pooler (PgBouncer, RDS Proxy, Supavisor):
[ pod 1: 200 client conns ] --\
[ pod 2: 200 client conns ] ---+--> [ PgBouncer ] --> PostgreSQL (fixed ~25 server conns)
[ pod 3: 200 client conns ] --/
PgBouncer modes: when is a server connection bound to a client?
session : for the client's whole session (safest, least sharing)
transaction : only between BEGIN and COMMIT/ROLLBACK (best sharing, breaks session state)
statement : per single statement (no multi-statement transactions)An in-app pool limits one process, but the total still scales with the number of instances, which is exactly what bites during autoscaling and with serverless functions that each open their own connection. An external pooler sits in the middle so thousands of cheap client connections can be multiplexed onto a small, fixed number of real server connections. Transaction mode gives the largest multiplexing gain because a server connection is only tied to a client for the length of a transaction, and most application connections are idle most of the time. The cost is that anything relying on session state can break, because your next transaction may run on a different server connection: session-level SET, temporary tables, session-level advisory locks, LISTEN/NOTIFY, and (depending on the PgBouncer version and configuration) protocol-level prepared statements. Session mode avoids these problems but shares far less, so it mainly helps connection setup cost rather than connection count.
Pool size = 3, every request needs a connection for one transaction:
t0 R1 takes conn A -> BEGIN -> calls payment API (2 s) ...still holding A
t0 R2 takes conn B -> BEGIN -> calls payment API (2 s) ...still holding B
t0 R3 takes conn C -> BEGIN -> calls payment API (2 s) ...still holding C
t1 R4, R5, R6 ... arrive: pool empty, they QUEUE for a connection
t2 wait exceeds the acquire timeout -> requests fail, threads pile up
The database itself is nearly idle. The pool is the bottleneck.
Fix: do the slow work OUTSIDE the transaction.
call payment API (no connection held)
BEGIN; write result; COMMIT (connection held for milliseconds)Exhaustion has a few recurring causes: connections that are never returned (a missing finally or context manager, which pool libraries detect with a "leak detection" threshold), slow queries, a database hiccup that makes every in-flight request take longer so the pool drains faster than it refills, and transactions held open across slow non-database work. The last one is the classic one, and the rule is firm: do not hold a database transaction open across HTTP or payment-API calls. Beyond exhausting the pool, an open transaction holds locks, and in PostgreSQL a long-lived transaction (especially one showing idle in transaction) also stops vacuum from reclaiming dead rows newer than its snapshot. Defences are layered: a short acquire timeout so callers fail fast instead of piling up, server-side statement_timeout and idle_in_transaction_session_timeout, a bounded queue, and separate pools for slow reporting work so it cannot starve the OLTP path.
instances x pool size must stay under max_connections minus a reserve for admin and replication.SET, temp tables, session advisory locks, LISTEN/NOTIFY, and prepared statements depending on version).finally; fail fast is better than a growing queue of blocked threads.acquire(). If an idle connection exists it is handed out immediately; otherwise, if the pool is below its maximum, a new one is opened; otherwise the caller waits in a queue.# Python 3 (standard library only). A toy pool built on queue.Queue.
import queue, sqlite3, threading, time
class PoolTimeout(Exception):
pass
class Pool:
def __init__(self, size):
self._q = queue.Queue()
for i in range(size):
c = sqlite3.connect(":memory:", check_same_thread=False)
self._q.put((i, c))
def acquire(self, timeout):
try:
return self._q.get(timeout=timeout)
except queue.Empty:
raise PoolTimeout(f"no connection free after {timeout}s")
def release(self, item):
self._q.put(item)
# 1. Reuse: 6 sequential requests on a pool of 2 -> only 2 real connections ever exist
pool = Pool(2)
used = []
for _ in range(6):
i, c = pool.acquire(1)
c.execute("SELECT 1")
used.append(i)
pool.release((i, c))
print("connection ids used by 6 requests:", used)
# 2. Exhaustion: two requests hold both connections, a third times out
pool = Pool(2)
a = pool.acquire(1); b = pool.acquire(1)
try:
pool.acquire(0.2)
except PoolTimeout as e:
print("third request:", type(e).__name__, "-", e)
pool.release(a); pool.release(b)
# 3. Holding a connection across a slow external call vs releasing it first
EXTERNAL = 0.2 # pretend payment API latency
def worker_hold(pool):
item = pool.acquire(5)
item[1].execute("SELECT 1")
time.sleep(EXTERNAL) # slow call WHILE holding the connection
pool.release(item)
def worker_release(pool):
item = pool.acquire(5)
item[1].execute("SELECT 1")
pool.release(item) # give it back first
time.sleep(EXTERNAL) # slow call with no connection held
def run(fn, n=8, size=2):
pool = Pool(size)
t0 = time.perf_counter()
ts = [threading.Thread(target=fn, args=(pool,)) for _ in range(n)]
[t.start() for t in ts]; [t.join() for t in ts]
return time.perf_counter() - t0
print("8 requests, pool=2, external call = 0.2s")
print(" connection held during call : %.1fs" % run(worker_hold))
print(" connection released before : %.1fs" % run(worker_release))
# Output (real run; timings rounded to 0.1s, exact values vary slightly by machine):
# connection ids used by 6 requests: [0, 1, 0, 1, 0, 1]
# third request: PoolTimeout - no connection free after 0.2s
# 8 requests, pool=2, external call = 0.2s
# connection held during call : 0.8s
# connection released before : 0.2s
# Why: holding => 8 jobs / 2 slots x 0.2s = 0.8s serial. Releasing => the slow calls overlap.
-- PostgreSQL: see who is holding connections (output depends on your system)
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;
SELECT pid, now() - xact_start AS xact_age, state, left(query, 60)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;
SHOW max_connections;
-- A growing 'idle in transaction' count means application code is holding transactions open.
Q: Why do we use a connection pool at all?
A: Opening a connection costs several network round trips plus authentication and server-side session setup, and the server keeps resources (a process in PostgreSQL, a thread in MySQL) for each live connection. A pool pays the setup cost once and reuses connections, which cuts per-request latency, and it also enforces a ceiling on concurrent server sessions so the database is not overwhelmed. The trade-off is that a bounded pool introduces queueing and a new failure mode, pool exhaustion, that you must monitor and time-limit.
Q: How do you decide the pool size? What is the trade-off between small and large?
A: Start from Little's Law: needed connections is roughly request rate times how long each request holds a connection, then cap it by what the database can actually run in parallel, which is bounded by its cores and disk. Too small a pool makes requests queue in the application; too large a pool lets more queries run concurrently than the hardware can serve, so CPU, lock and cache contention raise latency for everyone and you can exhaust max_connections across instances. The honest answer is a small heuristic starting point followed by load testing, and remembering that total connections is instances times pool size.
Q: PgBouncer transaction mode versus session mode: what is the difference and what breaks?
A: In session mode a client keeps the same server connection for its whole session, so session state works but you get little multiplexing. In transaction mode a server connection is assigned only for the duration of a transaction and then returned, so a few dozen server connections can serve thousands of clients, but consecutive transactions from one client may land on different server connections. That breaks session-scoped state: SET without LOCAL, temporary tables, session-level advisory locks, LISTEN/NOTIFY, and prepared statements unless your PgBouncer version and settings support them.
Q: Your service starts returning "timeout waiting for a connection" while database CPU is low. What is happening and how do you investigate?
A: The pool is exhausted, not the database. Check pool metrics (active, idle, pending, wait time) and pg_stat_activity for many sessions in idle in transaction or sitting on slow queries, and look for code paths that do not release connections or that call external services while holding one. The fixes are to shorten how long connections are held, add acquire and statement timeouts, and isolate slow workloads in their own pool; raising the pool size only helps if the database has headroom and can mask a leak until the next incident.
Q: A checkout handler opens a transaction, calls a payment API, then writes the result. What is wrong with that?
A: The transaction, its locks and its pooled connection stay held for the whole external call, which has unbounded, out-of-your-control latency. Under load this exhausts the pool with a nearly idle database and, in PostgreSQL, a long-open transaction also delays vacuum cleanup. Call the API outside any transaction, then open a short transaction to record the outcome, and use an idempotency key so a retry after a timeout does not charge twice.
Follow-up: Can reducing the pool size ever increase throughput?
A: Yes, when the database is already saturated. Extra concurrent queries just contend for CPU and locks, so each takes longer and total throughput stays flat or drops; a smaller pool queues excess work in the application where waiting is cheap and lets the database run fewer queries at full speed. This depends on the workload, which is why you measure, but it is why "more connections" is not a general performance lever.
Follow-up: You have 30 app instances and PostgreSQL max_connections = 200. How do you size things?
A: Reserve some connections for admin, monitoring, migrations and replication, then divide the rest, so with a reserve of 20 you could allow at most 6 connections per instance, and that ignores autoscaling headroom. If 6 per instance is not enough you introduce PgBouncer (or a managed proxy) so many client connections share a fixed pool of server connections, accepting the session-state limits of transaction mode. Also account for other clients of the same database such as cron jobs and BI tools.
Follow-up: Your app uses prepared statements and you put PgBouncer in transaction mode. Now some queries fail. Why?
A: A prepared statement lives in one server session, but in transaction mode the next execution may be routed to a different server connection that has never seen it, so the statement "does not exist" or the name collides. Options are to use a PgBouncer version and setting that tracks prepared statements, disable server-side prepared statements in the driver, or use session mode for that service. The answer depends on your PgBouncer version and driver, which is worth stating rather than guessing.
Connection limits shape how you scale the whole tier. When you autoscale stateless app servers you multiply the number of pools hitting the same database, so capacity planning has to be done on total connections, not per-pod settings, and this is why serverless designs almost always add an external pooler or a managed proxy in front of the database. Read replicas each have their own connection limits and their own pools, and sharded systems multiply pools by shard count, so the connection budget becomes an explicit part of the design. Pooling also sets your failure behaviour: bounded pools with short acquire timeouts, a queue limit and load shedding turn a slow database into fast, visible errors instead of a cascading pile-up of blocked threads, and bulkheads (separate pools for checkout, reporting and background jobs) keep one noisy workload from starving the critical path. For AI workloads such as RAG APIs that fan out many concurrent retrieval queries or hold connections during slow embedding and LLM calls, the rule from this page applies directly: fetch or write with a short-lived connection, do the slow model call with no connection held.
Pooling trades a little complexity and a new failure mode for lower latency and a protected database. The pool size is a genuine dilemma: too small and you queue in the application, too large and you overload the database, and the right number depends on hold time and hardware, so it must be measured and revisited as the workload changes. An external pooler in transaction mode buys large fan-in but costs you session features and adds another hop and another component to operate and monitor, and it can hide a bad access pattern instead of fixing it. Pooling is the wrong fix when the real problem is slow queries or long transactions, because a bigger pool just admits more concurrent slow work; it is also unnecessary overhead for a single long-lived worker process that needs one connection. The healthy stance is to keep transactions short, size pools conservatively, monitor wait time and set timeouts, and reach for a pooler when total connection count, not per-request cost, is what is hurting.