Database: every request and every worker poll opens a new database connection #1132

New issue

Open

opened 2026-10-31 03:04:15 +00:00 by tiagoagueda · 0 comments

tiagoagueda commented

2026-10-31 03:04:15 +00:00

Owner

What exists today

config/settings/base.py:172-178 builds DATABASES["default"] from POSTULO_DATABASE_URL with django-environ and sets only ATOMIC_REQUESTS; CONN_MAX_AGE is left at Django's default of 0, so Django closes the connection at the end of every request (request_finished runs close_old_connections).

Web: docker/Dockerfile:388-393 runs 3 sync gunicorn workers; each request opens a connection, and on SQLite runs init_command (PRAGMA journal_mode=WAL; PRAGMA synchronous=NORMAL;, config/sqlite.py:53), registers Django's own SQL functions and Postulo's two like functions (core/sqlite_like.py:47-51, on connection_created).
Worker: core/management/commands/work.py:57-86 builds a django_tasks_db Worker every --every seconds (default 2.0). Upstream db_worker.py:108 and :155 call close_old_connections() at the top and bottom of each pass, which with CONN_MAX_AGE = 0 closes the connection, so the next pass reconnects: one connection every 2 s, about 43,200 a day, with nothing queued.
Scheduler: send_due_reminders --loop --every 300 (docker/compose.yml:63) keeps one connection for the process lifetime, since a management command fires no request signals; unaffected.

Measurement (work/r4_conn.py in the audit scratchpad, Django 6.1.2's SQLite backend with Postulo's options and sqlite_like installed, a file database on the local disk): opening, setting up and closing one connection took 2,746 microseconds on average over 300 cycles; one SELECT 1 on an open connection took 5.4 microseconds. Linux is likely faster at opening the file, but the setup is the same. PostgreSQL was not measured (no local server); a new PostgreSQL connection costs a TCP connect, SCRAM authentication and a new backend process each time.

django-environ already accepts ?conn_max_age=60&conn_health_checks=true on POSTULO_DATABASE_URL (environ/environ.py:160-166), but nothing documents it.

What would change

In config/settings/base.py, beside ATOMIC_REQUESTS:

DATABASES["default"].setdefault("CONN_MAX_AGE", env.int("POSTULO_DATABASE_CONN_MAX_AGE", default=60))
DATABASES["default"].setdefault("CONN_HEALTH_CHECKS", True)

setdefault keeps whatever an operator put in the URL. Health checks make a connection dropped by a PostgreSQL restart cost one retry instead of a 500. With 3 web workers, one worker and one scheduler, a PostgreSQL instance holds at most 5 idle connections, far below the default max_connections of 100. The test settings replace DATABASES wholesale (config/settings/test.py:26-28) and are not affected. Document the variable and the URL options on the wiki page that lists the environment.

No library needed; this is Django's own setting.

What it saves or protects

medium: about 2.7 ms of connection setup saved per request and per worker pass on SQLite (measured on Windows), and a TCP plus SCRAM handshake and a PostgreSQL backend fork saved on PostgreSQL; the worker alone opens about 43,000 connections a day

Accessibility

none

Decided by the maintainer (2026-10-31)

CONN_MAX_AGE 60 with health checks, its own environment variable, setdefault so an operator's value wins

Decisions

Keep connections by default? Recommended: yes, 60 seconds with health checks. None (for ever) is also safe with health checks, but 60 bounds how long a connection outlives a configuration change.
A variable of its own or the URL only? Recommended: a variable, since the URL option is undiscoverable and a password beside the URL is already handled separately (database_password.apply_password).
Ties to M5. The PLAN puts M5 (gunicorn --preload) beside this as one question to the user; they are separate changes and can be decided separately.

