Postgres - PGBouncer
lightweight connection pooler for PostgreSQL
The aim of pgbouncer is to lower the performance impact of opening new connections to PostgreSQL.
Saving time when opening and closing connections.
Is this your bottleneck though?
What is a high number of connections in postgres?
- That all depends on your application, but generally when you get to the few hundred connection area in Postgres you’re in the higher end
- Generally a safe level for connections should be somewhere around 300-500 connections
- By default postgres sets max connections at 100 (check this with
pgBouncer can not do anything about network usage when it comes to running queries – it’s not a cache of data/query-results. So it will actually slow down queries a bit – because instead of sending the query to server, you will pass it to pgBouncer which, in turn, will send it to server.
Connection basics in Postgres#
- Each new connection to Postgres is a forked process
- This process comes with its own memory allocation of roughly 10 MB with at least some load for the query
- For 300 database connections this is 3 GB of memory going just to managing those connections—memory which could be better used for caching your data
Some frameworks grab a bunch of connections at startup - reducing the time needed to run a query as it does not need to first get a connection.
How do you check your idle connections (or all connections):
SELECT COUNT(*) FROM pg_stat_activity WHERE state <> 'idle'
Enter Connection Pooler#
A connection pooler will do the hard work of maintaining a pool of connections and then give them out as your application needs them, which is when a transaction or query happens. Django or rails doesn’t need to handle that.
2 key settings of a connection pooler:
- A max amount of active connections
- A max on idle connections
For Citus Cloud the limit is 300 active connections and 2000 idle connections.
Setting Up PGBouncer#
- session pooling - Most polite method - session for as long as a client stays connected (default method). When a client disconnects - the connection is put back in the pool.
- transaction pooling - A server connection is assigned to a client only during a transaction.
- statement pooling - Most aggressive method - The server connection will be put back into the pool immediately after a query completes.
Transaction pooling will grant a connection when you run
BEGIN;and return the transaction when you
To connect to PgBouncer on citus-cloud, you can simply swap your port from 5432 to 6432 and you’ll be connected to PgBouncer
Installing PG Bouncer on Ubuntu#
Using distro package maintainer:
sudo apt install libevent-dev pgbouncer # Check status sudo systemctl status pgbouncer # Edit config sudo vim /etc/pgbouncer/pgbouncer.ini
This will install the systemd service
cd /opt sudo wget https://www.pgbouncer.org/downloads/files/1.18.0/pgbouncer-1.18.0.tar.gz sudo tar xzf pgbouncer-1.18.0.tar.gz cd pgbouncer-1.18.0 sudo apt install libevent-dev sudo ./configure --prefix=/usr/local sudo make sudo make install
Edit the config then restart
In config: * = host=localhost port=5432 sudo systemctl restart pgbouncer
The log is found at
Get stats with:
sudo tail -f /var/log/postgresql/pgbouncer.log | grep "stats"
Setting up PG Bouncer for AWS RDS#
Check out Ankane.org: PGBouncer Setup
To connect to the pgbouncer instance:
psql -p 6432 pgbouncer SHOW pools;
Pg bouncer can store a logical database name. So when you connect on your app you only need to state the port and app name.