I have a golang program that does computation on data in multiple threads at once, all pulling their data from Postgres. The number of threads depends on a previous result. There can therefore be hundreds of threads trying to pull data from Postgres at the same time.
The golang sql library allows one to specify a connection limit, which prevents postgres from running out of shared memory or free connections.
If I hardcode the maximum number of connections I will run out of connections when something else is connected. On the other hand if I hardcode a too low number of allowed connections from the golang program performance will be unnecessary limited.
What would be the best way to allow the go program to use as many as possible connections, without running into the limits. I imagine this number to be a variable one depending on the number of other services that are connected to the database at that time.
I am thinking of running PgBouncer between the database and the golang program, hopefully accepting all the connections from the golang program, allowing as many as possible through, but blocking the rest until connections become free. I am however unsure if PgBouncer does this, but I will be testing this next.
Is there perhaps another method of having a connections pool that will block connections when no real connections are free? Blocking, not refusing, as refusing a connection will mean I have to add retry logic to my golang program.