Setting up PgBouncer
hey everyone! I'm currently in the process of setting up PgBouncer for my postgres.
I cloned https://railway.app/template/L09YMd and modified it to support PRIVATE_DATABASE_URL and have gotten it to stand up, but have a few questions.
1.) It says it's listening to 0.0.0.0:6432, but the Dockerfile says expose 5432 (postgres). Do I need to change any of these to PORT for Railway networking to work? I need to be able to create a connection string for my prisma clients in NextJS Serverless functions to use
2.) Do I only need 1 replica of pgbouncer? Based on my understanding it consumes all available connections to postgres and pools them together, so having multiple replicas wouldn't make sense right?
3.) For my long running applications (10 replicas of Bun, should I bypass pgbouncer and connect directly to the postgres via private network?) I think I need to carve out those connections so they're not consumed by PgBouncer?
225 Replies
Project ID:
f0b123dd-49d4-4bfc-b063-9a5238f876efRailway
404 - Page not found
Railway is an infrastructure platform where you can provision infrastructure, develop with that infrastructure locally, and then deploy to the cloud.
hmmm
Prisma
YouTube
Prisma Accelerate: One THOUSAND Times Faster Database Queries!
Discover the power of Prisma Accelerate, now available to everyone! In this video, we explore how Prisma Accelerate simplifies connection pooling and data caching, making your database operations faster and more efficient. Learn how to boost your application's performance effortlessly with Prisma Accelerate's easy-to-use features.
Prisma Accel...
prisma accelerate looks nice and handles connection pooling
might be worth to just use that and have it in the same region
It says it's listening to 0.0.0.0:6432, but the Dockerfile says expose 5432 (postgres). Do I need to change any of these to PORT for Railway networking to work? I need to be able to create a connection string for my prisma clients in NextJS Serverless functions to usei dont know what exactly you would need to change / add to make it work, but you would need to do some modification as the template is not uh perfect for lack of a better word
Do I only need 1 replica of pgbouncer? Based on my understanding it consumes all available connections to postgres and pools them together, so having multiple replicas wouldn't make sense right?makes sense, though its not like ive used pgbouncer before
For my long running applications (10 replicas of Bun, should I bypass pgbouncer and connect directly to the postgres via private network?) I think I need to carve out those connections so they're not consumed by PgBouncer?im not sure the best way to go about this tbh
@Brody is there a good resource to read about PORT that railway adds?
I think the docker container by itself tries to expose the main postgres port 5432
to act like a passthrough for the postgres that it connects to
if you dont set a PORT yourself, railway will set a random one for you, not all that much to read about
well I don't think it's getting used by the pgbouncer container rn
the
EXPOSE keyword in a dockerfile does not actually expose anything, its mearly to say "hey this docker container likely has an app running on x port"ah true
forgot
pgbouncer would use tcp connections right?
i don't know
am a little confused because the ini file says port 6432
but it obviously intends to use 5432
Prisma
YouTube
Deploying a Prisma app to Vercel and setting up connection pooling ...
In this video, Daniel shows how to deploy a Prisma app to Vercel and connect it to a PostgreSQL database in DigitalOcean, set up connection pooling with PgBouncer, and load test the API with K6.
00:00 - Intro
02:00 - Exploring the REST API
05:50 - Creating the PostgreSQL database in DigitalOcean
07:20 - Setting up PgBouncer in DigitalOcean
09:...
for all intensive purposes, you can ignore EXPOSE
for tcp, the PORT variable is irrelevant, as you just set the tcp proxy up with what port the apps does listen on
what is PORT for then?
http apps
oh i see
for anything that needs 443?
for anything http
gotcha
so ignore port for now then
yes, does pgbouncer have a tcp proxy?
not sure
never knew what pgbouncer really was till today
pgbouncer does indeed use tcp, same as postgres itself, give this a quick read https://docs.railway.app/deploy/exposing-your-app#tcp-proxying
ahhh wait
so I wouldn't create a domain for it?
nope, domains are for http
gotcha
can the tcp proxy support production loads?
maybe i'm confusing it with the generated domain limitations
the domains are only for http traffic, pgbouncer uses tcp
i see
that makes sense
ok, now need to figure out why pgbouncer is listening to 6432
rather than 5432
@Brody can applications only have 1 proxy?
thats correct
public at least, the private network has no such limitations, with the private network there is no proxy or firewall on the services what so ever
and I should be able to connect over monorail.proxy.rlwy.net?
publically
if you set it up right, yes
am trying to connect to the db through pgbouncer but not having any luck 
know why
hehe
one second
ok, I'm able to connect to the database directly
now going to try to connect via pgbouncer
know why
hehe
one second
ok, I'm able to connect to the database directly
now going to try to connect via pgbouncerhaha dont make me fix the template
π fix how
ok can confirm
am not able to connect
hmm
you seem to be doing a lot of messing about, templates should be usable out of the gate
this template is clearly not
the main thing was that it didn't work with DATABASE_PRIVATE_URL
also I didn't use the template, I cloned it and then added it to my project
it doesnt have one lol
ik, that's why I had to modify it
cause i want it to use private network
why didnt you just deploy it into your project and add whats missing after the fact, i know not ideal but thats the best flow
i had to change the code
why?
because it doesn't read DATABASE_PRIVATE_URL in the script at all
had to change this section of the sh script
dont think it needs to
for it to use private networking it does, right?
eh not really
i mean i could make variables but i thought i had to keep it as a refernce variable in case it updates
you can do both
let me test
also his parsing code wasn't quite right
it doesn't parse postgres.railway.internal as a proper host
why do you need to lol
because I want pgbouncer to use the private network when connecting to postgres?
i'm really confused what you mean lol
i don't want it to connect over tcp proxy
im stalling in hopes it will click
i am using this btw https://railway.app/template/L09YMd
i think that'd work
assuming it bypasses the parsing code
i dont even know what any of that code does, or why its needed
overcomplicating it
it creates the pgbouncer ini
yeah i get that part
the code above it was to handle if it got a database_url instead of the 5 split out variables
dont see why
^
hmm
true
i can go back and change it to your way so i don't need my own repo
but also doesn't quite fix the deployment issue
i can't seem to connect to pgbouncer 
sure why, it looks like it stood up properly
sure why, it looks like it stood up properlyrailway clones the repo so you have your own repo regardless lmao
fair enough lol
i think it would be cool to have an option to not clone
still testing stuff tho
oh hmm
i overcomplicated this huh
this thing looks fully modifable via env variables
is this how you do reference variables to other services?
yep, these are ref variables
why Postgres.5432?
haha typo, should have just been 5432
oh wait
can you not deploy templates into existing projects?
i think youre running into a bug there
leave beta
how?
ctrl / cmd + k
all good, already redeployed it in my project
can u send me this raw real quick please
haha its long gone
no worries
lmfao that works too
missed the dollar signs tho
where does railway private domain come from
u added?
its a variables thats automatically available https://docs.railway.app/develop/variables#railway-provided-variables
interesting
so that would return
postgres.railway.internal
indeed
cool
alrighty, redeploying
cool
worked first try
now to see if i can connect
hmmm
dns resolution error eh
check pgbouncers logs
it doesn't note it
im still testing
ur the best
got a public connection to pgbouncer working with this image https://hub.docker.com/r/bitnami/pgbouncer
oh you weren't able to connect in the template image?
i forget haha
i like docker images more
ok i'll try that image
woah these logs are so much more colofurl
nice
think i got it working
now yes theres an error, but thats an issue with the client
docker hub is so nice
i used to use it more
but i forgot
how nice it is just to be able to pull lego blocks down
indeed, going to do some more tests and then will make you a template
need to calculate number of connections and stuff
oops
RAILWAY comes first
hmm
don't worry I'm on the job
updated the client in my testing software and
π
HUGE
making template now
Solution
comes with pre configured ref variables, but it assumes your database is named "Postgres"
@Brody i left priority boarding but it still wants to deploy the template to a new project?
oh i have to do it through the project
exactly
beautiful
let me know if you run into any issues
just got it deployed
though I did try deploying the template into a project that already had a postgres database and it worked no config needed
@Brody if it's the same as the docker image, we should be able to alter other settings via env var right?
absolutely
π π π π π π π
awesome
you. are. the. best.
thank you
but you know, just test it as it is for now
of course, but i'll need to switch it to transaction mode in order to work with prisma + serverless
rather than the default of session
true true
also, i'm not sure if it's configured for the correct pool size
something something core count * 2?
default is 20
could be higher right?
yep, but ill leave my template at the defaults, will add them to the template so the user can config them during deploy if need be
hype!!!
we're connected
ty
no problem
i wish i was able to remove the previous pgbouncer template that doesnt work
is it possible to add conditionals into variables?
prisma for example needs this query param and pool mode set to transaction
I guess I can just add that on my prisma end
or just create my own directurl variable
you could add them on the reference variable on your app's service (not the pgbouncer service)
well so I'll need it synced to vercel
or i can have doppler send it down to vercel
but needs to get somewhere
i think that'll do it for me
not sure why it picks the vars it does
haha you wont be able to use the private url, but yeah
yeah 
wasn't expecting too on the nextjs side of things
that's ok tho
it's kind of an interesting setup
why does it pick the vars it does?
wasn't expecting too on the nextjs side of things
that's ok tho
it's kind of an interesting setup
why does it pick the vars it does?zero clue, i dont use vercel, frontends go on railway too π
nextjs features act weird not on vercel
they did make the framework after all
@Brody on a pro plan what should I consider core_count?
32 vCPU * 2 = max db 64 connections?
* 2 = 64 connections?
uh whats spindle count?
TIL
uh well volumes are SSDs
so just set it to 64 and call it a day, remember pgbouncer reuses those 64 connections
supposedly it actually hurts performance if you have connection count too high
because you want pgbouncer to handle things
not pg
good to know, then leave at 20
hey im learning with you!
are vCPUs actually CPU cores?
or are they like hyperthreaded
Medium
Database Connections: Less is More
As far as architectural aphorisms go, Mies van der Roheβs βLess is Moreβ seems to succinctly define a modernist ethic. Whatβs less wellβ¦
hmm
actually this is hyper optimizing
i won't fiddle with this
a team member told me the answer to this, but i forget, sorry
i'll leave it at defaults for now
lmao i was just gonna tell you this
defaults are fine lol
i mean this whole journey is hyper optimization so far
moving postgres from neon to railway
true, bun
but hey, it's finished π
thanks for all the help
ofc, its been fun and ive learnt lots
@Brody just learned something new that might be worth mentioning in your template
GitHub
Fully support prepared statements with pgbouncer 1.21.0 Β· Issue #21...
Problem Today, the way prepared statements are supported with pgbouncer is suboptimal. Prisma will clear all prepared statements in the connection to avoid mismatches. Now that pgbouncer supports p...
the latest version of pgbouncer (1.21.0) which the template uses, will cause prisma to break if you don't have ?pgbouncer=true in the connection string and max_prepared_statements = 0
but
you get significant performance gains by removing the pgbouncer=true from the connection string and setting max_prepared_statements to a non-zero value
is
max_prepared_statements a query parameter?
and what's a good "non-zero" value anyway?hey @Brody is this working? https://railway.app/template/L09YMd
no it's not, use mine https://railway.app/template/OpUzwe
if you don't already have a postgres database, first deploy postgres on your own, and then deploy that template into the same project
ok done , will that automatically take the database
that I have in the same project?
indeed
you now reference the url variables that the pgbouncer service has
that will be this one right?
yep!
I can not get connected π¦
well you would need to use the public url to connect to it publicly
and then use the internal url to connect to it from another service in the same project
yes , you are right now it is working
awesome
qq how do I make sure pg bouncer it is working in transaction mode?
I guess I need to change it here ?
correct
simply set it to
transaction
you can read about more environment variable options here https://hub.docker.com/r/bitnami/pgbouncerthank you
no problem!
I don't know what I'm doing wrong or maybe it is just Prisma, with nextjs (vercel) , I added the pg-bouncer and changed the configuration as they suggested and im still getting these errors(image) is this normal? I thought the pg-bouncer was in charge of avoiding these errors πhttps://www.prisma.io/docs/orm/prisma-client/setup-and-configuration/databases-connections/pgbouncer
well are you sure you are using the url from pgbouncer in code?
yes
i updated the env variables on vercel too
well ill be honest, i dont use pgbouncer, next, or vercel myself so im not too sure what the problem is, i only know how to make templates and i made the template for harris and im sure they would have let me know if it didnt work
π
@Harris do you have any idea?
I think I know what your issue is
newest version of prisma does not need the connection pooler in URL enabled
it aactually breaks it
so make sure you don't ahve that
GitHub
Fully support prepared statements with pgbouncer 1.21.0 Β· Issue #21...
Problem Today, the way prepared statements are supported with pgbouncer is suboptimal. Prisma will clear all prepared statements in the connection to avoid mismatches. Now that pgbouncer supports p...
although actually, usually that'd show an error of "prepared statement s1 does not exist" or something
if you're pgbouncer is setup to connect to pg correctly
and you're correctly connecting to pgbouncer
u shouldn't have client issues
is my setup
I noticed you dont have these 2 values that appeared by default to me
i added them after they deployed my template
all the values i have set are the default
@Harris thanks that help at least now I see a different error, looks like know at certain point they can not connect with the pgboucer any idea on how to solve this?
any errors in the logs of pgbouncer?
doesnt look like anything bad, im out of ideas π€£
π
you can still access it publicly with database software right?
yes even with vercel after few moments , I think it just reaches a limit and then it rejects the connections ? π€·π»ββοΈ
i honestly have no clue, sorry