PubTrivia is two deployments against one Postgres. The Next.js app runs as serverless functions and owns the pages, the rules and the dashboard. A WebSocket server runs as a long-lived process on a different platform and owns the clock. Same Supabase project, same connection string, same library.
The two files that connect them share one setting and disagree about every other one, and the disagreement is the whole point: a connection pool is a bet about how long your process is going to be alive.
The one line they agree on
prepare: false
Both clients set it, both for the same reason. The connection string points at Supabase's transaction pooler, and a pooler in transaction mode hands you a different backend connection per transaction, so a prepared statement created on one is not there on the next. postgres-js prepares by default. Turning it off is not optional, it is the price of the pooler.
Everything after that line is a different bet.
The serverless side bets the process is about to die
max: isProduction ? 1 : 5,
connect_timeout: 15,
idle_timeout: isProduction ? 0 : 30,
max_lifetime: isProduction ? 60 * 5 : 60 * 30,
fetch_types: false,
max: 1 in production looks like a mistake and is the most important setting in the file. A serverless instance handles one request at a time. A pool of ten on an instance that will never issue two concurrent queries is not throughput, it is nine idle connections holding nine pooler slots, multiplied by however many instances the platform decided to keep warm.
idle_timeout: 0 means close the connection the moment nothing is using it. This is the setting that fixes the failure everybody hits here first, which presents as "it works, then it breaks after a few page loads". postgres-js keeps TCP connections open between queries because that is the correct behaviour for a server. A serverless instance gets frozen between invocations and thawed later with its sockets pointing at something that is no longer listening, and the next query on that socket fails immediately. There is no retry that saves you, because the connection looks fine until it is used. Closing when idle means there is nothing left to go stale.
max_lifetime: 60 * 5 is the belt to that braces: rotate a connection after five minutes even if it has been busy the whole time.
connect_timeout: 15 is generous on purpose. A cold start plus a TLS handshake to the pooler can take several seconds, and the failure mode of a tight timeout is a request that dies from impatience during the exact window where the user is already waiting longest.
fetch_types: false skips the type introspection round trip postgres-js does on first connect. On a long-lived process that cost is paid once a month. On a client whose whole life is one request, it is paid every time.
The long-lived side bets the opposite way
max: 10,
idle_timeout: 30,
max_lifetime: 60 * 30,
connect_timeout: 5,
Four settings, four inversions.
Ten connections because this process genuinely does concurrent work: a question closes, and the server reads the state it needs to tell every phone in the room what happened. Thirty idle seconds because the work is bursty, a flurry around each question and silence in between, and reconnecting between questions buys nothing. Thirty minutes of lifetime because rotation here is hygiene rather than survival. And a five second connect timeout, because this process connects at boot, when nobody is waiting, and a fast failure is a fast restart.
There is no singleton either:
const client = postgres(process.env.DATABASE_URL!, { ... })
export const db = drizzle(client)
Module scope runs once, the process holds the pool until it is replaced, done.
The dev-only global, and why it is dev-only
The serverless file has a line that makes people wince:
declare global {
var __db_client: postgres.Sql | undefined
}
const client = global.__db_client ?? createClient()
if (!isProduction) global.__db_client = client
That is for hot reload, not for production. Editing a file re-evaluates the module and would build another pool, and twenty saves in an afternoon is twenty orphaned pools against a database that has opinions about connection counts. Stashing it on globalThis survives the reload.
The if (!isProduction) guard is the honest part. On a cold start globalThis is empty anyway, so the branch is not saving work, it is documentation: nothing in production is allowed to depend on the global existing.
The comment that describes a setting the file does not set
My favourite thing in this file is a stale sentence. The header comment explains the production strategy as:
We use `max: 1` + `connection: { keepalive: false }` so the
connection is closed once idle, preventing zombies.
There is no connection key in the options object. max: 1 is there, idle_timeout: 0 is there, and the thing the comment names is not. The behaviour it promises is delivered, by a different setting, and the comment was written against an earlier version of the fix and never updated.
I am leaving it in this post rather than quietly editing it first, because comments that describe settings are the ones that rot. A comment that says why survives a rewrite. A comment that says what becomes a claim you have to check against the code, which means it has become a second source of truth, which means it is now capable of lying.
The retry that only one side needs
The dashboard's read path wraps its queries:
async function withRetry<T>(fn: () => Promise<T>, attempts = 3, delayMs = 150): Promise<T> {
let lastError: unknown
for (let i = 0; i < attempts; i++) {
try {
return await fn()
} catch (err) {
const code = err?.code
if (code === '57014' || code === '57P01') {
lastError = err
if (i < attempts - 1) await new Promise(r => setTimeout(r, delayMs * (i + 1)))
} else {
throw err
}
}
}
throw lastError
}
Two Postgres error codes and nothing else. 57014 is a statement timeout, 57P01 is admin shutdown. Both are things the pooler does to a connection that has just been handed to a cold instance, and both are transient in the specific sense that the identical query a moment later succeeds. Everything else rethrows on the first attempt, because a retry on a constraint violation is a slower constraint violation.
The delay is 150, then 300. Not exponential, not jittered, because this is not congestion control: it is "give the pooler a beat". Three attempts and 450 ms of worst case, inside a server-rendered page where the alternative is an error screen.
The WebSocket server has no equivalent. Its connections are minutes old by the time they are used and its pool was warmed at boot, so the two codes that haunt the serverless side essentially do not occur. Different lifetime, different failure modes, different code.
The visible consequence
None of this is visible when it works, which is the only interesting thing about infrastructure. The live leaderboard moves the instant a question closes, and the reason it can is that the process holding the clock also holds ten warm connections and does not have to dial a database to answer. Session history still has last week's quiz in it, and the reason that page renders instead of erroring is a three attempt retry around a cold pooler connection on an instance that had been asleep.
One database, two clients, two bets. If you are copying a connection config from somewhere, the question worth asking first is not which numbers are correct. It is how long the process reading them expects to live.
Top comments (0)