[Feature]: Add pgBouncer as a built-in application

[rayzr522]

Is there an existing issue for this?

• [X] I have searched the existing issues

Summary

a lot of my development is in the serverless space, and dealing with serverless + database connections is a pain without proper DB connection pooling. it would be really nice if pgBouncer was available as a built-in application.

additionally, it would be really nice if this could integrate with existing postgres databases to make setting up the connection pooling a bit easier! not sure what's feasible here, but in an ideal world it would give you some option to connect to an existing Postgres database in coolify and automate some of the setup of the auth_query -- there are more details in this blog post

it's mainly just a matter of creating a user, schema, function, and then doing some access control. it should be doable, given that coolify would know the superuser username/password for postgres so it could just log in and run a few queries. that being said, even if that bit is manual just having pgBouncer as a built-in application would be hugely helpful :)

Why should this be worked on?

serverless + postgres is pretty impossible without proper connection pooling. preferably I would use something like Vercel or CloudFlare Pages to host my serverless front-end, and then host all my stateful infra like DB and backend services on coolify, but it would make things a lot easier if Coolify could natively support pgBouncer to assist in that setup process. it would also make it stand out compared to even some of the paid alternatives like railway.app, as they don't support pgBouncer natively :)

would be a huge boost to the value of coolify as the primary platform for managing your infra all in one place!

[kubeworkz]

Awesome idea. Also, thanks for the blog post link. You solved a long standing issue I had with pgbouncer and that userlist. Kudos

[klawdyo]

🎂🎂🎂🎉🎉🎉🎉🎉🎁🎁🎁🎁 1 year

[muke5hy]

Today I learned about Coolify, and first thing I wanted to search apart from postgres was about pgBouncer, It would be great to have this in build.

[sebiweise]

Would be great to be able to enable pgbouncer on my postgres databases

[mbeaudru]

If someone has managed to set this up, a tutorial / bulletpoint list would go a long way 🙏

Personally I feel like I'm not so far from having something working by instantiating https://github.com/edoburu/docker-pgbouncer, configuring the port & DATABASE_URL, yet I didn't succeed in making my app communicate with my database through pgbouncer

@andrasbacsai the use case for this is pretty simple: I would like to self host everything but the web-app, that could be hosted on something cheap but scalable like cloudflare-pages or AWS. At least at launch day where it's hard to predict traffic spike, then go back to full-selfhost if relevant.

For this to be possible, the web app has to communicate with a DB pooler since cloudflare pages only run on the edge + to be safe

---

A better alternative would be to make it very easy to spin up web-app instances with traefik load-balancing on coolify (since node is single threaded, it would help a lot). A simple "instances number" option on the web-app configuration page to hide the traefik config + automatic deployment stuff with git integration would be soooo great, but that's a lot more work I guess 😅

Thanks for opensourcing coolify, it's an incredible project 🙏

[mbeaudru]

In all fairness, I found out that it's actually pretty easy to use pm2 in cluster mode to run as many instances of my node app I need in a single container to act as a load balancer on my server

https://jonascodeblog.vercel.app/blog/horizontally-scalling-nextjs

It needs tweaking to work, I will update this comment to say which tomorrow 👍

---

How to setup multiple nextjs app instances in cluster hosted on coolify:

• Install pm2 dependency in your project (npm i pm2)
• Create a ecosystem.config.js file at repository root

Fill the ecosystem file with:

module.exports = {
  apps: [
    {
      name: "my-app-name",
      script: "node_modules/next/dist/bin/next",
      args: "start",
      instances: "max",
      exec_mode: "cluster",
      autorestart: true,
    },
  ],
};

Note: here "instances" is set at max, so if your VPS has 8 cores it will spin up 8 instances. You can change it to a number if you need to

• Create a nixpacks.toml file at repository root

Fill the nixpacks file with the following:

[start]
cmd = "./node_modules/.bin/pm2 list && ./node_modules/.bin/pm2 start ecosystem.config.js --no-daemon --log"

Note: This instructs coolify to start your project using these commands instead of next start Note 2: without the "pm2 list" before it won't work! Strange but 🤷‍♂️

Important note: If this is a nextjs app, you'll need to setup a redis database with a custom cache-handler file to share the cache between the instances, otherwise your app will be broken if you make mutations. I strongly recommend @neshca/cache-handler for this 👍