Prisma with pgbouncer behind a load balancer
Hi friends! We are running prisma with pgbouncer behind an AWS network load balancer to handle multiple pgbouncer instances. This generally is working great.
Our problem though is that when we bring up new pgbouncer instances, existing web app processes only use it for new connections (not existing ones) and so load is only actually evenly balanced after a server restart.
Ideally I would want a way to set an idle connection timeout in Prisma in our application so that connections would get recycled if they're not being used, but I don't see any option for that. In fact the only thing that I've found that's related to this is this reference to Zombie connections which implies that Prisma can't do this, but not why it can't.
There is a client_idle_timeout option in pgbouncer that can disconnect idle clients on pgbouncer's side but the docs say this should only be used for network problems.
It seems like one of the main benefits of using pgbouncer is cheap connection setup cost, so this feels like a pretty normal thing in this case despite the fact that it's normally a good idea to set up connections once and leave them for the life of the process. Have any of you run into this? Is the right way to do this to set the client_idle_timeout in pgbouncer? Is there an idle connection timeout in PrismaClient that I'm not seeing? Thanks for the help!
Database connections | Prisma Documentation
Databases connections
3 Replies
Hello @travisbeck 👋
Ideally I would want a way to set an idle connection timeout in Prisma in our application so that connections would get recycled if they're not being used, but I don't see any option for thatThere isn't an inbuilt Prisma option to achive this. However, I found this related GitHub Issue were a user posted this script to remove idle connections after a specified amount of time. I think this is similar to what you are looking for: https://github.com/prisma/prisma/issues/9044#issuecomment-936252891
GitHub
Node-API/
LibraryEngine: $disconnect does not free up memory / k...When Node-API/engineType=library is active, calling $disconnect() does not free up the memory the instance of the Engine is using. This is because the engine is not killed as it is/was with the Bin...
Hmmm, this looks like it would only work to terminate the connections from pgbouncer -> postgres, not from app -> pgbouncer since it's a query that has to run against postgres
This seems like it should be the job of Prisma to me, but it sounds like the pgbouncer client_idle_timeout might be my only option here?
Will prisma gracefully handle getting disconnected from the db by pgbouncer?
This seems like it should be the job of Prisma to me,I wasn't able to find a feature request for this scenario. This request was similar but not exactly the same. Do you mind creating one?
the pgbouncer client_idle_timeout might be my only option here?I would recommend trying the parameter in your dev/staging environment and check if you see improvements 🙂 Using
client_idle_timeout seems a better option than running the script from GitHub Issue in my opinion.GitHub
The initial size of the connection pool should be able to be specif...
Problem The web server starts with an empty pool because there is no initial size specification for the pool in the tuning parameter of the connection pool. Until the pool is filled, the latency wi...
GitHub
Build software better, together
GitHub is where people build software. More than 100 million people use GitHub to discover, fork, and contribute to over 420 million projects.
