Connection Pooling Configuration

By default, StackGres deploys Postgres clusters with a sidecar containing a connection pooler. StackGres currently uses PgBouncer as the connection pooler. 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 in the default values table. This default configuration will also be used for those parameters that are not specified during the creation or modification of the resource.

Some of the configuration’s parameters are part of a blocklist and specifying them will is possible during the creation or modification of the resource but those values will be ignored. For the complete list of those parameters see the blocked parameters table.

You can provide your own pooling configuration, by creating an instance of the SGPoolingConfig CRD. The SGPoolingConfig can be referenced from one or more Postgres clusters.

This is an example PgBouncer configuration definition:

apiVersion: stackgres.io/v1
kind: SGPoolingConfig
metadata:
  name: poolconfig
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 poolconfig) like any other Kubernetes resource.

An SGCluster can reference this configuration as follows:

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

A simple way to target this correctly, is to verify the usage of Prepared Statements, on top of which session mode will be the only compatible.

Some applications, do not handle connection closing properly, which may require to add certain timeouts for releasing server connections. –>

Changing Configuration

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

Check the following sections for more insights related to how to configure the connection pooler:

Apply Configuration changes

Each configuration, once applied, is automatically reloaded.

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:
  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.

Blocked Parameters

The list of blocked PgBouncer parameters:

Parameter
admin_users
stats_users
auth_file
auth_query
auth_type
auth_user
conffile
dns_max_ttl
dns_nxdomain_ttl
dns_zone_check_period
listen_addr
listen_port
logfile
pidfile
server_round_robin
server_tls_ca_file
server_tls_cert_file
server_tls_ciphers
server_tls_key_file
server_tls_protocols
server_tls_sslmode
syslog
syslog_facility
syslog_ident
unix_socket_dir
unix_socket_group
unix_socket_mode
host
password

Default Values

The default PgBouncer parameters (when not specified):

Parameter Value
admin_users pgbouncer_admin
application_name_add_host 1
auth_query SELECT usename, passwd FROM pg_shadow WHERE usename=$1
auth_type md5
default_pool_size 1000
ignore_startup_parameters extra_float_digits
max_client_conn 1000
max_db_connections 0
max_user_connections 0
pool_mode session
server_check_query ;
stats_users pgbouncer_stats