All notes
Programming

Connection pooling: "too many clients", CONN_MAX_AGE, the psycopg pool and PgBouncer

You scaled from 4 pods to 16 and PostgreSQL answered "too many clients". Why a connection is expensive, how Django manages it, and when you need CONN_MAX_AGE, the psycopg pool or PgBouncer.

Picture a sale day. Traffic is climbing, so you scale your gunicorn pods from 4 to 16. A couple of minutes later the logs fill with the same line:

psycopg.OperationalError: connection failed: FATAL:  sorry, too many clients already

The database isn't slow and the CPU is idle. The problem isn't your queries, it's the number of connections. Every new pod brought its own PostgreSQL connections, and the server hit max_connections. Scaling out didn't speed the app up. It took it down.

This post covers why a connection is expensive, how Django manages it, and three tools: CONN_MAX_AGE, the psycopg pool added in Django 5.1, and PgBouncer. At the end we'll do the max_connections math.

Why a connection is expensive

In PostgreSQL every connection is a separate OS process. When a client connects:

  1. A TCP handshake happens, plus a TLS handshake if TLS is on.
  2. Authentication runs: with scram-sha-256 that's several message round trips and some hashing.
  3. The postmaster fork()s a new backend process.
  4. The new process warms its catalog caches from scratch, so the first queries re-read table and index metadata.

All of this happens before your query runs. On top of that, every open connection uses memory even while idle, and max_connections is set at server start, so changing it takes a restart.

You can measure the cost on your own server. pgbench -C opens a new connection for every transaction:

# one-time: create test tables
pgbench -i -s 10 shop

# 1) connections are kept open for the whole run
pgbench -S -c 10 -j 2 -T 30 shop

# 2) -C: a NEW connection for every transaction
pgbench -S -c 10 -j 2 -T 30 -C shop

Compare the tps of the two runs. The gap is the cost of connecting. With the database on another host behind TLS, the gap gets bigger. The exact numbers depend on your network and config, so measure them yourself.

What Django does by default

The default is CONN_MAX_AGE = 0. Django opens a connection the first time the ORM touches the database during an HTTP request, and closes it when the request finishes (the request_finished signal).

So a page that takes 200 ms pays for connect and disconnect every time. On a small project you won't notice. Under load, a real share of time goes into handshakes instead of useful work, and PostgreSQL keeps forking new processes and tearing old ones down.

The second problem is that nobody controls the connection count. Every worker, every thread, every Celery process opens its own. Add pods and connections grow linearly.

CONN_MAX_AGE: persistent connections

The first and simplest fix is to not close the connection after the request.

# settings.py
import os

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "NAME": "shop",
        "USER": "shop",
        "PASSWORD": os.environ["DB_PASSWORD"],
        "HOST": "127.0.0.1",
        "PORT": "5432",
        "CONN_MAX_AGE": 60,          # keep the connection for up to 60 s
        "CONN_HEALTH_CHECKS": True,  # ping it before reusing (Django 4.1+)
    }
}

How it works:

  • The connection is bound to a thread. Each thread keeps one connection of its own.
  • At the start and end of every request Django calls close_old_connections(). If the connection is older than CONN_MAX_AGE or is in an error state, it's closed.
  • CONN_MAX_AGE = None means no limit: the connection lives as long as the worker.
  • With CONN_HEALTH_CHECKS = True, Django checks that the connection is alive before reusing it at the start of a request. That saves the first request after a database restart from failing with "server closed the connection".

The limit of CONN_MAX_AGE is that it isn't a pool. It only keeps the "one thread, one connection" model alive longer. A gunicorn worker with 8 threads holds 8 connections all the time, even while they do nothing. Your total still grows as workers × threads × pods.

Django 5.1+: the psycopg pool

Since Django 5.1 the PostgreSQL backend supports a real connection pool through psycopg 3. There's one pool per process. Threads check a connection out, use it, and give it back.

pip install "psycopg[binary,pool]"
# settings.py (Django 5.1+, psycopg 3)
DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "NAME": "shop",
        "USER": "shop",
        "PASSWORD": os.environ["DB_PASSWORD"],
        "HOST": "127.0.0.1",
        "PORT": "5432",
        # CONN_MAX_AGE stays 0: the pool replaces persistent connections
        "OPTIONS": {
            "pool": {
                "min_size": 2,   # opened up front, per process
                "max_size": 8,   # hard cap per process
                "timeout": 10,   # seconds to wait for a free connection
            },
        },
    }
}

