Open in app

Sign in

Write

Sign in

Enhancing OCI PostgreSQL Performance with PgBouncer

Shadab Mohammad
Oracle Developers
Published in
10 min readApr 19, 2024

--

Leveraging PgBouncer for Efficient PostgreSQL Connection Management

Introduction

PgBouncer is a lightweight connection pooler for Postgres, which guarantees optimal performance and availability when running mission critical apps. Connection pooling solutions like PgBouncer are an effective strategy to guarantee performance and scale for your workload running on Postgres.

This blog post will walk you through a comprehensive setup and testing scenario for PgBouncer with PostgreSQL 14 (on OCI, but the same can be used on AWS, Azure or GCPs managed Postgres offerings) on Ubuntu 18.04, focusing on improving transaction processing speeds (TPS) and reducing latency.

OCI Architecture

OCI Architecture (Can also be replicated on any other Cloud Platform including AWS, Azure & GCP)

PostgreSQL and pgbouncer Setup

1 x OCI PostgresSQL Cluster with 1 Writer and 1 Reader Instance
IP — 10.180.2.228 Port — 5432

2 x OCI Flexible VMs with Ubuntu 18.04 Installed
Hostname — pgbouncer1 IP — 10.180.2.254 Port — 6432
Hostname — pgbouncer2 IP — 10.180.2.170 Port — 6432

1 x OCI Network Load Balancer
IP — 10.180.2.42 ListenerPort — 6432
Backends
IP — 10.180.2.254 Port — 6432
IP — 10.180.2.170 Port — 6432

Install pgbouncer on VMs

Step 1: Installing PostgreSQL 14 client and PgBouncer

The first step involves setting up PostgreSQL 14 client and the latest version of PgBouncer on Ubuntu 18.04. Begin by opening a terminal session as a superuser:

sudo su

Add the PostgreSQL package repository to your system:

sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Edit the repository file if you encounter issues with outdated links, particularly for Ubuntu 18.04:

vim /etc/apt/sources.list.d/pgdg.list

# Change the repository link to the archive path if the main one is no longer supported

deb http://apt-archive.postgresql.org/pub/repos/apt bionic-pgdg main

Now, update your package list and install PostgreSQL 14 and PgBouncer:

apt update
apt install postgresql-14
apt install pgbouncer

Step 2: Configuring PgBouncer

Prepare PgBouncer for high load environments on 2 Nodes by editing /etc/pgbouncer/pgbouncer.ini:

sudo vim /etc/pgbouncer/pgbouncer.ini
# Configuration details:
[databases]
writer = host=<primary_db_host> port=5432 dbname=postgres
reader = host=<secondary_db_host> port=5432 dbname=postgreslisten_port = 6432
listen_addr = *
server_tls_sslmode = verify-ca
server_tls_ca_file = /home/ubuntu/CaCertificate-postgres-demo.pub
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = session
server_reset_query = DISCARD ALL
max_client_conn = 10000
default_pool_size = 20

Add user credentials in plain text for authentication in /etc/pgbouncer/userlist.txt:

sudo vim /etc/pgbouncer/userlist.txt
"postgres" "YourSecurePasswordHere"

Ensure the network allows traffic to PgBouncer by configuring the firewall:

sudo iptables -I INPUT -p tcp --dport 6432 -j ACCEPT
sudo netfilter-persistent save
sudo ufw allow 6432/tcp
sudo ufw reload

Enable and start the PgBouncer service:

sudo systemctl enable pgbouncer
sudo systemctl start pgbouncer

To check for any issues check the pgbouncer logfile

sudo tail -f /var/log/postgresql/pgbouncer.log

Step 3: Testing and Benchmarking with PgBench

We’ve configured 2 nodes of pgbouncer to connect to 1 PostgreSQL Cluster with one Writer (Primary) node and another Reader (Standby) node. Test connectivity to both the pgbouncer nodes from psql.

