Tuning Prisma connection_limit and pool_timeout

This guide is part of Prisma Connection Pool Management. It covers the error that Prisma services on long-lived Node.js processes eventually meet under load — a query that waited the full pool timeout without getting a connection:

PrismaClientKnownRequestError:
Invalid `prisma.order.findMany()` invocation:
Timed out fetching a new connection from the connection pool.
(Current connection pool timeout: 10, connection limit: 5)
    at Kn.handleRequestError (node_modules/@prisma/client/runtime/library.js:121:7753)
  code: 'P2024',
  clientVersion: '6.4.1'

The instinctive fix is to double connection_limit. Sometimes that is correct. Often the pool is being held by slow queries or long interactive transactions, and a larger pool moves the queue into PostgreSQL. The objective is to size connection_limit from measured in-flight queries, set pool_timeout so failures are fast and attributable, and confirm the change against the database.

Rapid incident diagnosis

Read the error first. The message states the limit and timeout the engine is actually using. Compare them with what you believe is configured. In the example above, a limit of 5 on a production pod implies either an explicit connection_limit=5 or a default computed from two physical cores, which happens when a container is given a two-CPU limit.

Check the fleet from PostgreSQL. If the service sets application_name, one query shows whether each replica is at its limit and what those sessions are doing:

SELECT client_addr,
       count(*)                                              AS sessions,
       count(*) FILTER (WHERE state = 'active')              AS active,
       count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_txn,
       max(now() - query_start) FILTER (WHERE state = 'active') AS longest_active
FROM pg_stat_activity
WHERE application_name = 'orders-api'
GROUP BY client_addr
ORDER BY sessions DESC;

Classify.

• sessions equals the limit and most are active with short longest_active: the pool is too small for the concurrency. Raising connection_limit is right.
• sessions equals the limit and longest_active is seconds long: a slow query or lock wait is holding connections. Find it with pg_stat_activity.wait_event and fix the query; a bigger pool will only admit more of it.
• idle_in_txn is a large share: interactive transactions are awaiting non-database work while holding connections. Look for $transaction(async (tx) => ...) callbacks that call external services.
• sessions exceeds the limit on some addresses: several PrismaClient instances exist in one process.

Look at pool wait in the process. With the metrics preview feature enabled, prisma_client_queries_wait counts queries currently queued for a connection and prisma_client_queries_wait_histogram_ms shows how long they queue. A p99 wait near pool_timeout × 1000 confirms the pool, not the database, is where time goes.

Mathematical sizing or parameter formula

In Node.js the number of queries in flight at once is the product of request concurrency and the share of each request spent inside a query. Little’s Law gives it directly:

in_flight_queries = requests_per_second × queries_per_request × mean_query_seconds
connection_limit  = ceil( in_flight_queries ÷ target_utilisation )

Worked example. One replica serves 250 requests per second. Each request runs 4 queries averaging 8 ms: 250 × 4 × 0.008 = 8 queries in flight on average. At a 70 % target utilisation, ceil(8 ÷ 0.7) = 12. The observed limit of 5 is less than the average demand, so the queue grows until queries hit pool_timeout — the incident is arithmetic.

Then check the fleet total against the database:

replicas × connection_limit ≤ max_connections − reserved − other_services

With 10 replicas at 12, the service claims 120 connections. If the database’s usable budget for this service is 100, either cap replicas, lower the limit to 10 and accept a higher wait at peak, or place PgBouncer between Prisma and PostgreSQL.

For pool_timeout, the constraint is the request deadline:

pool_timeout < request_deadline − p99_query_time_per_request − margin

With a 5-second gateway timeout and 1.5 seconds of p99 query time per request, a pool_timeout of 3 seconds leaves half a second of margin. The default of 10 seconds is longer than the gateway deadline, which means the gateway gives up first and the application logs nothing useful.

Interactive transactions need their own term. Each one holds a connection for its whole callback, so:

in_flight_from_transactions = transactions_per_second × mean_callback_seconds

Ten checkout transactions per second with a 400 ms callback (because a payment call is awaited inside) hold 10 × 0.4 = 4 connections continuously — a third of the pool of 12 — on top of the 8 from ordinary queries. Moving the payment call outside the transaction reduces the callback to about 20 ms and the term to 0.2.

Exact remediation & configuration

Set explicit values in the URL. Apply them through the deployment’s secret or environment, never through a code default that production may not see:

DATABASE_URL="postgresql://orders_app:${DB_PASSWORD}@orders-db.internal:5432/orders?connection_limit=12&pool_timeout=3&connect_timeout=5&application_name=orders-api"

Make the configuration visible at startup. Log the parsed parameters so the next incident does not start by guessing what a pod is running:

const url = new URL(process.env.DATABASE_URL!);
logger.info({
  connection_limit: url.searchParams.get("connection_limit") ?? "default (cpus*2+1)",
  pool_timeout: url.searchParams.get("pool_timeout") ?? "default (10)",
}, "prisma pool configuration");

