Deadlocked/hung pool when connected to Postgres through Supavisor

[nikhilro]

Hey @porsager, thanks for the package.

Creating this issue mostly in hopes that someone else has ran into this issue.

We use Supabase for our Postgres DB and connect through their pooling service Supavisor in transaction mode; Supavisor is an alternative to pgBouncer (link). On postgres.js side, we set prepare: false.

What we're seeing is that the postgres.js pooler will start swallowing queries at some point. Imagine a function like:

async function userGet(id: string) {
  console.log("trying to get the user");
  const users = await db<User[]>`SELECT * FROM user WHERE id=${id}`;
  console.log("got the user!");
  return users;
}

After a while, this will print "trying to get the user" but not "got the user!". Does this ring any bells?

P.S. been trying to find an isolated script to reproduce but unsuccessful so far. the only reproduction is live production traffic.

[nikhilro]

I'm more confident that is an issue with Supavisor under load. A thing that would be helpful is postgres.js statement limit.

I know we have:

   connection: {
     statement_timeout: 1000 * 60 * 0.5, // 30 seconds, pg expects milliseconds
   },

But, that is not useful when connecting to transaction mode poolers.

Is there an easy way to "abort" the query if it's taking too long?

[lllleonnnn]

Please forgive not answering your exact question but we have run into very similar issues and it boiled down to our queries being blocking & locking. We've used this query (modify as needed) to run down issues in our Supabase PG instance:

SELECT blocked_locks.pid AS blocked_pid,
 blocked_activity.usename AS blocked_user,
 blocking_locks.pid AS blocking_pid,
         blocking_activity.usename AS blocking_user, 
         blocked_activity.query    AS blocked_statement,
         blocking_activity.query   AS current_statement_in_blocking_process
   FROM  pg_catalog.pg_locks         blocked_locks 
    JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks         blocking_locks 
        ON blocking_locks.locktype = blocked_locks.locktype 
        AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
        AND blocking_locks.pid != blocked_locks.pid 
    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
   WHERE NOT blocked_locks.GRANTED;

Smashing this query over and over when the problem is happening may give some insight as well:

SELECT
	query,
	avg(now() - query_start) AS average_duration,
	usename,
	pid
FROM
	pg_stat_activity
WHERE
	state = 'active'
	AND query <> '<IDLE>'
	AND query NOT LIKE '%pg_stat_activity%'
GROUP BY
	query,
	usename,
	pid
ORDER BY
	average_duration DESC
LIMIT 10000;

If your logs aren't helpful, ye olde ALTER SYSTEM SET log_lock_waits TO on; may yield more helpful info in the Postgres and/or Pooler logging views, as well as altering log_statement_sample_rate and log_min_duration_sample.

