Backend Interview: How Do You Size a Database Connection Pool and Diagnose Exhaustion?

Problem and Scope

An order platform using PostgreSQL runs 100 stateless API instances and 20 background workers. The database has max_connections set to 500. Migrations, incident response, and operational tools require a reserve of 50, leaving an application-wide budget of 450 connections. Peak traffic is 6,000 requests per second, about 70% of requests access the database, and application traces show that a database operation holds a connection for 40 milliseconds on average. The end-to-end p99 target for API requests is 800 milliseconds.

Every process currently has a pool of 10, so the deployment can theoretically create 100 × 10 + 20 × 10 = 1,200 database sessions. Connection acquisition starts timing out at peak. Sometimes database CPU is only 55%; at other times, lock waits and query latency rise as well. Design an initial pool allocation, timeout policy, and connection lifecycle. Explain how to distinguish an undersized local pool, a connection leak, a long transaction, replica scaling beyond budget, and overload inside the database.

The instance counts, throughput, 40-millisecond hold time, and 500-connection limit are interview assumptions, not universal performance claims for PostgreSQL, HikariCP, or PgBouncer. Current backend interview guidance still evaluates performance, reliability, and operational considerations in system design, and a dedicated database connection-pool design question was published in 2026. The core skill is managing database concurrency and resource ownership across application replicas, so the category is backend.

What the Interviewer Is Evaluating

The first signal is treating a pool as concurrency admission control, not assuming that more connections produce more throughput. PostgreSQL's max_connections is database-wide, and raising it increases resource allocation. A number configured independently on one instance must be multiplied by API replicas, workers, scheduled jobs, and temporary replicas during a rolling release.

The second signal is using Little's Law for an order-of-magnitude check without presenting an average as a capacity answer. The average database arrival rate is 6,000 × 70% = 4,200 operations per second. Average connection occupancy is approximately 4,200 × 0.04 = 168. That describes steady-state average concurrency only. Bursts, p99 hold time, lock waits, transaction retries, and load imbalance among replicas still require measurements and load tests.

The third signal is locating the queue with evidence from both sides. The application should expose active, idle, and pending counts, acquisition latency and timeouts, and connection hold time. The database should expose pgstatactivity state, wait_event, active queries, idle transactions, and connection origin. Raising the pool because "connection acquisition timed out" can merely move the queue from the application into the database.

Finally, the answer should define failure boundaries. A strong design covers request deadlines, connection leaks, long transactions, rolling releases, autoscaling, database restarts, stale connections, and the difference between PgBouncer session and transaction pooling. It also names session-scoped features that may not be safe with transaction pooling.

Clarifying Questions Before Answering

• Does the limit of 500 apply to one primary or a larger cluster? Read replicas, failover targets, and operational

access may have different budgets. Calculate pools separately for the databases that actually serve each workload.

• What is the maximum replica count? Are 100 API instances normal or the autoscaling maximum? A rolling release may

temporarily run old and new replicas together. A configuration based only on steady-state replicas can exceed budget during a release or incident.

• What is the distribution of connection hold time? An average of 40 milliseconds does not show p95, p99, network

calls inside transactions, or lock waits. The long tail controls queueing and timeouts, so segment it by route, task, and transaction label.

• Can background work queue or limit concurrency? Long batch jobs should not compete freely with short API

requests. Separate pools are useful, but every pool still shares the same global budget of 450.

• What retries occur after a timeout? Immediate retries without backoff increase arrival rate precisely when the

database slows down. Acquisition timeout must fit inside the request deadline and work with admission control, backoff, or a defined failure response.

• Which session-scoped features does the application use? Temporary tables, LISTEN, session advisory locks,

cross-transaction SET state, and prepared-statement behavior affect whether PgBouncer transaction pooling is safe.

• Where would the connection proxy run? One PgBouncer instance creates a capacity and availability boundary.

Per-node sidecars, a dedicated proxy tier, and a managed proxy have different failure modes.

30-Second Answer Framework

"I would start with a global budget. Reserving 50 of 500 connections leaves 450 for applications; the current per-process cap expands to 1,200. The average concurrency of 168 is only a scale check. An initial allocation could give each API instance 2 and each worker 4, totaling 280 with 170 of headroom, then tune it under peak load. Acquisition timeout must fit inside the 800-millisecond request deadline. I would compare pool pending count, acquisition latency, and hold time with pgstatactivity and wait events. If the pool queues while the database has headroom, inspect skew, leaks, and local caps. If queries or locks are already degrading, do not enlarge the pool. Autoscaling must recompute the budget, and PgBouncer transaction pooling requires a session-state audit."

