If you have ever seen the dreaded FATAL: sorry, too many clients already error in your PostgreSQL logs, you have run into one of the most common scaling problems with Postgres. Each connection to PostgreSQL is backed by a real operating system process, and those processes are expensive. A few hundred idle connections can eat gigabytes of RAM and slow the whole server down, even when almost none of them are doing useful work.
This is where a connection pooler comes in, and PgBouncer is the most popular one for PostgreSQL. In this tutorial you will install PgBouncer on Ubuntu, place it in front of an existing PostgreSQL database, configure secure authentication, choose the right pooling mode for your workload, and verify everything works through PgBouncer’s built-in admin console.
This guide is written for developers, sysadmins, and DevOps engineers who already run PostgreSQL and want to handle more clients without throwing more RAM at the problem. You only need basic Linux command-line skills. If you do not have PostgreSQL installed yet, start with How to Install and Configure PostgreSQL on Ubuntu first.
Why PostgreSQL Needs a Connection Pooler
PostgreSQL uses a process-per-connection model. Every time a client connects, the server forks a dedicated backend process. That process holds memory for caches, sort buffers, and bookkeeping even when it is sitting idle waiting for the next query.
Modern applications make this worse. A typical web app runs several worker processes, each with its own connection pool, spread across several servers. Add a background job runner and a couple of analytics scripts, and suddenly you have 500 open connections to a database that comfortably handles maybe 100 active queries at once. Most of those connections are idle at any given moment, but they still consume resources and count against the max_connections limit.
A connection pooler sits between your application and PostgreSQL. The application connects to the pooler, and the pooler keeps a small, reusable set of real connections to PostgreSQL. When a client needs to run a query, the pooler hands it one of those server connections, then takes it back when the work is done. Thousands of client connections can share a pool of twenty server connections.
PgBouncer Pooling Modes
PgBouncer offers three pooling modes, and choosing the right one matters:
- Session pooling: A server connection is assigned to a client for the entire duration of that client’s connection. This is the safest mode and supports every PostgreSQL feature, but it gives you the least reuse.
- Transaction pooling: A server connection is assigned to a client only for the length of a single transaction. As soon as the transaction commits or rolls back, the connection goes back to the pool. This gives huge reuse and is the most popular mode for web applications.
- Statement pooling: The connection is returned after every single statement. This is the most aggressive mode and forbids multi-statement transactions entirely. It is rarely used.
Transaction pooling is the sweet spot for most web workloads, but it comes with one important catch: features that rely on session state, such as prepared statements, SET commands, advisory locks, and LISTEN/NOTIFY, do not behave as expected because the next transaction might land on a different server connection. We will use transaction pooling in this tutorial and cover the caveats later.
Prerequisites
Before you begin, make sure you have the following:
- An Ubuntu 22.04 or 24.04 server (the steps are identical for both)
- A running PostgreSQL instance you can connect to (local or remote)
- A database and a database user that your application already uses
- A user account with
sudoprivileges - Basic familiarity with editing config files and running
psql
For this tutorial I will assume PostgreSQL is running on the same machine at 127.0.0.1:5432, with a database named appdb owned by a user named appuser. Adjust these names to match your setup.
Step 1: Install PgBouncer
PgBouncer is in the standard Ubuntu repositories, so installation is a single command:
sudo apt update
sudo apt install -y pgbouncer
The package installs PgBouncer, creates a postgres system user it can run as, and sets up a systemd service. Confirm the version that landed:
pgbouncer --version
You should see output similar to this:
PgBouncer 1.21.0
The main configuration files now live in /etc/pgbouncer/. The two you care about are pgbouncer.ini (the main config) and userlist.txt (the authentication file).
Step 2: Prepare the Authentication Verifier
PgBouncer needs to authenticate clients before it forwards them to PostgreSQL. Modern PostgreSQL uses the scram-sha-256 password method, and the cleanest approach is to copy the existing SCRAM verifier for your user out of PostgreSQL and into PgBouncer’s user list. That way you never store a plain-text password in PgBouncer’s config.
Connect to PostgreSQL as a superuser and read the stored verifier:
sudo -u postgres psql -c "SELECT rolname, rolpassword FROM pg_authid WHERE rolname = 'appuser';"
The output looks like this (truncated for readability):
rolname | rolpassword
----------+-----------------------------------------------------------------------------------------------
appuser | SCRAM-SHA-256$4096:abc123...$def456...:ghi789...
(1 row)
Copy the entire SCRAM-SHA-256$... string. You will paste it into the user list in the next step. If your user still uses an older md5... hash, that works too, but consider upgrading to SCRAM with ALTER ROLE appuser PASSWORD 'newpassword'; after setting password_encryption = scram-sha-256 in PostgreSQL.
Step 3: Configure the User List
Open the user list file:
sudo nano /etc/pgbouncer/userlist.txt
Add a line with the username in double quotes, a space, and the verifier in double quotes:
"appuser" "SCRAM-SHA-256$4096:abc123...$def456...:ghi789..."
We also want a separate user just for the PgBouncer admin console, so we can inspect the pools without using the application credentials. Add a second line with a plain password (PgBouncer can authenticate console users against a plain string):
"pgbadmin" "a-strong-admin-password"
Save and exit. Lock the file down so only the PgBouncer user can read it, since it contains credentials:
sudo chown postgres:postgres /etc/pgbouncer/userlist.txt
sudo chmod 600 /etc/pgbouncer/userlist.txt
Step 4: Configure pgbouncer.ini
Now edit the main configuration file. Back up the default first so you always have a reference:
sudo cp /etc/pgbouncer/pgbouncer.ini /etc/pgbouncer/pgbouncer.ini.bak
sudo nano /etc/pgbouncer/pgbouncer.ini
Replace the contents with the following. I have kept it minimal and commented every important line:
[databases]
; Map a pooler database name to a real PostgreSQL backend.
; Clients will connect to "appdb" on PgBouncer, and PgBouncer
; forwards them to appdb on the real server.
appdb = host=127.0.0.1 port=5432 dbname=appdb
[pgbouncer]
; Listen on localhost only. Use 0.0.0.0 to accept remote clients,
; but only do that behind a firewall or private network.
listen_addr = 127.0.0.1
listen_port = 6432
; How PgBouncer checks client passwords.
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
; Pooling mode. transaction is best for most web apps.
pool_mode = transaction
; Maximum number of client connections PgBouncer will accept.
max_client_conn = 1000
; How many server connections to open per database/user pair.
default_pool_size = 20
; Keep a few connections warm so the first request is not slow.
min_pool_size = 5
; Users allowed into the admin console.
admin_users = pgbadmin
; Where PgBouncer writes its logs and pid file on Ubuntu.
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
The key numbers to understand are max_client_conn and default_pool_size. The first is how many clients can connect to PgBouncer (cheap, so it can be large). The second is how many real PostgreSQL connections PgBouncer opens per database and user (expensive, so keep it small). In this example up to 1000 application clients share a pool of 20 real connections.
Make sure default_pool_size multiplied by the number of pools stays comfortably below PostgreSQL’s own max_connections setting, otherwise PgBouncer cannot open the connections it promises.
Step 5: Start PgBouncer
Restart the service and enable it so it survives reboots:
sudo systemctl restart pgbouncer
sudo systemctl enable pgbouncer
sudo systemctl status pgbouncer
A healthy service shows active (running). If it failed to start, the log file will tell you why:
sudo tail -n 30 /var/log/postgresql/pgbouncer.log
If you want to understand the systemd service management used here in more depth, see Manage Background Services and Timers with systemd on Ubuntu.
Step 6: Connect Through PgBouncer
PgBouncer now listens on port 6432. Connect to your database through it exactly as you would connect to PostgreSQL, but point at the new port:
psql "host=127.0.0.1 port=6432 dbname=appdb user=appuser"
Enter the appuser password when prompted. If you land at the appdb=> prompt, traffic is flowing through PgBouncer to PostgreSQL. Run a quick query to be sure:
SELECT now(), current_user;
The final step in production is to update your application’s connection string. Change the port from 5432 to 6432 and leave everything else the same. Your application does not need to know PgBouncer exists; it just talks to what looks like a normal PostgreSQL server.
Step 7: Inspect the Pools with the Admin Console
PgBouncer ships with a virtual database called pgbouncer that exposes live statistics. Connect to it with the admin user:
psql "host=127.0.0.1 port=6432 dbname=pgbouncer user=pgbadmin"
This is not a real SQL database; it accepts a small set of SHOW commands. The most useful one lists the active pools:
SHOW POOLS;
You will see columns like cl_active (clients currently using a server connection), cl_waiting (clients waiting for a free connection), and sv_idle (idle server connections ready to reuse). A healthy pool under normal load shows zero or very few waiting clients.
Other handy commands:
SHOW STATS; -- query and traffic totals per database
SHOW CLIENTS; -- every connected client
SHOW SERVERS; -- every real PostgreSQL connection PgBouncer holds
If cl_waiting is consistently high, your default_pool_size is too small for the load and clients are queuing for a free connection.
Common Mistakes and Troubleshooting
FATAL: password authentication failed. This almost always means the verifier in userlist.txt does not match what PostgreSQL expects. Re-copy the SCRAM-SHA-256$... string from pg_authid, and make sure both the username and verifier are wrapped in double quotes. Reload PgBouncer after editing the file with sudo systemctl reload pgbouncer.
FATAL: no such database. The database name the client used does not appear in the [databases] section of pgbouncer.ini. PgBouncer only routes databases you explicitly list, unless you add a wildcard entry like * = host=127.0.0.1 port=5432.
Prepared statements break under transaction pooling. Many ORMs and drivers use server-side prepared statements by default. Because transaction pooling moves clients between server connections, a prepared statement created on one connection will not exist on the next. Either switch the driver to disable server-side prepared statements (for example, set prepareThreshold=0 for the JDBC driver or use simple query mode), or enable PgBouncer’s built-in support by setting max_prepared_statements = 200 in the config, which is available in PgBouncer 1.21 and later.
SET commands and session settings get lost. In transaction mode, a SET statement_timeout = ... issued in one transaction does not carry over to the next. Apply session-level settings per transaction, or move them into PostgreSQL’s ALTER ROLE ... SET ... so they apply automatically.
PgBouncer cannot connect to PostgreSQL. Check that PostgreSQL is actually listening and that pg_hba.conf allows connections from 127.0.0.1. Test the backend directly with psql "host=127.0.0.1 port=5432 dbname=appdb user=appuser" to isolate whether the problem is PgBouncer or PostgreSQL itself.
Best Practices
Use auth_query for many users. Copying verifiers by hand is fine for a handful of users, but it does not scale. For larger setups, configure auth_query so PgBouncer looks up passwords from PostgreSQL automatically using a dedicated low-privilege role and a SECURITY DEFINER function. This keeps userlist.txt to a single auth user.
Never expose port 6432 to the public internet. PgBouncer is a database entry point. Keep listen_addr = 127.0.0.1 when the app runs on the same host, or bind it to a private network interface and protect it with a firewall. If you manage firewall rules with UFW, the rules apply here just like any other service.
Right-size your pools with real numbers. Start default_pool_size near the number of CPU cores on the database server times two, then watch SHOW POOLS under real load. Growing the pool past what PostgreSQL can actually run in parallel just moves the queue from PgBouncer into the database and makes everything slower.
Run PgBouncer close to your application. Network latency between the app and PgBouncer is paid on every query, so running the pooler on the application host (or as a sidecar) usually beats running it on the database host. If you operate replicas, you can point separate PgBouncer database entries at your read replicas; this pairs nicely with PostgreSQL Streaming Replication to spread read traffic.
Monitor it like any other service. Scrape SHOW STATS periodically, or run a dedicated PgBouncer exporter, so you get alerted before clients start queuing rather than after users notice slow pages.
Conclusion
You now have PgBouncer sitting in front of PostgreSQL, accepting up to a thousand client connections while holding only a small pool of real database connections open. You installed PgBouncer, set up secure SCRAM authentication without storing plain-text passwords, configured transaction pooling, pointed your application at the new port, and learned to read the admin console to spot trouble early.
Connection pooling is one of those changes that pays for itself the first time a traffic spike hits and your database keeps serving requests instead of refusing them. From here, good next steps are configuring auth_query so user management scales, adding a read-replica pool to offload reporting queries, and wiring PgBouncer metrics into your monitoring stack so pool saturation never takes you by surprise.