PgBouncer Setup

Your PostgreSQL server is silently choking on connection overhead. Every new connection spawns a heavyweight backend process, consuming memory and CPU cycles. By default, PostgreSQL only allows 100 concurrent connections, and if you're running a modern Python application with async frameworks like FastAPI or Django, you'll hit that ceiling fast. The result: angry FATAL: sorry, too many clients already errors and degraded performance. But there's a battle-tested solution used by production teams everywhere: PgBouncer. This lightweight connection pooler sits between your app and PostgreSQL, reusing connections and keeping your database calm under load. By the end of this lesson, you'll know how to set up connection pooling with PgBouncer—from installation to production-ready configuration—and you'll have the skills to eliminate connection bottlenecks once and for all.

The problem this lesson solves

PostgreSQL's process-per-connection model is robust but expensive. Each new client connection forks a backend process that can consume tens of megabytes of RAM. When your application opens a new connection for every request—a common pattern in Python with psycopg2 or SQLAlchemy—you quickly exhaust resources. The default max_connections of 100 is a hard limit, and exceeding it produces FATAL: sorry, too many clients already.

Connection pooling solves this by reusing a small set of database connections across many client sessions. Instead of your app negotiating a new backend every time, it borrows a connection from a pool, uses it, and returns it. PgBouncer is the most popular pooler for PostgreSQL because it's lightweight (less than 1MB), fast, and supports multiple pooling modes. This lesson is your practical guide to setting it up correctly.

Core concept / mental model

