Should PGBouncer be used when using supabase with fly.io

[msevestre]

Hi there,
Thanks first of all for all the work you put into this. This is very helpful.

I am working on a project setting up supabase with fly.io and remix as well and I am trying to understand if the pgbouncer option of supabase should be used. I understand that this is required for serverless environments such as vercel or netlify. But what about fly.io

See

https://supabase.com/docs/guides/integrations/prisma#connection-pooling-with-supabase

[rphlmr]

Hello,

Good question.
From what I understand, I think it's not mandatory for fly.io

fly.io works different from AWS, vercel, netlify functions. These functions handle one request at a time and start new instances on load. After that they die and this is why bouncer is required (To prevent connection pool limit issue).

In fly.io, your server is like a classic one and stay alive.
I would use bouncer if I would have multiple replicas of my fly.io instance (maybe > 3)

https://supabase.com/blog/2021/04/02/supabase-pgbouncer

The pool size seems to be 15 (may it changed since 2021).
Default pool mode is transaction (on my database). But Session could be use I guess because with fly.io the prisma client stay connected (the fly.io instance doesn't end after a request, like functions).

After all, I think it's safe to use bouncer in case of (I don't have a heavy load on my very young "MVP" project :D ).

Thanks to open this discussion, It could be very valuable if such issue happen on projects !

[michaelwills]

First, thank you for the project @rphlmr. I hadn't checked for a few months and the updates look quite good!

For fly.io once you go multi-region it helps. Migrations will fail though unless you use the original non-pgbouncer URL. See prisma/prisma#14069. I have some other values in the connection string so I override it with this which works so far:

    "db:deploy-migration": "cross-env DATABASE_URL=$(dotenv -- bash -c 'echo $DATABASE_URL_NO_PGBOUNCER') prisma migrate deploy",

though that feels clunky. The other issue mentioned you really just need use the 5432 default port for migrations.

[rphlmr]

Thanks for the feedback.
If you don't like what you did, you can create a .env for migration only :

"db:deploy-migration": "dotenv -e .env.production.migrate -- prisma migrate deploy"

I don't handle multi-region in my current mission but I have some .env for different "CI/CD" actions (migrate in dev env, migrate in prod, prepare migration, etc)