Here's how it works:

  • "pool": True creates a pool with default settings. A dict is passed as keyword arguments to psycopg_pool.ConnectionPool.
  • The pool is created lazily, on the process's first database access.
  • At the end of a request Django "closes" the connection, which really returns it to the pool.
  • If all max_size connections are busy, a new request waits up to timeout seconds and then gets PoolTimeout. That's a useful error. It shows a connection shortage at the application level, long before PostgreSQL's "too many clients".
  • You can't combine the pool with CONN_MAX_AGE. They are two answers to the same problem, and Django rejects that configuration. Keep CONN_MAX_AGE at 0.

When does a pool pay off most? When one process has many threads (gunicorn gthread, ASGI) that use the database in turns rather than all at once. A sync gunicorn worker has a single thread, so a max_size above 1 only adds idle connections. There the main win is reuse, and you should keep min_size small.

PgBouncer: sharing connections across processes

Both Django's pool and CONN_MAX_AGE live inside one process. Sixteen pods with 4 processes each and max_size=8 can reach 512 connections in the worst case. Processes can't share connections with each other.

PgBouncer fixes that by sitting between the app and PostgreSQL. The app opens thousands of cheap connections to PgBouncer, and PgBouncer multiplexes them onto a small number of real server connections.

; /etc/pgbouncer/pgbouncer.ini
[databases]
shop = host=127.0.0.1 port=5432 dbname=shop

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = transaction
max_client_conn = 2000      ; client connections PgBouncer accepts
default_pool_size = 20      ; real server connections per db/user pair
max_prepared_statements = 100
# settings.py: Django talks to PgBouncer, not to PostgreSQL directly
DATABASES["default"].update({
    "PORT": "6432",
    "CONN_MAX_AGE": 60,                  # cheap: this is a PgBouncer connection
    "CONN_HEALTH_CHECKS": True,
    "DISABLE_SERVER_SIDE_CURSORS": True, # .iterator() must not use named cursors
})

PgBouncer has three modes:

Mode When a client gets a server connection Session state Efficiency
session For as long as the client stays connected Fully kept Low: barely different from Django's pool
transaction Only for the duration of one transaction Not kept High: the usual choice
statement For a single statement Not kept; multi-statement transactions break Not usable with Django

In transaction mode Django's connection goes to PgBouncer and is cheap, so CONN_MAX_AGE is fine to use here.

The limits of transaction mode

The core rule of transaction mode: between two transactions you may land on a different server connection. That makes any session-level state unreliable.

In transaction mode, only what lives inside a single transaction is reliable. Everything else (SET, advisory locks, temp tables, LISTEN) may stay behind on another connection by the time your next query runs.

Django runs in autocommit mode, so every query outside atomic() is its own transaction. The classic mistake:

from django.db import connection
from orders.models import Order

# WRONG behind PgBouncer in transaction mode
def heavy_report():
    with connection.cursor() as cur:
        cur.execute("SET statement_timeout = '2s'")
    # autocommit: the SET has already finished its own transaction,
    # the next query may go to a different server connection
    return list(Order.objects.filter(status="paid").values("id", "amount"))

The SET may stay on a different server connection and, worse, affect someone else's query that picks up that connection later. The right way:

from django.db import connection, transaction
from orders.models import Order

# RIGHT: SET LOCAL lives exactly as long as the transaction
def heavy_report():
    with transaction.atomic():
        with connection.cursor() as cur:
            cur.execute("SET LOCAL statement_timeout = '2s'")
        return list(Order.objects.filter(status="paid").values("id", "amount"))


# Same idea for advisory locks: transaction-level, not session-level
def charge_once(order_id: int) -> None:
    with transaction.atomic():
        with connection.cursor() as cur:
            cur.execute("SELECT pg_advisory_xact_lock(%s)", [order_id])
        ...  # the lock is released automatically on COMMIT/ROLLBACK

Other limits:

  • Server-side cursors. QuerySet.iterator() opens a named cursor in PostgreSQL. In autocommit Django uses a WITH HOLD cursor, which outlives the transaction and can end up on another connection. That's why you set DISABLE_SERVER_SIDE_CURSORS = True. Then iterator() no longer streams rows in batches through psycopg, but it still saves memory because model instances are built in chunks.
  • Prepared statements. With psycopg 3, Django uses client-side binding by default: parameters are merged into the query text on the client, and no server-side prepared statements are created. If you set "server_side_binding": True in OPTIONS, psycopg starts preparing statements on the server, and transaction mode starts causing trouble. Since 1.21, PgBouncer can track protocol-level prepared statements via max_prepared_statements, but SQL-level PREPARE/EXECUTE still doesn't work. The safest choice is to leave server-side binding off.
  • LISTEN/NOTIFY, temp tables and pg_advisory_lock (the session version) don't work reliably through transaction mode. Use a separate direct connection that bypasses PgBouncer for them.
  • Migrations. They can take long locks and use SET. Most setups run migrate over a direct connection to PostgreSQL on port 5432.

Pooling under ASGI and async

In Django's async views, ORM calls run in a thread via sync_to_async. The Django docs recommend disabling persistent connections (CONN_MAX_AGE) under ASGI and using a pool instead. In an async setup it's hard to control which thread a connection is left on, and connections can pile up without being closed.

# views.py: async view, ORM behind a pool
from django.http import JsonResponse
from orders.models import Order

async def my_orders(request):
    user = await request.auser()
    orders = [
        {"id": o.id, "amount": str(o.amount)}
        async for o in Order.objects.filter(user=user).order_by("-id")[:20]
    ]
    return JsonResponse({"orders": orders})

A practical ASGI setup:

  • CONN_MAX_AGE = 0, plus either OPTIONS["pool"] or an external PgBouncer.
  • Size max_size by how many database calls happen in parallel, not by how many requests are in flight on the event loop. One uvicorn process can hold hundreds of requests, but if they all hit the database at once the pool fills up and PoolTimeout starts firing. That's good: it's backpressure that protects the database.
  • Don't hold a connection for the whole lifetime of a long stream or websocket handler.

Doing the max_connections math

Now the real question: how many connections do you need?

SHOW max_connections;
SHOW superuser_reserved_connections;

-- who is holding connections right now
SELECT usename, application_name, state, count(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY usename, application_name, state
ORDER BY count(*) DESC;

Write down every consumer:

Consumer Formula
Web (CONN_MAX_AGE) pods × workers × threads
Web (Django pool) pods × workers × max_size
Celery workers × concurrency
Through PgBouncer default_pool_size × number of (db, user) pairs
Other migrate, cron, admin/psql, monitoring, replication

Add headroom and check that the total stays under max_connections - superuser_reserved_connections. PostgreSQL 16 also added reserved_connections, which reserves slots for the pg_use_reserved_connections role.

One important point: raising max_connections to 1000 isn't a fix. Every backend uses memory, and work_mem is allocated per operation. Also, running far more active queries than you have CPU cores won't make the database faster. A small pool with a queue often beats a big one. You can measure that too: raise -c in pgbench and watch tps and latency.

To tell consumers apart, set application_name:

DATABASES["default"]["OPTIONS"] = {
    "application_name": "shop-web",   # shows up in pg_stat_activity
    "pool": {"min_size": 2, "max_size": 8, "timeout": 10},
}

Production pitfalls

  • Celery. Celery's Django integration calls close_old_connections around tasks and respects CONN_MAX_AGE. But concurrency=16 still means 16 connections, so count them.
  • Connecting before fork. gunicorn with --preload loads the app in the master process. If something touches the database at import time, the connection or pool ends up shared by several workers after fork. Keep the ORM out of import time.
  • Open transactions. A connection stuck idle in transaction holds a pool slot and a PgBouncer server connection. Set idle_in_transaction_session_timeout.
  • Double pooling. Django's pool and PgBouncer can run together, but then limits live in two places. Monitor both so you know where the queue forms (SHOW POOLS; in the PgBouncer admin console).
  • Long queries. In transaction mode a 30-second report holds one server connection for 30 seconds. Route heavy reports to a separate pool or a replica.

Checklist

  • Measure connection cost in your own environment with pgbench -C.
  • Plain sync project: CONN_MAX_AGE = 60 + CONN_HEALTH_CHECKS = True.
  • Many threads or ASGI on Django 5.1+: OPTIONS["pool"], CONN_MAX_AGE = 0, a small min_size.
  • Many pods and processes: PgBouncer in transaction mode, DISABLE_SERVER_SIDE_CURSORS = True.
  • In transaction mode: SET LOCAL, pg_advisory_xact_lock, server-side binding off.
  • A direct connection for migrate and LISTEN/NOTIFY.
  • Put every consumer in a table and check the total against max_connections.
  • Set application_name and watch pg_stat_activity.

Takeaway

A PostgreSQL connection isn't a cheap handle. It's a whole process. CONN_MAX_AGE removes the handshakes, Django 5.1's psycopg pool shares connections within a process, and PgBouncer shares them across the whole fleet. Whichever you pick, count your consumers first: "too many clients" almost always comes from scaling without doing the arithmetic.

// comments

Loading comments...

    // next step

    Let's build something together.

    Message me on Telegram
    aslbek@asldev: ~
    ❯