PostgreSQL Production Checklist for Django

Problem statement

PostgreSQL is forgiving at small scale. A default installation serves a new Django app well for months, which makes it easy to skip the configuration and operational work that matters later: the application connects as a superuser, backups were never restored, autovacuum falls behind on a busy table, connection limits are hit during a traffic spike, and nobody can see which queries are slow.

This checklist reviews a PostgreSQL deployment for a Django application, whether self-managed or on a managed service. For managed databases, some items are handled by the provider; the checklist notes where you still need to verify settings.

Quick answer

1. Access: a dedicated, non-superuser role per application; TLS for remote connections; network access limited to app servers.
2. Connections: total connections from all processes below max_connections, with pooling if needed.
3. Memory: shared_buffers, effective_cache_size, and work_mem set for the server size.
4. Maintenance: autovacuum enabled and keeping up; bloat and dead tuples monitored.
5. Visibility: pg_stat_statements enabled; slow query logging with a sensible threshold.
6. Safety: statement_timeout and idle_in_transaction_session_timeout for the app role.
7. Backups: automated, off-server, with point-in-time recovery if your RPO requires it, and tested restores.
8. Lifecycle: supported PostgreSQL major version, planned upgrades, and monitoring of disk, replication, and locks.

Checklist

1. Roles and privileges

What to check:

SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin
FROM pg_roles
WHERE rolname NOT LIKE 'pg_%'
ORDER BY rolname;

Guidelines:

• The Django app role is not a superuser and cannot create roles or databases.
• It owns the application database (needed for migrations) or has explicit privileges.
• Consider separate roles for migrations (DDL privileges) and runtime (DML only) for high-security environments.
• Read-only roles for analytics, reporting tools, and replicas.
• Revoke CONNECT from PUBLIC on databases that other roles should not access.

REVOKE CONNECT ON DATABASE myapp FROM PUBLIC;
GRANT CONNECT ON DATABASE myapp TO myapp;

2. Authentication and TLS

What to check (self-managed):

