Connection Pooling Configuration

By default, StackGres deploys Postgres clusters with a sidecar containing a connection pooler. StackGres currently uses PgBouncer. The connection pooler fronts the database and controls the incoming connections (fan-in). This keeps Postgres operating with a lower number of concurrent connections, while allowing a higher number of external connections (from the application to the pooler). If no custom pooling configuration is specified at cluster creation, StackGres will create a default configuration, which you can see here.

StackGres provides a production-grade default configuration. You can provide your own pooling configuration, by creating an instance of the SGPoolingConfig CRD. The SGPoolingConfig is referenced from one or more Postgres clusters.

This is an example PgBouncer configuration definition:

apiVersion: stackgres.io/v1
kind: SGPoolingConfig
metadata:
  namespace: demo
  name: poolconfig1
spec:
  pgBouncer:
    pgbouncer.ini:
      pgbouncer:
        max_client_conn: '200'
        default_pool_size: '200'
        pool_mode: transaction

This definition is created in Kubernetes (e.g. using kubectl apply) and can be inspected (kubectl describe sgpoolconfig poolconfig1) like any other Kubernetes resource.

StackGres clusters can reference this configuration as follows:

apiVersion: stackgres.io/v1
kind: SGCluster
metadata:
  namespace: demo
  name: cluster
spec:
# [...]
  configurations:
    sgPoolingConfig: 'poolconfig1'

Reloading Configuration

The SGPoolingConfig Customizing Pooling Configuration Section explains the different options for scaling connections properly.

Each configuration, once applied, need to be reloaded. This can be done by getting the corresponding primary node pod name and issue the same signal it is done on most of the environments:

PRIMARY=$(kubectl get pod -l role=master -n cluster -o name)
kubectl exec -n cluster -it ${PRIMARY} -c postgres-util -- pkill --signal HUP pgbouncer

Check the following to know more about it:

Disabling Pooling

Certain set of applications, particularly those for reporting or OLAP, may not need a pooling middleware in order to issue large queries and a low number of connections. It is possible to disable pooling by setting disableConnectionPooling to true at the Cluster configuration (for more information, see CRD Cluster Pods configuration ).

apiVersion: stackgres.io/v1
kind: SGCluster
metadata:
  namespace: demo
  name: cluster
spec:
  pods:
    disableConnectionPooling: false
...

Either way, if your application does internal pooling or it already has a pooling middleware, you can consider disabling internal pooling mechanisms. Although, we encourage the user to keep pooling enabled internally, as it serves as a contention barrier for unexpected connection spikes that may occur, bringing more stability to the cluster.