beginnerintermediateadvanced

Django · Section 75

Database Connection Management

Level: advanced
Read: 34 min
Concepts: 3

This section is built around one question the roadmap asks directly: if you increase web workers from 4 to 40, what happens to database connections? With persistent connections the answer is that they go from 4 to 40, per host — and that is the whole subject in miniature, because connections scale with *processes* rather than with traffic. By default Django opens a connection when a request needs one and closes it at the end of the response, which is wasteful but self-limiting: the count tracks requests actually in flight. Setting `CONN_MAX_AGE` above zero removes the per-request setup cost and silently changes the denominator to worker processes, which is larger, constant, and paid whether or not anyone is using the site. That is why turning it on can produce connection exhaustion with no change in traffic, and why the arithmetic — hosts × processes × concurrency, plus every Celery worker, scheduled job, migration and shell — belongs written down in the settings file. Persistence brings a second obligation: a parked connection is a live socket the other end can close, through an idle timeout, a restart or a failover, so `CONN_HEALTH_CHECKS = True` is not optional alongside it. The ceiling itself behaves unlike most limits. PostgreSQL refuses connections past `max_connections` rather than slowing down, so the failure arrives as an error in whatever asked last — often a deploy or an incident-response shell, which is why headroom is part of the budget rather than slack in it. Anything that holds a connection longer shrinks that budget in practice, which makes `statement_timeout`, `idle_in_transaction_session_timeout` and `lock_timeout` part of connection management rather than of query tuning. Pooling then splits into two things that share a name. Django 5.1 added a per-process pool for psycopg 3, which amortises setup within one process and cannot reduce a fleet-wide total — its `min_size` can raise it. An external pooler such as PgBouncer is shared, and transaction pooling is the mode that genuinely decouples worker count from connection count, in exchange for a rule: nothing may live on a connection across transactions, so server-side cursors have to be disabled. Underneath all of it sits the same discipline — transactions hold connections, so they should contain database work and nothing else.

Django overview

Python Django Full Course for Beginners | Complete All-in-One Tutorial | 3 HoursDave Gray

What is true here

CONN_MAX_AGE trades per-request setup for one held connection per worker process.
Pair any persistence with CONN_HEALTH_CHECKS = True.
Connections are a product across hosts, processes and background tiers — write the budget down.
The ceiling refuses rather than degrades, so leave headroom for deploys and shells.
Only a shared pooler reduces a fleet-wide count; Django's built-in pool is per process.

What you will be able to do

Answer the 4-to-40 question with the arithmetic, not a guess
Enable persistent connections without walking into exhaustion
Bound how long any single query or transaction can hold a connection
Choose between an in-process pool and PgBouncer, and configure Django for transaction pooling

From a request to a database backend — every place a connection can be held

Request

Worker process

one per gunicorn/uvicorn worker, per host

CONN_MAX_AGE

0 → connect and close per request · >0 → keep it parked

Per-process pool

"pool": True — psycopg 3, this process only

PgBouncer (shared)

transaction mode: the connection returns after every transaction

PostgreSQL

max_connections — a hard, refusing ceiling

Celery workers

the tier nobody counts, spending the same budget

Held too long

a long transaction, an HTTP call inside atomic(), no statement_timeout

FATAL: too many clients already

the deploy or the incident shell is what fails

The lifecycle, and what persistence changes

Connect-per-request against connect-per-process, and the health check that has to come with the second one.

The connection lifecycle, and `CONN_MAX_AGE`

coreadvanced

By default Django opens a database connection when a request first needs one and closes it when the response is finished. That is `CONN_MAX_AGE = 0`, and it means every request pays to establish a connection — a TCP handshake, authentication, and on PostgreSQL a new backend process. Setting `CONN_MAX_AGE` to a positive number of seconds keeps the connection open for reuse across requests; `None` keeps it forever. The catch is that a reused connection can have been closed at the other end while it sat idle, which is what `CONN_HEALTH_CHECKS = True` exists to handle.

Think of it as