re: your actual question - I did implement something hideous with await Promise.race(... which did not help. We did not have any specific issues with Supavisor but switched to running our own pgbouncers.

also likewise shout out to @porsager for making an amazing library that lets me avoid the hell of ORMs.

[akbarsahata]

I don't intend to answer the question, but just wanted to add to this discussion since I encountered the same issue

we use postgres.js as driver for drizzle-orm on our project and connect the service to supabase database through supavisor. to give more context, this is a next.js based project deployed on vercel. after some research on what are the suitable NPMs for this kind of project, we went with pairing drizzle-orm + postgres.js for doing backend stuffs.

we also noticed when my code has big Promise.all operation, the issue became more prevalent. so before solving this, the patching method that we used was to blame code with Promise.all and refactored it. Honestly I hated it since it hindered us from using Promise.all which is known to improve the speed of execution.

initially we thought it was the issue with supavisor, but with trial and errors also hours of google search, we finally managed to resolve (sort of) my problem with 2 methods.

1. connect directly to postgres with session pooler (port 5432)
2. change the driver from postgres.js with node-postgres

method 1 improved the stability. and after trying method 2, we realized not only it did solve the problem but also improved the system performance. thus, we decided to go with the later method.

we did some empirical test on it by doing stress-test

using node-postgres: my test endpoint took around 4-5s to response. 20vu in 1min gave better throughput

using postgres.js with direct connection: it took more than 8s in average, less throughput but all requests were responded successfully

using postgres.js with supavisor: not only worse througput than using other driver, also worse goodput by only < 80% request responded successfully

PS: I am not supporting or villaining any driver here, but I hope my findings help pinpointing the issue and yielding the solutions

[porsager]

It's great @akbarsahata , but can you please share some code? 😊

[pascalmtts]

We’ve recently encountered the same issue in our backend and wanted to share some findings that might help with debugging.

We’re currently using the dedicated pooler provided by Supabase (PgBouncer). Interestingly, switching to a self-hosted PgBouncer instance (via Docker) completely resolves the problem. We’ve also observed that using node-postgres does not result in this issue.

One key detail: the issue appears to be directly related to how concurrent connections are handled. When we use the max option to limit the number of active connections, it significantly influences the behavior. For example, setting max: 1 makes the issue much worse — almost all SQL commands fail or don’t go through. Increasing the value to something like max: 50 nearly eliminates the issue, but obviously that’s not a scalable or long-term solution.

If there’s anything we can do on our end to help pinpoint the root cause, we’re happy to assist.

Edit: Here is the code we are using to create the postgres client for drizzle (max setting only for test purposes)

function makeClient() {
  const db = parseDatabaseUrl(env.DATABASE_URL);

  return postgres(env.DATABASE_URL, {
    /**
     * @see https://supabase.com/docs/guides/database/drizzle
     * @see https://supabase.com/docs/guides/database/connecting-to-postgres#supavisor-transaction-mode
     * Disable prepared statements when using pgBouncer in transaction mode, as it doesn't support them.
     */
    prepare: db.isPooler ? false : true,
    ssl: db.ssl,
    max: 1
  });
}

[AnDeVerin]

Minimal reproduction + root cause analysis

We hit this exact issue in production (Supabase Pooler port 6543 + Vercel serverless). After investigation, we found a reliable reproduction and the root cause.

Reproduction

import postgres from "postgres";

// Connection through Supabase Pooler (PgBouncer, transaction mode)
const sql = postgres(process.env.DATABASE_URL, { prepare: false, max: 3 });

await sql`SELECT 1`; // warmup

// This hangs forever — 5 concurrent queries on pool of 3
await Promise.all(Array.from({ length: 5 }, () => sql`SELECT 1`));

Deadlock occurs when concurrent queries exceed pool size, forcing postgres.js to pipeline queries on the same connection.

Root cause

postgres.js pipelines multiple queries on a single TCP connection by default (max_pipeline: 100). PgBouncer/Supavisor in transaction mode can reassign the backend PostgreSQL connection between pipelined queries. When this happens, the response to the pipelined query is lost — the promise hangs indefinitely.

Verification matrix

Configuration	5 concurrent SELECT 1, pool max: 3
Pooler + pipelining (default)	DEADLOCK ❌
Pooler + max_pipeline: 0	244ms ✅
Direct connection + pipelining	254ms ✅
Direct connection + max_pipeline: 0	255ms ✅

Only the combination of pooler + pipelining fails. Direct connections handle pipelining fine.

Debug trace

With debug logging, we can see the lost query:

conn=2 query=SELECT 1 AS x   ← first query
conn=2 query=SELECT 1 AS x   ← second query PIPELINED on same connection
conn=4 query=SELECT 1 AS x
conn=3 query=SELECT 1 AS x
conn=4 query=SELECT 1 AS x

req 1: done   (conn 4)
req 4: done   (conn 4)
req 0: done   (conn 2, first query)
req 2: done   (conn 3)
req 3: NEVER RESOLVES   ← conn 2's pipelined query lost

conn 4 successfully handles 2 pipelined queries, but conn 2's second pipelined query never gets a response. The timing/backend reassignment by PgBouncer is non-deterministic.

Workaround

const sql = postgres(url, {
  prepare: false,
  max: 3,
  max_pipeline: 0, // disable pipelining entirely
});

max_pipeline: 0 = one query per connection at a time. No pipelining, no lost responses. No performance impact in practice since queries are fast and PgBouncer handles connection multiplexing.

Why small pools trigger it more

Default max: 10 means pipelining only kicks in with 11+ concurrent queries — rare. With max: 3 (common for serverless), just 4 concurrent queries trigger pipelining. Vercel recommends small pools for serverless, making this a likely hit for Supabase + Vercel users.

Environment

• postgres.js 3.4.8
• Supabase Pooler (PgBouncer, port 6543, transaction mode)
• Vercel serverless (Node.js 24)
• Drizzle ORM (but reproduction is pure postgres.js)

[BuildShipGrowRepeat]

We hit this in production (Next.js on Vercel + Supabase Supavisor transaction mode) and root-caused it. Sharing since this issue matches our symptoms exactly (hung pool, backends stuck active/ClientRead, only recovery is killing the connection).

Root cause

postgres.js pipelines queued queries onto an already-busy connection (execute() writes the query to the socket even when another query is active). Supavisor transaction mode cannot handle pipelining: the connection wedges deterministically once ≥2 queries are pipelined behind the active one. The active query and the first pipelined query complete; the bytes of everything after are never forwarded to a backend — the pooled backend sits in ClientRead forever and every query queued on that connection hangs.

Minimal repro (any Supabase transaction-mode URL, port 6543):

const sql = postgres(url, { max: 1, prepare: false });
const q = () => sql`select 1`;
await Promise.all([q(), q(), q(), q(), q()]);
// → resolves, resolves, hangs, hangs, hangs — 100% reproducible

Same five queries awaited sequentially: all fine. With a pool (max: 6) and 24 concurrent queries we got exactly 12 ok / 12 hung on every run — active + 1 pipelined survive per connection, the rest wedge it.

Workaround — and a small bug blocking it

max_pipeline: 0 fixes it completely (strict one-query-per-connection; our failing loads went from 50% hang to 100% clean, ~10% wall-clock cost). But max_pipeline: 0 breaks sql.begin: in execute() the onexecute callback (which reserves the connection for the transaction) is chained after the pipeline gate:

return write(toBuffer(q))
  && !q.describeFirst
  && !q.cursorFn
  && sent.length < max_pipeline                                  // 0 < 0 → false, short-circuits ↓
  && (!q.options.onexecute || q.options.onexecute(connection))   // reservation never runs

so the synthetic BEGIN completes on an unreserved connection and falsely trips UNSAFE_TRANSACTION: Only use sql.begin, sql.reserved or max: 1.

Swapping the last two terms fixes it with no behavior change otherwise (with max_pipeline: 0, sent is always empty, and onexecute returning undefined keeps the overall result falsy exactly as before):

  && (!q.options.onexecute || q.options.onexecute(connection))
  && sent.length < max_pipeline

We run that as a patch-package patch in production; happy to open a PR if wanted.

Notes:

• max_pipeline: 1 does NOT help — still wedges. 0 is the only safe value against Supavisor transaction mode.
• prepare: false alone is not sufficient (that's the only thing Supabase's docs mention for postgres.js).
• Session mode (port 5432) is unaffected under identical load.
• Arguably Supavisor should support extended-protocol pipelining or at least fail loudly; from postgres.js's side, documenting max_pipeline: 0 for transaction-mode poolers (+ the one-line fix above) would make it survivable.

[edobry]

We hit what looks like exactly this (queries silently never settling against Supavisor transaction mode), and were able to reduce it to a deterministic minimal repro plus a mechanism analysis. Posting in case it unblocks this issue — happy to open a separate issue instead if you'd prefer.

Minimal repro

120 concurrent parameterless sql.unsafe() queries against a max: 15 pool, pointed at a Supabase/Supavisor transaction-mode pooler (:6543), reproduce it on every run for us (verbatim script below, re-verified immediately before posting):

import postgres from "postgres";

const sql = postgres(process.env.SUPAVISOR_TXN_URL, {
  max: 15,
  prepare: false,
  connect_timeout: 10,
  idle_timeout: 60,
});

let settled = 0;
const queries = Array.from({ length: 120 }, (_, i) =>
  Promise.resolve(sql.unsafe("SELECT 1 AS one")).then(
    () => settled++,
    (err) => {
      settled++;
      console.log(`query ${i} rejected: ${err.message}`);
    }
  )
);

await Promise.race([
  Promise.all(queries),
  new Promise((r) => setTimeout(r, 20_000)),
]);
console.log(`${settled}/120 settled after 20s`);

Observed output, consistently: a cluster of write CONNECTION_DESTROYED rejections, and then 34/120 settled after 20s — 86 promises never settle at all, no resolution, no rejection, indefinitely (we've waited 90s+). Smaller overloads (e.g. 6 concurrent against max: 2) reproduce the same thing intermittently — it's a race during connection ramp-up, so modest concurrency sometimes squeaks through; 8× the pool size has been 100% reliable for us.

Environment: postgres@3.4.8 (the 3.4.9 diff is types-only, so latest should behave the same), Bun 1.2.13 and 1.2.21 (not yet verified under Node), Supabase Supavisor transaction pooler (aws-0-us-west-2.pooler.supabase.com:6543), prepare: false.

The discriminating matrix

We varied one factor at a time; the discriminator is whether the query carries bind parameters, not protocol, not query identity:

pool max	N concurrent	query shape	result
2	6	unsafe, no params	intermittent WEDGE (ramp-up race) — when it hits: 5/6 settle, 1 lost
15	120	unsafe, no params	WEDGE — 34/120 settle, ~86 never settle
15	120	unsafe with params ($1)	all settle, ~1.8s
15	120	tagged template	all settle, ~1.8s
15	120	unsafe, no params, { simple: false } forced	WEDGE — extended protocol does not help
15	120	unsafe, identical param [1] ×120	all settle (identity irrelevant)
15	120	no params, session pooler :5432	all settle 0.7s — overload rejects cleanly (EMAXCONNSESSION)
15	120	no params, in-flight capped at max (app-side semaphore)	all settle, ~1.2s

Why binds-presence is the variable

In build() (connection.js):

q.describeFirst = q.onlyDescribe || (parameters.length && !q.prepared)

• With binds (and prepare: false), describeFirst is true → the client sends Parse+Describe+Flush and waits a round trip before Bind/Execute — submission is self-pacing.
• Without binds, the whole sequence goes out in one pipelined write (unnamed() for extended; a single Q for simple — which is why forcing { simple: false } doesn't help).

Under concurrent pool ramp-up, those one-shot pipelined writes are what Supavisor's transaction mode kills (write CONNECTION_DESTROYED client-side). That part may arguably be Supavisor's problem.

The part that looks like a postgres-js bug regardless of who kills the connection: when the connection is destroyed, only some of its queries reject — the rest are silently lost and their promises never settle. error() does drain sent, so the leak appears to be queries associated with a connection in the connecting/queue state; we didn't pin the exact line. A destroyed connection settling all of its queries (even with rejections) would turn this from an infinite hang into a recoverable error.

Workaround

Capping in-flight unsafe() queries at the pool's max with an app-side semaphore fully avoids the wedge for us (last matrix row).

---

Investigation and repro by Claude (an AI agent) operated by @edobry, debugging a production hang in our own system; the matrix results are from live runs against our Supabase project.

[43jay]

(claude and) I hit this standing up an ordinary Next.js app on a freshly seeded database with a single user and no load at all.

The triggering code is one route handler doing idiomatic JS:

const [template, priorities, briefs, weeks, dayBlocks, briefings] =
  await Promise.all([ /* six trivial selects */ ]);

Config	Result
max: 1, default pipeline	round 1 OK → round 2 hangs
max: 5, default pipeline	round 1–2 OK → round 3 hangs
max: 5, max_pipeline: 1	round 2 hangs
max: 1, max_pipeline: 1	round 1 hangs
max: 5, max_pipeline: 0	10/10 rounds OK, ~129ms
max: 1, max_pipeline: 0	10/10 rounds OK, ~423ms
max: 5, sequential awaits	stable indefinitely, ~575ms

Versions: postgres@3.4.9, drizzle-orm@0.45.2, Next.js 16.2.12, Node 22/23, Supabase transaction pooler.

max_pipeline: 0 is not available to Drizzle users who use transactions

@BuildShipGrowRepeat noted max_pipeline: 0 breaks sql.begin because the onexecute reservation callback is short-circuited by the pipeline gate. I confirm this.

drizzle-orm's postgres-js driver implements db.transaction() as client.begin() (session.js).

The documented happy path leads straight into this

Supabase's Drizzle guide recommends drizzle-orm + postgres, pointed at the Shared Pooler, with prepare: false and the comment "Disable prefetch as it is not supported for 'Transaction' pool mode". Follow this, but write a Promise.all, and you land here.

Question

Is issuing concurrent queries on a single client considered unsupported usage against a transaction-mode pooler?

I don't understand whether the expectation is that a postgres client handles one query at a time and pipelining is an optimisation that pooler users must opt out of, or whether pipelining is meant to be transparent and this is squarely a bug. Promise.all over independent queries seems a normal thing to write.

Ask

Could a maintainer weigh in on the intended path forward? Specifically:

1. Is max_pipeline: 0 the sanctioned workaround for transaction-mode poolers, or a workaround that happens to work?
2. If it is sanctioned — would you take a PR for the execute() reordering @BuildShipGrowRepeat proposed, so it doesn't break sql.begin?
3. If not — is the recommendation to "use session mode / node-postgres with Supavisor", as @akbarsahata and @pascalmtts landed on (instead of @postgres)?

[brianjeffrelyarson]

Adding a reproduction, since this issue has none. We hit what looks like the same
thing and narrowed it down; posting the matrix in case it helps others land here
from a search.

Symptom

Not "queries hang" — concurrent queries return each other's data. Parameters
and result parsers cross between queries in flight:

round 1: ok
round 2: FAIL — value.map is not a function
round 5: FAIL — invalid input syntax for type uuid: "f"
round 6+: FAIL — timeout

"f" is the first character of false — a boolean arriving in a uuid parameter
slot. We also saw a text value handed to a bigint parser (Cannot convert <a name> to a BigInt) and an array parser applied to a scalar. These are
positional crossing, not type coercion.

Round 1 always passes; it degrades from round 2 as the pool starts reusing
connections.

Setup

• postgres 3.4.x, prepare: false
• Supabase pooler (Supavisor) in transaction mode, port 6543
• ~12 independent queries issued concurrently via Promise.all, mixed column
  types (uuid, bigint, text[], boolean), repeated on a short interval in
  one long-lived process

What we measured

Configuration	Result
transaction pooler, concurrent	✗ corrupts round 1–2
transaction pooler, max: 1	✗ corrupts round 1
transaction pooler, sequential (same queries, awaited one at a time)	✓ clean
session mode (port 5432), concurrent	✓ clean, 10+ rounds
transaction pooler, pg (node-postgres) instead, concurrent	✓ clean, 20 rounds at ~24 concurrent

Also tested and not the cause: fetch_types: false, pool size, credentials,
idle connection age, and the ORM's own query cache (a fresh ORM instance per round
still corrupts).

Why we think it is pipelining

The max: 1 row is the informative one. With a single pooled connection the
concurrent callers all share it — and it still corrupts — while the same queries
awaited one at a time are clean. So the variable is not concurrency across
connections; it is more than one query in flight on a single connection.

postgres.js pipelines by default. A transaction-mode pooler binds a server
connection per transaction and releases it when it believes the statement is
complete, so pipelined independent statements can be assigned to different
backends, and their bind parameters and row descriptions cross.

pgbouncer hit the same thing and describes it in the same terms —
pgbouncer/pgbouncer#203, "swapping query parameters in
txn pooling mode" — and its fix was to count expected ReadyForQuery packets so
the server is not released too early. That mechanism matches every row above,
including why session mode is clean (a dedicated server connection per client) and
why pg is clean (it queues per client rather than pipelining).

Note for anyone reproducing

Use queries whose columns have distinguishable types. We first tried eight
concurrent select 1 / select 2 and it passed — crossing between two integer
results is invisible. The failure only becomes observable when a crossed value hits
a parser that rejects it.

[self-similar]

Confirming this against Supabase Supavisor (transaction mode, port 6543) on postgres@3.4.8, Node 24, Windows — and adding two findings for anyone applying the workarounds.

Reproduction (production-shaped): { max: 4, prepare: false, idle_timeout: 20 }, concurrent bursts of parameterless sql.unsafe('select 1') interleaved with parameterized queries. Default pipelining: 11/33 pipelining-exposed promises never settled (plus some CONNECTION_DESTROYED rejections); the parameterized control group was 33/33 clean (consistent with describeFirst self-pacing under prepare: false); max_pipeline: 0 was 100% clean across the same matrix. In production this manifested for us as single lost queries with pg_stat_activity fully idle — the promise sits in the connection's private sent queue where no pool recovery path reaches it, exactly as diagnosed above.

Two notes on the max_pipeline: 0 + execute() reorder workaround (moving the onexecute term ahead of the sent.length < max_pipeline gate so sql.begin reservations still run):

1. Patch both builds. The package ships src/ (ESM) and cjs/src/ — patching only src/connection.js leaves require() consumers on the unpatched CJS mirror, and sql.begin keeps failing with UNSAFE_TRANSACTION there. Bit us via a CJS-resolving test harness while the ESM app worked.
2. max_pipeline is missing from types/index.d.ts Options — TypeScript users need a typings addition alongside.

With both in place: sql.begin works under max_pipeline: 0 on ESM and CJS paths, drizzle-orm transactions work, and repeated burst matrices show zero losses. Happy to share the harness script if useful.