Prisma connection pool exhaustion: find the cause

Each running instance of the app has its own pool. The database sees the sum. Example: 20 serverless instances with a pool of 5 each can open 100 connections. If max_connections is 100, there is nothing left for migrations, admin tools or monitoring.

The default pool size depends on the Prisma version. In version 6 it was derived from the CPU count (physical CPUs times 2, plus 1), set by the connection_limit URL parameter. From version 7, the pool belongs to the database driver, and for the PostgreSQL driver the setting is max. Read your version's page before you copy a number.

An interactive $transaction keeps one connection until its callback ends. A slow call to another service inside it holds that connection the whole time. Count sessions by state to see it:

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

Many idle in transaction sessions mean transactions are open longer than they need to be. See Find idle-in-transaction sessions in PostgreSQL.

await prisma.$transaction(async (tx) => {
  for (const item of items) {
    await tx.stock.update({ where: { id: item.id }, data: { qty: { decrement: item.qty } } });
  }
});

One write per item, one after the other, on one held connection. With 500 items the connection and every row lock it took stay held for 500 round trips. Other requests wait for the pool, or for those row locks. Batching the writes into one statement (updateMany, or a single raw statement) shortens the hold. Whether it is safe depends on your logic, so test it.