Database URLs and Postgres Connection Counts in Python

Postgres does not melt because of one slow query as often as it melts because of too many clients. Each Gunicorn worker, Uvicorn worker, and Celery child opens a pool. Pools multiply. max_connections is finite. On a single Ubuntu VPS the database and the app share RAM, so a generous pool is a self-denial-of-service.

The 12-factor way to point at Postgres is one URL: DATABASE_URL=postgres://user:pass@127.0.0.1:5432/app. Parse it once. Size the pool against process count. Leave headroom for admin, migrations, and cron.

Parse the URL, Do Not Assemble Fragments

Scattered DB_HOST, DB_USER, DB_PASSWORD settings drift. A URL is one secret, one env var, one thing to rotate.

import os
from urllib.parse import urlparse, unquote

def database_from_url(url):
    u = urlparse(url)
    return {
        "ENGINE": "django.db.backends.postgresql",
        "NAME": u.path.lstrip("/"),
        "USER": unquote(u.username or ""),
        "PASSWORD": unquote(u.password or ""),
        "HOST": u.hostname,
        "PORT": str(u.port or 5432),
        "CONN_MAX_AGE": 60,
        "OPTIONS": {"connect_timeout": 5},
    }

DATABASES = {"default": database_from_url(os.environ["DATABASE_URL"])}

dj-database-url and pydantic PostgresDsn do the same job. Use them if you already depend on them. Unquote the password; a URL-encoded character in the secret is a real-world footgun. CONN_MAX_AGE=60 (Django) keeps a connection for a minute of idle, which cuts handshake cost without holding a session overnight.

SQLAlchemy should look similar: create_engine(os.environ["DATABASE_URL"], pool_size=5, max_overflow=2, pool_timeout=30, pool_pre_ping=True). pool_pre_ping runs a cheap SELECT 1 before reuse so a recycled connection after idle_session_timeout does not explode the next request.

The Multiplication You Must Write Down

Write this on the box, not in your head:

• web_workers * (pool_size + max_overflow)
• celery_concurrency * (pool_size + max_overflow)
• plus one for migrate, plus one for psql, plus PgBouncer if you add it

Three Gunicorn sync workers, Django default CONN_MAX_AGE with no cap, can each hold a connection. That is 3, which is fine. SQLAlchemy with pool_size=10, max_overflow=10, four Uvicorn workers is 80. Celery concurrency=8 with the same engine is another 80. Postgres default max_connections=100. The next deploy starts failing with too many connections for role, and the API looks randomly dead.

SHOW max_connections;
SELECT count(*) FROM pg_stat_activity;
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;
SELECT application_name, count(*) FROM pg_stat_activity GROUP BY 1;

application_name is worth setting in the URL query or OPTIONS so you can see web versus worker. In SQLAlchemy connect_args={"application_name": "web"}. When idle in transaction dominates, you have a leaked session, not a load problem. Fix the leak before you raise max_connections.

Raising max_connections is the last knob. Each slot has RAM cost. On a 2 GB VPS sharing the box with Python, 40 to 80 is a sane ceiling. High-Speed SSD helps checkpoints; it does not shrink per-connection memory. Intel Xeon Platinum will run the queries; it will not invent RAM.

PgBouncer, or Just Smaller Pools

On one box, smaller pools beat another proxy. Set Django CONN_MAX_AGE=60, SQLAlchemy pool_size=2, max_overflow=2 per process, and keep worker counts low. That is often enough.

PgBouncer in transaction mode is the next step when you truly need many Python processes. It sits on 127.0.0.1:6432 and multiplexes. Django and SQLAlchemy then use small pools against the bouncer, and the bouncer uses a small pool against Postgres. Named prepared statements and LISTEN/NOTIFY are the usual gotchas in transaction pooling; if you use those, stay in session mode or skip the bouncer.

Do not point Celery beat, migrate, and the web pool at a transaction-pooled bouncer without testing. Migrations want a real session.

Timeouts Beat Meltdowns

statement_timeout in Postgres (SET or ALTER ROLE) kills a runaway query before it holds a connection forever. idle_in_transaction_session_timeout kills a leaked transaction. Gunicorn --timeout 30 should be higher than a typical statement_timeout so the worker dies after the query, not during it, or lower if you prefer the worker to be the backstop. Pick one backstop and log it.

ALTER ROLE app SET statement_timeout = '15s';
ALTER ROLE app SET idle_in_transaction_session_timeout = '30s';

Back up pg_dump on a schedule. Restores on the same Lucknow VPS are your disaster plan; there is no second region to fail over to. Keep the dump off-box if you can, but the connection-count problem is still local: a backup slot is another client. Run dumps when traffic is low, not with a 20-worker pool still attached.

L3/L4 DDoS filtering will not protect Postgres if you bind 5432 to 0.0.0.0. listen_addresses = localhost. Only Python on the box should connect.

Takeaway

One DATABASE_URL, small pools, written multiplication, statement timeouts, Postgres on localhost. Raise max_connections last. The database and the app share the same 2 GB; treat connections as RAM.

You can spin up an Ubuntu 24.04 instance on Netbay in under 60 seconds and keep Postgres on 127.0.0.1 from the first migrate — netbayhosts.in.