A connection is not a lightweight handle — on PostgreSQL it is a server-side process with its own memory, so opening one is expensive and holding one has an ongoing cost. Those two facts pull in opposite directions, and `CONN_MAX_AGE` is where you choose between them. At `0` you pay setup on every request and hold nothing between requests, which is wasteful under load but very well behaved: the number of connections tracks the number of *in-flight* requests. At a positive value you pay setup rarely, but the number of connections now tracks the number of *worker processes*, whether or not they are doing anything, because each one keeps its connection parked. That change of denominator is the thing to internalise — it is why turning on persistent connections can push a system straight into connection exhaustion without any change in traffic. The second thing is that a persistent connection is a stateful object that outlives your control of it. The database may close it for being idle, a failover may replace the server underneath it, and a network device may drop the session silently. Django cannot know without asking, which is what the health check does — once per request, only when the database is actually used. Without it, the first query after such an event raises an error that looks random and clusters right after a database restart. Finally, `CONN_MAX_AGE` is per process, and the age is only checked between requests: a connection is never closed mid-request for being too old.

python

DATABASES["default"]["CONN_MAX_AGE"] = 60        # seconds; 0 = per request, None = forever
DATABASES["default"]["CONN_HEALTH_CHECKS"] = True

What we're doing: Turn on persistent connections without walking into exhaustion — by doing the arithmetic first.

shop/settings/production.pypython

# Before changing CONN_MAX_AGE, count what will be held:
#
#   web:    3 hosts x 8 gunicorn workers        = 24
#   celery: 2 hosts x 12 worker processes       = 24
#   beat / cron / admin shells                  ~  4
#                                                 --
#                                                  52   <= must fit under max_connections
#
# PostgreSQL max_connections = 100, and ~3 are reserved for superusers.

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "NAME": env("DB_NAME"),
        "USER": env("DB_USER"),
        "PASSWORD": env("DB_PASSWORD"),
        "HOST": env("DB_HOST"),

        # Persistent connections: setup cost disappears, but the count below
        # is now driven by PROCESS count rather than by concurrent requests.
        "CONN_MAX_AGE": 60,

        # Required companion. A parked connection can be closed at the other
        # end by an idle timeout, a restart or a failover; without this the
        # next query on it raises instead of reconnecting.
        "CONN_HEALTH_CHECKS": True,

        "OPTIONS": {
            # A statement that runs longer than this is killed by the database,
            # so one pathological query cannot hold a connection indefinitely.
            "options": "-c statement_timeout=15000",
        },
    }
}

1–9: The arithmetic belongs in the settings file, not in someone's head. Every process that imports Django holds its own connection, and Celery workers are usually the group people forget to count.
21: Sixty seconds is long enough to cover the gaps between a busy worker's requests and short enough that a rolling restart clears everything within a minute.
27: Never enable persistence without this. The failure it prevents appears as a burst of errors right after a database restart, which is exactly when you can least afford extra noise.
30–32: `statement_timeout` is the backstop for the whole scheme: it caps how long any single statement can occupy a connection, so a runaway query cannot quietly consume one of your 52.

Why this works: Persistent connections are a trade of setup cost against held connections, and the trade is only safe once the held number is known — which is why the count comes before the setting.

Setting `CONN_MAX_AGE` without `CONN_HEALTH_CHECKS`

Wrong

python

DATABASES["default"]["CONN_MAX_AGE"] = None       # keep connections forever
# no health check

Better

python

DATABASES["default"]["CONN_MAX_AGE"] = 60
DATABASES["default"]["CONN_HEALTH_CHECKS"] = True

What you see: Every database maintenance window, failover or idle-timeout produces a burst of `OperationalError: server closed the connection unexpectedly` — from workers that were doing nothing at the time, which makes the errors look unrelated to the event.

