How to Configure PgBouncer for PostgreSQL Connection Pooling

PostgreSQL handles each client connection by forking a new backend process. For small applications with a handful of concurrent users, this is not a problem. But as your application grows — more users, more concurrent requests, more background workers — the cost of maintaining hundreds or thousands of individual processes adds up. Memory usage climbs. Context switching becomes measurable. Eventually, you hit the max_connections limit and your application slows to a crawl as it waits for connections to become free.

PgBouncer solves this by acting as a lightweight proxy between your application and PostgreSQL. Your application connects to PgBouncer, which maintains a small pool of real PostgreSQL connections and multiplexes them across all of your application’s requests. Instead of PostgreSQL managing a thousand processes, it manages twenty — while your application still has on-demand access to the database.

This guide shows you how to install PgBouncer, connect it to an existing PostgreSQL database, and update your application to route connections through it.

Prerequisites

  • A Linux server running Ubuntu (20.04 or later)
  • PostgreSQL installed and running locally
  • Sudo access
  • A PostgreSQL user and database already created for your application

Step 1: Install PgBouncer

Install PgBouncer using apt:

sudo apt install pgbouncer

Step 2: Configure pgbouncer.ini

PgBouncer is configured through a single INI file at /etc/pgbouncer/pgbouncer.ini. The file has two sections: [databases], which maps alias names to your PostgreSQL databases, and [pgbouncer], which controls PgBouncer’s own behavior.

Open the file:

sudo nano /etc/pgbouncer/pgbouncer.ini

Replace the default contents with the following, substituting your own database name where noted:

[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
listen_addr = *
listen_port = 6543
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
admin_users = postgres
stats_users = postgres
pool_mode = session
server_reset_query = DISCARD ALL
server_check_query = select 1
server_check_delay = 10
max_client_conn = 10000
default_pool_size = 20
log_connections = 0
log_disconnections = 0

Here’s what each setting does:

[databases] section

  • myapp: The alias your application uses in its connection string. The value tells PgBouncer where to find the real database — in this case, PostgreSQL running on localhost on its default port. You can define as many database aliases as you need, one per line.

[pgbouncer] section

  • listen_addr: The address PgBouncer listens on for incoming connections. * means all interfaces. If PgBouncer and your application are on the same server, you can restrict this to 127.0.0.1.
  • listen_port: The port your application connects to. 6543 is used here to avoid conflicting with PostgreSQL on 5432.
  • auth_type: The authentication method. md5 tells PgBouncer to verify client passwords against the entries in the auth_file.
  • auth_file: The path to a file containing allowed usernames and their hashed passwords.
  • admin_users: PostgreSQL users allowed to connect to PgBouncer’s admin console.
  • stats_users: Users allowed to run read-only SHOW commands on the admin console.
  • pool_mode: How PgBouncer decides when a server connection can be returned to the pool. session holds the server connection for the duration of the client session, which is the most compatible choice and works with all PostgreSQL features. The other options are transaction, which returns connections after each transaction, and statement, which returns them after each statement.
  • server_reset_query: The query PgBouncer runs before returning a server connection to the pool, to clear any session state left behind by the previous client. DISCARD ALL clears prepared statements, advisory locks, and other session-level settings. This is required when using session pool mode.
  • server_check_query: A lightweight query PgBouncer runs to verify that a server connection is still alive before assigning it to a client.
  • server_check_delay: How many seconds PgBouncer waits between liveness checks on idle server connections.
  • max_client_conn: The maximum total number of client connections PgBouncer will accept across all pools. Set this high enough to cover all of your application instances and worker processes.
  • default_pool_size: The number of real PostgreSQL connections maintained per database/user pair. This is the number that determines how many backend processes PostgreSQL itself needs to manage.
  • log_connections / log_disconnections: Logging every connect and disconnect generates significant noise in a busy environment. Setting both to 0 keeps your logs focused on errors and warnings.

Step 3: Add Users to the Auth File

PgBouncer authenticates clients against a flat text file. The format is one entry per line, with the username and password hash both wrapped in double quotes:

"username" "md5hash"

PgBouncer expects the same md5-hashed passwords that PostgreSQL stores internally. Rather than computing these by hand, you can pull them directly from PostgreSQL’s pg_shadow view, which requires superuser access.

Connect to PostgreSQL as the postgres user:

sudo su - postgres
psql

Query the password hash for your application’s database user:

SELECT usename, passwd FROM pg_shadow WHERE usename = 'myapp';

The output will look like this:

 usename |               passwd
---------+-------------------------------------
 myapp   | md5a1b2c3d4e5f6...

Copy the full value from the passwd column, including the md5 prefix. Open the auth file:

sudo nano /etc/pgbouncer/userlist.txt

Add an entry for each user PgBouncer needs to authenticate:

"myapp" "md5a1b2c3d4e5f6..."

If you also want to allow the postgres user to access the PgBouncer admin console, add its hash in the same way:

"myapp" "md5a1b2c3d4e5f6..."
"postgres" "md5..."

Save the file and lock down its permissions so only the postgres user can read it:

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

Step 4: Start PgBouncer

Start the PgBouncer service and enable it to start automatically on reboot:

sudo systemctl start pgbouncer
sudo systemctl enable pgbouncer

Verify it started cleanly:

sudo systemctl status pgbouncer

If there are configuration errors, the service will fail to start and the status output will include the relevant error message. The most common issues at this stage are a malformed pgbouncer.ini or a userlist.txt with incorrect formatting.

Step 5: Test the Connection

Verify that PgBouncer is accepting connections and can reach PostgreSQL by connecting through it the same way you would connect to PostgreSQL directly, using port 6543 and the database alias you defined in pgbouncer.ini:

psql -h 127.0.0.1 -p 6543 -U myapp myapp

If the connection succeeds and you can run queries, PgBouncer is working correctly and routing connections through to PostgreSQL.

Step 6: Update Your Application Connection String

The only change your application needs is a different port number. Point it to 6543 instead of 5432:

Before:

host=127.0.0.1 port=5432 dbname=myapp user=myapp

After:

host=127.0.0.1 port=6543 dbname=myapp user=myapp

Because PgBouncer presents the same interface as PostgreSQL, your application does not need to know it is talking to a proxy. The database name in the connection string must match the alias defined in the [databases] section of pgbouncer.ini — not necessarily the name of the actual PostgreSQL database, though they are often the same.

Monitoring PgBouncer

PgBouncer exposes an admin console you can connect to for live statistics. Connect using any PostgreSQL client, specifying pgbouncer as the database name:

psql -h 127.0.0.1 -p 6543 -U postgres pgbouncer

From here you can inspect pool health and activity in real time:

-- Show all connection pools and their state
SHOW POOLS;

-- Show aggregate statistics per database
SHOW STATS;

-- Show active client connections
SHOW CLIENTS;

-- Show active server connections
SHOW SERVERS;

The most useful view is SHOW POOLS, which tells you how many client connections are active, how many are idle, and — most importantly — how many are waiting. If the cl_waiting column is consistently non-zero, your default_pool_size is too small for your workload and needs to be increased.

You can reload the configuration without restarting PgBouncer, which is convenient when you add or remove users from the auth file:

RELOAD;

With PgBouncer in place, PostgreSQL sees a steady, manageable number of connections regardless of how many clients your application opens. The result is lower memory consumption on the database server, less overhead per query, and headroom to scale your application further without hitting PostgreSQL’s connection limits.