PostgreSQL PgBouncer Connection Pooling

Introduction

In this lab, you will learn how to set up and use PgBouncer, a lightweight connection pooler for PostgreSQL. Connection pooling is a critical technique for improving database performance, especially for applications that handle many short-lived connections. By managing a pool of reusable database connections, PgBouncer reduces the overhead of establishing new connections for each client request.

You will begin by installing and configuring PgBouncer, creating the necessary configuration and user authentication files. Next, you will start the PgBouncer service and test the connection to your PostgreSQL database through the pooler. You will then use pgbench, a standard PostgreSQL benchmarking tool, to simulate a client load and observe how PgBouncer manages the connections. Finally, you will learn how to connect to the PgBouncer admin console to monitor its performance and view connection statistics.

Configure PgBouncer

In this step, you will create the necessary configuration files for PgBouncer. This involves defining the database connection string and setting up an authentication file for users.

First, create a dedicated directory for your PgBouncer configuration files within your project folder.

mkdir -p /home/labex/project/pgbouncer
cd /home/labex/project/pgbouncer

Next, create the main configuration file, pgbouncer.ini, using the nano editor.

nano pgbouncer.ini

Paste the following configuration into the editor. This configuration tells PgBouncer how to connect to your PostgreSQL database and which port to listen on for client connections.

[databases]
postgres = host=127.0.0.1 port=5432 dbname=postgres

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /home/labex/project/pgbouncer/userlist.txt
admin_users = postgres
pidfile = /home/labex/project/pgbouncer/pgbouncer.pid
logfile = /home/labex/project/pgbouncer/pgbouncer.log
pool_mode = session
default_pool_size = 20
max_client_conn = 100

Let's review the key parameters:

• [databases]: Defines the connection string for the target database.
• listen_port: The port PgBouncer will listen on (6432). Your application will connect to this port.
• auth_type: Specifies the authentication method. md5 is a common choice.
• auth_file: The path to the file containing usernames and passwords.
• admin_users: A comma-separated list of users allowed to connect to the PgBouncer admin console.
• pool_mode: Set to session, meaning a server connection is assigned to a client for the entire session.

Press Ctrl+O to save the file, then Enter, and Ctrl+X to exit nano.

Now, create the authentication file userlist.txt that you referenced in the configuration.

nano userlist.txt

Add the following line to the file. The password labex_password was set for the postgres user during the lab's setup phase.

"postgres" "labex_password"

Save and exit the editor by pressing Ctrl+O, Enter, and Ctrl+X.

You have now successfully configured PgBouncer to manage connections for your PostgreSQL database.

Start PgBouncer and Test the Connection

With the configuration in place, the next step is to start the PgBouncer service and verify that you can connect to the PostgreSQL database through it.

In your terminal, start PgBouncer as a daemon (-d flag) using the configuration file you created.

pgbouncer -d /home/labex/project/pgbouncer/pgbouncer.ini

You can verify that the PgBouncer process is running with the following command. You should see the pgbouncer process listed.

ps aux | grep pgbouncer

The output will look similar to this:

labex      1234  0.0  0.0  12345   678 ?        Ss   12:00   0:00 pgbouncer -d /home/labex/project/pgbouncer/pgbouncer.ini

Now, test the connection by using psql to connect to the port where PgBouncer is listening (6432), not the default PostgreSQL port (5432).

psql -h 127.0.0.1 -p 6432 -U postgres -d postgres

You will be prompted for the password. Enter labex_password. If the connection is successful, you will see the psql prompt.

To confirm you are connected to the database, run a simple query to check the PostgreSQL version.

SELECT version();

