Moving a Django prototype to PostgreSQL, when would you use OPTIONS={'pool': ...} instead of CONN_MAX_AGE, and what constraints come with it?

## Two ways to reuse connections When a prototype moves from SQLite to PostgreSQL, connection setup cost appears for the first time: every request that opens a new PostgreSQL connection pays for a network handshake and authentication. Django offers two built-in ways to avoid that. | | `CONN_MAX_AGE` (persistent) | `OPTIONS["pool"]` (pooled) | |---|---|---| | Unit of reuse | One connection per thread | A pool per process and alias | | Idle threads | Each holds a connection | Hold nothing | | ASGI | Docs say disable it | Suitable | | Backends | All | PostgreSQL (psycopg 3) and Oracle | | Since | Long-standing | Django 5.1 for PostgreSQL | ## How the PostgreSQL pool works ```python DATABASES = { "default": { "ENGINE": "django.db.backends.postgresql", "NAME": "timetable", "USER": "timetable_app", "HOST": "db.internal", "CONN_MAX_AGE": 0, "CONN_HEALTH_CHECKS": True, "OPTIONS": { "pool": {"min_size": 2, "max_size": 8, "timeout": 10}, }, } } ``` - `"pool": True` uses `psycopg_pool.ConnectionPool` defaults; a dict is passed through as its arguments. - The pool is created lazily, **once per process and alias**, and shared by that process's threads. - When Django "opens" a connection it checks one out; when it "closes" one at the end of a request, it returns it to the pool rather than disconnecting. - Django configures each new pooled connection (time zone, role) when the pool creates it. - `CONN_HEALTH_CHECKS = True` sets the pool's `check` so a connection is verified when it is checked out. ## Hard constraints 1. **psycopg 3 and the pool package.** Without `psycopg_pool` installed, Django raises `ImproperlyConfigured` ("Did you install psycopg[pool]?"). With psycopg2, it raises `ImproperlyConfigured` because pooling requires psycopg 3. 2. **`CONN_MAX_AGE` must be 0.** Any other value raises `ImproperlyConfigured: Pooling doesn't support persistent connections.` The two mechanisms are alternatives, not layers. 3. **Per-process pools add up.** Each server process has its own pool. Four processes with `max_size` 8 can hold 32 connections, before counting migrations, cron jobs, the admin shell and replicas. That total must stay under PostgreSQL's connection limit with headroom. 4. **Waiting instead of failing.** When all connections are in use, a request waits for one up to the pool's `timeout`, then errors. A pool that is too small turns load into latency. ## The external pooler alternative Many PostgreSQL deployments put a separate pooler between application and database. Django's own pool and an external one solve different scopes: the built-in pool shares connections inside one process; an external pooler shares them across every process and host. With an external pooler in **transaction pooling** mode: - Set `DISABLE_SERVER_SIDE_CURSORS = True` for that alias, because server-side cursors used by `QuerySet.iterator()` do not survive the pooler moving a session between transactions. - Django already disables psycopg 3 prepared statements by default to keep such poolers working. ## Failure modes to recognise - **Requests slow down under load, then error.** Threads are queuing for a pooled connection; the pool is too small for the concurrency, or slow queries hold connections too long. Look at query time before raising `max_size`. - **The database refuses new connections.** The sum of all pools and other clients exceeded the server's limit; reduce `max_size` per process or the number of processes. - **Errors after a database restart.** Without `CONN_HEALTH_CHECKS`, a stale pooled connection can be handed out once; enabling it makes the pool verify connections at checkout. ## Choosing for the migrated prototype - **WSGI, a few threaded processes, modest traffic**: `CONN_MAX_AGE` of a minute with health checks is simple and fine. - **ASGI, or many threads that mostly wait**: the built-in pool, sized from the connection budget. - **Many processes or hosts sharing one server**: an external pooler, with the Django settings above. Whatever the choice, measure: count connections on the server under load and compare with the limit before production traffic arrives.