What is the risk of the Postgres connection pool being too large?
I am doing a ton of jobs in parallel using Sidekiq and many of them cannot connect to the database because I only have a connection pool size of 5.
I would like to just bump up to 15 (at least on localhost), but wondered what the possible negative implications of this might be.
Setup is Ruby on Rails, default is 5.
source to share
It depends on many factors, such as:
- how much memory you want to allocate to your database pool.
- how long does your connection last.
- connection timeout
- the locality of your database server versus your application / webserver
There are other settings that some connection pools have, such as the minimum number of connections that are open (even if they are not in use) and the maximum open connections that look like you are trying to establish.
I heard that you can redirect your NIC with just 10 open connections.
I think the only answer is to track your cpu / memory / io usage based on what you have, so you have some kind of baseline and then bump the connection count and comparison.
Personally I think you should be fine with 15 connections, assuming you aren't "pushing your server to the limit already or have a tiny virtual machine with 256MB of RAM :)
Setting it too high can saturate # of allowed open connections for postgres (check the default, but it could be around 100). This can be especially problematic if you close your services prematurely, preventing you from gracefully closing connections. Then when you try to restart the app server, it will falsely state that postgres does not allow any additional connections. This is not a too high level problem as it will happen anyway, but it will be def. speed up the problem.
source to share