Best Practices for Using PgBouncer

Introduction to PgBouncer

PgBouncer is a lightweight connection pooler for PostgreSQL. It can:

• Cache connections to PostgreSQL. When a connection request is received, an idle process is allocated. PostgreSQL does not need to fork a new process to establish a connection. No resources need to be used for creating a new process and establishing a connection.
• Improve the connection usage and prevent excessive invalid connections from consuming too many database resources and causing high CPU usage.
• Restrict client connections to prevent excessive or malicious connection requests.

It is lightweight because:

• It uses libevent for socket communication, improving the communication efficiency.
• It uses the C language and only 2 KB of memory is consumed by each connection.

PgBouncer supports the following types of connection pooling:

• Session pooling: PgBouncer does not reclaim the allocated connection until the client session ends.
• Transaction pooling: PgBouncer reclaims the allocated connection after the transaction is complete. The client only has exclusive access to a connection during a transaction. Non-transaction requests do not have exclusive connections.
• Statement pooling: PgBouncer reclaims the connection anytime a database request completes. In this mode, the client cannot use transactions. Using transactions in this case will cause data inconsistency.

The default pooling type for PgBouncer is session. You are advised to change it to transaction.

Installation and Configuration

Before deploying PgBouncer on the cloud, purchase an ECS. To reduce network latency, you are advised to select the same VPC and subnet as those of the backend RDS instance for the ECS. After the purchase is complete, log in to the ECS to set up the environment. Using Ubuntu as an example, the steps for installing PgBouncer are as follows:

1. Install PgBouncer using the official Ubuntu APT repository.

   sudo apt update
   sudo apt install -y pgbouncer

2. After the installation is complete, modify the PgBouncer configuration file pgbouncer.ini.
   The default file path is /etc/pgbouncer/pgbouncer.ini. The main configuration items are as follows:

   [databases]
   ; Configure the PostgreSQL connection information.
   * = host=xxx.xxx.xxx.xxx port=5432
   [pgbouncer]
   logfile = /var/log/postgresql/pgbouncer.log
   pidfile = /var/run/postgresql/pgbouncer.pid
   listen_addr = *
   listen_port = 6432
   auth_type = md5
   auth_file = /etc/pgbouncer/userlist.txt
   admin_users = root
   stats_users = stats, postgres                            
   pool_mode = transaction                     
   server_reset_query = DISCARD ALL              
   max_client_conn = 100
   default_pool_size = 20

   For details about the parameters in the configuration file, see the official PgBouncer documentation.

3. Configure authentication users.

   Log in to PostgreSQL and run the following SQL statement to obtain the MD5 or SCRAM ciphertext of the specified user. Then, write the information to the /etc/pgbouncer/userlist.txt file.

   SELECT format('"%s" "%s"', usename, passwd) FROM pg_shadow;

   The /etc/pgbouncer/userlist.txt file content uses the format "username" "password". The following is an example:

   "root" "****************************" 
   "user1" "****************************"

Common Operations

An Example for Read/Write Splitting

PgBouncer cannot automatically parse or split read and write requests. Read and write requests need to be distinguished on the application side.

1. Modify the database information in the pgbouncer.ini file and add the connection configurations of the primary instance and read replica to the file. In this example, the parameters are set as follows:

   [databases]
   ;; * = host=127.0.0.1 port=5432
   # The connection information of the read replica.
   mydb_read: host=10.xx.xx.xx port=5432 dbname=postgres user=root password=***
   # The connection information of the primary instance.
   mydb_write: host=10.xx.xx.xx port=5432 dbname=postgres user=root password=***
   [pgbouncer]
   logfile = /var/log/pgbouncer/pgbouncer.log
   pidfile = /var/run/pgbouncer/pgbouncer.pid
   listen_addr = *
   listen_port = 6432
   auth_type = md5
   auth_file = /etc/pgbouncer/userlist.txt
   admin_users = postgres
   stats_users = stats, postgres                            
   pool_mode = transaction                     
   server_reset_query = DISCARD ALL 
   max_client_conn = 100
   default_pool_size = 20                    
   ;; resolve: unsupported startup parameter: extra_float_digits
   ;;ignore_startup_parameters = extra_float_digits

2. Check whether the primary instance and read replica can be connected. The primary instance and read replica have been connected using psql and read/write splitting is supported.

   psql -U root -d mydb_write -h 127.0.0.1 -p 6432
   Password for user root:
   psql  (14.6)
   mydb_write=>  SELECT pg_is_in_recovery();
     pg_is_in_recovery
   ----------------------
     f
   (1 row)
   psql -U root -d mydb_read -h 127.0.0.1 -p 6432
   Password for user root:
   psql  (14.6)
   mydb_read=>  SELECT pg_is_in_recovery();
   pg_is_in_recovery
   ----------------------
     t
   (1 row)