Set Up PgBouncer for Django

Problem statement

Every PostgreSQL connection is a separate server process that uses memory whether it is busy or idle. A Django deployment multiplies connections quickly: servers × Gunicorn workers × threads, plus Celery workers, cron jobs, and admin shells. Long before the database runs out of CPU, it can hit max_connections and start refusing clients with FATAL: sorry, too many clients already.

PgBouncer is a lightweight connection pooler. Django connects to PgBouncer, and PgBouncer shares a small number of real PostgreSQL connections among many clients. The benefit is large, but transaction pooling changes connection semantics, and several Django features assume a dedicated connection:

• server-side cursors used by QuerySet.iterator() break across transactions
• session state (SET, advisory locks, LISTEN) does not persist between transactions
• prepared statements can collide unless configured deliberately
• migrations that expect session-level behavior are safer on a direct connection

This guide installs PgBouncer on the application server, configures transaction pooling with SCRAM authentication, adjusts Django settings, and adds monitoring.

Quick answer

1. Install PgBouncer and listen on 127.0.0.1:6432.
2. Configure pool_mode = transaction, auth_type = scram-sha-256, and a userlist.txt with the role's SCRAM secret.
3. Size default_pool_size well below PostgreSQL's max_connections, and set max_client_conn for your client count.
4. Point Django's PORT at 6432 and set DISABLE_SERVER_SIDE_CURSORS = True.
5. Keep psycopg client-side parameter binding (Django's default with psycopg 3), or enable max_prepared_statements in PgBouncer before using server-side binding.
6. Run migrations against PostgreSQL directly.
7. Monitor with SHOW POOLS; and SHOW STATS; on the admin console.

Step-by-step solution

1. Understand the pool modes

Transaction pooling is the usual choice: a Django request that runs a few queries holds a real connection only while each transaction (or each autocommit query) runs.

2. Install PgBouncer

sudo apt install pgbouncer
pgbouncer --version

Installing it on each application server keeps connection setup local and avoids another network hop to a shared pooler. A central pooler on the database host is also common; the configuration is the same.

3. Prepare authentication

Make sure PostgreSQL stores the password as SCRAM (the default in current PostgreSQL versions):

sudo -u postgres psql -c "SHOW password_encryption;"
sudo -u postgres psql -At -c "SELECT '\"' || rolname || '\" \"' || rolpassword || '\"' FROM pg_authid WHERE rolname = 'myapp';"

The second command prints a line like:

"myapp" "SCRAM-SHA-256$4096:...$...:..."

Write it to /etc/pgbouncer/userlist.txt:

sudo tee /etc/pgbouncer/userlist.txt >/dev/null <<'EOF'
"myapp" "SCRAM-SHA-256$4096:...$...:..."
EOF
sudo chown postgres:postgres /etc/pgbouncer/userlist.txt
sudo chmod 600 /etc/pgbouncer/userlist.txt

Storing the SCRAM verifier rather than a plain password means the file cannot be used directly as a password, although it should still be protected. If the database runs on another host, retrieve the verifier there. For many roles, PgBouncer's auth_query can look up credentials dynamically instead of a static file.

4. Configure PgBouncer

/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
unix_socket_dir = /var/run/postgresql

auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3

server_idle_timeout = 300
query_wait_timeout = 30

admin_users = postgres
stats_users = myapp

log_connections = 0
log_disconnections = 0

Key settings:

• default_pool_size is the number of server connections per database and user pair. This is the number PostgreSQL actually sees.
• max_client_conn is how many client connections PgBouncer accepts. It can be much higher than the pool size, because clients wait in a queue for a free server connection.
• reserve_pool_size adds a few extra server connections when clients have waited longer than reserve_pool_timeout seconds.
• query_wait_timeout fails a client that waits too long for a connection, instead of hanging a Gunicorn worker indefinitely.

Restart and check:

sudo systemctl restart pgbouncer
sudo systemctl status pgbouncer
psql "host=127.0.0.1 port=6432 dbname=myapp user=myapp" -c "SELECT 1;"

5. Size the pools

Work backwards from the database:

1. Check PostgreSQL's limit: SHOW max_connections; (often 100 by default).
2. Reserve connections for superusers, migrations, backups, and monitoring (for example 15).
3. Divide the remainder across poolers: with two app servers each running PgBouncer, 40 each is the ceiling; default_pool_size = 20 leaves headroom.

More server connections are not faster once they exceed what the database's CPU and disks can execute concurrently. A modest pool with a short queue is usually better than a large pool that overloads PostgreSQL.

6. Configure Django

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "NAME": "myapp",
        "USER": "myapp",
        "PASSWORD": os.environ["DB_PASSWORD"],
        "HOST": "127.0.0.1",
        "PORT": "6432",
        "CONN_MAX_AGE": 60,
        "CONN_HEALTH_CHECKS": True,
        "DISABLE_SERVER_SIDE_CURSORS": True,
    }
}

