Trouble using Supabase's Postgres transaction pooler

[frederikheld]

I'm trying to use Sidequest with Supabase's postgres transaction pooler. It works until it fails with the error relation "sidequest_jobs" does not exist.

I had the same error in my own code, so I know what is going on here: the relation does exist but not in the current schema. This is an issue with the transaction pooler, which might hand out a connection that was previously used on a different schema. Therefore you have to explicitly set the schema before you run a query.

I tried to figure out how to configure Knex to work with the transaction pooler. The safe approach would be to execute the query SET search_path to sidequest (replace sidequest with the respective config value). So far I couldn't figure out how to do that.

What is the right way to set up sidequest to work with a pooled connection?

Thanks in advance!

EDIT: another relevant thing to mention is that the transaction pooler does not support prepared statements

Here's the full error log:

[info] [2025-12-16 15:53:20] [Sidequest] [Worker] : Starting worker with provided configuration...
[error] [2025-12-16 16:00:00] [Sidequest] [Worker] : Error on running CleanupJob! delete from "sidequest_jobs" where ("completed_at" < $1 or "failed_at" < $2 or "canceled_at" < $3) - relation "sidequest_jobs" does not exist
{
  length: 113,
  name: 'error',
  severity: 'ERROR',
  code: '42P01',
  detail: undefined,
  hint: undefined,
  position: '13',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_relation.c',
  line: '1449',
  routine: 'parserOpenTable',
  [Symbol(level)]: 'error',
  [Symbol(splat)]: [
    error: delete from "sidequest_jobs" where ("completed_at" < $1 or "failed_at" < $2 or "canceled_at" < $3) - relation "sidequest_jobs" does not exist
        at Parser.parseErrorMessage (/path/to/project/task-runner/node_modules/pg-protocol/dist/parser.js:285:98)
        at Parser.handlePacket (/path/to/project/task-runner/node_modules/pg-protocol/dist/parser.js:122:29)
        at Parser.parse (/path/to/project/task-runner/node_modules/pg-protocol/dist/parser.js:35:38)
        at Socket.<anonymous> (/path/to/project/task-runner/node_modules/pg-protocol/dist/index.js:11:42)
        at Socket.emit (node:events:524:28)
        at addChunk (node:internal/streams/readable:561:12)
        at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
        at Readable.push (node:internal/streams/readable:392:5)
        at TCP.onStreamRead (node:internal/stream_base_commons:189:23) {
      length: 113,
      severity: 'ERROR',
      code: '42P01',
      detail: undefined,
      hint: undefined,
      position: '13',
      internalPosition: undefined,
      internalQuery: undefined,
      where: undefined,
      schema: undefined,
      table: undefined,
      column: undefined,
      dataType: undefined,
      constraint: undefined,
      file: 'parse_relation.c',
      line: '1449',
      routine: 'parserOpenTable'
    }
  ]
}
error: delete from "sidequest_jobs" where ("completed_at" < $1 or "failed_at" < $2 or "canceled_at" < $3) - relation "sidequest_jobs" does not exist
    at Parser.parseErrorMessage (/path/to/project/task-runner/node_modules/pg-protocol/dist/parser.js:285:98)
    at Parser.handlePacket (/path/to/project/task-runner/node_modules/pg-protocol/dist/parser.js:122:29)
    at Parser.parse (/path/to/project/task-runner/node_modules/pg-protocol/dist/parser.js:35:38)
    at Socket.<anonymous> (/path/to/project/task-runner/node_modules/pg-protocol/dist/index.js:11:42)
    at Socket.emit (node:events:524:28)
    at addChunk (node:internal/streams/readable:561:12)
    at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
    at Readable.push (node:internal/streams/readable:392:5)
    at TCP.onStreamRead (node:internal/stream_base_commons:189:23)

[GiovaniGuizzo]

How are you configuring the backend? You need to provide the schema if you applied migrations before on a different schema.

[frederikheld]

I don't change anything in the Sidequest schemas. I'm just using Sidequest with the same Postgres database I'm deploying my own schemas to. Those schemas are in different search paths, so there should be no interference, as Sidequest allows me to configure a custom search path.

The issue here is not the schema but the reused connections from the transaction pooler. The connection will run the query on the search path that is currently set. If the previous request set a different search path than the one that I have configured for Sidequest, the query will fail. Connections are re-used across schemas!

Sidequest needs to set the search path before it runs the query. Otherwise it can't be used with pooled connections.

[frederikheld]

Maybe it shouldn't be used with pooled connections? As it is polling the database every 100 ms, the direct connection might be the better choice?

The issue with the direct connection was that it breaks after some time. Idk if it breaks because Supabase terminates idle connections (it shouldn't be idle if it polls every 100 ms), if Supabase terminates connections after a certain time or if it was terminated due to network issues.

However: the direct connection was unreliable. But the pooler doesn't work well either.

So what's the reliable way to connect to a postgres database that is neither on the same server nor in the same data center (I think it isn't even on the same continent) than my Sidequest task runner?