-- Node 1 --
psql -h 10.180.2.254 -p 6432 -U postgres -d writer

psql -h 10.180.2.254 -p 6432 -U postgres -d reader




-- Node 2 --
psql -h 10.180.2.170 -p 6432 -U postgres -d writer

psql -h 10.180.2.170 -p 6432 -U postgres -d reader

To validate the setup and quantify performance improvements, use PgBench for benchmarking:

Check the maximum connections on the Postgres cluster :

$ psql -h 10.180.2.228 -p 5432 -U postgres -d postgres

Password for user postgres:
psql (10.21, server 14.10)
WARNING: psql major version 10, server major version 14.
         Some psql features might not work.
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=> show max_connections;
 max_connections
-----------------
 110
(1 row)

postgres=> select setting, source, sourcefile, sourceline from pg_settings where name = 'max_connections';
 setting |       source       | sourcefile | sourceline
---------+--------------------+------------+------------
 110     | configuration file |            |
(1 row)
  1. Direct Connection to PostgreSQL (without PgBouncer):
  • Initialize the PgBench schema and attempt to connect with 1000 concurrent clients, which should result in connection errors due to reaching the PostgreSQL connection limit.
Initialize pgbench :

pgbench -i postgres -h 10.180.2.228 -p 5432 -U postgres

Without pgbouncer :

pgbench -c 1000 -T 60 postgres -h 10.180.2.228 -p 5432 -U postgres

It results in error : 
pgbench (14.8 (Ubuntu 14.8-1.pgdg18.04+1), server 14.10)
starting vacuum...end.
pgbench: error: connection to server at "10.180.2.228", port 5432 failed: FATAL:  remaining connection slots are reserved for non-replication superuser connections
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 1000
number of threads: 1
duration: 60 s
number of transactions actually processed: 0
pgbench: fatal: Run was aborted; the above results are incomplete.

Now let’s compare a latency and a number of transactions per second (tps) that the database performs when the application connects to the database without pgbouncer

Create a file with a simple SELECT query :

vim postgres.sql

select 1;

The -C option in the pgbench indicates that for every single transaction, pgbench will close the open connection and create a new one. This is useful for measuring the connection overhead.

Without pgbouncer :

pgbench -c 20 -t 100 -S postgres -h 10.180.2.228 -p 5432 -U postgres -C -f postgres.sql

pgbench (14.8 (Ubuntu 14.8-1.pgdg18.04+1), server 14.10)
starting vacuum...end.
transaction type: multiple scripts
scaling factor: 1
query mode: simple
number of clients: 20
number of threads: 1
number of transactions per client: 100
number of transactions actually processed: 2000/2000
latency average = 388.167 ms
average connection time = 19.371 ms
tps = 51.524256 (including reconnection times)
SQL script 1: <builtin: select only>
 - weight: 1 (targets 50.0% of total)
 - 971 transactions (48.5% of total, tps = 25.015026)
 - latency average = 186.457 ms
 - latency stddev = 110.604 ms
SQL script 2: postgres.sql
 - weight: 1 (targets 50.0% of total)
 - 1029 transactions (51.5% of total, tps = 26.509230)
 - latency average = 182.901 ms
 - latency stddev = 111.216 ms

2. Connection Through PgBouncer:

  • Repeat the test using PgBouncer, which efficiently manages the connections, demonstrating significant improvements in transaction throughput and latency.
With pgbouncer :

pgbench -c 1000 -T 60 writer -h 10.180.2.254 -p 6432 -U postgres

pgbench (14.8 (Ubuntu 14.8-1.pgdg18.04+1), server 14.10)
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 1000
number of threads: 1
duration: 60 s
number of transactions actually processed: 25251
latency average = 2165.274 ms
initial connection time = 7368.358 ms
tps = 461.835296 (without initial connection time)

Now let’s compare a latency and a number of transactions per second (tps) that the database performs when the application connects to the database with pgbouncer

With pgbouncer :

