Connection Pooling v1
EDB Postgres for Kubernetes provides native support for connection pooling with
PgBouncer, one of the most popular open source
connection poolers for PostgreSQL, through the
In a nutshell, a
Pooler in EDB Postgres for Kubernetes is a deployment of
PgBouncer pods that sits between your applications and a PostgreSQL service
(for example the
rw service), creating a separate, scalable, configurable,
and highly available database access layer.
The following diagram highlights how the introduction of a database access layer based on PgBouncer changes the architecture of EDB Postgres for Kubernetes, like an additional blade in a Swiss Army knife. Instead of directly connecting to the PostgreSQL primary service, applications can now connect to the equivalent service for PgBouncer, enabling reuse of existing connections for faster performance and better resource management on the PostgreSQL side.
The easiest way to explain how EDB Postgres for Kubernetes implements a PgBouncer pooler is through an example:
Pooler name should never match with any Cluster name within the same namespace.
This creates a new
Pooler resource called
pooler-example-rw (the name is
arbitrary) that is strictly associated with the Postgres
Cluster resource called
cluster-example and pointing to the primary, identified by the read/write
Pooler must live in the same namespace of the Postgres cluster.
It consists of a Kubernetes deployment of 3 pods running the
latest stable image of PgBouncer,
configured with the
session pooling mode
and accepting up to 1000 connections each - with a default pool size of 10
user/database pairs towards PostgreSQL.
Pooler only sets the
* fallback database in PgBouncer, meaning
that all parameters in the connection strings passed from the client are
relayed to the PostgreSQL server (please refer to "Section [databases]"
in PgBouncer's documentation).
Additionally, EDB Postgres for Kubernetes automatically creates a secret with the same name of the pooler containing the configuration files used with PgBouncer.
For details, please refer to
in the API reference.
Pooler resource lifecycle
Pooler resources are not
Cluster-managed resources. You are supposed to
create poolers manually when they are needed. Additionally, you can deploy
multiple poolers per PostgreSQL Cluster.
What is important to note is that the lifecycles of the
Cluster and the
Pooler resources are currently independent: the deletion of the
doesn't imply the automatic deletion of the
Pooler, and viceversa.
Now that you know how a
Pooler works, you have full freedom in terms of
possible architectures: you can have clusters without poolers, clusters with
a single pooler, or clusters with several poolers (i.e. one per application).
Any PgBouncer pooler is transparently integrated with EDB Postgres for Kubernetes support for in-transit encryption via TLS connections, both on the client (application) and server (PostgreSQL) side of the pool.
Specifically, PgBouncer automatically reuses the certificates of the PostgreSQL
server. Moreover, it uses TLS client certificate authentication to connect
to the PostgreSQL server to run the
auth_query for clients' password
authentication (see the "Authentication" section below).
Containers run as the
pgbouncer system user, and access to the
database is only allowed via local connections, through
By default, PgBouncer pooler will use the same certificates that are used by the cluster itself, but if the user provides those certificates the pooler will accept secrets with the following format:
- Basic Auth
In the Opaque case, it will look for specific keys that needs to be used, those keys are the following:
So we can treat this secret as a TLS secret, and start from there.
Password based authentication is the only supported method for clients of PgBouncer in EDB Postgres for Kubernetes.
Internally, our implementation relies on PgBouncer's
auth_query options. Specifically, the operator:
- creates a standard user called
cnp_pooler_pgbouncerin the PostgreSQL server
- creates the lookup function in the
postgresdatabase and grants execution privileges to the
- issues a TLS certificate for this user
- configures PgBouncer to use the TLS certificate to authenticate
cnp_pooler_pgbounceragainst the PostgreSQL server
- removes all the above when it detects that a cluster does not have any pooler associated to it
If you specify your own secrets the operator will not automatically integrate the Pooler.
To manually integrate the Pooler, in the case that you have specified your own secrets, you must run the following queries from inside your cluster.
First, you must create the role:
Then, for each application database, grant the permission for
cnp_pooler_pgbouncer to connect to it:
Finally, connect in each application database, then create the authentication function inside each of the application databases:
You can take advantage of pod templates specification in the
section of a
Pooler resource. For details, please refer to
section in the API reference.
Through templates you can configure pods as you like, including fine
control over affinity and anti-affinity rules for pods and nodes.
By default, containers use images from
Here an example of Pooler specifying PodAntiAffinity:
.spec.template.spec.containers has to be explicitly set to
 when not modified, as it's a required field for a
.spec.template.spec.containers is not set the kubernetes api-server will return the following error when trying to apply the manifest:
error validating "pooler.yaml": error validating data: ValidationError(Pooler.spec.template.spec): missing required field "containers"
Here an example setting resources and changing the used image:
High Availability (HA)
Thanks to Kubernetes' deployments, you can configure your pooler to run
on a single instance or over multiple pods. The exposed service will
make sure that your clients are randomly distributed over the available
pods running PgBouncer - which will then automatically manage and reuse
connections towards the underlying server (if using the
or servers (if using the
ro service with multiple replicas).
Please be aware of network hops in case your infrastructure spans multiple availability zones with high latency across them. Consider for example the case of your application running in zone 2, connecting to PgBouncer running in zone 3, pointing to the PostgreSQL primary in zone 1.
PgBouncer configuration options
The operator manages most of the configuration options for PgBouncer, allowing you to modify only a subset of them.
You are responsible to correctly set the value of each option, as the operator does not validate them.
Below you can find a list of the PgBouncer options you are allowed to customize. Each of them contains a link to the PgBouncer documentation for that specific parameter. Unless differently stated here, the default values are the ones directly set by PgBouncer:
ignore_startup_parameters: to be appended to
extra_float_digits,options- required by CNP
log_stats: by default disabled (
0), given that statistics are already collected by the Prometheus export as described in the "Monitoring" section below
Customizations of the PgBouncer configuration are written
declaratively in the
The operator reacts to the changes in the Pooler specification, and every PgBouncer instance reloads the updated configuration without disrupting the service.
Every PgBouncer pod will have the same configuration, aligned with the parameters in the specification. A mistake in these parameters could disrupt the operability of the whole Pooler. The operator does not validate the value of any option.
The PgBouncer implementation of the
Pooler comes with a default
Prometheus exporter that automatically makes available several
metrics having the
cnp_pgbouncer_ prefix, by running:
Similarly to the EDB Postgres for Kubernetes instance, the exporter runs on port
9127 of each pod running PgBouncer, and also provides metrics related to the
Go runtime (with prefix
go_*). You can debug the exporter on a pod running
PgBouncer through the following command:
An example of the output for
Clusters, if you are using the Prometheus Operator
you can configure it to scrape a specific Pooler by defining the following PodMonitor:
Logs are directly sent to standard output, in JSON format, like in the following example:
Pooler specification allows you to take advantage of PgBouncer's
RESUME commands, using only declarative configuration - via the
option, by default set to
false. When set to
true, the operator internally
PAUSE command in PgBouncer, which:
- closes all active connections towards the PostgreSQL server, after waiting for the queries to complete
- pauses any new connection coming from the client
paused option is set back to
false, the operator will invoke the
RESUME command in PgBouncer, re-opening the taps towards the PostgreSQL
service defined in the
For further information, please refer to the
PAUSE section in the PgBouncer documentation.
In future versions, the switchover operation will be fully integrated
with the PgBouncer pooler, and take advantage of the
features to reduce the perceived downtime by client applications.
At the moment, you can achieve the same results by setting the
true, then issuing the switchover command through the
cnp plugin, and finally restoring the
Single PostgreSQL cluster
The current implementation of the pooler is designed to work as part of a specific EDB Postgres for Kubernetes cluster (a service, to be precise). It is not possible at the moment to create a pooler that spans over multiple clusters.
EDB Postgres for Kubernetes transparently manages several configuration options
that are used for the PgBouncer layer to communicate with PostgreSQL. Such
options are not configurable from outside and include TLS certificates,
databases section, and
users section. Also,
considering the specific use case for the single PostgreSQL cluster, the
adopted criteria is to explicitly list the options that can be configured by
We have reasons to believe that the adopted solution addresses the majority of use cases, while leaving room for the future implementation of a separate operator for PgBouncer to complete the gamma with more advanced and customized scenarios.