Why: A parked connection is a live socket that the other end can close without telling you: an idle timeout on the server, a failover to a new primary, or a firewall dropping an idle session. Django hands the connection back out and the first query on it fails. The health check exists exactly for this — the documentation describes it as improving "the robustness of connection reuse and prevent[ing] errors when a connection has been closed by the database server" — and it runs once per request, only when the database is used, so the cost is negligible. Preferring a bounded `CONN_MAX_AGE` over `None` also helps: connections recycle on their own, so a bad one cannot live forever.

One worker process, three requests — with and without persistence

req 1 · 0 ms
CONN_MAX_AGE = 0: connect
TCP handshake, auth, a new PostgreSQL backend process
req 1 · end
close
the connection is discarded — nothing is kept
req 2
connect again, close again
the same setup cost, paid per request forever
req 1 · 0 ms
CONN_MAX_AGE = 60: connect once
the same setup, but only this time
req 2 · req 3
reuse — no setup at all
and the connection stays parked between requests, even while idle
idle > 60 s
closed at the *start* of the next request
age is checked between requests; a long request is never cut off mid-flight
after a DB restart
the parked connection is dead
CONN_HEALTH_CHECKS notices and reconnects; without it, the next query errors

What each setting actually changes

What each setting actually changes
Setting	Connections held	Cost per request	Watch out for
`CONN_MAX_AGE = 0` (default)	one per in-flight request	a full connection setup	setup cost dominates short requests
`CONN_MAX_AGE = 60`	one per worker process	nearly none	idle workers still hold connections
`CONN_MAX_AGE = None`	one per worker, forever	nearly none	stale connections after a restart or failover
`CONN_HEALTH_CHECKS = True`	unchanged	one cheap check	nothing — pair it with any persistence

Together

python

DATABASES = {"default": {
    "ENGINE": "django.db.backends.postgresql",
    "CONN_MAX_AGE": 60,
    "CONN_HEALTH_CHECKS": True,     # always pair these two
}}

Remember: `CONN_MAX_AGE = 0` is the default and means a connection per request — expensive but self-limiting, because the count tracks in-flight requests. A positive value changes the denominator to worker *processes*, which is a larger and constant number, so do the multiplication across web, Celery, beat and ad-hoc processes before enabling it. Always pair persistence with `CONN_HEALTH_CHECKS = True`, since a parked connection can be closed at the other end by an idle timeout or a failover, and prefer a bounded age over `None` so connections recycle on their own.

The budget, and the ceiling

The 4-to-40 question answered with arithmetic, and the three timeouts that bound how long a slot is held.

Worker multiplication and connection exhaustion

coreadvanced

The section's own question is the whole lesson: if you increase web workers from 4 to 40, what happens to database connections? With persistent connections, they go from 4 to 40 — per host. Connections are a product, not a sum: hosts × processes × (threads, if each can query concurrently), plus every Celery worker, every scheduled job, and every shell someone left open. PostgreSQL enforces a hard `max_connections`, and past it new connections are refused outright with `FATAL: sorry, too many clients already` — so the failure is an error, not a slowdown.

Think of it as

Treat connections as a fixed budget you are spending, and write the multiplication down. The trap is that the two levers people reach for under load — more workers, and persistent connections — both increase spending, and neither is visible from the application side until the database refuses. It gets worse in a specific way: scaling out is the natural response to slowness, and if the slowness was database-bound then the extra workers add queueing on the resource that was already the constraint *and* consume connections faster. That is the two-sided failure to keep in mind, because the instinct is exactly wrong in that case. The second idea is that a connection is held for as long as something occupies it, so anything that makes a query or a transaction long makes the budget smaller in practice. A missing `statement_timeout` means one pathological query can hold a connection indefinitely; a transaction left open across a slow external call holds one for the length of that call. That is why the timeouts belong in the same discussion as the arithmetic: they set an upper bound on how long any one unit of the budget can be tied up. Finally, the connection budget is shared and nobody owns it. A migration, a data-fix shell, an analytics tool and an autoscaler all draw from the same pool, so the safe target leaves headroom rather than fitting exactly — the connection you cannot get is usually the one your deploy needed.

text