pgbench -c 20 -t 100 -S writer -h 10.180.2.254 -p 6432 -U postgres -C -f postgres.sql

pgbench (14.8 (Ubuntu 14.8-1.pgdg18.04+1), server 14.10)
starting vacuum...end.
transaction type: multiple scripts
scaling factor: 1
query mode: simple
number of clients: 20
number of threads: 1
number of transactions per client: 100
number of transactions actually processed: 2000/2000
latency average = 150.456 ms
average connection time = 7.500 ms
tps = 132.929423 (including reconnection times)
SQL script 1: <builtin: select only>
 - weight: 1 (targets 50.0% of total)
 - 998 transactions (49.9% of total, tps = 66.331782)
 - latency average = 70.305 ms
 - latency stddev = 41.560 ms
SQL script 2: postgres.sql
 - weight: 1 (targets 50.0% of total)
 - 1002 transactions (50.1% of total, tps = 66.597641)
 - latency average = 72.212 ms
 - latency stddev = 42.585 ms

Results

Without pgbouncer vs With pgbouncer
------------------------------------

latency average = 182.901 ms vs latency average = 72.212 ms -->Improvement
latency stddev = 111.216 ms vs latency stddev = 42.585 ms -->Improvement
tps = 26.509230 vs tps = 66.597641 -->Improvement

PgBouncer demonstrate significant performance improvements when PgBouncer is utilized.

Latency Reduction

  • Average Latency: The integration of PgBouncer led to a substantial reduction in average latency, decreasing from 182.901 milliseconds to 72.212 milliseconds. This represents a reduction of approximately 60.5%, indicating a faster response time for transactions when PgBouncer manages connection pooling.
  • Latency Standard Deviation: There was also a notable improvement in the consistency of latency measurements. The standard deviation of latency decreased from 111.216 milliseconds to 42.585 milliseconds, suggesting that response times became more predictable with less variability under the connection pooling model provided by PgBouncer.

Transactions Per Second (TPS) Increase

  • Throughput: The number of transactions per second (TPS) increased significantly with PgBouncer, from 26.509230 to 66.597641. This improvement of over 150% in TPS indicates that the PostgreSQL server could handle more transactions within the same time frame when aided by PgBouncer. Such an increase is attributable to the reduced need for establishing and closing connections, a process that consumes time and resources.

Analysis and Implications

The enhancements in both latency and TPS with PgBouncer can be attributed to its efficient management of database connections. By maintaining a pool of active connections, PgBouncer minimizes the overhead associated with frequently establishing new database connections, which is particularly beneficial under high load conditions. The reduction in average latency and its standard deviation underscores a more reliable and consistent performance, enhancing user experience and system efficiency.

These findings affirm that PgBouncer is an effective tool for scaling PostgreSQL implementations to accommodate higher loads by improving response times and throughput. Organizations looking to optimize their PostgreSQL database systems for high performance and reliability should consider implementing connection pooling mechanisms such as PgBouncer to achieve these improvements.

The results from this benchmarking exercise not only illustrate the operational benefits of using PgBouncer but also provide a compelling case for its adoption in environments where database performance and efficiency are critical.

Add a OCI Network Load Balancer for creating High Availability and Load Balancing for the 2 pgbouncer nodes

Now that we’ve configured the 2 pgbouncer nodes for connection pooling, let us create a OCI Flexible Network Load Balancer (Layer 4) to do high availability and load balancing of the connections.

Check the load balancer and it should be healthy for the backend sets

This way the application only needs to connect to a single IP and the OCI network load balancer will cycle the connections through a 5-tuple hash policy.

Let’s now connect and test to the databases using the single NLB endpoint

$  psql -h 10.180.2.42 -p 6432 -U postgres -d reader

Password for user postgres:
psql (10.21, server 14.10)
WARNING: psql major version 10, server major version 14.
         Some psql features might not work.
Type "help" for help.

reader=> \conninfo