The output will display the version of your PostgreSQL server, confirming that PgBouncer has successfully proxied your connection.

                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 14.18 (Ubuntu 14.18-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0...
(1 row)

Finally, exit the psql shell by typing \q and pressing Enter.

\q

Simulate Client Load with pgbench

To observe connection pooling in action, you will use pgbench, a standard benchmarking tool included with PostgreSQL. pgbench can simulate multiple clients concurrently accessing the database.

First, you need to initialize the pgbench environment. This creates a few tables and populates them with sample data. Run the following command, making sure to connect through the PgBouncer port (6432).

pgbench -i -h 127.0.0.1 -p 6432 -U postgres postgres

You will be prompted for the password (labex_password). The -i flag initializes the database for benchmarking. You should see output indicating that the tables have been created and populated.

dropping old tables...
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
100000 of 100000 tuples (100%) done (elapsed 0.01 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 0.13 s (drop tables 0.00 s, create tables 0.00 s, client-side generate 0.06 s, vacuum 0.04 s, primary keys 0.02 s).

Now, run the benchmark to simulate a load. The following command simulates 10 concurrent clients (-c 10), with each client running 300 transactions (-t 300).

pgbench -c 10 -t 300 -h 127.0.0.1 -p 6432 -U postgres postgres

Again, enter the password when prompted. The benchmark will run for a few seconds and then display a summary of the results, including the number of transactions per second (tps).

pgbench (14.18 (Ubuntu 14.18-0ubuntu0.22.04.1))
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
number of transactions per client: 300
number of transactions actually processed: 3000/3000
latency average = 5.935 ms
initial connection time = 1.342 ms
tps = 1685.027854 (without initial connection time)

This test generated significant traffic through PgBouncer, which we will inspect in the next step.

Monitor PgBouncer Statistics

PgBouncer provides a special administrative database that allows you to monitor its activity and view statistics about the connection pools.

Connect to the PgBouncer admin console. Note that the database name is pgbouncer.

psql -h 127.0.0.1 -p 6432 -U postgres -d pgbouncer

Enter the password labex_password when prompted.

Once connected, you can run special SHOW commands. First, view the overall statistics for all databases.

SHOW STATS;

This command displays cumulative data since PgBouncer was last started. You will see columns like total_xact_count (total transactions) and total_query_count, which should now have high values from the pgbench test.

pgbouncer=## SHOW STATS;
 database  | total_xact_count | total_query_count | total_received | total_sent | total_xact_time | total_query_time | total_wait_time | avg_xact_count | avg_query_count | avg_recv | avg_sent | avg_xact_time | avg_query_time | avg_wait_time
-----------+------------------+-------------------+----------------+------------+-----------------+------------------+-----------------+----------------+-----------------+----------+----------+---------------+----------------+---------------
 pgbouncer |                1 |                 1 |              0 |          0 |               0 |                0 |               0 |              0 |               0 |        0 |        0 |             0 |              0 |             0
 postgres  |             3019 |             21033 |        2860191 |     561691 |        17758077 |         16667957 |          277707 |             36 |             250 |    34117 |     6700 |          5882 |            792 |          3312
(2 rows)

Next, to see the state of the connection pools in real-time, use the SHOW POOLS command.

SHOW POOLS;

This command provides a snapshot of the connection pools, showing active and idle connections for both clients and servers.

• cl_active: Client connections that are actively linked to a server connection.
• sv_active: Server connections that are currently in use.
• sv_idle: Server connections that are idle and ready to be used by a new client.

 database  |   user    | cl_active | cl_waiting | cl_cancel_req | sv_active | sv_idle | sv_used | sv_tested | sv_login | maxwait | maxwait_us | pool_mode
-----------+-----------+-----------+------------+---------------+-----------+---------+---------+-----------+----------+---------+------------+-----------
 pgbouncer | pgbouncer |         1 |          0 |             0 |         0 |       0 |       0 |         0 |        0 |       0 |          0 | statement
 postgres  | postgres  |         0 |          0 |             0 |         0 |      10 |       0 |         0 |        0 |       0 |          0 | session
(2 rows)

By examining these statistics, you can understand how effectively PgBouncer is managing connections and reusing them to serve client requests.

Exit the psql shell when you are finished.

\q

Summary

In this lab, you have successfully configured and used PgBouncer for PostgreSQL connection pooling. You learned how to create the pgbouncer.ini configuration and userlist.txt authentication files. You started the PgBouncer service and verified that it could proxy connections to your PostgreSQL database. By using pgbench, you simulated a realistic client load and then monitored the results using PgBouncer's administrative console. This provided practical insight into how connection pooling works and how it can be monitored to ensure efficient database performance.