Step-by-Step Deep Dive

Establish a Global Connection Budget

Write the budget as an explicit invariant:

application_connection_cap
  = max_connections
  - operations_reserve
  = 500 - 50
  = 450

The sum of all API, worker, migration, administration, and rolling-release pool caps must stay at or below 450. If other applications use the same database, subtract their budgets too. The operations reserve is not spare throughput for normal traffic. It preserves the ability to connect, observe, and repair the database during an incident.

The average concurrency check is:

database_arrival_rate = 6,000 × 70% = 4,200 operations/second
average_in_flight     = 4,200 × 0.04 seconds = 168

This quickly shows that 1,200 connections are not justified by the average workload and that 20 total connections are probably too few. It does not produce the final pool size because averages hide bursts, p99 hold time, transaction retries, and queueing feedback. The final number must combine service SLOs, sustainable active database concurrency, and load tests.

Propose an Initial Allocation That Can Be Tested

A conservative starting point is 2 connections per API instance and 4 per worker:

API cap      = 100 × 2 = 200
worker cap   = 20 × 4  = 80
allocated    = 280
app headroom = 450 - 280 = 170

The worker allocation of 4 is only an initial isolated cap, not a value that follows directly from "longer jobs." Real values should follow each workload's hold-time distribution, arrival rate, and concurrency limit. Two hundred eighty is a candidate that respects the global budget and can be load-tested; the remaining 170 should not be allocated automatically. If API replicas grow to 200, keeping 2 per replica consumes 400, and the workers' 80 pushes the total above 450. Scaling must reduce the per-instance cap, bound maximum replicas, or use a proxy to enforce a more centralized server-connection budget.

Do not set a large minimumIdle merely so every process always has spare connections. Idle sessions still consume the global limit. A pool can grow on demand to a hard maximum. Whether it should retain a minimum number of idle connections must be justified by measured connection-establishment cost and burst latency.

Set Acquisition Timeouts and Connection Lifetimes

Acquisition timeout must be shorter than the remaining request deadline. With an 800-millisecond p99 target, a thread cannot wait 30 seconds for a connection. A first test could give acquisition 100 to 200 milliseconds and adjust it from the measured queue distribution. That range is a scenario design choice, not a universal library recommendation. After timeout, return a recognizable overload error and use upstream admission control or jittered backoff. Do not retry immediately without a limit.

Maximum connection lifetime should be shorter than any forced lifetime imposed by the database, proxy, or network, and it should include jitter so many connections do not expire together. Keepalive is for an idle connection that should remain valid and must run more frequently than maximum lifetime. Testing every connection before each borrow adds a round trip and needs measurement. More importantly, handle the first failed operation after a database restart or failover correctly and retry only idempotent business operations.

Determine Whether the Application Pool Is the Bottleneck

Every pool should expose at least these metrics with service, instance, and workload labels:

• configured maximum, active, idle, and pending;
• p50, p95, and p99 acquisition latency and timeout count;
• connection hold time tagged by route, task, or transaction;
• connection creation, close, failed validation, and recreation rates;
• the theoretical total cap: configured pool maximum multiplied by current replica count.

Interpret combinations, not isolated values. Rising pending count and acquisition latency with active connections pinned at the cap, while the database still has sustainable active-connection and CPU headroom, can indicate a small local pool, skewed traffic, or a few operations holding connections too long. If active borrows never fall after the requests finish, or stacks remain in application code, a connection may not be returned. A sudden rise in connection creation and closure can indicate a lifetime mismatch, proxy timeout, or network issue.

Use structured connection scopes so success, error, cancellation, and early-return paths all release the connection. Leak detection thresholds can help locate a path, but they do not replace hold-time distributions and code review. A threshold that is too short will label legitimate long transactions as leaks.

Use PostgreSQL Evidence to Separate Database Overload

PostgreSQL exposes one pgstatactivity row per server process. Group it by application_name, client address, state, and wait event. Start with:

SELECT application_name, state, wait_event_type, wait_event, count(*)
FROM pg_stat_activity
WHERE datname = current_database()
GROUP BY application_name, state, wait_event_type, wait_event
ORDER BY count(*) DESC;

Then find sessions that remain idle inside a transaction:

