Databases
Connection pooling: interview questions and how to answer them
A pool reuses a fixed set of open connections so requests skip the handshake — and the fixed size is a deliberate capacity limit, not a constraint to raise away.
Written and reviewed by Sahil Srivastav
What it actually is
Opening a database connection is expensive: a TCP handshake, TLS negotiation, authentication, and in PostgreSQL the fork of a dedicated backend process with its own memory. A pool opens a set of connections once and lends them out, so a request pays none of that.
The part that matters more in interviews is the second function, which people under-weight: the pool is a concurrency limiter. Its size caps how many database operations can be in flight, and that cap is doing useful work. When demand exceeds it, requests queue — and queueing is the correct behaviour, because the alternative is overwhelming the database and making everything slower for everyone.
The consequence most people get wrong is that a bigger pool is frequently slower. A database's throughput is bounded by cores and disk, not by how many clients are waiting. Past that point, extra connections add context switching, lock contention and memory pressure while adding no capacity — so the queue moves from your application into the database, where it is harder to see and affects every query rather than just the overloaded path.
Why it matters in production
Because pool exhaustion is one of the most common production incidents in backend services, and the instinct it triggers — raise the pool size — is usually wrong and sometimes makes it worse. Diagnosing it correctly means distinguishing three different situations that present identically as "connection is not available".
The three are genuinely different bugs. A leak, where connections are borrowed on a path that throws and never returned: the failure appears some number of requests after the one that caused it, and only a restart clears it. An open transaction, where the connection is returned but the transaction was never committed or rolled back: the pool thinks the slot is free and the database sees idle in transaction. And real saturation, where every connection is legitimately busy. Only the third has anything to do with pool size.
How it works
Size from the bottleneck, not from traffic
For a database, the useful starting point is roughly (cores × 2) + effective_spindles per instance, which is small — often under twenty. Then multiply by instance count and check the total against the server's max_connections, leaving headroom for migrations, admin sessions and BI tools. Teams usually discover their arithmetic exceeds the ceiling when a scale-up triggers "too many clients already".
Release must be unconditional
The connection has to be returned on every exit path, including the ones you did not anticipate. That means a language-level construct — try-with-resources, a context manager, a finally — rather than a call at the end of the method body, which is skipped the moment anything throws above it.
Returning a connection is not ending a transaction
A connection handed back with an open transaction still holds a snapshot and possibly row locks, and PostgreSQL reports it as idle in transaction. The slot counts as available and is useless. Commit or roll back explicitly before release; do not rely on close to do it, since behaviour varies by driver and pool.
Do not hold a connection across work that does not need it
A handler that borrows a connection, reads rows, makes an HTTP call, then writes, holds the slot for the whole network round trip. Under any downstream latency spike the pool drains. Fetch, release, call, then reacquire — and if the write and the remote call must be consistent, that is what an outbox is for.
External poolers, and what transaction mode costs
PgBouncer in transaction mode multiplexes many client connections onto few server connections, which is the only real answer when instance count is elastic or the workload is serverless. The price is session state: server-side prepared statements, advisory locks spanning statements, session-level SET, temp tables and LISTEN/NOTIFY all break or behave unexpectedly, because consecutive statements may land on different server connections.
Implementing it
Enable leak detection in non-production permanently — HikariCP's leakDetectionThreshold and its equivalents log the exact borrow site, turning a guess into a file and line number.
Alarm on pending-acquisition count rather than on the timeout exception. Pending rises for a while before the first failure, which is your warning window; the exception is the aftermath.
Set application_name on every connection string so pg_stat_activity can attribute connections to a service. Without it, a shared-database incident has no owner.
Keep idle_in_transaction_session_timeout and statement_timeout set in production so an application bug cannot hold a slot or a snapshot indefinitely.
-- The decisive query during an incident: which state are the connections in?
-- Many 'idle in transaction' => lifecycle bug in the application.
-- Many 'active' with long durations => genuine saturation or slow queries.
SELECT application_name, state, count(*),
max(now() - state_change) AS longest
FROM pg_stat_activity
WHERE datname = current_database()
GROUP BY 1, 2 ORDER BY 3 DESC;Interview questions and how to answer them
Your service reports "connection is not available". What do you check first?
Whether the connections are busy or idle in transaction, because those are opposite bugs. Group pg_stat_activity by state. Many idle in transaction means a lifecycle bug — something returns connections without ending the transaction. Many active with long durations means real saturation or slow queries. Also check whether the count ever drops when traffic stops: if not, it is a leak and no tuning will help.
Why might a bigger pool make things slower?
Because database throughput is bounded by cores and disk, not by waiting clients. Beyond that point, more connections add context switching, lock contention and per-connection memory without adding capacity. The queue moves from your application into the database, where it slows every query rather than just the overloaded path — and in PostgreSQL each connection is a process, so the memory cost is real.
How would you size a pool?
Start from the database bottleneck — roughly (cores × 2) + effective_spindles per instance — then multiply by instance count and verify the total fits under max_connections with headroom for migrations and admin. Then measure: if pending acquisitions are consistently zero and latency is fine, it is big enough. The common error is sizing from expected request concurrency.
What does transaction-mode pooling break?
Anything relying on session state, because consecutive statements can land on different server connections: server-side prepared statements, advisory locks held across statements, session-level SET, temp tables, and LISTEN/NOTIFY. Most applications are fine, but the driver's prepared-statement behaviour needs checking before rollout — it is the usual source of surprise.
Why does the failure appear long after the request that caused it?
Because a leak consumes one slot per failure while the pool keeps serving from the remaining ones. The timeout only fires when the last slot is gone, which can be hundreds of requests later — so the endpoint that surfaces the error is usually not the one with the bug. That lag is exactly why leak detection beats reasoning from the stack trace.
Answers that lose the round
- Raising pool size as the first response to exhaustion, before establishing which of the three causes it is
- Releasing the connection at the end of the method instead of in a finally or context manager
- Returning a connection without committing or rolling back, so it is idle in transaction
- Holding a connection across an HTTP call with no timeout
- Sizing per instance without multiplying by instance count and comparing to max_connections
- Adopting transaction-mode pooling without checking prepared-statement and session-state usage
- Alarming on the timeout exception rather than on the pending count that precedes it
Practise connection pooling in a real repository
Gronex ships this as a runnable repository: a small pool, an exception path that skips cleanup, and sessions returned while still idle in transaction. The tests assert the lifecycle invariant rather than the happy path, so a bigger pool size does not make them pass.
FAQ
Is one pool per service or one shared pool better?
One per service instance, sized so the total across instances fits the database ceiling. Sharing a pool between unrelated workloads means a slow batch job can starve request traffic — separate pools for request and background work is a common and useful split.
Do serverless functions need a different approach?
Yes, and this is where external poolers stop being optional. Each concurrent invocation may open its own connection with no reuse, so connection count scales with request concurrency and hits max_connections quickly. A pooler in front, or a data proxy, is effectively required.
Should the pool have a minimum idle size?
Keeping a few warm avoids handshake latency on the first requests after a quiet period, which matters for user-facing paths. Keeping many idle wastes database resources for no benefit. A small minimum with a sensible idle timeout is the usual balance.
How does this relate to thread pool sizing?
Same reasoning, different resource. Both are bounded queues in front of a finite capacity, both degrade badly when made unbounded, and both should be sized from the bottleneck rather than from arrival rate. A thread pool larger than the connection pool simply moves the queue to the connection acquisition.