• DISABLE_SERVER_SIDE_CURSORS: QuerySet.iterator() normally uses a named server-side cursor that lives across fetches. With transaction pooling, those fetches can land on different server connections, causing errors such as cursor "_django_curs_..." does not exist. Disabling them makes iterator() fetch results client-side in chunks.
• CONN_MAX_AGE: persistent connections from Django to PgBouncer are cheap and avoid reconnect overhead. PgBouncer still multiplexes the real server connections.
• Prepared statements: with psycopg 3, Django uses client-side parameter binding by default, which does not create server-side prepared statements, so it works with transaction pooling. If you set "OPTIONS": {"server_side_binding": True}, configure max_prepared_statements in PgBouncer (supported since PgBouncer 1.21) first.
• Django's built-in pool: do not combine Django's "pool" option (Django 5.1+ with psycopg 3) with PgBouncer on the same connection path; use one pooling layer.

7. Avoid session state in application code

In transaction mode, anything that depends on a session persisting across transactions is unreliable:

• SET search_path or SET statement_timeout outside a transaction
• pg_advisory_lock held across transactions (transaction-scoped pg_advisory_xact_lock is fine)
• LISTEN/NOTIFY consumers
• temporary tables used across transactions

Set per-role defaults in PostgreSQL instead, for example:

ALTER ROLE myapp SET statement_timeout = '30s';

8. Run migrations directly against PostgreSQL

Migrations run long transactions and sometimes use features that behave differently through a pooler. Use a direct connection for release tasks by overriding the port in the migration environment:

DATABASES["default"]["PORT"] = os.environ.get("DB_PORT", "6432")

DB_PORT=5432 python manage.py migrate --noinput

Make sure the migration command can reach port 5432 (for example it runs on the database host or an allowed application server).

9. Monitor PgBouncer

Connect to the admin console as a user in admin_users or stats_users:

sudo -u postgres psql -p 6432 -h /var/run/postgresql pgbouncer -c "SHOW POOLS;"
sudo -u postgres psql -p 6432 -h /var/run/postgresql pgbouncer -c "SHOW STATS;"

In SHOW POOLS, watch:

• cl_waiting: clients waiting for a server connection. Sustained non-zero values mean the pool is too small or queries are too slow.
• maxwait: how long the oldest waiting client has waited, in seconds.
• sv_active and sv_idle: server connections in use and available.

Export these to your metrics system with a PgBouncer exporter so you can alert on waiting clients.

10. Verify

# Django connects through PgBouncer
python manage.py shell -c "from django.db import connection; connection.ensure_connection(); print(connection.settings_dict['PORT'])"

# PostgreSQL sees few connections even under load
sudo -u postgres psql -c "SELECT usename, application_name, count(*) FROM pg_stat_activity GROUP BY 1, 2 ORDER BY 3 DESC;"

