Configuring CONN_MAX_AGE for Django and PgBouncer

This guide is part of Django Database Connection Management. It addresses a specific production failure: Django’s CONN_MAX_AGE persistent-connection setting silently multiplies the backend count when PgBouncer sits in front of PostgreSQL, and the two layers fight over who owns connection lifetime. The visible symptom is a Postgres log line that appears even though PgBouncer reports plenty of free server connections:

FATAL: remaining connection slots are reserved for non-replication superuser connections

or, on the application side, intermittent psycopg2.OperationalError: server closed the connection unexpectedly after a deploy or a quiet period. Both come from the same root cause: every Gunicorn worker that Django holds open via CONN_MAX_AGE pins a client-side connection, and depending on PgBouncer’s pool_mode those pins either stack against the server pool or get severed underneath Django without it noticing. This guide explains the connection math, the interaction with PgBouncer’s pooling modes, and the exact settings that make persistent connections and a transaction pooler coexist.

Key operational takeaways:

• Total client connections to PgBouncer ≈ web_processes × workers × DATABASES when CONN_MAX_AGE > 0. This is client-side, not server-side, but it caps PgBouncer’s max_client_conn.
• CONN_MAX_AGE keeps a Django connection object warm; under PgBouncer transaction pooling that “warm” connection is not pinned to a server backend between transactions, so the saving is the TCP+TLS+auth handshake to PgBouncer only.
• Always enable CONN_HEALTH_CHECKS = True (Django 4.1+) when CONN_MAX_AGE > 0 behind a pooler, or stale sockets surface as the first query of a request failing.
• CONN_MAX_AGE must be shorter than PgBouncer’s server_idle_timeout and PostgreSQL’s idle_in_transaction_session_timeout reasoning windows, or you reuse a socket the proxy already recycled.
• Never set CONN_MAX_AGE to a large value with prefork Celery workers; pair it with close_old_connections() as covered in Preventing Django Connection Leaks During Celery Tasks.

Rapid incident diagnosis

Start by separating client-side exhaustion (Django → PgBouncer) from server-side exhaustion (PgBouncer → PostgreSQL). They have different fixes and different log signatures.

Check PgBouncer first. Connect to the admin console and read the pool table:

-- psql against PgBouncer's admin interface (port 6432, database pgbouncer)
SHOW POOLS;
SHOW CLIENTS;

In SHOW POOLS, cl_active + cl_waiting is how many Django connections PgBouncer is holding; sv_active + sv_idle is how many real PostgreSQL backends are in use. If cl_waiting is non-zero and maxwait is climbing, clients are queuing for a server slot — that is default_pool_size being too small or pool_mode = session pinning backends. If cl_active is near max_client_conn and PgBouncer rejects new connections, Django is opening too many client connections, which is the CONN_MAX_AGE multiplication problem.

The signature that specifically indicts CONN_MAX_AGE is the combination of healthy sv_active (PostgreSQL is not saturated) with cl_active saturated at max_client_conn. Django is hoarding client connections it is not actively querying on.

Connection math: the per-worker formula

Django opens one connection object per database alias per process, and CONN_MAX_AGE > 0 keeps that object alive between requests. With a prefork web server the process count is the multiplier.

Define the peak client-connection demand against PgBouncer:

client_conns = web_processes
             × workers_per_process       (Gunicorn/uWSGI worker count)
             × db_aliases                 (entries in settings.DATABASES)
             × (1 if CONN_MAX_AGE > 0 else burst_factor)

Worked example. You run 4 application hosts, each Gunicorn with --workers 9 (the common 2 × vCPU + 1 for a 4-vCPU box), one database alias, and CONN_MAX_AGE = 60:

client_conns = 4 hosts × 9 workers × 1 alias × 1 = 36 persistent client connections

Those 36 connections live against PgBouncer continuously, even at low traffic, because each warm worker holds its connection for up to 60 seconds of idle. Set max_client_conn comfortably above this — at least 36 × 1.5 = 54 to absorb deploys where old and new workers overlap. Critically, these 36 client connections do not require 36 PostgreSQL backends under transaction pooling. PgBouncer multiplexes them onto default_pool_size server connections (commonly 20–25). That decoupling is the entire reason to put PgBouncer in front of a CONN_MAX_AGE-heavy Django fleet: persistent client connections, bounded server connections.

If you add async workers (gthread) the multiplier is still the process/worker count, not the thread count — each Django connection is thread-local, but threads within a worker reuse via the same lifecycle, and only as many as are concurrently in a request hold a distinct connection. Size max_client_conn for the worst case of threads_per_worker simultaneous in-flight requests if you use threaded workers.