Think of PgBouncer as a valet parking service for database connections. Without it, every driver (your app's request) has to find their own parking spot (connection) and pay the parking fee (connection overhead). With PgBouncer, a valet takes your car, parks it in a small, well-managed lot, and returns it exactly when you need it. The lot is your pool of persistent connections, and the valet is PgBouncer.

PgBouncer operates in two main modes:

• Session pooling: The pool assigns a PostgreSQL connection to a client for the entire client session. The connection is released only when the client disconnects. This matches PostgreSQL's native behavior but doesn't help reduce connections for short-lived sessions.
• Transaction pooling: The pool assigns a connection only for the duration of a single transaction. After the transaction commits or rolls back, the connection returns to the pool. This mode allows dozens or hundreds of clients to share a handful of connections, offering the biggest scalability win.

In this lesson, we'll focus on transaction pooling, which is the recommended default for most web applications.

Pro tip: While PgBouncer is a separate daemon, it's not a literal PostgreSQL proxy—it understands the PostgreSQL wire protocol and can even route based on the database name in the connection request.

How it works step by step

Setting up PgBouncer involves these logical steps:

1. Install PgBouncer on the same host as your PostgreSQL server (or on a dedicated machine), using your package manager or official binaries.
2. Configure the key files: the main config pgbouncer.ini and the authentication file userlist.txt.
3. Define a pool size that matches your app's concurrency needs without overwhelming your database.
4. Point your application at PgBouncer's port (usually 6432) instead of PostgreSQL's 5432.
5. Start and verify: launch the daemon, check logs, and test with psql.

Each step has its details and pitfalls, which we'll cover hands-on next.

Hands-on walkthrough

Let's set up PgBouncer from scratch. We'll use Ubuntu/Debian commands, but the process is similar on other systems.

Step 1: Install PgBouncer

sudo apt update
sudo apt install pgbouncer

For other platforms, you can compile from source or use Docker (see variations below).

Step 2: Edit the main configuration

The default config is at /etc/pgbouncer/pgbouncer.ini. Open it and adjust these key sections:

# /etc/pgbouncer/pgbouncer.ini
[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 50
min_pool_size = 10
reserve_pool_size = 5
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid

• [databases] maps a database name that clients will use to the actual PostgreSQL backend. In this case, clients connecting to myapp will be redirected to the local PostgreSQL on port 5432.
• listen_addr and listen_port define where PgBouncer listens (port 6432 is the standard).
• auth_type = md5 means usernames/passwords are checked against the auth file.
• pool_mode = transaction enables transaction pooling.

Step 3: Create the auth file

printf '"myapp" "secret"\n' > /etc/pgbouncer/userlist.txt
sudo chown postgres:postgres /etc/pgbouncer/userlist.txt
sudo chmod 600 /etc/pgbouncer/userlist.txt

The file contains plaintext passwords (or md5 hashes). For security, set tight file permissions.

Step 4: Start PgBouncer

sudo systemctl enable pgbouncer
sudo systemctl start pgbouncer

Step 5: Test with psql

psql -h 127.0.0.1 -p 6432 -U myapp -d myapp

You should see a normal PostgreSQL prompt. To verify pooling works, check PgBouncer's stats:

SHOW POOLS;

This command returns a table showing active clients vs server connections—proof that many clients share few actual connections.

Step 6: Point your Python app at PgBouncer

In Python with psycopg2, just change the port:

import psycopg2
conn = psycopg2.connect(
    host="localhost",
    port=6432,
    dbname="myapp",
    user="myapp",
    password="secret"
)

Your connection now uses the pool. That's it!

Compare options / when to choose what

You have several connection pooling options. The table below compares PgBouncer with alternatives.

When to choose what:

• Choose PgBouncer when you need a lightweight, battle-tested pooler with minimal overhead and you're comfortable with a separate daemon.
• Choose Pgpool-II if you need advanced features like query load balancing or automatic failover.
• Choose application-level pooling (e.g., psycopg2.pool.ThreadedConnectionPool) only if you have a few hundred queries per second and can manage connections in code—but remember, the backend process limit still applies.

Variations: You can also run PgBouncer in a Docker container (official image), or use a cloud-managed pooler like Amazon RDS Proxy, which sits between you and RDS PostgreSQL.

Troubleshooting & edge cases

Even with a simple config, you'll hit issues. Here are the most common ones and how to fix them.

• FATAL: sorry, too many clients already: This tells you the max_client_conn is too low, or default_pool_size is too small. Increase them in the config and reload. But remember, the total number of server connections is default_pool_size plus reserve_pool_size, so keep it below max_connections in postgresql.conf.
• auth failed errors: The username/password in userlist.txt doesn't match what PostgreSQL expects, or the auth_type doesn't match (e.g., you use scram-sha-256 in PostgreSQL but md5 in PgBouncer). For Postgres 14+, switch auth_type to scram-sha-256 and generate the correct hash.
• Transaction pooling breaks session-scoped features: If your app uses SET search_path or advisory locks, these don't work well with transaction pooling because the connection might change between transactions. Use session pooling for those apps, or restructure the app to set config per transaction using SET LOCAL.
• Prepared statements fail with "does not exist": Prepared statements are tied to a session. In transaction pooling, different clients may hit different backends. Use server-side prepared statements sparingly, or enable prepared_statements_per_session = 0 (which disables them).
• Connections hang or timeout: Check server_idle_timeout and client_idle_timeout—too low values can drop idle connections prematurely.

Pro tip: Use SHOW STATS; and SHOW POOLS; to debug in real time. These give you a wealth of information about connection reuse and wait times.

What you learned & what's next

You've mastered the core idea of connection pooling and can now set up PgBouncer for your PostgreSQL database. You know how to install it, configure transaction pooling, point your Python app at it, and troubleshoot common pitfalls. You can explain the trade-offs between PgBouncer and alternatives like Pgpool-II or app-level pooling.

In the next lesson, we'll dive into B-tree index internals — understanding how PostgreSQL's default index structure performs and when to choose other index types. This knowledge complements your pooling setup by making your queries even faster, so you can handle more traffic with fewer resources.

Key takeaway: Connection pooling with PgBouncer is a low-lift, high-impact way to scale your PostgreSQL-backed Python applications. You're now ready to apply it in production.