DT
Drizzle Team•5w ago
Misc

Consistently hitting max clients/connections on postgres

Hey, I just launched my app 2 days ago, its being hosted on vercel atm. Despite having barely no users (max spike was 34 users/h) I keep getting an error about hitting the max connections allowed on the db. This is a Nextjs app using Drizzle along with Supabase. Only using the supabase client for checking auth, always awaited, and all db queries are done through Drizzle. I am using the transaction pool url and followed the setup on the supabase docs. I will also be posting this on the Supabase discord. Has anyone experienced something like this before? I imagine its something related on how the clients are getting called or the client setup but any help is more than welcome.
6 Replies
Tyler
Tyler•5w ago
I had this a lot on dev mode, but not production. What's your config look like?
Misc
MiscOP•5w ago
I was having this on dev as well due the HRM but i changed a bit the config and it helped a lot, just not sure why this would happen on prod tho. 
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as schema from "./schema";

declare global {
  // eslint-disable-next-line no-var
  var postgresSqlClient: ReturnType<typeof postgres> | undefined;
}

let postgresSqlClient;

const connectionString = process.env.DATABASE_URL;

if (!connectionString) {
  throw new Error("DATABASE_URL is not set in .env.local");
}

// Connection pool settings
const poolConfig = {
  max: 20, // Below Supabase limit of 30
  idle_timeout: 10, // 10 seconds
  connect_timeout: 5, // Timeout after 5 seconds if connection cannot be established
  max_lifetime: 60 * 30, // Connection lifetime of 30 minutes
};

if (process.env.NODE_ENV !== "production") {
  if (!global.postgresSqlClient) {
    global.postgresSqlClient = postgres(connectionString, {
      ...poolConfig,
      prepare: false,
    });
  }
  postgresSqlClient = global.postgresSqlClient;
} else {
  postgresSqlClient = postgres(connectionString, {
    ...poolConfig,
    prepare: false,
  });
}

// Disable prefetch as it is not supported for "Transaction" pool mode
export const db = drizzle(postgresSqlClient, { schema });
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as schema from "./schema";

declare global {
  // eslint-disable-next-line no-var
  var postgresSqlClient: ReturnType<typeof postgres> | undefined;
}

let postgresSqlClient;

const connectionString = process.env.DATABASE_URL;

if (!connectionString) {
  throw new Error("DATABASE_URL is not set in .env.local");
}

// Connection pool settings
const poolConfig = {
  max: 20, // Below Supabase limit of 30
  idle_timeout: 10, // 10 seconds
  connect_timeout: 5, // Timeout after 5 seconds if connection cannot be established
  max_lifetime: 60 * 30, // Connection lifetime of 30 minutes
};

if (process.env.NODE_ENV !== "production") {
  if (!global.postgresSqlClient) {
    global.postgresSqlClient = postgres(connectionString, {
      ...poolConfig,
      prepare: false,
    });
  }
  postgresSqlClient = global.postgresSqlClient;
} else {
  postgresSqlClient = postgres(connectionString, {
    ...poolConfig,
    prepare: false,
  });
}

// Disable prefetch as it is not supported for "Transaction" pool mode
export const db = drizzle(postgresSqlClient, { schema });
Tyler
Tyler•5w ago
Is there a specific reason why you set poolConfig? This is my implementation, which is similar but I just use the defaults of things. 
const connectionString = String(process.env.DATABASE_URL);

declare module global {
  let postgresSqlClient: ReturnType<typeof postgres> | undefined;
}

let postgresSqlClient;

if (process.env.NODE_ENV !== 'production') {
  if (!global.postgresSqlClient) {
    global.postgresSqlClient = postgres(connectionString);
  }
  postgresSqlClient = global.postgresSqlClient;
} else {
  postgresSqlClient = postgres(connectionString);
}

export const client = postgresSqlClient;
export const db = drizzle(postgresSqlClient);
const connectionString = String(process.env.DATABASE_URL);

declare module global {
  let postgresSqlClient: ReturnType<typeof postgres> | undefined;
}

let postgresSqlClient;

if (process.env.NODE_ENV !== 'production') {
  if (!global.postgresSqlClient) {
    global.postgresSqlClient = postgres(connectionString);
  }
  postgresSqlClient = global.postgresSqlClient;
} else {
  postgresSqlClient = postgres(connectionString);
}

export const client = postgresSqlClient;
export const db = drizzle(postgresSqlClient);
Misc
MiscOP•5w ago
just testing things out, thought it might solve the issue ahahah
Jökull Sólberg
Jökull Sólberg•3w ago
I'm also getting max connections. I'm using the same module level configuration to ensure I'm re-using the pool. Are you using Fluid runtime? @Misc I'm also using Supabase, but I'm on the pro plan so I get a dedicated pgbouncer instance to connect to. However even on that I'm getting max connection errors! (1000 is the max)
Misc
MiscOP•3w ago
I think the issue might stem from serverless deployment solutions. This is just me speculating as i haven't looked into it yet but could be that each instance just has its own connections and clients

Did you find this page helpful?