Type: Suggestion
Description:
As mentioned in the title we are using postgresql-ha helm chart deployed to a kubernetes cluster with 6 or so nodes. We initially thought that by increasing pgpool replicas that would increase the number of connections available to us. We also considered that we might also need to scale postgresql replicas in conjunction. Our app connects through a Load Balancer service (typically GKE provisioned) that is enabled through the values.yaml upon deployment.
However when we run tests we see connecting to the load balancer will select an available pgpool it seems based on some resource aside from available connections (CPU?) so it could be a random pgpool replica/pod. Then pgpool communicates to repmanager and postgresql and we ultimately hit the error “Too many connections” no matter how many pgpool/postgres replicas we create.
If we start at 1 pgpool and 1 postgresql replica we see the abillity to handle 30-40 connections before producing the error. If we increase to 2 pgpool and 1 postgresql we see double the connections capable before producing errors. However we hit a wall to where we can not achieve more than 105 connections. We assume 5 are reserved connections and the 100 is max_connection setting on the postgresql.
We are confused at this behavior from how the load balancer is selecting the pgpool and that all postgresql seem to be limited by the same amount of max_connections. Also confused that we see 5 more connections than max_connections. Do all postgresql replicas share the same max_connections meaning if we had 2 it would still only be 100 connections and in this case what is the point of pgpool replication or are we thinking about it incorrectly.
We see mostly idle connections when we query pg_stat_activity, but are confused on how told scale postgresql-ha without running out of connections in this case.