You are connected to database "reader" as user "postgres" on host "10.180.2.42" at port "6432".


$  psql -h 10.180.2.42 -p 6432 -U postgres -d writer

Password for user postgres:
psql (10.21, server 14.10)
WARNING: psql major version 10, server major version 14.
         Some psql features might not work.
Type "help" for help.

writer-> \conninfo

You are connected to database "writer" as user "postgres" on host "10.180.2.42" at port "6432".

Test with pgbench:

 pgbench -c 1000 -T 60 writer -h 10.180.2.42 -p 6432 -U postgres

pgbench (14.8 (Ubuntu 14.8-1.pgdg18.04+1), server 14.10)
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 1000
number of threads: 1
duration: 60 s
number of transactions actually processed: 21531
latency average = 2500.937 ms
initial connection time = 8506.785 ms
tps = 399.850177 (without initial connection time)

pgbench -c 20 -t 100 -S writer -h 10.180.2.42 -p 6432 -U postgres -C -f postgres.sql

pgbench (14.8 (Ubuntu 14.8-1.pgdg18.04+1), server 14.10)
starting vacuum...end.
transaction type: multiple scripts
scaling factor: 1
query mode: simple
number of clients: 20
number of threads: 1
number of transactions per client: 100
number of transactions actually processed: 2000/2000
latency average = 165.426 ms
average connection time = 8.247 ms
tps = 120.899811 (including reconnection times)
SQL script 1: <builtin: select only>
 - weight: 1 (targets 50.0% of total)
 - 1029 transactions (51.5% of total, tps = 62.202953)
 - latency average = 78.869 ms
 - latency stddev = 46.790 ms
SQL script 2: postgres.sql
 - weight: 1 (targets 50.0% of total)
 - 971 transactions (48.5% of total, tps = 58.696858)
 - latency average = 78.980 ms
 - latency stddev = 46.688 ms

Check pgbouncer connectivity via Network Load Balancer endpoint through a Simple Python3 app

Ensure you have Python installed on your system. You can install psycopg2 using pip if it's not already installed:

pip install psycopg2

Alternatively, you might want to use psycopg2-binary for a simpler installation without the need for a compiler:

pip install psycopg2-binary

Create file conn_pgbouncer.py

import psycopg2

def connect_to_pgbouncer():
    
    conn_params = {
        "dbname": 'writer',
        "user": 'postgres',
        "password": 'YOursecurePassword',
        "host": '10.180.2.42',
        "port": 6432
    }

    try:
        
        conn = psycopg2.connect(**conn_params)
        print("Connected to the PostgreSQL server successfully through PgBouncer.")

        
        cursor = conn.cursor()

        
        cursor.execute("SELECT version();")
        version = cursor.fetchone()

        print("PostgreSQL version:", version[0])

        
        cursor.close()
        conn.close()
        print("Connection closed successfully.")

        return "Connection successful"
    except Exception as e:
        print("Connection failed!")
        print(e)
        return "Connection failed"


if __name__ == "__main__":
    result = connect_to_pgbouncer()
    print(result)

Connect

$ python3 conn_pgbouncer.py

Connected to the PostgreSQL server successfully through PgBouncer.
PostgreSQL version: PostgreSQL 14.10 OCI Optimized, 64-bit
Connection closed successfully.
Connection successful

Appendix : Network Troubleshooting Commands

sudo tail -f /var/log/postgresql/pgbouncer.log

sudo netstat -tuln | grep 6432

nc -vz 10.180.2.254 6432

sudo traceroute -T -p 6432 10.180.2.254

ip route show
Postgres
Pgbouncer
Connection Pooling
Performance
Oracle Cloud

--

--

Shadab Mohammad
Oracle Developers

Written by Shadab Mohammad

279 Followers

Cloud Solutions Architect@Oracle (The statements and opinions expressed here are my own & do not necessarily represent those of my employer)

Help

Status

About

Careers

Press

Blog

Privacy

Terms

Text to speech

Teams