Our app opens two Postgres pools, and exactly one method crosses from the first to the second

PubTrivia is a Next.js 16 app on Vercel talking to Supabase through the transaction pooler, with a second long-running WebSocket process on Railway talking to the same database. The file that makes the database connection is 119 lines, most of them comment, and 26 files import the single db it exports.

It used to be one client. It is now two, plus a Proxy whose entire job is to send one method name to the second one. This is the sequence of three bugs that got it there, in the order they happened.

The hang

The symptom was dashboard pages that loaded forever after a quiet spell, with no error anywhere: not a timeout, not a connection error, nothing in the logs. The request simply never finished, and the platform eventually killed the function at 300 seconds.

The cause is pipelining. postgres-js will send a query down a connection that is still waiting on the reply to an earlier one, up to 100 deep, which is a real performance win against a Postgres server. Supavisor in transaction mode is not a Postgres server. It loses track of the pipelined query, the reply never arrives, and because there is no query timeout on the client, the caller waits for as long as the platform allows.

With a pool of one, which is what a cold serverless instance has, any page that issued two queries at once could trip it: a Promise.all, or simply a layout and a page rendering at the same time. That is most dashboard loads.

The whole repro is five queries and no app:

import postgres from 'postgres'

const sql = postgres(process.env.DATABASE_URL, {
  prepare: false,     // required for a transaction-mode pooler anyway
  max: 1,
  fetch_types: false,
})

// Against Postgres directly: five rows, immediately.
// Through a transaction-mode pooler: this never resolves.
await Promise.all([1, 2, 3, 4, 5].map(() => sql`select 1`))

So pipelining goes off, and a few connections go on so that parallel reads stay parallel instead of queueing behind each other:

{
  max_pipeline: 0,          // one query per connection at a time
  max: 3,                   // in production
  idle_timeout: 10,         // seconds
  prepare: false,
  fetch_types: false,
  connect_timeout: 15,
}

Two of those deserve a note. max_pipeline is honoured by postgres-js but missing from its TypeScript definitions, so the options object needs a widening type to pass it. And idle_timeout: 0, which is the value you reach for when you want sockets dropped as soon as they go quiet, means never close. On a platform that can suspend an instance between requests and resume it later, holding a socket across the suspension is how you get a dead connection that looks alive, so the value you actually want is a small number of seconds.

Then nothing could write

The next deploy broke every write in the app. Joining a quiz failed. Creating anything failed. The error was UNSAFE_TRANSACTION, on all five db.transaction call sites.

postgres-js claims a dedicated connection for sql.begin inside its pipelining check. Set max_pipeline: 0 and that code path no longer runs, so BEGIN goes out on a connection the client has not reserved, and the driver refuses it rather than risk interleaving a transaction with other traffic. The refusal is correct. It is also total: no pipelining means no transactions.

The fix that worked warm and hung cold

The documented way to hold a connection yourself is sql.reserve(), so begin was rebuilt on top of it: reserve a connection, send BEGIN on it, run the body, commit, release. In development it worked first time.

In production it hung, and only on the first transaction after a quiet period. With nothing open in the pool, reserve() waits for a connection to become free, and with fetch_types: false the client does not go and open a fresh one for the waiter. A pool of zero open connections and one waiter is a pool that stays at zero open connections.

That failure mode is worth generalising: a client option that saves a round trip on cold start can also remove the thing that makes a cold start finish.

Two pools, one crossing

The shape that works gives transactions their own client, with the default claim-on-BEGIN behaviour left switched on:

	queries client	transactions client	WebSocket server
max_pipeline	0	1	0
max	3	2	10
idle_timeout	10 s	10 s	30 s
connect_timeout	15 s	15 s	5 s
transactions	none	all of them	none

max_pipeline: 1 is the smallest value at which postgres-js still claims the connection on BEGIN. Nothing but a BEGIN ever reaches that pool, and inside a transaction the queries run one after another on the claimed connection, so in practice it never pipelines either. That last part is a property of our code rather than of the client: it holds as long as no transaction fires queries in parallel inside itself, and none of ours do.

Then the two clients have to look like one to the 26 files that import db. Drizzle's db.transaction calls exactly one method on the client it was given, which is begin, so the join is a one-line Proxy:

const client = new Proxy(clients.queries, {
  get: (target, prop, receiver) =>
    prop === 'begin' ? clients.transactions.begin : Reflect.get(target, prop, receiver),
})

export const db = drizzle(client)

Every read, write and raw statement goes to the queries pool. begin goes to the transactions pool. No call site knows, and there is no second exported db for somebody to pick wrongly.

One more line is needed, and it looks like dead code:

drizzle(clients.transactions)

The return value is thrown away on purpose. Drizzle installs its own date and JSON parsers on whichever client you hand it, so without that call a timestamp column read inside a transaction comes back in a different shape from the same column read outside one. Calling drizzle() for its side effect is not pretty, and the alternative is a class of bug that only shows up inside transactions.

The other process disagrees about every number

The WebSocket server is a long-lived Node process, so its client is the opposite of the serverless one on nearly every option: ten connections instead of three, idle for thirty seconds instead of ten, a five second connect timeout because there is no cold start to forgive. The one setting the two agree on is max_pipeline: 0, because that is not a tuning choice, it is the pooler's shape.

It also has no transactions at all. Everything it does is a read or a single statement, so it never needed the second pool, and the comment in that file points at the app's client for the recipe if it ever does.

Three things worth taking away

• A hang with no error is the house style of a connection pooler. Before you look at your query, check whether two queries were in flight on one connection.
• idle_timeout: 0 means never. Several of these options have a zero value that means "off" in the opposite direction from the one you want.
• If you change how connections are claimed, test the first call after a quiet period, not the fiftieth. Every bug in this story was invisible on a warm pool.

Check it yourself

The pages that used to hang are behind a login, so the honest invitation is the one on the pricing page: there is one free session with no card needed, and the dashboard you land on is the one that spent a fortnight timing out.

The pooler behaviour, though, needs none of our code. Point the five-query snippet above at any transaction-mode pooler with max: 1, then run it again against a direct connection on port 5432, and the difference is the whole first bug.