How CONN_MAX_AGE interacts with PgBouncer pool_mode

The value of CONN_MAX_AGE means something different depending on PgBouncer’s pool_mode. Read PgBouncer Transaction vs Statement Pooling for the full mechanics; the summary for Django is:

The trap: people set CONN_MAX_AGE = 600 expecting connection reuse, run PgBouncer in session mode “to be safe,” and end up with every worker pinning a backend for ten minutes. That is strictly worse than no pooler. Under transaction mode, CONN_MAX_AGE is purely a client-side optimization that saves the handshake to PgBouncer — keep it modest (30–60s) because the server-side win is already delivered by PgBouncer.

Exact remediation & configuration

Set Django to keep client connections warm but bounded, and let PgBouncer own the server backends.

# settings.py
DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "NAME": "appdb",
        "USER": "app",
        "PASSWORD": os.environ["DB_PASSWORD"],
        "HOST": "pgbouncer.internal",   # point at PgBouncer, NOT PostgreSQL directly
        "PORT": 6432,
        # Keep the client socket to PgBouncer warm for 60s of idle.
        "CONN_MAX_AGE": 60,
        # Django 4.1+: ping the connection before reuse, drop it if dead.
        "CONN_HEALTH_CHECKS": True,
        "OPTIONS": {
            # Required for transaction pooling: psycopg must not rely on
            # server-side prepared statements that outlive a transaction.
            # psycopg3:
            "prepare_threshold": None,
        },
    }
}

PgBouncer side, matched to the math above:

; pgbouncer.ini
[databases]
appdb = host=postgres-primary.internal port=5432 dbname=appdb

[pgbouncer]
pool_mode = transaction
; Above the worked-example peak (36) with deploy headroom.
max_client_conn = 200
; Server backends multiplexed across all clients. Keep well under
; PostgreSQL max_connections, leaving room for migrations and admin.
default_pool_size = 25
reserve_pool_size = 5
; Must be LONGER than Django CONN_MAX_AGE so Django never reuses a
; socket PgBouncer already closed server-side. 60s CONN_MAX_AGE -> 90s+ here.
server_idle_timeout = 120

The ordering constraint is non-negotiable: CONN_MAX_AGE (60) < server_idle_timeout (120). If server_idle_timeout were shorter, PgBouncer would recycle the server backend, but Django would still believe its 60-second connection is fine and send a query into a stale state, producing server closed the connection unexpectedly. CONN_HEALTH_CHECKS = True is the safety net that turns that race into a transparent reconnect instead of a 500.

Apply with zero downtime by rolling Gunicorn workers (HUP for graceful reload) after deploying the settings change; existing warm connections drain naturally as their CONN_MAX_AGE expires. Reload PgBouncer config with RELOAD; on the admin console — it does not drop existing client connections.

For Celery, do not rely on CONN_MAX_AGE for teardown. Long-lived workers must call close_old_connections() between tasks; see Preventing Django Connection Leaks During Celery Tasks for the signal handler.

Validation & verification

After deploying, confirm three things: client connections match the formula, server backends stay bounded, and no stale-socket errors appear.

Count what Django actually opened, from PostgreSQL’s view (these are PgBouncer’s server backends):

SELECT count(*), state
FROM pg_stat_activity
WHERE datname = 'appdb' AND usename = 'app'
GROUP BY state;

Under transaction pooling this count should hover near default_pool_size (25) regardless of how many Gunicorn workers are warm — that is the proof PgBouncer is multiplexing correctly. If it climbs toward your worker count instead, pool_mode is session.

Confirm the client side from PgBouncer:

SHOW POOLS;
-- cl_active should approximate web_processes × workers (your formula).
-- sv_active + sv_idle should stay <= default_pool_size.

Verify health checks are recycling stale sockets rather than erroring. Force a quiet period longer than server_idle_timeout, then issue a request and watch the application log: with CONN_HEALTH_CHECKS = True you see a transparent reconnect; without it you see OperationalError. Run a load test that idles between bursts to exercise exactly this path.

Frequently Asked Questions

Related

• Django Database Connection Management — the parent topic covering Django’s full ORM connection lifecycle.
• Preventing Django Connection Leaks During Celery Tasks — teardown for long-lived workers where CONN_MAX_AGE is insufficient.
• PgBouncer Transaction vs Statement Pooling — how pool_mode changes what CONN_MAX_AGE persists.