Connection Pooling with PgBouncer

Every new PostgreSQL connection isn't just a socket — it's a full backend process forking, allocating memory, and negotiating the protocol. If your app churns through hundreds of connections a second, your database spends more time spawning processes than executing queries. This lesson shows you how to use connection pooling with PgBouncer to slash that overhead, handle thousands of clients with a handful of connections, and keep your PostgreSQL instance fast and stable.

The problem this lesson solves

PostgreSQL is a process-per-connection database. Each psql or application library session maps to a separate OS process that holds a slice of RAM, shares buffers, and consumes CPU just to stay alive. Under load, that becomes a death spiral:

• A web app with 50 app instances opens 20 connections each → 1,000 PostgreSQL processes.
• Each idle connection still consumes memory and lock-manager slots.
• Under a traffic spike, the server hits max_connections (default 100) and starts rejecting new sessions with FATAL: sorry, too many clients already.
• Even with low traffic, connection setup/teardown (TCP handshake + auth + process spawn) adds 50–100ms per new session.

That's the pain: you can't simply raise max_connections because each slot costs memory, and contention spikes. The standard fix is a connection pooler — a proxy that sits between your app and PostgreSQL and multiplexes many client connections onto a small set of real server connections.

Core concept / mental model

Think of PgBouncer as a connection valet at a busy restaurant. Thousands of customers (client connections) ring the bell, but only a dozen tables (real PostgreSQL connections) exist. The valet takes your order, passes it to a free table, and if none are free, you wait in a queue. When a table frees up, the valet seats you — you never see the kitchen directly.

The key numbers to remember:

• Clients: the connections your app opens to PgBouncer (can be thousands).
• Servers: the real connections PgBouncer holds open to PostgreSQL (much smaller).
• Pool size: the max number of server connections per user/database.

PgBouncer implements three pooling modes:

In transaction mode, the pooler releases a server connection back to the pool as soon as COMMIT or ROLLBACK finishes — so a single server connection can serve hundreds of clients per second.

How it works step by step

Setting up PgBouncer involves four moving parts. Follow this logical order:

1. Install PgBouncer on the same machine as your app (or a dedicated proxy host).
2. Create a configuration file (pgbouncer.ini) with connection details and pool sizing.
3. Create a userlist file with credentials that PgBouncer will use to connect to PostgreSQL.
4. Start PgBouncer, then point your app at PgBouncer's port (usually 6432) instead of PostgreSQL's 5432.

Under the hood, PgBouncer listens on its own port and forwards queries from clients to a cached set of backend connections. When a client disconnects, PgBouncer keeps the server connection alive for the next client — hence "pooling."

The flow looks like this in a transaction pool:

• Client opens a connection to PgBouncer (fast, light).
• Client sends BEGIN; → PgBouncer checks out a server connection from the pool and forwards the query.
• Client sends COMMIT; → PgBouncer forwards it, then returns the server connection to the pool.
• If all server connections are busy, the client waits in PgBouncer's queue until one becomes free.

Hands-on walkthrough

Let's get PgBouncer running. The examples assume Ubuntu/Debian, PostgreSQL 14+, and Python 3.10+ for the client test.

1. Install PgBouncer

# On Debian/Ubuntu
sudo apt update
sudo apt install -y pgbouncer

# On RHEL/CentOS
# sudo yum install pgbouncer

# Or with Docker
# docker run -d --name pgbouncer -p 6432:6432 edoburu/pgbouncer

2. Create configuration file

Create /etc/pgbouncer/pgbouncer.ini:

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

[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 = 2000
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid

Key directives:

• pool_mode: transaction is ideal for web apps.
• default_pool_size: how many server connections per (user, database) pair. Start at 20 even if you have 100 max_connections.
• max_client_conn: max incoming client connections to PgBouncer.
• reserve_pool_size: extra connections to allocate when the pool is exhausted — a safety valve.

3. Create auth file

Create /etc/pgbouncer/userlist.txt with the same credentials as your PostgreSQL user:

"postgres" "secretpassword"
"appuser" "apppassword"

4. Start PgBouncer and test

sudo systemctl restart pgbouncer
sudo systemctl status pgbouncer

Verify you can connect via PgBouncer:

psql -h 127.0.0.1 -p 6432 -U appuser -d appdb -c "SELECT 1;"

Expected output:

 ?column?
----------
        1
(1 row)

5. Test with a Python script

Here's how your app connects now — instead of port 5432, use 6432:

import psycopg2

def get_conn():
    return psycopg2.connect(
        host="127.0.0.1",
        port="6432",  # PgBouncer
        dbname="appdb",
        user="appuser",
        password="apppassword"
    )

with get_conn() as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT count(*) FROM users")
        print(cur.fetchone())

Output: (1000,) — a normal result, but now your app uses a pooled connection.

Pro tip: In transaction mode, never hold a connection open across user interaction — every transaction should be short. PgBouncer will still work but may block other clients if you keep long-running idle transactions.

Compare options / when to choose what

You have several pooling solutions. Here's a comparison:

When to choose PgBouncer:

• You have a web app with many app instances (Docker, Kubernetes).
• You hit too many clients errors.
• You want to keep max_connections low (100–300) for stability.

When NOT to use PgBouncer:

• Your workload uses heavy NOTIFY/LISTEN — PgBouncer can break event notifications in transaction mode.
• You need prepared statements across transactions (PgBouncer can disable them, but it's tricky).
• You run a tiny prototype with 5 connections — not worth the extra hop.

Troubleshooting & edge cases

• FATAL: sorry, too many clients already — This is from PostgreSQL, meaning PgBouncer's pool is too small relative to concurrent transactions. Increase default_pool_size or max_client_conn carefully, check SHOW POOLS; and SHOW STATS; in psql -p 6432 to see queue lengths.
• ERROR: prepared statement "" already exists — In transaction mode, prepared statements don't persist across transactions. Use DEALLOCATE ALL on connection close, or disable prepared statements in your driver (e.g., set prepared_statements=False in psycopg2).
• NOTICE: transaction blocks not allowed in statement mode — You've set pool_mode=statement but your app uses multi-statement transactions. Switch to transaction mode.
• Authentication failures — Ensure the user in userlist.txt matches exactly the PostgreSQL role, and that auth_type matches the PostgreSQL pg_hba.conf (e.g., md5 vs scram-sha-256). PgBouncer also supports auth_type = scram-sha-256.
• PgBouncer not accepting connections — Check listen_addr is correct (0.0.0.0 binds all interfaces), and ensure no firewall blocking port 6432. Also check the log file for startup errors.

What you learned & what's next

You now understand the core problem: PostgreSQL's process-per-connection model is expensive and limiting. You can use connection pooling with PgBouncer to multiplex thousands of client connections onto a small pool of backend connections using transaction pooling mode. You've configured pgbouncer.ini, set authentication, and pointed your app at port 6432. You also know how to compare PgBouncer with other pooling strategies and troubleshoot common issues like too many clients and prepared statement conflicts.

Next lesson in this PostgreSQL track covers read replicas and load balancing — how to scale reads across multiple PostgreSQL instances. Connection pooling is the foundation; Read replicas extend your architecture horizontally.

Ready to apply this? In the next exercise, you'll set up PgBouncer for a Flask app and see how your query latency drops under concurrency.