# iterator() works with transaction pooling
python manage.py shell -c "from django.contrib.auth import get_user_model; print(sum(1 for _ in get_user_model().objects.iterator(chunk_size=100)))"

Verification check:

• the Django shell prints 6432
• pg_stat_activity shows at most default_pool_size connections for the app role per pooler during a load test
• iterator() completes without cursor errors
• SHOW POOLS shows cl_waiting returning to zero after bursts

Rollback

Point Django back at port 5432 and restart the application. PgBouncer can stay installed; nothing in the database changes.

Explanation

PgBouncer decouples the number of connections your application opens from the number PostgreSQL has to serve. Transaction pooling achieves the biggest reduction because most web requests spend far more time outside transactions (rendering templates, calling APIs) than inside them. The trade-off is that a client no longer owns a server session, which is why Django's server-side cursors and any session-level state must be avoided.

Edge cases / notes

Managed PostgreSQL with a built-in pooler

Many managed services offer PgBouncer or a similar pooler as a feature. The same Django settings apply; check which pool mode the provider uses.

Celery workers

Celery workers hold connections too. Route them through PgBouncer as well, and keep CONN_MAX_AGE moderate for long-running workers.

TLS

If PgBouncer connects to a remote database, set server_tls_sslmode = verify-full with the CA file. If clients connect over a network, configure client_tls_* settings too.

Troubleshooting PgBouncer

Configuration changes can be applied without dropping clients:

sudo -u postgres psql -p 6432 -h /var/run/postgresql pgbouncer -c "RELOAD;"

Some settings, such as listen_addr and listen_port, require a restart instead.

File descriptor limits

Each client and server connection uses a file descriptor. With max_client_conn = 1000, the process needs more than the common default of 1024. On systemd, raise it with a drop-in:

sudo systemctl edit pgbouncer

[Service]
LimitNOFILE=4096

Restart PgBouncer and confirm with cat /proc/$(pidof pgbouncer)/limits | grep "open files".

Pausing for database maintenance

PgBouncer can hold client queries while the database restarts or fails over, turning a short outage into a brief delay:

psql -p 6432 -h /var/run/postgresql -U postgres pgbouncer -c "PAUSE myapp;"
# perform the database restart or failover
psql -p 6432 -h /var/run/postgresql -U postgres pgbouncer -c "RESUME myapp;"

PAUSE waits for active transactions to finish, then queues new ones until RESUME. Keep the pause shorter than query_wait_timeout and Gunicorn's worker timeout.

Choosing pool sizes by workload

• Mostly short web requests: a pool of 10 to 30 per pooler is often enough even for many Gunicorn workers.
• Long analytics queries mixed with web traffic: give them a separate database entry or user in PgBouncer with its own small pool, so reports cannot starve web requests.
• Celery workers with long transactions: a separate pool per workload keeps queue backlogs from blocking web requests.

Next steps

• If you are already seeing connection errors, start with Fix PostgreSQL "Too Many Connections" in Django.
• Compare with Django's native pool in Use Django's Built-In PostgreSQL Connection Pool.
• Review database settings in the PostgreSQL Production Checklist for Django.

FAQ

Which PgBouncer pool mode should Django use?

Transaction mode, with DISABLE_SERVER_SIDE_CURSORS = True in Django. Session mode works without changes but reduces connections much less; statement mode is incompatible with Django transactions.

Why do I get "cursor does not exist" errors with PgBouncer?

QuerySet.iterator() uses a server-side cursor that must stay on one server connection. In transaction pooling, later fetches can use another connection. Set DISABLE_SERVER_SIDE_CURSORS = True.

Should I keep CONN_MAX_AGE when using PgBouncer?

Yes, a moderate value such as 60 seconds is fine. Connections from Django to PgBouncer are cheap, and PgBouncer controls how many real PostgreSQL connections exist.

Should Django migrations run through PgBouncer?

Prefer a direct connection to PostgreSQL for migrations. They run long transactions and are one-off release tasks that do not benefit from pooling.