connections = hosts x processes x concurrent_queries_per_process
              + celery_hosts x celery_concurrency
              + headroom for migrations, shells and autoscaling

What we're doing: Make the budget explicit and enforce the two timeouts that stop one query eating a slot.

shop/settings/production.pypython

# ---- the budget, written down ------------------------------------------
#   max_connections           100
#   superuser reserved         -3
#   headroom (deploys, shells) -12
#   ------------------------------
#   usable                     85
#
#   web:    WEB_HOSTS x GUNICORN_WORKERS
#   celery: CELERY_HOSTS x CELERY_CONCURRENCY
#   assert web + celery <= 85

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "CONN_MAX_AGE": 60,
        "CONN_HEALTH_CHECKS": True,
        "OPTIONS": {
            "options": " ".join([
                # No single statement may hold a connection longer than this.
                "-c statement_timeout=15000",

                # Worse than a slow query: a transaction left open holds locks
                # as well as the connection. Kill it faster.
                "-c idle_in_transaction_session_timeout=30000",

                # Fail fast rather than queueing behind a lock for minutes.
                "-c lock_timeout=5000",
            ]),
        },
    }
}

# Celery is a separate settings module and a separate slice of the same budget.
# CELERY_WORKER_CONCURRENCY is a database connection count, not only a CPU knob.

1–10: Headroom is deliberate, not slack. The connection that cannot be obtained is usually the one a migration or an incident-response shell needed, which is the worst possible time to be at the ceiling.
19–20: `statement_timeout` is the cap on how long a single unit of the budget can be tied up. Without it, one query with a bad plan can hold a connection for as long as it takes to notice.
23–24: Idle-in-transaction is the more damaging state: the connection is held *and* the locks are held, so other requests queue behind it. Thirty seconds is generous for a web application.
27–28: `lock_timeout` turns "wait indefinitely for a lock" into a fast, retryable error. Waiting on a lock consumes a connection just as effectively as running a query.
33–34: The line that prevents the drawing above. Celery concurrency is usually tuned as a throughput setting, and it spends from the same fixed budget as the web tier.

Why this works: The arithmetic states what is being spent and the timeouts bound how long each unit can be held — the two halves of not running out, one of which is a comment and the other of which is enforced by the database.

Scaling out to fix a database-bound endpoint

Wrong

bash

# p95 is bad, workers look idle -> scale
gunicorn --workers 40 shop.wsgi          # was 8
# latency gets worse, then: FATAL: sorry, too many clients already

Better

python

# workers look idle because they are BLOCKED, not free.
# remove the waiting first:
Order.objects.select_related("customer__region")     # 431 queries -> 1
# then re-measure; the original 8 workers are usually enough

What you see: Latency rises after the scale-up and the database starts refusing connections — so a change made to add capacity removed it, and the dashboards blame the database.

Why: Idle-looking workers blocked on a socket are the signature of a database bottleneck, and adding more of them does two harmful things at once: it puts more concurrent queries on the resource that was already the constraint, and it multiplies the connection count. Both failures point away from the real cause, since the visible symptoms are "the database is slow" and "the database is refusing connections". Fix the waiting first — usually a query count, not a query — and only add workers once the database is no longer the constraint.

The senior question, drawn: workers 8 → 40, against a hard ceiling

Connections are a product of processes, not of traffic — so a five-fold worker increase is a five-fold connection increase, whether or not anyone is using the site.

Every term in the product, and what raises it

Every term in the product, and what raises it
Term	Typical source	Raised by
hosts	web tier replicas	autoscaling — often automatically
processes per host	gunicorn/uvicorn workers	"we scaled up to fix latency"
queries in flight per process	threads, or async concurrency	switching to a threaded or async worker class
background tier	Celery workers × concurrency	adding a queue or raising `--concurrency`
ad-hoc	shells, migrations, analytics, admin tools	a person, at the worst moment

Together

text

