DEV Community

Dmitry Romanoff
Dmitry Romanoff

Posted on • Edited on

PgBouncer. Reduce the load on the PostgreSQL server and improve performance.

#postgres #database #devops #programming

PostgreSQL Database Server accepts and processes requests and returns results. Requests come from client applications. Application code interacts with the database. The database searches, saves, manipulates data, and responds to the client. How does PostgreSQL work with client connections?

PostgreSQL creates a separate process for each client connection that serves that connection. For example, if we have ten client connections, PostgreSQL creates ten processes. If we have one hundred client connections, then one hundred PostgreSQL server processes serve those one hundred client connections.

Would such an architecture work quickly and efficiently? The answer is no. Such an architecture would not be efficient, fast, and well scalable.

What needs to be done is to not create a separate database connection for each request.

How can this be done? This can be done using a special utility called connection poolers. For the PostgreSQL Database server, one of the commonly used connection poolers is PgBouncer.

PgBouncer can handle a large number of incoming connections and redirect them to a very small number of actual connections to the database. If there are hundreds of requests coming from the client application to the database, they go through PgBouncer, which distributes them to several dozen connections to the database, creates queues where necessary, redirects where necessary, and everything works Ok.

What is the advantage of this approach? Firstly, the application continues to work even if the number of requests has sharply increased, as each request does not create a separate process to the database. Instead, a request is made to PgBouncer, which translates them into a small number of connections to the database. The second advantage is that our application works faster, as time is saved by not creating a separate dedicated process in the database per each request.

How to install and configure pgbouncer?

Let's assume we have a PostgreSQL server.