SELECT pid, application_name, xact_start, state_change, wait_event_type, wait_event
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

If active sessions, query latency, lock waits, or I/O worsen as connection count rises, the bottleneck is in queries, transactions, or database resources. Enlarging the pool increases contention. If the database is near 500 sessions but many are idle application sessions, per-instance idle pools have consumed the budget; shrink them or introduce multiplexing. If idle in transaction persists, repair transaction boundaries because those sessions consume a connection and may retain locks or prevent vacuum progress.

Total sessions are not the same as parallel execution. Idle sessions and sessions waiting for locks require different explanations. CPU at 55% does not prove spare database capacity: lock contention, storage latency, one hot partition, or a serial execution path can block work at low aggregate CPU.

Isolate Workloads and Account for Scaling

When short API requests and long workers share one pool, a few batch jobs can cause head-of-line blocking. Give them separate pools and separate concurrency queues, such as the 200/80 split in this scenario. Isolation protects latency; it does not create a larger budget. A worker pool should be allowed to shrink when idle, and the jobs themselves need a concurrency limit.

The autoscaler must know the database budget. The simplest hard constraint is:

max_replicas × pool_size_per_replica + other_pool_caps <= 450

Include rolling deployment maxSurge. If replica count changes widely, a fixed per-instance cap wastes capacity with few replicas and exceeds budget with many. Use a small fixed pool plus request queueing, or use PgBouncer to multiplex many client sessions onto a controlled number of server connections. Either way, continue to limit active concurrency inside the database.

Decide Whether to Use PgBouncer

PgBouncer session pooling retains the same server connection until a client session ends. It supports PostgreSQL session behavior well but provides less multiplexing. Transaction pooling assigns a server connection only for the duration of a transaction. It can prevent idle clients from holding server connections, but it removes the assumption that the next transaction uses the same server session.

Before transaction pooling, audit cross-transaction SET/RESET, LISTEN, session advisory locks, temporary tables, and other session state. If the application needs those semantics, retain session pooling, give selected paths a separate direct pool, or move the state inside a transaction. A lower connection count alone does not prove success. Test proxy queueing, proxy failure, authentication, connection recreation, and database failover.

Close the Capacity Loop With Failure Tests

Begin with stepped load, increase toward 6,000 requests per second, then add bursts and autoscaling. At each step record end-to-end p95/p99, acquisition latency, pending count, hold time, active database sessions, lock waits, query latency, CPU, and I/O. Compare several candidate allocations around the initial 200/80 split and find where throughput stops growing or database latency and queueing begin to worsen.

Adversarial tests should include an injected path that does not return a connection, several transactions that hold connections for seconds, lock contention, old and new replicas running during a rolling release, a database restart, a proxy dropping existing server connections, and a burst of worker backlog. Passing does not mean "no errors." Under the global connection budget, the system should fail fast or shed load, metrics should identify the queue, and recovery should not create a connection storm.

High-Quality Sample Answer

"I would split the 500 database connections into budgets before choosing a per-instance number. Reserving 50 for operations leaves 450 for applications. The current 120 processes with 10 each produce a theoretical cap of 1,200, so rolling releases and bursts can exceed the database limit.

The prompt gives 4,200 database operations per second and a 40-millisecond average hold time. Little's Law gives about 168 average concurrent operations. I would use that only as a scale check, not p99 capacity. An initial allocation could give 2 connections to each of 100 API replicas and 4 to each of 20 workers, totaling 280 and leaving 170 connections of application headroom. I would load-test candidates around 280 with the real hold-time distribution, bursts, and lock contention. The autoscaling maximum and rolling-release replicas belong in the formula; otherwise 200 API replicas plus the worker pools exceed 450.

Acquisition timeout must fit inside the 800-millisecond request deadline. I would start by testing 100 to 200 milliseconds. On the application side I would monitor active, idle, pending, acquisition latency, timeout count, hold time, and connection recreation. In PostgreSQL I would group pgstatactivity by application name and inspect active, idle, idle-in-transaction, and wait events. If the pool queues while the database has sustainable headroom, inspect replica skew, the local cap, and unreleased connections. If queries, locks, or I/O are already degrading, a larger pool only increases database concurrency. If most sessions near max_connections are idle, shrink per-instance idle pools or multiplex them through a proxy.

I would isolate short APIs and long workers with separate pools and concurrency limits, while keeping their sum under

1. If PgBouncer is needed, I would choose the mode deliberately: session pooling preserves more compatibility;

