Best practice on connections for PostgreSQL

I got the following error;

remaining connection slots are reserved for non-replication superuser connections

remaining connection slots are reserved for non-replication superuser connections

remaining connection slots are reserved for non-replication superuser connections

remaining connection slots are reserved for non-replication superuser connections

So I'm currently the only person using my DB in dev mode - and only 2 apps in a Turborepo are connected to it.

One is a Next.js back-end and one is an SST AWS stack - they both are connected through the code below;

import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';

const pool = postgres(process.env.DATABASE_URL as string);

const client = drizzle(pool, { logger: true });

export { client, pool };

import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';

const pool = postgres(process.env.DATABASE_URL as string);

const client = drizzle(pool, { logger: true });

export { client, pool };

import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';

const pool = postgres(process.env.DATABASE_URL as string);

const client = drizzle(pool, { logger: true });

export { client, pool };

import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';

const pool = postgres(process.env.DATABASE_URL as string);

const client = drizzle(pool, { logger: true });

export { client, pool };

Drizzle is my ORM and postgres (also known as postgres.js) is my PG client. Whenever, either in my Next.js app or in my SST app, I want to connect to my database - I'll just call my

client

client

client

client constant, and apply either a

client.select()

client.select()

client.select()

client.select() or any other query I want to run.

The problem ? Well, running a little

SELECT * FROM pg_stat_activity

SELECT * FROM pg_stat_activity

SELECT * FROM pg_stat_activity

SELECT * FROM pg_stat_activity returned me 76 connections (all from my machine, ClientRead, on status idle)...

It's as if whenever I call my

client

client

client

client it opens a new connection, ultimately reaching PSQL limit.

What's the best approach ? I believe if many users connect to my app once it's live, it'd be problematic.

I've heard about pgBouncer but I don't know if I need session or transaction mode to be enabled.

Any best practice ? Again, I just want my Next.js app to support any number of user I want (like 1000 daily users) without any problem with my PSQL reaching connection limits.

Best practice on connections for PostgreSQL

Similar Threads

Similar Threads

Similar Threads