sudo grep -v "^#" /etc/postgresql/*/main/pg_hba.conf | grep -v "^$"
sudo -u postgres psql -c "SHOW password_encryption;" -c "SHOW ssl;"

Guidelines:

• scram-sha-256 authentication for password logins, not md5 or trust.
• hostssl entries for any remote connections, so plain TCP is rejected.
• Django connects with sslmode=require at minimum, or verify-full with the CA certificate for protection against man-in-the-middle attacks:

DATABASES["default"]["OPTIONS"] = {
    "sslmode": "verify-full",
    "sslrootcert": "/etc/ssl/certs/db-ca.pem",
}

3. Network exposure

What to check:

sudo ss -tlnp | grep 5432

• Self-managed: listen_addresses is localhost or a private address; firewall allows 5432 only from app servers.
• Managed: public access disabled or limited to specific addresses; private networking preferred.

4. Connections

What to check:

SHOW max_connections;
SELECT count(*) FROM pg_stat_activity WHERE backend_type = 'client backend';
SELECT usename, application_name, state, count(*)
FROM pg_stat_activity
GROUP BY 1, 2, 3
ORDER BY 4 DESC;

Guidelines:

• Total potential connections (servers × Gunicorn workers × threads, plus Celery workers, cron jobs, and tools) stay below about 80 percent of max_connections.
• Django CONN_MAX_AGE and CONN_HEALTH_CHECKS configured deliberately.
• Pooling (PgBouncer or Django's psycopg pool) when connections approach limits.
• application_name set per process type for visibility.

5. Memory and planner settings

For a dedicated self-managed database server, common starting points (tune with measurements):

work_mem applies per operation and per connection, so large values multiplied by many connections can exhaust memory. Managed services set many of these from instance size; review the provider's parameter group rather than assuming defaults suit your workload.

6. Timeouts for the application role

What to check:

SELECT rolname, rolconfig FROM pg_roles WHERE rolname = 'myapp';

Set role-level defaults:

ALTER ROLE myapp SET statement_timeout = '30s';
ALTER ROLE myapp SET idle_in_transaction_session_timeout = '60s';
ALTER ROLE myapp SET lock_timeout = '10s';

• statement_timeout stops runaway queries from holding workers and locks.
• idle_in_transaction_session_timeout terminates sessions that opened a transaction and stopped doing anything.
• lock_timeout makes queries fail rather than wait indefinitely for locks.

Use a separate role or SET in migrations and reporting jobs that legitimately need longer limits.

7. Autovacuum and bloat

What to check:

SELECT relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

Guidelines:

• autovacuum is enabled (SHOW autovacuum; returns on)
• busy tables are vacuumed and analyzed regularly; a high dead tuple percentage with old last_autovacuum indicates it is falling behind
• tune per table for large, frequently updated tables:

ALTER TABLE django_session SET (autovacuum_vacuum_scale_factor = 0.02);

• run clearsessions regularly if you use database sessions, so django_session does not grow indefinitely

8. Query visibility

Enable pg_stat_statements (on managed services, usually a parameter or extension toggle):

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT calls, round(total_exec_time::numeric) AS total_ms,
       round(mean_exec_time::numeric, 2) AS mean_ms, left(query, 100)
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

It requires shared_preload_libraries = 'pg_stat_statements' in self-managed configurations, which needs a restart.

Log slow queries:

log_min_duration_statement = 500ms
log_lock_waits = on

Review the top queries regularly; most performance gains come from a handful of statements.

9. Indexes

What to check:

-- Large tables with many sequential scans
SELECT relname, seq_scan, idx_scan, n_live_tup
FROM pg_stat_user_tables
WHERE n_live_tup > 10000
ORDER BY seq_scan DESC
LIMIT 10;

-- Unused indexes
SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 10;

Add indexes for frequent filters and orderings through Django migrations (Meta.indexes), created concurrently on large tables. Remove unused indexes carefully after confirming statistics cover a representative period.

10. Backups and recovery

Guidelines:

• automated backups with retention matching your recovery needs
• point-in-time recovery (continuous WAL archiving or managed PITR) if losing up to a day of data is unacceptable
• copies stored off the database server and ideally in another account or region
• restores tested on a schedule, with measured restore time
• backups taken before major migrations or upgrades

11. Replication and high availability

If you use replicas or managed high availability:

• replication lag monitored and alerted
• failover tested; the application reconnects through a stable endpoint
• replicas not treated as backups

12. Version and upgrades

What to check:

SELECT version();

Guidelines:

• run a PostgreSQL major version that is still supported (the PostgreSQL project supports each major version for five years)
• apply minor releases promptly; they contain bug and security fixes
• plan major upgrades (dump and restore, pg_upgrade, or logical replication) with a tested runbook
• confirm your Django version supports the PostgreSQL version you plan to run

13. Monitoring

Alert on:

• disk usage and growth rate
• connection usage percentage
• replication lag
• long-running transactions and lock waits
• transaction ID wraparound risk (age of datfrozenxid), which autovacuum normally prevents but must be watched on very busy systems

SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;

Verify

Verification check:

• the app role is not a superuser and connects over TLS
• PostgreSQL is not reachable from the internet
• connection usage stays below 80 percent at peak
• pg_stat_statements is enabled and reviewed
• autovacuum keeps dead tuples low on busy tables
• a backup restore was tested recently

Explanation

Most PostgreSQL problems in Django deployments are operational rather than exotic: over-privileged access, unmanaged connection counts, missing visibility into slow queries, maintenance that falls behind, and untested recovery. Reviewing these areas before they become incidents keeps the database predictable as traffic and data grow, and makes performance work a matter of reading pg_stat_statements rather than guessing.

Edge cases / notes

Managed services

Providers handle hardware, patching, and often backups, but not application roles, query performance, connection counts, or restore testing. Review parameter groups and extension availability.

Multiple applications on one cluster

Separate databases and roles per application, and consider separate clusters when workloads interfere with each other's performance or maintenance windows.

Time zones

Django stores aware datetimes in UTC with USE_TZ = True; the database timezone setting affects how values display in tools like psql, not what Django stores.

Common mistakes

• Connecting the application as the postgres superuser.
• sslmode=disable for connections across networks.
• Large work_mem values with hundreds of connections.
• Never looking at pg_stat_statements until a performance incident.

Next steps

• Configure the connection in How to Connect Django to PostgreSQL in Production.
• Implement backups with Backup and Restore PostgreSQL for Django Apps.
• Add pooling with Set Up PgBouncer for Django.
• Change large tables safely with Run Schema Changes on Large Tables in Django Safely.

FAQ

Should Django connect to PostgreSQL as a superuser?

No. Use a dedicated role that owns or has privileges on the application database only. A superuser connection turns application vulnerabilities into full database compromise.

What PostgreSQL settings matter most for Django performance?

Connection limits and pooling, shared_buffers, effective_cache_size, work_mem, and keeping autovacuum healthy, plus the indexes for your most common queries.

How do I find slow queries from a Django app in PostgreSQL?

Enable pg_stat_statements and sort by total execution time, and log statements above a duration threshold with log_min_duration_statement.

Do I need point-in-time recovery for a Django database?

If losing up to a day of data since the last dump is unacceptable, yes. Point-in-time recovery lets you restore to a moment just before data loss.