transaction pooling multiplexes more aggressively but requires an audit of LISTEN, session advisory locks, temporary tables, and cross-transaction SET. Finally, I would validate p99, queueing, total connections, and recovery with stepped load, bursts, a leak, long transactions, lock contention, rolling deployment, database restart, and proxy disconnects."

Common Mistakes

• Assign 20 connections to every instance → total sessions become uncontrolled when replica count changes →

define the database-wide budget first and divide it among all pools and maximum replicas.

• Configure exactly 168 connections from the average → bursts, p99 hold time, lock waits, and allocation granularity

are missing → use Little's Law as a scale check, then decide with distributions and load tests.

• Increase the pool whenever acquisition times out → application queueing can move into database locks, I/O, or CPU

queues → inspect pool pending and database active sessions, wait events, and query latency together.

• Look only at database CPU → locks, storage latency, and hot spots can block connections at low CPU →

interpret sessions by state and wait event.

• Ignore idle connections → idle pools across many replicas can exhaust max_connections first →

monitor pool cap times replica count and control minimum idle.

• Treat idle-in-transaction as ordinary idle → the transaction can hold locks, retain an old snapshot, and impede

cleanup → locate transaction start and code boundaries and apply appropriate timeouts.

• Put every workload in one pool → long workers occupy connections needed by short APIs →

isolate pools and queues by latency profile without exceeding the global budget.

• Adopt PgBouncer and default to transaction pooling → session state can disappear between transactions or land on

another server connection → audit compatibility before selecting a pooling mode.

• Retry all connection errors immediately → database recovery faces a connection storm and a higher arrival rate →

bound retries, add jittered backoff, and retry only idempotent operations.

• Test only steady state → rolling releases, scaling, long transactions, and database restarts expose budget and

lifecycle failures → include them in the acceptance matrix.

Follow-up Questions

Follow-up 1: Why Can More Connections Make the System Slower?

Database CPU, cache, locks, and storage bandwidth are finite. Beyond sustainable active concurrency, more connections increase context switching, cache contention, and lock waits. Each query becomes slower, which lengthens connection hold time and creates positive feedback. A small pool provides bounded queueing in the application, which is usually easier to control than letting every request enter the database. The pool still must not be so small that it leaves sustainable database capacity unused.

Follow-up 2: What Changes If the API Scales to 200 Instances?

Keeping 2 connections per instance would create 400 API connections; the workers' 80 would push the total above 450. Reduce each API pool to 1 and reallocate the worker budget, limit maximum replicas and rolling-release surge, or multiplex clients with PgBouncer. Use burst queueing and sustainable database concurrency tests for the decision. An autoscaler cannot look only at CPU while ignoring downstream database capacity.

Follow-up 3: How Do You Prove a Leak Instead of a Genuinely Slow Query?

A leak often appears as active borrows that only rise, persistent pending requests, and a request that has already finished while its database session may be idle. Associate each borrow with route, task, and sampled stack; compare hold time with request lifetime; and inspect exception, cancellation, and early-return paths. If the PostgreSQL session remains active or waits on a lock, explain the query and transaction before labeling it a leak.

Follow-up 4: Can You Apply HikariCP's Pool-Size Formula Directly?

(corecount × 2) + effectivespindle_count is a starting heuristic in HikariCP's documentation, which explicitly says to load-test around it. SSDs, cache hit rate, query type, and a remote database change the result. More importantly, when one database is shared by many application replicas, any database-level candidate still has to be divided among them. The formula cannot replace the global budget of 450 or real SLO evidence.

Follow-up 5: Are Prepared Statements Always Unsupported With PgBouncer Transaction Pooling?

Do not make an absolute claim across every version and configuration. Validate PgBouncer's feature matrix against the deployed version, protocol-level prepared-statement support, and driver settings. Transaction pooling still does not guarantee that two transactions use the same server session. List the actual session semantics the application depends on, verify them in integration tests, and then choose transaction pooling, session pooling, or a direct pool for each path.

Follow-up 6: Acquisition Latency Fell After Increasing the Pool, but End-to-End p99 Rose. Why?

The queue moved from the application pool into the database. With more queries entering together, lock contention, I/O, or CPU queueing increases query execution time. A better acquisition metric does not mean better user latency. Compare end-to-end p99, database query duration, wait events, and throughput, and choose the concurrency point with the lowest total latency and stable headroom.