We run our RAG prototype on Postgres with pgvector, and last week it worked on the laptop and fell over on the server. Same code, same queries, same Postgres version. This is what was different, and it is the most common difference between development and production that is not the code.
The symptom
In development: a handful of requests, everything is fast. In production: a burst of traffic, and then requests that hang for seconds and eventually fail with "too many clients already". CPU on the database is idle. The application logs show requests waiting, not running.
What was different
Postgres has a hard limit on concurrent connections, max_connections, and the default is 100. That is per database, not per user.
In development the application opened a handful of connections and closed them. In production it opened a connection per request and never closed it, because the code path that closed it was only on the success branch.
So the application was creating connections at request rate, holding them, and eventually the database said no. The fix was not a bigger max_connections; that just moves the failure.
The fix is a pool, and it is one object
The pg library has a pool in it, and the whole change is to create it once:
import { Pool } from "pg";
export const pool = new Pool({
connectionString: process.env.DATABASE_URL,
max: 10,
idleTimeoutMillis: 30_000,
connectionTimeoutMillis: 5_000,
});
export async function query(text, params) {
return pool.query(text, params);
}
Ten connections for an application that handles a burst of requests is plenty, because a query that takes 20 milliseconds does not need a connection of its own for the other 980 milliseconds of the second. The pool is a queue in front of a small number of connections.
Two settings worth knowing. idleTimeoutMillis closes connections that have been idle for a while, so an application that is quiet between bursts does not hold the whole allocation open. connectionTimeoutMillis is the one that turns pool exhaustion into a fast error instead of a slow hang: a request that cannot get a connection in five seconds fails loudly rather than waiting.
When the application has too many instances
The pool fixes one application. If you run six instances of the application, that is six pools of ten, which is 60 connections, and each of those is a Postgres backend process with its own memory. Now you need a pooler in front of Postgres, and the standard one is pgbouncer.
Two modes matter:
- Session pooling passes a connection through unchanged. Everything works, and there is no saving in connections.
- Transaction pooling hands a connection back to the pool at the end of each transaction, so one Postgres connection serves many clients. This is where the savings are, and this is where prepared statements break: a prepared statement lives on the Postgres session, and in transaction mode the next transaction may get a different session. pgbouncer 1.21 and later can track prepared statements, or you turn them off.
The third mode, statement pooling, is for very simple workloads.
The symptom that looks like a deadlock
We also hit the opposite-looking case: a query that took 20 minutes and came back with "canceling statement due to lock timeout". That was not a deadlock, it was a transaction that held a row lock while waiting for something else, and the pool made it worse by handing the same connection to a second request that then waited on the lock the first one was holding. If you see lock timeouts, look for a transaction that is not committing, not at the pool.