This episode moves the expense-tracker app from SQLite to PostgreSQL, turns on persistent connections with CONN_MAX_AGE, uses PgBouncer to reduce database load, and sets up automatic backups. The result: the app runs stably on PostgreSQL, stops opening a new connection for every request, and has backups you can actually restore. Following episode 3 on Gunicorn and Nginx, this one covers the data layer behind them — where a wrong setting can bring the system down even while the web server runs fine.

1. Moving from SQLite to PostgreSQL

SQLite is fine for development, but in production it cannot handle many concurrent writes and lacks remote backups. Install the driver, then declare the database through environment variables — never commit secrets into code, as episode 2 advised.

pip install "psycopg[binary]"

Production settings:

import environ
env = environ.Env()
DATABASES = {
    "default": env.db("DATABASE_URL"),
}

With DATABASE_URL=postgres://chi_tieu_app:strong-password@db:5432/chi_tieu_prod, Django picks up PostgreSQL as the engine automatically. Then run python manage.py migrate. If the app has real data on SQLite, do not copy the file: export with dumpdata, load it back with loaddata, and check foreign keys carefully before switching servers.

2. CONN_MAX_AGE: keep connections instead of opening one per request

By default Django closes the database connection when a request ends, so with several Gunicorn workers, every request pays for a fresh TCP handshake and authentication:

DATABASES = {
    "default": {
        **env.db("DATABASE_URL"),
        "CONN_MAX_AGE": 60,
        "CONN_HEALTH_CHECKS": True,
    }
}

CONN_MAX_AGE = 60 keeps each connection for at most 60 seconds, while CONN_HEALTH_CHECKS (since Django 4.1) lets Django drop dead connections before reusing them. Do not set it too high: long-lived connections occupy slots on PostgreSQL.

3. PgBouncer: when workers outnumber allowed connections

PostgreSQL by default allows around 100 concurrent connections, while a few Gunicorn workers can already exceed that. PgBouncer pools many app connections into fewer real ones:

[databases]
chi_tieu_prod = host=db port=5432 dbname=chi_tieu_prod
[pgbouncer]
listen_port = 6432
pool_mode = transaction
default_pool_size = 20
max_client_conn = 200

transaction mode is the most common: the connection returns to the pool as soon as the transaction ends, so hundreds of clients run fine on a few dozen real connections. One caveat: in this mode, Django must add "DISABLE_SERVER_SIDE_CURSORS": True to the DATABASES OPTIONS, because server-side cursors do not survive across transactions.

4. Basic backups: untested backups are not backups

The simplest backup is pg_dump on a schedule, compressed and pushed somewhere other than the database server — for example:

pg_dump -h db -U chi_tieu_app chi_tieu_prod | gzip > /backup/chi_tieu_prod_$(date +%F).sql.gz

Run this with cron every night, keep the 7 most recent copies, and sync them to object storage. More important: rehearse a restore once a month onto an empty database to confirm the data is actually readable.

Apply it to your project

For the expense-tracker app: move to PostgreSQL on staging first, turn on CONN_MAX_AGE = 60, watch the connection count with SELECT count(*) FROM pg_stat_activity;, and only bring in PgBouncer once you hit the limit. With a few dozen concurrent users, persistent connections plus a nightly backup are enough.

Related posts

Frequently asked questions

Should I use SQLite in production if the app is still small?

No, unless the app is almost read-only with barely any concurrent writes. SQLite locks the whole file on write, so two simultaneous writes force one request to wait.

Is a larger CONN_MAX_AGE always better?

No. Large values hold connections longer and make it easy to hit max_connections. Start at 60 seconds, watch pg_stat_activity, and only raise it if opening connections is genuinely the bottleneck.

When is PgBouncer mandatory?

When the total number of workers and processes exceeds the connections PostgreSQL allows, or when several services point at one database. Below that threshold, CONN_MAX_AGE is enough.

💬 Have you tried this in your own project? If you hit an error or know a cleaner way, leave a comment below — I read them all and will fold the best ideas into the post.

Sources

  • Django: Databases, Persistent connections (docs.djangoproject.com)
  • PgBouncer: Configuration, pool modes (pgbouncer.github.io)
  • PostgreSQL: pg_dump, max_connections (postgresql.org)