dmi@dmi-VirtualBox:~$ psql -h 127.0.0.1 -p 5432 -U postgres -d postgres
Password for user postgres:
psql (15.1 (Ubuntu 15.1-1.pgdg22.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

postgres=# select version();
                                                              version                                                          
-----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 15.1 (Ubuntu 15.1-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.3.0-1ubuntu1~22.04) 11.3.0, 64-bit
(1 row)
Enter fullscreen mode Exit fullscreen mode

Let's install pgbouncer:

sudo apt-get install pgbouncer
Enter fullscreen mode Exit fullscreen mode

The next step is to configure pgbouncer.

sudo vi /etc/pgbouncer/pgbouncer.ini
Enter fullscreen mode Exit fullscreen mode

In the [databases] block, I add the following entry:

* = host=localhost port=5432
Enter fullscreen mode Exit fullscreen mode

In the "Pooler personality questions" section, I define pool_mode=transaction.

...
;;;
;;; Pooler personality questions
;;;

;; When server connection is released back to pool:
;;   session      - after client disconnects (default)
;;   transaction  - after transaction finishes
;;   statement    - after statement finishes
pool_mode = transaction
...
Enter fullscreen mode Exit fullscreen mode

In the "Connection limits" section, we set the total number of clients that can connect to some high value:
max_client_conn=5000.

...
;;;
;;; Connection limits
;;;

;; Total number of clients that can connect
max_client_conn = 5000
...
Enter fullscreen mode Exit fullscreen mode

In the "Authentication settings" section, we set auth_type = md5 to authenticate users by password. The file with database login and password will be located at /etc/pgbouncer/userlist.txt

...
;;;
;;; Authentication settings
;;;

;; any, trust, plain, md5, cert, hba, pam
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
...
Enter fullscreen mode Exit fullscreen mode

In the "Users allowed into database 'pgbouncer'" section, we set the admin_users parameter. This is the database user who will have permission to make pgbouncer settings in the database.

...
;;;
;;; Users allowed into database 'pgbouncer'
;;;

;; comma-separated list of users who are allowed to change settings
;admin_users = user2, someadmin, otheradmin

admin_users = my_db_user

...
Enter fullscreen mode Exit fullscreen mode

Now let's open the file with users at /etc/pgbouncer/userlist.txt.

If you are using PostgreSQL versions up to 13 inclusively, then the default password_encryption method is md5.

Here we place the username in double quotes and the md5 password hash (in one line):

"my_db_user" "md5badc318d987f61146c6ad8e15d84a111"
Enter fullscreen mode Exit fullscreen mode

To determine the md5 password hash, you can use the following method:

echo "md5"$(echo -n 'YourdbpasswordYourdbusername' | md5sum | awk ' { print $1 } ')
Enter fullscreen mode Exit fullscreen mode

After that, let's reload pgbouncer:

sudo service pgbouncer restart
Enter fullscreen mode Exit fullscreen mode

And after that, we will be able to connect to the database through pgbouncer using port 6432 (pgbouncer default port).

If you are using PostgreSQL versions starting from 14, then the default password_encryption method is scram-sha-256.

Here we place the username in double quotes and the scram-sha-256 password hash (in one line):

"my_db_user" "SCRAM-SHA-256$4096:lLN4+i05+kpeffD4s3rRiw==$Oq62iUGamAaF5cpB+agWV4u3xfc5cZCRtvMhmA+Zm3E=:hHkCesEi0p0wLWk1uUEeTtJTYLXHKDLdy2te3VAOe8s="
Enter fullscreen mode Exit fullscreen mode

To determine the scram-sha-256 password hash, you can use the following method:

psql -h <db_host> -p <db_port> -Atq -U postgres -d postgres -c "SELECT concat('\"', usename, '\" \"', passwd, '\"') FROM pg_shadow"
Enter fullscreen mode Exit fullscreen mode

To make our application use pgbouncer when connecting to the database, all we need to change is the port number: use 6432 instead of 5432.

Let's run a performance test to compare the performance of connecting to PostgreSQL with and without pgbouncer, using the pgbench utility.

You can learn about the pgbench utility from my blog.

The maximum number of connections for my database is set to 100:

postgres=# show max_connections;
 max_connections
-----------------
 100
(1 row)
Enter fullscreen mode Exit fullscreen mode

Connecting to the Postgres database server without using pgbouncer.

This command will start a test with 1000 concurrent clients for 60 seconds, connecting directly to the PostgreSQL database.

dmi@dmi-VirtualBox:~$  pgbench -c 1000 -T 60 my_benchmark_test_db -h 127.0.0.1 -p 5432 -U my_db_user
Password:
pgbench (15.1 (Ubuntu 15.1-1.pgdg22.04+1))
starting vacuum...end.
pgbench: error: connection to server at "127.0.0.1", port 5432 failed: FATAL:  sorry, too many clients already
connection to server at "127.0.0.1", port 5432 failed: FATAL:  sorry, too many clients already
pgbench: error: could not create connection for client 44
Enter fullscreen mode Exit fullscreen mode

Simulating the work of 1000 clients interacting with a database where only 100 clients can be connected at maximum results in an error.

FATAL:  sorry, too many clients already
Enter fullscreen mode Exit fullscreen mode

Connecting to the Postgres database server using pgbouncer:

When connecting to the database using pgbouncer, everything works without any issues.

pgbench -c 1000 -T 60 my_benchmark_test_db -h 127.0.0.1 -p 6432 -U my_db_user
Password:
pgbench (15.1 (Ubuntu 15.1-1.pgdg22.04+1))
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 50
query mode: simple
number of clients: 1000
number of threads: 1
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 47370
number of failed transactions: 0 (0.000%)
latency average = 1106.280 ms
initial connection time = 8788.955 ms
tps = 903.930420 (without initial connection time)
dmi@dmi-VirtualBox:~$
Enter fullscreen mode Exit fullscreen mode

Let's compare the number of transactions per second that the database performs when the application connects to the database without using pgbouncer and when it uses pgbouncer.

dmi@dmi-VirtualBox:~$ cat mysql.sql
select 1;
Enter fullscreen mode Exit fullscreen mode

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 to measure the connection overhead.

In my test I perfom select-only transactions. The reason is I want to exclude measuring update contention, when lots of transactions blocked waiting for other transactions.

The application connects to the database without using pgbouncer:

dmi@dmi-VirtualBox:~$ pgbench -c 20 -t 100 -S my_benchmark_test_db -h 127.0.0.1 -p 5432 -U my_db_user -C -f mysql.sql
Password:
pgbench (15.1 (Ubuntu 15.1-1.pgdg22.04+1))
starting vacuum...end.
transaction type: multiple scripts
scaling factor: 50
query mode: simple
number of clients: 20
number of threads: 1
maximum number of tries: 1
number of transactions per client: 100
number of transactions actually processed: 2000/2000
number of failed transactions: 0 (0.000%)
latency average = 340.479 ms
average connection time = 16.910 ms
tps = 58.740729 (including reconnection times)
SQL script 1: <builtin: select only>
 - weight: 1 (targets 50.0% of total)
 - 979 transactions (49.0% of total, tps = 28.753587)
 - number of failed transactions: 0 (0.000%)
 - latency average = 158.504 ms
 - latency stddev = 133.666 ms
SQL script 2: mysql.sql
 - weight: 1 (targets 50.0% of total)
 - 1021 transactions (51.0% of total, tps = 29.987142)
 - number of failed transactions: 0 (0.000%)
 - latency average = 162.888 ms
 - latency stddev = 136.175 ms
Enter fullscreen mode Exit fullscreen mode

The application connects to the database through pgbouncer:

dmi@dmi-VirtualBox:~$ pgbench -c 20 -t 100 -S my_benchmark_test_db -h 127.0.0.1 -p 6432 -U my_db_user -C -f mysql.sql
Password:
pgbench (15.1 (Ubuntu 15.1-1.pgdg22.04+1))
starting vacuum...end.
transaction type: multiple scripts
scaling factor: 50
query mode: simple
number of clients: 20
number of threads: 1
maximum number of tries: 1
number of transactions per client: 100
number of transactions actually processed: 2000/2000
number of failed transactions: 0 (0.000%)
latency average = 178.276 ms
average connection time = 8.867 ms
tps = 112.185757 (including reconnection times)
SQL script 1: <builtin: select only>
 - weight: 1 (targets 50.0% of total)
 - 1022 transactions (51.1% of total, tps = 57.326922)
 - number of failed transactions: 0 (0.000%)
 - latency average = 85.993 ms
 - latency stddev = 50.377 ms
SQL script 2: mysql.sql
 - weight: 1 (targets 50.0% of total)
 - 978 transactions (48.9% of total, tps = 54.858835)
 - number of failed transactions: 0 (0.000%)
 - latency average = 84.039 ms
 - latency stddev = 51.036 ms
dmi@dmi-VirtualBox:~$
Enter fullscreen mode Exit fullscreen mode

Both the latency average and the tps indicate improvement when an application connects to the database through pgbouncer:

latency average: 340.479 ms -> 178.276 ms --- improvement 
tps: 58 -> 112  --- improvement
Enter fullscreen mode Exit fullscreen mode

ask_dima@yahoo.com

Top comments (0)

Read next

abhay_yt_52a8e72b213be229 profile image

React.js Explained: A Comprehensive Guide to Building Modern Web Applications

Abhay Singh Kathayat -

viksingh profile image

How to Update Java JDK [A Simple Guide]

Vikas Singh -

kafeel_ahmad profile image

JavaScript Best Practices

Kafeel Ahmad (kaf shekh) -

abhay_yt_52a8e72b213be229 profile image

What is SQL and Why It’s Essential for Data Management

Abhay Singh Kathayat -