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
Pooler custom resource definition (CRD).
In brief, 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
service. It creates a separate, scalable, configurable, and highly available
database access layer.
The following diagram highlights how introducing a database access layer based on PgBouncer changes the architecture of EDB Postgres for Kubernetes. Instead of directly connecting to the PostgreSQL primary service, applications can connect to the equivalent service for PgBouncer. This ability enables reuse of existing connections for faster performance and better resource management on the PostgreSQL side.
This example helps to show how EDB Postgres for Kubernetes implements a PgBouncer pooler:
The pooler name can't be the same as any cluster name in the same namespace.
This example creates a
Pooler resource called
that's strictly associated with the Postgres
Cluster resource called
cluster-example. It points to the primary, identified by the read/write
Pooler resource must live in the same namespace as 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. The default pool size is 10
user/database pairs toward PostgreSQL.
Pooler resource sets only the
* fallback database in PgBouncer. This setting means that
that all parameters in the connection strings passed from the client are
relayed to the PostgreSQL server. For details, see "Section [databases]"
in the PgBouncer documentation.
EDB Postgres for Kubernetes also creates a secret with the same name as the pooler containing the configuration files used with PgBouncer.
For details, see
in the API reference.
Pooler resources aren't cluster-managed resources. You create poolers
manually when they're needed. You can also deploy multiple poolers per
What's important is that the life cycles of the
Cluster and the
resources are currently independent. Deleting the cluster doesn't imply the
deletion of the pooler, and vice versa.
Once 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, that is, one per application.
Any PgBouncer pooler is transparently integrated with EDB Postgres for Kubernetes support for in-transit encryption by way of TLS connections, both on the client (application) and server (PostgreSQL) side of the pool.
Specifically, PgBouncer reuses the certificates of the PostgreSQL server. It
also uses TLS client certificate authentication to connect to the PostgreSQL
server to run the
auth_query for clients' password authentication (see
Containers run as the pgbouncer system user, and access to the
database is allowed only by way of local connections, through peer authentication.
By default, a PgBouncer pooler uses the same certificates that are used by the cluster. However, if you provide those certificates, the pooler accepts secrets with the following formats:
- Basic Auth
In the Opaque case, it looks for the following specific keys that need to be used:
So you 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, the 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 cnp_pooler_pgbouncer user (PoLA)
- 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 doesn't have any pooler associated to it
If you specify your own secrets, the operator doesn't automatically integrate the pooler.
To manually integrate the pooler, if you 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, and 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, see
PoolerSpec in the API reference.
Using 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
This example shows
Pooler specifying `PodAntiAffinity``:
 when not modified,
as it's a required field for a
isn't set, the Kubernetes api-server returns the following error when trying to
apply the manifest:
error validating "pooler.yaml": error validating data:
ValidationError(Pooler.spec.template.spec): missing required field
This example sets resources and changes the used image:
Because of Kubernetes' deployments, you can configure your pooler to run on a
single instance or over multiple pods. The exposed service makes sure that your
clients are randomly distributed over the available pods running PgBouncer,
which then manages and reuses connections toward the underlying server (if
rw service) or servers (if using the
ro service with multiple
If your infrastructure spans multiple availability zones with high latency across them, be aware of network hops. Consider, for example, the case of your application running in zone 2, connecting to PgBouncer running in zone 3, and pointing to the PostgreSQL primary in zone 1.
The operator manages most of the configuration options for PgBouncer, allowing you to modify only a subset of them.
You are responsible for correctly setting the value of each option, as the operator doesn't validate them.
These are the PgBouncer options you can customize, with links to the PgBouncer documentation for each parameter. Unless stated otherwise, 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 has the same configuration, aligned with the parameters in the specification. A mistake in these parameters might disrupt the operability of the whole pooler. The operator doesn't validate the value of any option.
The PgBouncer implementation of the
Pooler comes with a default
Prometheus exporter. It makes available several
metrics having the
cnp_pgbouncer_ prefix by running:
Like 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 the prefix
You can inspect the exported metrics on a pod running PgBouncer. For instructions, see
How to inspect the exported metrics.
Make sure that you use the correct IP and the
This example shows the output for
As for clusters, a specific pooler can be monitored using the
Prometheus operator's resource
PodMonitor correctly pointing to a pooler can be created by the operator by setting
true in the
Pooler resource. The default is
Any change to
PodMonitor created automatically is overridden by the
operator at the next reconciliation cycle. If you need to customize it, you can
do so as shown in the following example.
To deploy a
PodMonitor for a specific pooler manually, you can define it as
follows and change it as needed:
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. You can ado this
paused option, which by default is set to
false. When set to
true, the operator internally invokes the
PAUSE command in PgBouncer,
- Closes all active connections toward the PostgreSQL server, after waiting for the queries to complete
- Pauses any new connection coming from the client
paused option is reset to
false, the operator invokes the
RESUME command in PgBouncer, reopening the taps toward the PostgreSQL
service defined in the
For more information, see
PAUSE 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.
Currently, you can achieve the same results by setting the
true, issuing the switchover command through the
cnp plugin, and then restoring the
The current implementation of the pooler is designed to work as part of a specific EDB Postgres for Kubernetes cluster (a service). It isn't currently possible to create a pooler that spans multiple clusters.
EDB Postgres for Kubernetes transparently manages several configuration options that are used
for the PgBouncer layer to communicate with PostgreSQL. Such options aren't
configurable from outside and include TLS certificates, authentication
databases section, and the
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 users.
The adopted solution likely addresses the majority of use cases. It leaves room for the future implementation of a separate operator for PgBouncer to complete the gamma with more advanced and customized scenarios.