[GiovaniGuizzo]

Seems like that the problem is Supabase's strategy in keeping connections alive. If neither the pooled connection nor the direct connection work with Supabase, maybe you are missing something. Maybe a timeout somewhere?

Have you managed to solve this issue?

[frederikheld]

Thank you for coming back to this issue.

It works using the Session Pooler, but it's not an optimal solution because the session pooler is shared across Supabase tenants and Supabase only recommends it as a fallback if the Transaction Pooler is not an option. Both are made for short-lived connections, so I don't really see the difference here.

How is Sidequest dealing with the connections? In my own code I make sure to always use a fresh connection if working with the poolers. It feels like Sidequest is re-using the connection and has no fallback to get a fresh one if the connection dies. I feel like this might be the issue because Sidequest works using the transaction pooler for some time until I suddenly get the error.

[GiovaniGuizzo]

Sidequest uses Knex under the rug to manage the connections. If you can configure timeouts/reconnection strategies with Knex, then you can configure with Sidequest. We don't actively touch low-level DB connections - we simply delegate it all to Knex.

That said, what we do is to re-use the same connection across the system. IIRC, we create one connection for the main worker, then one connection for each worker (job runner). If we could, we would share the same connection with all workers, but that's infeasible due to worker threads architecture.

[frederikheld]

"we create one connection for the main worker"

This is the issue then. I would need to use the Direct Connection for that, which is long lived. But the workers operate in the serverless regime, so the Direct Connection would be the sub-obtimal approach.

Would it be by any chance possible to either configure two different connections for the Sidequest core and its workers or update the core in a way that it can handle Transaction Pooler connections?

Here's a comparison of the different connection methods: https://supabase.com/docs/guides/database/connecting-to-postgres#how-to-choose-the-right-connection-method

[GiovaniGuizzo]

I really don't understand what is your request here. Take a look at that: https://github.com/sidequestjs/sidequest/blob/master/packages/backends/postgres/test/pooled.test.ts

How are you not able to use pooling with Sidequest?

[frederikheld]

The issue is that Sidequest core (I think that's what you call the main worker?) is a long-lived process that works best with a Direct Connection because it is designed to be long-lived.

But each job runs in a worker which are short-lived processes and those work best with pooler connections because those are less limited wrt. number of parallel connections (and other stuff, I don't know all the details, why Supabase recommends one over the other).

Supabase advises to use the transaction pooler but using that in Sidequest will cause dropped connections after a while. So currently I'm using Supabase's Session Pooler, which seems to be the fallback for both connection methods and isn't really performant in either of those scenarios. Supabase doesn't recommend to use it at all.

I don't understand well enough how Supabase handles database connections. But what you said sounds like you are creating many short-lived connections (one for each worker) with the same connection url that also serves the long-lived core. This creates a conflict of interest when choosing the right connection type.

IIRC, we create one connection for the main worker, then one connection for each worker (job runner).

[frederikheld]

BTW: I don't think that this has anything to do with the pool that I can configure in Knex. This is the pool Supabase provides and manages. You can get as many connections as you want from that pool (within limits that are different depending on the connection type you're using).

[frederikheld]

I did some more digging on this and it turns out that it is a known issue with Knex, which is currently not solved, but being worked on.

TL;DR: Knex Pooling and PgBouncer (Postgres' pooling solution) don't work well together. Knex is not a good fit for stateless applications because pooling can't be turned off. The Knex team is aware, but fixing this appears to be rather complex.

Discussion from the developers' perspective:

• How to disable knex connection pooling? knex/knex#5159 (comment)
• META: External Connection Pooling and Custom Pool Integration knex/knex#6286

Discussion from the users' perspective (which seems to be the same issue I have):

• PgBouncer transaction pooling vs. Knex pooling? knex/knex#3069

I'm gonna try the workarounds and see how far it gets me.

[frederikheld]

Setting Knex's pool size to one seems to work for now:

await Sidequest.start({
  backend: {
    driver: '@sidequest/postgres-backend',
    config: {
      pool: {
        min: 0,
        max: 1
      }
    }
  }
})

IDK the performance implications of this. Will all jobs share one single connection now, or does each job open their own connection anyway? It's definitely more performant than failed jobs though :-D

[GiovaniGuizzo]

Each job will open their own connection (if necessary - only when you enqueue a job from within a job) because they live in a different thread.

You will probably have 2 connections most of the time: one for the main worker and one for the job executor worker

[frederikheld]

Will the main worker keep its connection open or will it open a connection whenever it is needed and close it afterwards?

[GiovaniGuizzo]

It will keep it open based on the pool configuration. If the pool min is 0, it might be closed after some idle time and reopened when needed. If you want to ensure the connection is always open, set both min and max to 1.