Automatically scale pool maximum connections

[tonyarnold]

I see that right now, DatabaseConnectionPoolConfig.maximumConnections is hardcoded to 10 by default.

It would be nice if this number could dynamically scale at startup based on something like:

database maximum available connections / number of workers (cores?) 

My database knowledge is rusty, but they all support some kind of connection reporting, right?, ie for Postgres:

select max_conn,used,res_for_super,max_conn-used-res_for_super res_for_normal 
from 
  (select count(*) used from pg_stat_activity) t1,
  (select setting::int res_for_super from pg_settings where name=$$superuser_reserved_connections$$) t2,
  (select setting::int max_conn from pg_settings where name=$$max_connections$$) t3

Produces:

max_conn	used	res_for_super	res_for_normal
300	175	3	122

[0xTim]

One issue I can see with querying the number of connections available/used is that it might cause problems if your app scales horizontally. E.g. if you have 300 connections and one app instance, we might choose

max_connections - some_buffer / number of workers

So let's say 200. However, if we then scale up to 5 instances we either end up in the situation where each app is trying to use 200 connections (so 1000 attempted connections for 300 max connections) or one instance has 200 and the other 4 have to share the remaining 100, meaning that they're less performant