Found by the efficiency and reliability audit of 2026-10-30 (finding R4; index postulo/postulo#1108). Checked against main at daee30b15 (2026-10-30).

## What exists today `config/settings/base.py:172-178` builds `DATABASES["default"]` from `POSTULO_DATABASE_URL` with django-environ and sets only `ATOMIC_REQUESTS`; `CONN_MAX_AGE` is left at Django's default of 0, so Django closes the connection at the end of every request (`request_finished` runs `close_old_connections`). - Web: `docker/Dockerfile:388-393` runs 3 sync gunicorn workers; each request opens a connection, and on SQLite runs `init_command` (`PRAGMA journal_mode=WAL; PRAGMA synchronous=NORMAL;`, `config/sqlite.py:53`), registers Django's own SQL functions and Postulo's two `like` functions (`core/sqlite_like.py:47-51`, on `connection_created`). - Worker: `core/management/commands/work.py:57-86` builds a `django_tasks_db` `Worker` every `--every` seconds (default 2.0). Upstream `db_worker.py:108` and `:155` call `close_old_connections()` at the top and bottom of each pass, which with `CONN_MAX_AGE = 0` closes the connection, so the next pass reconnects: one connection every 2 s, about 43,200 a day, with nothing queued. - Scheduler: `send_due_reminders --loop --every 300` (`docker/compose.yml:63`) keeps one connection for the process lifetime, since a management command fires no request signals; unaffected. Measurement (`work/r4_conn.py` in the audit scratchpad, Django 6.1.2's SQLite backend with Postulo's options and `sqlite_like` installed, a file database on the local disk): opening, setting up and closing one connection took 2,746 microseconds on average over 300 cycles; one `SELECT 1` on an open connection took 5.4 microseconds. Linux is likely faster at opening the file, but the setup is the same. PostgreSQL was not measured (no local server); a new PostgreSQL connection costs a TCP connect, SCRAM authentication and a new backend process each time. django-environ already accepts `?conn_max_age=60&conn_health_checks=true` on `POSTULO_DATABASE_URL` (`environ/environ.py:160-166`), but nothing documents it. ## What would change In `config/settings/base.py`, beside `ATOMIC_REQUESTS`: ```python DATABASES["default"].setdefault("CONN_MAX_AGE", env.int("POSTULO_DATABASE_CONN_MAX_AGE", default=60)) DATABASES["default"].setdefault("CONN_HEALTH_CHECKS", True) ``` `setdefault` keeps whatever an operator put in the URL. Health checks make a connection dropped by a PostgreSQL restart cost one retry instead of a 500. With 3 web workers, one worker and one scheduler, a PostgreSQL instance holds at most 5 idle connections, far below the default `max_connections` of 100. The test settings replace `DATABASES` wholesale (`config/settings/test.py:26-28`) and are not affected. Document the variable and the URL options on the wiki page that lists the environment. No library needed; this is Django's own setting. ## What it saves or protects medium: about 2.7 ms of connection setup saved per request and per worker pass on SQLite (measured on Windows), and a TCP plus SCRAM handshake and a PostgreSQL backend fork saved on PostgreSQL; the worker alone opens about 43,000 connections a day ## Accessibility none ## Decided by the maintainer (2026-10-31) CONN_MAX_AGE 60 with health checks, its own environment variable, setdefault so an operator's value wins ## Decisions - **Keep connections by default?** Recommended: yes, 60 seconds with health checks. `None` (for ever) is also safe with health checks, but 60 bounds how long a connection outlives a configuration change. - **A variable of its own or the URL only?** Recommended: a variable, since the URL option is undiscoverable and a password beside the URL is already handled separately (`database_password.apply_password`). - **Ties to M5.** The PLAN puts M5 (gunicorn `--preload`) beside this as one question to the user; they are separate changes and can be decided separately. --- Found by the efficiency and reliability audit of 2026-10-30 (finding R4; index postulo/postulo#1108). Checked against `main` at `daee30b15` (2026-10-30).

tiagoagueda added this to the 1.0.0 milestone

2026-10-31 03:04:15 +00:00

tiagoagueda added the

performance

enhancement

labels

2026-10-31 03:04:15 +00:00

tiagoagueda referenced this issue

2026-10-31 03:04:41 +00:00

Efficiency and reliability audit of 2026-10-30: index #1108