NODELOG by THIVELAB
Subscribe
Home/Engineer/Databases/PgBouncer Connection Pooling…
DatabasesIntermediateUbuntuDebianCentOSpgbouncerpostgresqlpostgres

PgBouncer Connection Pooling — PostgreSQL Performance Optimization

Covers installing and configuring PgBouncer, choosing a pooling mode (session/transaction/statement), setting up user authentication, and using monitoring queries to cut PostgreSQL connection counts and improve performance.

NONodelog0 views
Contents
  1. Why You Need PgBouncer
  2. Installation
  3. Choosing a Pooling Mode
  4. pgbouncer.ini Configuration
  5. User Authentication
  6. Connecting Your App Through PgBouncer
  7. Monitoring
  8. Tuning Guide

Why You Need PgBouncer

PostgreSQL uses about 5–10MB of memory per connection. When hundreds of app connections open at once:

  • Memory pressure
  • Context-switching overhead
  • Hitting the connection limit (max_connections)

PgBouncer sits between the app and the database and reuses connections (pooling).

Installation

Bash
# Ubuntu / Debian
sudo apt install -y pgbouncer

# check
sudo systemctl status pgbouncer

Choosing a Pooling Mode

ModeDescriptionWhen to use
sessionHolds a connection for the entire client sessionWhen using SET commands or temporary tables
transactionAssigns a connection only for the duration of a transactionMost web apps (recommended)
statementAssigns a connection for a single querySimple queries with no transactions

pgbouncer.ini Configuration

INI
# /etc/pgbouncer/pgbouncer.ini

[databases]
; =
myapp = host=127.0.0.1 port=5432 dbname=myapp
;
myapp_prod = host=db.example.com port=5432 dbname=myapp

[pgbouncer]
listen_addr = 127.0.0.1 ; (external when 0.0.0.0)
listen_port = 6432 ; PgBouncer
auth_type = md5 ; method
auth_file      = /etc/pgbouncer/userlist.txt

pool_mode = transaction ;
max_client_conn = 1000 ;
default_pool_size = 25 ; DB
min_pool_size   = 5
reserve_pool_size = 5

;
logfile        = /var/log/postgresql/pgbouncer.log
pidfile        = /var/run/postgresql/pgbouncer.pid

;
server_idle_timeout = 600
client_idle_timeout = 0
query_timeout       = 0

;
admin_users    = postgres
stats_users    = monitor

User Authentication

Bash
# userlist.txt : "User" " when"

# PostgreSQL when
sudo -u postgres psql -c "SELECT usename, passwd FROM pg_shadow WHERE usename = 'appuser';"

# /etc/pgbouncer/userlist.txt
echo '"appuser" "md5when"' | sudo tee /etc/pgbouncer/userlist.txt
echo '"postgres" "md5when"' | sudo tee -a /etc/pgbouncer/userlist.txt

# settings
sudo chmod 640 /etc/pgbouncer/userlist.txt
sudo chown postgres:postgres /etc/pgbouncer/userlist.txt

# when
sudo systemctl restart pgbouncer

Connecting Your App Through PgBouncer

CODE
# PostgreSQL
postgresql://appuser:pass@localhost:5432/myapp

# PgBouncer
postgresql://appuser:pass@localhost:6432/myapp

Monitoring

Bash
# PgBouncer
psql -h 127.0.0.1 -p 6432 -U postgres pgbouncer

#
SHOW POOLS;
# database | user | cl_active | cl_waiting | sv_active | sv_idle

#
SHOW STATS;

# list
SHOW CLIENTS;

# (DB) list
SHOW SERVERS;

# settings (pgbouncer.ini after)
RELOAD;

# quit without settings
RELOAD;

Tuning Guide

INI
; (100 when , DB max_connections=100)
pool_mode         = transaction
max_client_conn   = 500
default_pool_size = 20 ; PgBouncer → PostgreSQL
min_pool_size     = 5
reserve_pool_size = 5

Actual PostgreSQL connections = default_pool_size × number of databases Client connections = max_client_conn → Handle 500 app connections with 20 database connections

#pgbouncer#postgresql#postgres
Editorial note

AI tools may assist drafting. Check the evidence in each guide for whether commands were run and which environment was used. Confirm the current docs for your version before applying instructions, and email us about errors. email us

Topic-related official docs (auto-linked · not per-claim sources)PostgreSQL documentation ↗
How we write and review →Editorial policy →

Questions & answers

Ask a question about this guide. We'll review and reply.