3 hosts x 8 workers = 24   +   2 celery hosts x 12 = 24   +   ~4 ad-hoc   =  52
   scale workers 8 -> 40:   3 x 40 = 120   +   24   +   4          = 148   >  100

Remember: Connections are a product — hosts × processes × concurrent queries — plus every Celery worker, scheduled job and shell, and the answer to "workers 4 to 40" is "connections 4 to 40, per host". Write the budget down, leave headroom for deploys and incident shells, and remember that the tier nobody edited still spends from it. Then bound how long a slot can be held: `statement_timeout` for runaway queries, `idle_in_transaction_session_timeout` for transactions left open, and `lock_timeout` so waiting for a lock fails fast instead of consuming a connection indefinitely.

Pooling, and what defeats it

Two different things called a pool, the rule transaction pooling imposes, and the transaction shape that ruins either.

Pooling, PgBouncer, and long-running transactions

coreadvanced

A pool lets many application processes share a smaller number of real database connections. Django 5.1 added a built-in one for PostgreSQL — `"pool": True` in `OPTIONS`, which needs psycopg 3 and is ignored with psycopg2 — and that pool is per process. A separate pooler such as PgBouncer sits outside your application and is shared by every process, which is what actually solves the multiplication problem. Its most useful mode, transaction pooling, hands a connection back after each transaction, and that changes the rules: session state does not persist, and server-side cursors must be turned off.

Think of it as

The two kinds of pool solve different halves of the problem, and confusing them is the usual mistake. An in-process pool amortises connection *setup* for one process — useful, and it does nothing about the total across a fleet, because each process still has its own pool. An external pooler is shared, so it is the one that decouples "how many processes we run" from "how many connections the database sees". If the multiplication in the previous concept is your problem, only the external one helps. Then comes the mode, which is where the real trade-off lives. Session pooling gives each client a connection for its whole session and behaves exactly like a direct connection, which limits how much it can help. Transaction pooling returns the connection after every transaction, which is what allows a small number of real connections to serve a large number of clients — and in exchange, anything that lives on a connection beyond one transaction stops working. Server-side cursors are the concrete example Django documents, because `iterator()` uses them on PostgreSQL and a cursor is only valid on the connection that created it. The last piece connects back to everything else: a pooler makes long transactions dramatically more expensive. In transaction pooling the real connection is held for the whole transaction, so a transaction spanning a slow HTTP call is not just holding locks, it is holding one of the few real connections everybody shares. The general rule that keeps all of this healthy is the same one: transactions should contain database work and nothing else.

python

# behind PgBouncer in transaction pooling mode:
"DISABLE_SERVER_SIDE_CURSORS": True,
"CONN_MAX_AGE": 0,          # the pooler owns connection reuse now

What we're doing: Configure both pool styles correctly, and fix the transaction that makes a pooler worse than useless.

shop/settings/production.pypython

# ---- A. In-process pool (Django 5.1+, psycopg 3) -----------------------
# Amortises setup for THIS process. Does nothing for the fleet-wide total.
DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "OPTIONS": {"pool": {"min_size": 2, "max_size": 8}},
        # Do not also set CONN_MAX_AGE: the pool owns connection lifetime.
    }
}


# ---- B. Behind PgBouncer in transaction pooling mode -------------------
# The shared layer: 120 processes can share ~20 real connections.
DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "HOST": "pgbouncer.internal",
        "PORT": 6432,
        "CONN_MAX_AGE": 0,                    # PgBouncer handles reuse
        "DISABLE_SERVER_SIDE_CURSORS": True,  # REQUIRED in transaction mode
    }
}


# ---- C. The transaction that ruins either one --------------------------
def settle(order):
    with transaction.atomic():
        locked = Order.objects.select_for_update().get(pk=order.pk)
        charge = payment_client.charge(locked.total)     # 800ms, sometimes 15s
        locked.charge_id = charge["id"]
        locked.save()
# Holds a SHARED real connection (and its locks) for the whole HTTP call.


