PostgreSQL PgBouncer Connection Pooling

Introduction

In this lab, you will learn how to configure and use PgBouncer for connection pooling with PostgreSQL. PgBouncer acts as a lightweight connection pooler, sitting between your application and the PostgreSQL database to manage connections efficiently. This reduces the overhead of creating new connections for each request, significantly improving performance, especially for applications with frequent, short-lived database connections.

The lab guides you through configuring PgBouncer, including creating a configuration file with database connection details and pool settings. You'll then connect to the database through PgBouncer, simulate multiple client connections to observe the pooling in action, and monitor PgBouncer's performance to understand the benefits of connection pooling.

Skills Graph

Configure PgBouncer and Connect to PostgreSQL

In this step, you will configure PgBouncer for connection pooling and connect to your PostgreSQL database through it.

1. Create a PgBouncer configuration file:

   First, create a directory to store the PgBouncer configuration file:

   mkdir -p ~/project/pgbouncer
   cd ~/project/pgbouncer

   Then, create the configuration file pgbouncer.ini using nano:

   nano pgbouncer.ini

   Paste the following configuration into the pgbouncer.ini file:

   [databases]
   mydb = host=127.0.0.1 port=5432 dbname=postgres user=postgres
   
   [pgbouncer]
   listen_addr = 127.0.0.1
   listen_port = 6432
   user = postgres
   pool_mode = transaction
   server_reset_query = DISCARD ALL
   default_pool_size = 20
   max_client_conn = 100

   • [databases] defines the database connection. mydb is an alias.
   • [pgbouncer] configures PgBouncer. listen_addr and listen_port are the address and port PgBouncer listens on. pool_mode is set to transaction.

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

2. Create a user list file:

   PgBouncer needs a user list file for authentication. Create userlist.txt:

   nano userlist.txt

   Add the following line, replacing labex_password with the actual password for the postgres user (you can set it using sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD 'labex_password';") :

   "postgres" "labex_password"

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

3. Start PgBouncer:

   Start PgBouncer using the configuration file:

   pgbouncer pgbouncer.ini

   Keep this terminal open. Open a new terminal for the next steps.

4. Connect to PostgreSQL through PgBouncer:

   In the new terminal, connect to PostgreSQL via PgBouncer using psql:

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

   Enter the password you set in userlist.txt. If successful, you'll see the psql prompt.

   psql (13.3, server 1.18.0)
   Type "help" for help.
   
   postgres=#

Create Sample Table and Data

Now that you're connected to PostgreSQL through PgBouncer, let's create a sample table and insert some data.

1. Create a table:

   Execute the following SQL command to create a table named users:

   CREATE TABLE users (
       id SERIAL PRIMARY KEY,
       name VARCHAR(255),
       email VARCHAR(255)
   );

   You should see the output:

   CREATE TABLE

2. Insert data into the table:

   Insert some sample data into the users table:

   INSERT INTO users (name, email) VALUES
   ('Alice', '[email protected]'),
   ('Bob', '[email protected]'),
   ('Charlie', '[email protected]');

   You should see the output:

   INSERT 0 3

3. Verify the data:

   Verify that the data has been inserted correctly by querying the table:

   SELECT * FROM users;

   You should see the inserted data:

    id |  name   |       email
   ----+---------+-----------------------
     1 | Alice   | [email protected]
     2 | Bob     | [email protected]
     3 | Charlie | [email protected]
   (3 rows)

Simulate Multiple Client Connections

In this step, you will simulate multiple client connections to the PostgreSQL database through PgBouncer.

1. Create a script to connect and query:

   Create a script named connect_and_query.sh in your ~/project directory:

   nano ~/project/connect_and_query.sh

   Paste the following script into the file:

   #!/bin/bash
   
   psql -h 127.0.0.1 -p 6432 -U postgres -d postgres -c "SELECT COUNT(*) FROM users;"

   This script connects to the database through PgBouncer and executes a query to count the number of users.

   Make the script executable:

   chmod +x ~/project/connect_and_query.sh

2. Simulate concurrent connections:

   Use xargs to run the script multiple times concurrently:

   seq 5 | xargs -P 5 -I {} ~/project/connect_and_query.sh

   Enter the password when prompted. You should see the count of users printed multiple times.

Monitor Pooling Performance

In this step, you will monitor PgBouncer's pooling performance.

1. Connect to the PgBouncer administration database:

   Connect to the pgbouncer database:

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

   Enter the password when prompted.

2. Show PgBouncer statistics:

   Execute the following SQL command to show the statistics:

   SHOW STATS;

   This will display statistics about PgBouncer's activity, including total requests, received bytes, and sent bytes.

3. Show pool statistics:

   Execute the following SQL command to show the pool statistics:

   SHOW POOLS;

   This will display statistics about the connection pool, including active client connections, waiting client connections, active server connections, and idle server connections.

   By examining these statistics, you can understand how PgBouncer is managing connections and identify potential bottlenecks.

4. Exit psql:

   \q

Summary

In this lab, you configured PgBouncer for connection pooling, created a sample table and data, simulated multiple client connections, and monitored PgBouncer's performance. You learned how PgBouncer efficiently manages database connections, reducing overhead and improving performance for applications with frequent, short-lived connections. By monitoring PgBouncer's statistics, you can optimize its configuration and ensure efficient connection pooling.