Shorten interactive transactions and cap their wait.

// External work first, with an idempotency key so a retry is safe
const reservation = await inventoryApi.reserve(items, { idempotencyKey: cartId });

await prisma.$transaction(
  async (tx) => {
    await tx.cart.update({ where: { id: cartId }, data: { status: "RESERVED", reservationId: reservation.id } });
    await tx.outbox.create({ data: { type: "CART_RESERVED", payload: { cartId } } });
  },
  { maxWait: 1_000, timeout: 3_000 },
);

Translate pool timeouts into a retryable response. A P2024 means the service was saturated for three seconds. Returning 503 with Retry-After lets load balancers and clients back off instead of retrying immediately:

import { Prisma } from "@prisma/client";

app.use((err: unknown, _req, res, next) => {
  if (err instanceof Prisma.PrismaClientKnownRequestError && (err.code === "P2024" || err.code === "P2028")) {
    res.set("Retry-After", "2").status(503).json({ error: "database busy" });
    return;
  }
  next(err);
});

Roll out without a connection spike. A rolling deploy briefly runs old and new pods. If the new limit is higher, the overlap claims old_limit × old_pods + new_limit × surge_pods; keep maxSurge at one pod for the first rollout, and check the database’s free slots before continuing.

Validation & verification

Load test at peak rate and assert on wait, not just on errors. Enable the metrics preview feature in a staging build and scrape it during the test:

k6 run --vus 300 --duration 10m checkout-load.js
curl -s http://orders-api.staging:3000/metrics | grep -E 'prisma_pool_connections_(busy|open)|prisma_client_queries_wait '

During the test, prisma_pool_connections_busy should peak below connection_limit most of the time, and prisma_client_queries_wait should return to zero between bursts. The p99 of prisma_client_queries_wait_histogram_ms should stay under a few hundred milliseconds — far below the 3,000 ms pool_timeout.

Confirm on PostgreSQL that the fleet stayed inside its budget and that no sessions linger idle inside transactions:

SELECT count(*) AS sessions,
       count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_txn
FROM pg_stat_activity
WHERE application_name = 'orders-api';

sessions must stay at or below replicas × 12, and idle_in_txn should be zero or momentary.

Verify fast failure. In staging, set connection_limit=1 temporarily and send concurrent slow requests. Requests beyond the first must fail within about three seconds with 503 and a logged P2024, not hang until the gateway timeout.

When a larger pool is the wrong answer

Raising connection_limit fixes P2024 only when the database has capacity to serve the extra concurrent queries. Three situations make it counterproductive, and each has a recognisable signature.

The database is CPU-bound. If PostgreSQL’s CPU is already high and pg_stat_activity shows many active sessions, more connections add more concurrent queries competing for the same cores. Throughput stays flat and every query gets slower, so connections are held longer and the pool saturates again at the new limit. The signature is query latency in pg_stat_statements rising after the pool was enlarged:

SELECT query, calls, mean_exec_time, max_exec_time
FROM pg_stat_statements
WHERE query ILIKE '%orders%'
ORDER BY mean_exec_time DESC
LIMIT 10;

Queries are waiting on locks. If sessions show wait_event_type = 'Lock', a larger pool admits more transactions into the same lock queue. The fix is shorter transactions or a different locking pattern, such as SELECT ... FOR UPDATE SKIP LOCKED for queue-like tables.

The fleet is at its budget. If replicas × connection_limit already approaches max_connections, a larger limit converts P2024 in the application into too many clients from PostgreSQL — the same shortage, now visible to every service sharing the database.

Reduce queries per request. Often the cheapest capacity gain is fewer round trips. Prisma’s include and select fetch related rows in fewer queries than loops of findUnique, and findMany with where: { id: { in: ids } } replaces N single-row lookups. Halving queries per request halves the in-flight term in the sizing formula, which is equivalent to doubling the pool with none of the database cost:

// Before: 1 + N queries, each borrowing a connection
const orders = await prisma.order.findMany({ where: { customerId } });
for (const o of orders) o.items = await prisma.orderItem.findMany({ where: { orderId: o.id } });

// After: 2 queries
const ordersWithItems = await prisma.order.findMany({
  where: { customerId },
  include: { items: true },
});

Frequently Asked Questions

Related

• Prisma Connection Pool Management — the parent guide to Prisma’s pool architecture.
• Using Prisma with PgBouncer and Serverless Functions — when the fleet total no longer fits the database.
• Tuning node-postgres Pool Timeout Settings — the equivalent settings when a driver adapter owns the pool.
• Applying Little’s Law to Connection Pool Sizing — the derivation behind the sizing formula.
• Using Prisma Driver Adapters with node-postgres Pools — moving the pool into pg.Pool and what the settings become.