def settle(order):                                       # fixed
    charge = payment_client.charge(order.total)          # slow work, no transaction

    with transaction.atomic():                           # milliseconds
        locked = Order.objects.select_for_update().get(pk=order.pk)
        locked.charge_id = charge["id"]
        locked.save()

6–7: A dict is passed straight to psycopg's `ConnectionPool`. `True` uses its defaults. With psycopg2 the option is ignored entirely — silently, so check the driver before assuming it took effect.
19–20: The two settings that must change together behind a transaction-mode pooler. `CONN_MAX_AGE` is redundant because the pooler owns reuse, and server-side cursors cannot work when the next transaction may land on a different real connection.
27–30: The anti-pattern, and it gets strictly worse behind a pooler: the transaction now holds one of a small number of *shared* real connections for the duration of a third-party call.
34–39: The fix is ordering, not tuning: do the slow work first, then open a short transaction for the write. Connection hold time drops from the length of an HTTP call to the length of an `UPDATE`.

Why this works: The two configurations are for different problems — one amortises setup, one decouples worker count from connection count — and the third block is what determines whether either of them helps, because a pooler cannot recycle a connection that a transaction refuses to release.

Using `.iterator()` behind transaction pooling without disabling server-side cursors

Wrong

python

# HOST = pgbouncer (transaction mode), server-side cursors left on
for order in Order.objects.all().iterator(chunk_size=2000):
    ...
# psycopg errors: cursor "_django_curs_..." does not exist

Better

python

DATABASES["default"]["DISABLE_SERVER_SIDE_CURSORS"] = True
# .iterator() still works — it fetches client-side instead

What you see: Exports and long-running jobs fail with a missing-cursor error that does not reproduce against the database directly, only through the pooler — which makes it look like an intermittent network problem.

Why: Django uses server-side cursors for `iterator()` on PostgreSQL, and a server-side cursor lives on one connection. In transaction pooling the connection is returned to the pool at the end of each transaction, so the next fetch can arrive on a different real connection where that cursor does not exist. Django documents the fix directly: set `DISABLE_SERVER_SIDE_CURSORS` to `True` for that connection. `iterator()` keeps working — the chunking becomes client-side — so the memory benefit remains, and only the mechanism changes.

Where each layer sits, and which one your worker count reaches

120 application processes

web workers + Celery workers, each importing Django

Per-process pool — `"pool": True` or `CONN_MAX_AGE`

amortises setup inside one process; 120 processes still mean 120 pools

PgBouncer — shared, transaction pooling

the only layer that makes 120 processes look like 20 connections

PostgreSQL — max_connections = 100

a hard ceiling that refuses rather than degrades

The constraint transaction pooling adds

no session state across transactions → DISABLE_SERVER_SIDE_CURSORS, so `.iterator()` streams client-side

Three ways to manage connections, and what each one actually fixes

Three ways to manage connections, and what each one actually fixes
Approach	Scope	Fixes	Costs
`CONN_MAX_AGE`	per process	setup cost per request	one held connection per process
`"pool": True` (Django 5.1+)	per process	setup cost, with a managed lifetime	still per process; psycopg 3 only
PgBouncer, session mode	shared	setup cost across the fleet	little reduction in connection count
PgBouncer, transaction mode	shared	the multiplication problem	no session state, no server-side cursors

Together

python

DATABASES = {"default": {
    "ENGINE": "django.db.backends.postgresql",
    "OPTIONS": {"pool": {"min_size": 2, "max_size": 8}},   # psycopg 3, per process
}}

Remember: Two different things are called pooling. Django 5.1's `"pool"` option is per process, needs psycopg 3, and amortises setup — it cannot reduce a fleet-wide total, and `min_size` can raise it. An external pooler such as PgBouncer is shared, and transaction pooling is the mode that decouples worker count from connection count; the price is that nothing may live on a connection across transactions, so `DISABLE_SERVER_SIDE_CURSORS` becomes mandatory and `CONN_MAX_AGE` becomes redundant. Either way, keep transactions short and free of network calls — a pooler cannot recycle a connection a transaction will not release.