Two URLs, One Database: What a Pooler Actually Changes

The .env on the project I maintain has two Postgres URLs in it. One on port 6543 with ?pgbouncer=true glued to the end, one on port 5432 with nothing. Prisma takes both — url and directUrl. I had been treating the second as "the real one" for about a year without ever checking what made it real.

So I spent an afternoon sending the same query down each of them and writing down every place the answers diverged. Some of it is the standard transaction-pooling story. Some of it is not, including the part where my own test run deadlocked itself on a lock I had left behind on a connection I no longer had.

The server is PostgreSQL 17.6, max_connections is 60 with 3 reserved for superusers, the driver is node-postgres, and Prisma Client is 6.19.2.

The first surprise: there is no direct connection

I opened client connections one at a time through port 5432, expecting to walk up to 57 and then get 53300 too many clients already. It stopped at fifteen:

port 5432 | client connections accepted: 15
          | refusal: XX000 (EMAXCONNSESSION) max clients reached in session mode
                     - max clients are limited to pool_size: 15

EMAXCONNSESSION is not a Postgres error code. Port 6543 took 26 and never complained.

Both hostnames end in pooler.supabase.com. Port 5432 is Supavisor in session mode; port 6543 is Supavisor in transaction mode. The "direct" URL is a pooler too — it just hands you one server connection and lets you keep it. Nothing below works on 5432 because the pooler is absent; it works because session mode never takes the connection away from you.

The server agrees. With thirteen client connections held open, pg_stat_activity looks identical through either port:

33 client backends of a possible 60, and not one of them knows my application's name.

pg_backend_pid() is the cheapest proof

Open eight client connections through each URL. Ask each one which backend process is serving it.

// pid-probe.mjs — run with a connection string in PGURL
import pg from "pg";
const probeUrl = process.env.PGURL;
const open = async () => {
  const c = new pg.Client({ connectionString: probeUrl, ssl: { rejectUnauthorized: false } });
  await c.connect();
  return c;
};
const pid = async (c) => (await c.query("select pg_backend_pid() p")).rows[0].p;
 
const probeClients = await Promise.all(Array.from({ length: 8 }, open));
const sequential = [];
for (const c of probeClients) sequential.push(await pid(c));
const overlapping = await Promise.all(probeClients.map(pid));
 
console.log("sequential :", new Set(sequential).size, "backends", sequential.join(","));
console.log("overlapping:", new Set(overlapping).size, "backends", overlapping.join(","));
for (const c of probeClients) await c.end();

Port 5432 says eight and eight, and the pids are identical between the two lines:

sequential : 8 backends 4127647,4127636,4127644,4127646,4127645,4127642,4127641,4127643
overlapping: 8 backends 4127647,4127636,4127644,4127646,4127645,4127642,4127641,4127643

Port 6543, same script, same moment:

sequential : 1 backends 4127648,4127648,4127648,4127648,4127648,4127648,4127648,4127648
overlapping: 4 backends 4127650,4127648,4127651,4127652,4127648,4127648,4127648,4127648

Eight client connections, one server backend, as long as their queries do not overlap. Make them overlap and the pooler grabs three more, then hands most of them back. Client zero moved from 4127648 to 4127650 between the two lines and got no notification: no event, no warning, no change in any driver-visible property. A "connection" on the pooled path is a lease that expires at COMMIT and is silently renewed on whichever process is free.

The failure that flag is preventing

Everything else follows from that lease. The clearest case is prepared statements, because it fails loudly rather than quietly.

Prepare a statement, then force the lease to move by making twelve other clients hammer the pooler, then execute:

// prepared-churn.mjs
import { Client as ChurnClient } from "pg";
const churnUrl = process.env.PGURL;
const connect = async () => {
  const c = new ChurnClient({ connectionString: churnUrl, ssl: { rejectUnauthorized: false } });
  await c.connect();
  return c;
};
const backendOf = async (c) => (await c.query("select pg_backend_pid() p")).rows[0].p;
 
const victim = await connect();
const pidAtPrepare = await backendOf(victim);
await victim.query("prepare zz_rev11_demo (int) as select $1 + 1");
 
const noisy = await Promise.all(Array.from({ length: 12 }, connect));
let pidNow = pidAtPrepare;
for (let i = 0; i < 10 && pidNow === pidAtPrepare; i++) {
  await Promise.all(noisy.map((c) => c.query("select pg_sleep(0.08)")));
  pidNow = await backendOf(victim);
}
 
try {
  const r = await victim.query("execute zz_rev11_demo(41)");
  console.log("prepared at", pidAtPrepare, "-> now", pidNow, "-> EXECUTE ok", r.rows[0]);
} catch (e) {
  console.log("prepared at", pidAtPrepare, "-> now", pidNow, "-> EXECUTE", e.code, e.message);
}
await victim.query("deallocate all").catch(() => {});
await victim.end();
for (const c of noisy) await c.end();

On port 5432 the pid never budges:

prepared at 4127643 -> now 4127643 -> EXECUTE ok { '?column?': 42 }

On port 6543, with the same script:

prepared at 4127652 -> now 4127650 -> EXECUTE 26000 prepared statement "zz_rev11_demo" does not exist

26000 is invalid_sql_statement_name. Swapping the SQL-level PREPARE for a protocol-level named Parse — node-postgres query({ name, text, values }), which is what every serious driver uses for parameterised queries — produces exactly the same code.

Now the part I had never bothered to test. Prisma's query engine prepares everything it sends and names the statements s0, s1, s2. Take the flag off the pooled URL and run 150 concurrent $queryRaw calls:

