What you will learn
5- Explain what a connection pool does and why a pool is not tied to a user session
- Estimate how many connections a workload needs from request rate and hold time
- Calculate the real connection ceiling across several pools and instances
- Read a pool timeout error and tell a queueing symptom from a configuration mistake
- Choose between raising the limit, shortening hold time and adding an external pooler
A connection pool keeps a set of database connections open and lends them out to queries. It saves the cost of creating and releasing a connection on every transaction, and it caps how many connections your application can hold.
Most pool trouble comes from two numbers: how many connections are in use at once, and how long each one is held. This piece builds that idea from first principles and then applies it to timeouts and the errors you meet in production.
Pool settings differ by driver and product. The examples below use names from Siebel, Advanced Authentication and Prisma. Check your own driver's documentation for the exact setting names it uses.
What is a database connection pool?
A pool is a set of already-open database connections that your application lends to queries and takes back afterwards. Instead of opening a connection for each request, the code borrows one, runs its statement and returns it.
The benefit is reuse. Connections are reused instead of each transaction creating one, which reduces the computing overhead of creating and releasing connections, both for your application and for the database engine itself.
A pool also gives you a limit. Each pool has a minimum and a maximum size, and the maximum is the most connections that pool will ever hold.
How does a query use a pooled connection?
The Siebel Application Object Manager shows the pattern clearly. A user session is not tied to a database connection. When a user issues a query, it runs on any free connection in the pool, and that connection is blocked until the statement finishes. When the query completes, the connection goes back to the pool and can serve any other user.
This is why fast queries let many users share one connection without blocking each other.
When every connection is busy, the pool creates a new one, but only while it holds fewer than the maximum. In Siebel, the MaxSharedDbConns server parameter sets that maximum. Once the maximum is reached, the Application Object Manager checks the existing connections at random and assigns the query to the first free one it finds.
The key point: a connection is occupied for the whole duration of a statement. Pool pressure is a function of time, not only of user count.
How many connections does a workload need?
The number you need is roughly the number of queries in flight at any moment. That equals your request rate multiplied by how long each request holds its connection.
- Ten requests per second, each holding a connection for 100 ms: about one connection.
- Ten requests per second, each holding a connection for 2 seconds: about twenty connections.
Query duration is a multiplier on pool pressure. When a query gets slower, you need more slots for the same traffic. That is why adding connections so often fails to cure a slowdown: you are buying more places for a slow query to be slow.
The script below does the arithmetic so you can try your own numbers.
def connections_needed(requests_per_second, hold_seconds):
# Queries in flight = arrival rate x time each one holds a connection
return requests_per_second * hold_seconds
def ceiling(instances, per_instance_limit):
# Every instance owns a complete pool of its own
return instances * per_instance_limit
print(connections_needed(10, 0.1))
print(connections_needed(10, 2))
print(ceiling(50, 9))
Why does the total across all pools matter?
Each pool is sized on its own, but the database sees the sum. Two examples show how fast it grows.
Several services, one database. The Advanced Authentication documentation describes pools with a minimum of 4 and a maximum of 32 connections. With 2 Risk Authentication services, 2 Strong Authentication services and 1 Case Management service, the totals are:
- At start: (2 x 4) + (2 x 4) + (1 x 4) = 20 connections.
- At the maximum: (2 x 32) + (2 x 32) + (1 x 32) = 160 connections.
If you do not clean up idle connections, that estimate has resource implications for the database engine, especially a shared one.
Serverless. In a long-running server there is one process and one pool. In a serverless platform, every concurrent function instance is its own process with its own complete Prisma pool. Prisma's default pool size is num_physical_cpus * 2 + 1, so a four-core machine gets nine connections. Fifty concurrent instances at nine connections each is 450 connections, arriving at a PostgreSQL server that is often configured for a fraction of that.
So work out the real ceiling first: app instances multiplied by the per-instance limit must stay comfortably below PostgreSQL max_connections. On serverless, the instance count is something you do not control.
def services_total(counts, per_pool):
# counts: number of service instances; per_pool: connections per instance
return sum(counts) * per_pool
services = [2, 2, 1] # Risk, Strong, Case Management
print(services_total(services, 4))
print(services_total(services, 32))
What does a pool timeout actually measure?
A pool timeout is the time a caller waits for a free connection before giving up. It is a queueing symptom, not a database fault.
Queues form for exactly two reasons: too many clients arrive at once, or each client holds a connection for too long.
Prisma reports this as error code P2024:
Timed out fetching a new connection from the connection pool.
(Current connection pool timeout: 10, connection limit: 10)
Older Prisma versions word it as "Timed out fetching a new connection from the pool", so search for both. The two numbers in the parentheses are your current pool_timeout in seconds and your current connection_limit. If they are not the values you thought you configured, you have found your bug. The default acquire timeout is 10 seconds.
A longer pool_timeout is not a fix on its own. It converts a fast error into a slow request. That can be what you want during an incident, but it does not remove the queue.
What goes wrong when you raise the pool limit?
Raising connection_limit addresses only the first cause of queues, too many simultaneous clients, and only until you reach the PostgreSQL max_connections limit. After that you have moved the error rather than removed it.
A common failure sequence looks like this. About a third of requests return 500 under load. Someone raises the limit from 9 to 50 and redeploys, and the errors stop. Some time later PostgreSQL refuses connections outright, and the service is down instead of degraded.
The pool timeout was warning you about load. A bigger pool let the load through to a database that could not take it.
What holds a connection longer than it should?
Two things stretch hold time.
Slow queries. Find them before touching any configuration. On MySQL or MariaDB the slow query log is the fastest route. On PostgreSQL, pg_stat_statements ordered by total_exec_time does the same job.
Transactions that wait on non-database work. An interactive transaction takes a connection out of the pool and does not return it until the callback resolves. In Prisma, a $transaction callback that awaits an HTTP request holds a pooled connection for the entire round trip. Audit those callbacks for network calls and move them outside the transaction.
Short hold times shrink the pool you need, which is the cheapest fix available.
SELECT *
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
How do you set the limit and the timeout explicitly?
Set connection_limit and pool_timeout explicitly in DATABASE_URL instead of relying on the default of num_physical_cpus * 2 + 1. Explicit values mean the numbers in the error message match what you intended.
The example below shows the two settings as URL query parameters, with placeholders for credentials. Confirm the exact connection string syntax for your database in Prisma's own documentation before you use it.
export DATABASE_URL="postgresql://YOUR_USER:YOUR_PASSWORD@YOUR_HOST:5432/YOUR_DB?connection_limit=5&pool_timeout=20"
When should an external pooler sit in front of the database?
If you run on serverless, or with more than a handful of instances, put PgBouncer in transaction mode (or Prisma Accelerate) between Prisma and PostgreSQL, and set connection_limit=1 per instance. A bigger in-process pool is not the fix, because the number of pools is what you cannot control.
The external pooler holds a small, stable set of real database connections and multiplexes many short client sessions onto them. Your database then sees a count you chose, not a count set by your traffic.
What does an idle timeout do?
An idle timeout closes connections that sit unused, so a pool shrinks after a burst instead of holding its maximum forever.
In Advanced Authentication, the ODBC pools by default remove idle connections only on an actual network or connection issue. The Max Idle Timeout setting, in seconds, changes that. Its default is 0, which means idle connection management is not triggered. With a value of 3600, connections can stay idle for 60 minutes, after which they decrement gradually to MinConnections.
Two rules from that page:
Max Idle Timeoutmust be lower than your firewall timeout settings, so the pool closes a connection before a firewall silently drops it.Monitor Thread Sleep Timemust be lower thanMax Idle Timeout, so the monitor checks often enough to act.
The menu path and setting names are specific to that product. For other drivers, look for the equivalent idle or lifetime setting in their documentation.
What is the order of work when you see a pool timeout?
Follow this order, cheapest and safest first:
- Read the two numbers in the error: current timeout and current limit. Confirm they are what you configured.
- Compute instances x per-instance limit and compare it with PostgreSQL
max_connections. - Look for slow queries with the slow query log or
pg_stat_statements. - Remove network calls from inside transactions.
- On serverless or with many instances, add PgBouncer in transaction mode and use
connection_limit=1. - Only then raise the limit, and raise
max_connectionsto match.
Waiting on the pool can also cascade: requests pile up behind a slow database and the failure spreads to callers. Keep timeouts finite at every layer so a slow database fails fast somewhere you can see it.
Glossary
- Connection pool
- A set of open database connections that an application borrows for queries and returns afterwards.
- Pool timeout
- The time a caller waits for a free pooled connection before the request fails.
- Hold time
- How long a query or transaction keeps a connection checked out of the pool.
- max_connections
- The PostgreSQL setting that limits how many client connections the server accepts.
- Transaction mode
- A PgBouncer mode that lends a real database connection to a client only for the duration of a transaction.
- Idle timeout
- A limit after which an unused pooled connection is closed, down to the pool minimum.
Frequently asked questions
Why does a bigger connection pool sometimes make things worse?
A bigger pool only helps when too many clients arrive at once, and only until the total reaches the database connection limit, such as PostgreSQL max_connections. If queries are slow, a bigger pool just gives them more slots, and the database can end up refusing connections.
How do I estimate the pool size I need?
Multiply the request rate by the time each request holds a connection. Ten requests per second at 100 ms needs about one connection. At 2 seconds it needs about twenty.
What do the two numbers in the Prisma P2024 message mean?
They are the current pool timeout in seconds and the current connection limit. If they differ from what you configured, the settings are not being applied as you expected.
Should I increase the pool timeout?
Not as a fix. A longer pool timeout turns a fast error into a slow request. It can help during an incident, but the queue remains until you cut hold time or the number of competing clients.
Why do serverless apps exhaust connections so quickly?
Each concurrent function instance has its own complete pool. Fifty instances at nine connections each is 450 connections, and you do not control the instance count.
What is the default idle connection behaviour in Advanced Authentication?
Max Idle Timeout defaults to 0, so idle connection management is not triggered. The ODBC pool then removes idle connections only on an actual network or connection issue.
Next steps
- Read your driver's documentation to find its pool size, acquire timeout and idle settings
- Measure the real hold time of your slowest queries and recompute the connections you need
- Check your database's connection limit and compare it with the combined ceiling of all your pools
References
From Grokem: https://www.grokem.com/programming/web-development/backend/connection-pool-size-and-timeouts-why-a-pool-runs-out-and-how-to-size-it








