Getting max connection reached using supavisor

[marcustut]

As far as I understand although the default max_connections limit is 60 from Postgres, if we use supavisor to connect to it we shouldn't be restricted by that right? Or is there also an upper limit to how many connections supavisor can support?

This the exact error I get PostgresError: Max client connections reached, this happens only when they are a sudden surge of requests.

FYI, I'm establishing a new database connection on every user request then close it once the request returns, similar to how serverless functions work even though it is a long-running process.

[MightComeback]

Having the exact same issue, are you using Prisma? There are some reports prisma x supavisor doesnt work properly, it completely evaporates all of the connections you have and the only solution is db restart,.

[sabigara]

I'm facing the same issue with Next.js (App Router) + Prisma on Vercel.

At first, the Prisma client couldn't connect to the DB but turned out that ?pgbouncer=true is required by some internal implementation of Prisma. With the query param, it started to connect to Suapabase but now I'm getting the Max client connections reached errors.

Adding connection_limit=1 didn't help. It leads to another timeout error.

[mlynch]

I'm also seeing this issue but not using Prisma, using pg-pool and PgBoss for queuing. Any progress since?

[MightComeback]

getting marginally less errors when setting the connection limit to 10 ?connection_limit=10, try it. pool size in the supabase dashboard is set to 100 btw.

[bennyszantar]

I also have this issue...

[Nicolasvegam]

same issue here!

[dwolrdcojp]

Seeing this issue with vercel next.js serverless and prisma

[lauri865]

Same issue. Even when way below the limit.

[rinvii]

I found a temporary fix.

Project Settings > Database > Network Restrictions

Click "Restrict All Access" and then re-enable access.

[rinvii]

I found a fix, at least for my use case with Drizzle ORM. I'm using Next.js in dev mode. Every time HMR occurred, I don't think the database connection was properly closed and it would make a new connection leaving the previous connections still open.

In order for the connection to persist across reloads, we can implement a singleton pattern:

// index.ts
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as schema from "./schema";

let connection: postgres.Sql;

if (process.env.NODE_ENV === "production") {
  connection = postgres(process.env.DATABASE_URL, { prepare: false });
} else {
  const globalConnection = global as typeof globalThis & {
    connection: postgres.Sql;
  };

  if (!globalConnection.connection) {
    globalConnection.connection = postgres(process.env.DATABASE_URL, {
      prepare: false,
    });
  }

  connection = globalConnection.connection;
}

const db = drizzle(connection, {
  schema,
  logger: env.NODE_ENV === "development",
});

export * from "./schema";
export { db };

[juliomuhlbauer]

This works!

[chasers]

Generally speaking, it’s because you are creating too many connections too quickly for whatever connection limits are currently in place for your project. Any combination of the following will help:

• increase compute
  • increases client connection limits with a compute add-on
• increase pool size
  • increases parallelism to the db
  • reduces concurrent clients needed for the same throughput
• decrease query time
  • allows more throughput per connection
  • reduces concurrent clients needed for the same throughput
• disconnect appropriately
  • immediately frees up unused connections
• reduce connections per client
  • don’t open unneeded connections on each client instance
  • clients often use a pool of their own, configure accordingly
• use transaction mode
  • session mode limits concurrent connections to the Supavisor pool size
  • use port 6543 with your pooler connection string
  • configure your client to disable prepared statements, all clients support this

[Ashwin27]

It looks like the right approach here is to use "transaction mode", but seems like this is incompatible with ORMs (SqlAlchemy, Prisma...) since the IPV6 migration. (Ref: prisma/prisma#22779)

Hence we are left to optimize the backend/scale out to circumvent for now. Is that a fair summary or am I missing something?

[dwolrdcojp]

ORMs are compatible. With Prisma you need to supply the pooler with session mode for direct_url. This will run migrations using direct_url and send all other transactions through the database_url hooked up to the pooler.

At our company we have actually just enabled the IPV4 for $4/month add-on from supabase (AWS fee passed onto us) which allows us to continue using the ipv4 non pooler direct url with our serverless hosting on vercel. Supavisor has very inconsistent latency even without serverless calls so we decided to increase our RAM and connection limits on the database plan instead of trying to manage it through the pooler.

[Ashwin27]

My apologies yes, the ORMs also work with the Supavisor. I must have missed this (or this was fixed after I tried it).

And thanks for your note on the add-on. Do you manage your own connection pool (in app or using PGBouncer) without the Supavisor or just rely on the increased RAM & connection limits?

[dwolrdcojp]

Currently we're just relying on the increased RAM & connection limits but our active users is very predictable so even though we're running function calls from Prisma on vercel serverless we haven't run into any issues with too many connections or connection timeouts. Probably not even close... just need to make sure you're not wildly spawning off multiple Prisma calls in a for-loop or something.

You can still tune the connection_limit and timeout I believe for the direct_url so that your serverless function doesn't spawn additional client connections and doesn't die prematurely if in the rare instance it needed to wait for a connection.

[chasers]

Supavisor has very inconsistent latency even without serverless calls so we decided to increase our RAM and connection limits on the database plan instead of trying to manage it through the pooler

@dwolrdcojp I think we've figured this out btw and should see nice p99 improvements next week.

[jenswilms]

I had a similar issue, using pg or node-postgress, I kept receiving this error.

Solution was simple: Supabase now defaults to session mode and you have to go to your Supabase dashboard and change the port to 6543 to use their transaction mode.

[Gregg-Casey]

@jenswilms are you saying that all I have to do is:

1. change the port in the Supabase dashboard.
2. Change the port that I use in my API calls BUT NOT change anything else in the API calls or code.
   And everything will work and the bug will go away?
   If so, that sounds great.

[SergioBarbero]

Having similar issues using Spring Boot. I have no applications or users using this database at this moment, and this error keeps appearing over and over again.

Any solution?