Add PgBouncer for connection pooling in development

[haydn]

trafficstars

Feature request

Is your feature request related to a problem? Please describe.

I'm developing a Next.js app that often hits this error in local development:

sorry, too many clients already

This isn't a problem in production because it's using the connection pooling.

Describe the solution you'd like

Ideally, I'd like to be able to use PgBouncer for connection pooling by simply setting a different port for the local database connection.

Describe alternatives you've considered

Currently, I workaround this problem by tearing down the database and rebuilding it. Not ideal because you lose any data you've been working with.

Additional context

An additional benefit is that this will make the development environment more closely match the production environment, which could come in handy when debugging bugs that might be arise from things like the "Pool Mode" configuration.

[haydn]

It looks like Realtime is included nowadays, so I'm assuming it just needs its port exposed?

[soedirgo]

I believe we need to add a pgbouncer container for this (it's unrelated to Realtime)

[haydn]

Haha. Right, that makes sense. I got myself confused there.

[jeremyisatrecharm]

I'm also running into frequent local DB crashes via Prisma with the following error message: Error querying the database: db error: FATAL: sorry, too many clients already.

As noted by OP, tearing down the DB is a pain due to loss of data. Is there another workaround? Can we increase the connection limit? I do use Vercel's lambdas locally, but there aren't many open connections so I am not even sure why I am running into this (perhaps I have some bugs leading to long-open connections?)

[sweatybridge]

Currently you can run supabase stop --backup to preserve local data between restarts.

We are also working on integrating pgbouncer in a new postgres image. Stay tuned.

[jeremyisatrecharm]

Currently you can run supabase stop --backup to preserve local data between restarts.

We are also working on integrating pgbouncer in a new postgres image. Stay tuned.

Amazing, thank you!

[riccardolardi]

Currently you can run supabase stop --backup to preserve local data between restarts.

We are also working on integrating pgbouncer in a new postgres image. Stay tuned.

Hi @sweatybridge any update on this? I'm using a self-hosted version of Supabase and can't find any documenation whether it's possible to activate / install pgBouncer in such environment.

[bombillazo]

Any progress on this issue?

[sweatybridge]

Thanks for the reminder. We are currently working on a connection pooler to replace pgbouncer. I will look into adding pgbouncer container this week to help unblock some of the use cases. Just beware that we may repalce it in the upcoming months.