How to setup PostgreSQL Connection Pooling using pgbouncer in GKE (Google Kubernetes Engine) and CloudSQL

PostgreSQL

Introduction

Database connection pooling is a technique used to manage and optimize the utilization of database connections in a multi-user, multi-threaded application environment. It involves the creation of a pool of pre-established connections to a database, which can be shared and reused by multiple clients or threads, rather than having each client create and close connections individually.

Connection pooling improves application performance and resource management by reducing the overhead associated with establishing and closing database connections. Establishing a connection to a database can be a time-consuming and resource-intensive process. By maintaining a pool of connections that are already established, the application can quickly borrow and return connections as needed, reducing the latency and resource overhead.

Some benefits of connection pooling include:

1. Improved performance: Reusing existing connections reduces the time spent on establishing and closing connections to the database, resulting in faster response times for the application.
2. Efficient resource utilization: Connection pooling minimizes the number of open connections to the database, reducing the load on both the application server and the database server.
3. Easier management: Connection pooling provides a centralized place to manage and configure database connections, making it easier to monitor and adjust settings as needed.
4. Scalability: Connection pooling allows applications to handle a larger number of concurrent users, as connections can be shared and reused efficiently.

Most modern database management systems (DBMS) and programming languages provide built-in support for connection pooling or offer third-party libraries that can be integrated into the application to handle this functionality, one of them is PgBouncer.

PgBouncer

PgBouncer is a lightweight connection pooler for PostgreSQL that helps manage and optimize database connections. It acts as an intermediary between the application and the PostgreSQL database server, allowing multiple clients to share a limited number of database connections. PgBouncer maintains a pool of open connections to the PostgreSQL server and assigns them to clients as needed, reducing the overhead of creating and closing connections for each client request.

How to setup PgBouncer in GKE

Setup Database Instance

You need to set up a PostgreSQL Database Instance. In this tutorial, we will use Terraform to create a Database Instance in Cloud SQL. However, you can also create it using the Google Cloud Console or manually install PostgreSQL on a GKE cluster.

It assumes:

1. You have GCP Project: my-gcp-project in region asia-southeast2
2. We will create DB Instance my-db-instance with user admin and password xxx
3. We will create a database example_db
4. We will operate in Kubernetes Namespace default
5. We will create Kubernetes Service Account cloudsql-k8s-sa and associate it with a CloudSQL Service Account named cloudsql-sa

First, we will establish VPC Peering between our GKE Cluster and CloudSQL. CloudSQL runs on a Google-owned private network, and we can only establish a connection to it through VPC Peering. This process involves creating a connection between our own VPC and the Google-owned private network, allowing for secure and private communication between the GKE Cluster and CloudSQL.

After establishing the VPC Peering connection, we can proceed to set up our Database Instance along with a database called example_db. Make sure to configure the instance with the desired settings, such as the region, zone, database version, and machine type. While creating the CloudSQL instance associate the instance with the VPC network you've already set up for VPC Peering. This will ensure that the database instance is only accessible through the private network established between the GKE cluster and CloudSQL.

Next step is creating DB User and Password, along with CloudSQL Service Account. This service account will have role as CloudSQL Admin.

In order to GKE can have permission to access CloudSQL, we need to create a Kubernetes Service Account and associate it with CloudSQL Service Account. We can do all of this through Terraform or do it manually.

Using Terraform:

Using kubectl:

~$ kubectl create serviceaccount cloudsql-k8s-sa --namespace=default~$ kubectl create serviceaccount cloudsql-k8s-sa --namespace=defaul

~$ gcloud iam service-accounts add-iam-policy-binding \
--role roles/iam.workloadIdentityUser \
--member "serviceAccount:my-gcp-project.svc.id.goog[default/cloudsql-k8s-sa]" \
cloudsql-sa@my-gcp-project.iam.gserviceaccount.com

~$ kubectl annotate serviceaccount \
--namespace default \
cloudsql-k8s-sa \
iam.gke.io/gcp-service-account=cloudsql-sa@my-gcp-project.iam.gserviceaccount.com --overwrite=true

Apply Terraform:

~$ terraform apply

Install PgBouncer

In this tutorial, we will use a ready-to-use Helm Chart provided by 8grams to set up PgBouncer on GKE. Helm is a package manager for Kubernetes that simplifies the deployment and management of applications on a Kubernetes cluster.

Install Helm on your local machine
If you haven’t already installed Helm, follow the official Helm installation guide to install the Helm CLI on your local machine.

Clone the Helm Chart repository

~$ git clone https://github.com/8grams/gke-pgbouncer-helm-chart.git /app/gke-pgbouncer-helm-chart && cd /app/gke-pgbouncer-helm-chart

Configure the Helm Chart
Create values.yaml file in the gke-pgbouncer-helm-chart directory to configure PgBouncer settings, such as the CloudSQL Instance URI, database credentials, and any other necessary configurations.

~$ vim values.yml

Install the Helm Chart on your GKE cluster
Make sure you have an active kubectl context pointing to your GKE cluster. Then, install the Helm Chart using the following command:

~$ helm install pgbouncer ./template -f values.yaml --namespace=default

Verify the PgBouncer deployment
Use the following kubectl commands to verify that PgBouncer has been deployed successfully:

~$ kubectl get deployment pgbouncer --namespace=default                
NAME       READY     UP-TO-DATE   AVAILABLE   AGE
pgbouncer   1/1          1           1        1m

Done! Now you have PgBouncer running on your GKE Cluster.

Using PgBouncer in Application

After installing PgBouncer in your GKE cluster, you should reference the PgBouncer Service when making a connection to the database. Your PostgreSQL connection URI should look like the following:

postgres://admin:xxx@pgbouncer.default:5432/example_db

With this connection URI, your application will connect to the PostgreSQL database through the PgBouncer service, taking advantage of the connection pooling provided by PgBouncer. Ensure that your application uses this connection URI when establishing a connection to the database.

About 8grams

We are a small DevOps Consulting Firm that has a mission to empower businesses with modern DevOps practices and technologies, enabling them to achieve digital transformation, improve efficiency, and drive growth.

Ready to transform your IT Operations and Software Development processes? Let’s join forces and create innovative solutions that drive your business forward.