// prisma-flag.mjs
import { PrismaClient } from "@prisma/client";
const pooledUrl = process.env.POOLED_URL;              // :6543?pgbouncer=true
const variants = {
  "with flag": pooledUrl,
  "flag removed": pooledUrl.replace("?pgbouncer=true", ""),
};
for (const [label, url] of Object.entries(variants)) {
  const prisma = new PrismaClient({ datasources: { db: { url } } });
  let passed = 0;
  const failures = {};
  for (let round = 0; round < 5; round++) {
    const settled = await Promise.allSettled(
      Array.from({ length: 30 }, (_, i) => prisma.$queryRaw`select ${i}::int + 1 as v`)
    );
    for (const s of settled) {
      if (s.status === "fulfilled") passed++;
      else {
        const key = s.reason?.meta?.message ?? String(s.reason?.message).slice(0, 60);
        failures[key] = (failures[key] ?? 0) + 1;
      }
    }
  }
  console.log(label, "->", passed, "ok,", 150 - passed, "failed", failures);
  await prisma.$disconnect().catch(() => {});
}

with flag    -> 150 ok, 0 failed {}
flag removed -> 37 ok, 113 failed {
  'ERROR: prepared statement "s150" does not exist': 16,
  'ERROR: prepared statement "s151" does not exist': 14, ... }

I ran that twice: 37 survivors the first time, 33 the second. Three quarters of the queries die either way, and which quarter survives is a race. ?pgbouncer=true is not a compatibility hint, it is the switch that turns off Prisma's named prepared statements. Neither of this project's URLs sets connection_limit, so Prisma is separately running its own client-side pool of roughly cpus * 2 + 1 on top — I saw nine distinct backends from a single PrismaClient — and that pool is unrelated to the 15 or so server connections Supavisor is holding on the other side.

Seven kinds of session state, and the four that lie to you

Same pattern for each: do the thing, force the lease to move, check whether the thing is still true. I checked behaviour and not just SHOW, which turned out to matter — an early run of mine reported that SET survived, because SHOW happened to land back on a backend that still had the value.

The two that error are fine. You will find them in staging, the stack trace names the object, and you go fix it. The four above them are the problem: SET, SET search_path, LISTEN and pg_advisory_lock all return success and then do nothing. Your timeout is not set. Your search path is whatever the database default happens to be. Your listener hears nothing forever.

The advisory lock row is the worst of them, because pg_advisory_unlock returns a boolean. It returned false — "you did not hold this" — and nobody checks the return value of an unlock. Meanwhile the real lock sat on the backend that had served the original pg_advisory_lock, now idle in Supavisor's pool with my lock attached. My next test run, through port 5432, blocked on it. I had to reconnect through the pooler over and over until I landed on that same pid and call pg_advisory_unlock_all() to get my own database back.

One piece of good news: there is no cross-client bleed. Client A sets a value and disconnects, ten fresh clients read it back — statement_timeout came back clean 10 times out of 10, and so did a custom GUC, across six distinct backends. Supavisor resets state when a connection goes back to the pool. It just will not carry yours forward.

What to write instead

Every broken row has a transaction-scoped twin, and all four of these I confirmed working on port 6543:

// scoped.mjs — all four verified through the pooled URL
import { Client as ScopedClient } from "pg";
const scopedClient = new ScopedClient({
  connectionString: process.env.POOLED_URL,
  ssl: { rejectUnauthorized: false },
});
await scopedClient.connect();
 
await scopedClient.query("begin");
await scopedClient.query("set local statement_timeout = '150ms'");          // not SET
await scopedClient.query("select pg_try_advisory_xact_lock(911012)");       // not pg_advisory_lock
await scopedClient.query("create temp table zz_rev11_scoped(x int) on commit drop");
await scopedClient.query("declare zz_rev11_c cursor for select generate_series(1,5)"); // no WITH HOLD
const fetched = await scopedClient.query("fetch 3 from zz_rev11_c");
console.log("rows fetched inside the transaction:", fetched.rowCount);
await scopedClient.query("commit");
await scopedClient.end();

Prints 3, and the advisory lock is gone from pg_locks the instant the transaction commits, which is the point. The rule is not "avoid session state" — it is "your transaction is the only scope the pooler respects, so make it the scope your state lives in."

Which of the three I measured, and which I only read about

This matters more than a tidy comparison table, so I will be blunt about it: I measured Supavisor and only Supavisor. I have no PgBouncer and no pgcat here. Everything in the other two columns is read out of their own documentation, which I fetched and read, and nothing in them is a number I produced.

Three of those rows correct things I believed before this afternoon. PgBouncer's default mode is session, not transaction — its config reference says so in one word: "Default." Statement mode does not spray a transaction's statements across backends; it forbids multi-statement transactions outright. And the prepared-statement problem is solved in PgBouncer: set max_prepared_statements above zero and, per the config docs, it "makes sure that any statement prepared by a client is available on the backing server connection. Even when the statement was originally prepared on another server connection." That is the feature whose absence I measured as 26000.

Supavisor's README, incidentally, still lists session pooling under Future Work, while the deployed service I was talking to implements it and names it in an error string. Where the docs and the socket disagree, believe the socket.

The number I could not get

I wanted the cost of the extra hop. I ran select 1 sixty times per run, four runs per port, on a warm connection:

There is a ~145 ms round trip between my sandbox and this database, and the difference between the two ports is smaller than the difference between two runs of the same port. Connection setup was the same story: 992 to 1039 ms p50 for connect plus TLS plus auth plus first query, through both. So I have no pooler-hop figure to give you, and I would rather say that than pick a plausible-looking millisecond count. If you want that number, measure it inside your own VPC where the floor is under a millisecond and the hop is actually visible.

What I do have is the number that would have cost me a production outage, and it fits in one row: