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:
- A TCP handshake happens, plus a TLS handshake if TLS is on.
- Authentication runs: with
scram-sha-256that's several message round trips and some hashing. - The
postmasterfork()s a new backend process. - 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 thanCONN_MAX_AGEor is in an error state, it's closed. CONN_MAX_AGE = Nonemeans 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": Truecreates a pool with default settings. A dict is passed as keyword arguments topsycopg_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_sizeconnections are busy, a new request waits up totimeoutseconds and then getsPoolTimeout. 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. KeepCONN_MAX_AGEat 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 aWITH HOLDcursor, which outlives the transaction and can end up on another connection. That's why you setDISABLE_SERVER_SIDE_CURSORS = True. Theniterator()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": TrueinOPTIONS, psycopg starts preparing statements on the server, and transaction mode starts causing trouble. Since 1.21, PgBouncer can track protocol-level prepared statements viamax_prepared_statements, but SQL-levelPREPARE/EXECUTEstill doesn't work. The safest choice is to leave server-side binding off. LISTEN/NOTIFY, temp tables andpg_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 runmigrateover 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 eitherOPTIONS["pool"]or an external PgBouncer.- Size
max_sizeby 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 andPoolTimeoutstarts 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_connectionsaround tasks and respectsCONN_MAX_AGE. Butconcurrency=16still means 16 connections, so count them. - Connecting before fork. gunicorn with
--preloadloads 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 transactionholds a pool slot and a PgBouncer server connection. Setidle_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 smallmin_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
migrateandLISTEN/NOTIFY. - Put every consumer in a table and check the total against
max_connections. - Set
application_nameand watchpg_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...