Tune PostgreSQL Settings for a Django Workload

Problem statement

PostgreSQL's default configuration is deliberately conservative so it starts on almost any machine: shared_buffers of 128 MB and planner costs that assume spinning disks. On a dedicated database server with 8 or 16 GB of RAM and SSDs, those defaults leave memory unused and can push the planner towards slow sequential scans. Django workloads (many short queries from a connection pool, frequent small writes, occasional reports) benefit from a handful of settings changes; most other knobs can stay at their defaults.

1. shared_buffers about 25% of RAM on a dedicated server; effective_cache_size about 50-75%.
2. work_mem modest (8-32 MB): it is per sort or hash operation, multiplied by concurrent queries.
3. Keep max_connections low (100-200) and use connection pooling (PgBouncer or psycopg pool) instead of raising it.
4. On SSDs: random_page_cost = 1.1, effective_io_concurrency = 200.
5. Smooth checkpoints: max_wal_size larger (for example 4 GB), checkpoint_completion_target = 0.9.
6. Set statement_timeout for the app role and idle_in_transaction_session_timeout to catch stuck sessions.
7. Measure with pg_stat_statements before and after; managed services expose these as parameter groups.

Step-by-step solution

1. Memory settings

# postgresql.conf, dedicated server with 16 GB RAM
shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 16MB
maintenance_work_mem = 1GB

shared_buffers is PostgreSQL's own cache; the operating system's page cache holds much of the rest, which is what effective_cache_size tells the planner. work_mem applies to each sort, hash join, or hash aggregate in each query, so 100 connections running a two-sort query could use 100 × 2 × 16 MB. Raise it for specific reporting roles rather than globally:

ALTER ROLE reporting SET work_mem = '256MB';

maintenance_work_mem speeds up CREATE INDEX and vacuum.

2. Connections

Each PostgreSQL connection is a process using several megabytes, and performance degrades with too many active connections. Django with Gunicorn and Celery can open many connections (processes × threads). Keep max_connections moderate and pool:

max_connections = 200

Put PgBouncer in transaction mode in front, or use Django 5.1's psycopg pool option, so hundreds of app workers share a few dozen server connections.

3. Planner costs on SSDs

random_page_cost = 1.1
effective_io_concurrency = 200

The default random_page_cost = 4 models spinning disks where random reads are expensive; on SSDs and cloud block storage, random reads are nearly as cheap as sequential ones, so the planner should prefer index scans more readily.

4. WAL and checkpoints

wal_compression = on
max_wal_size = 4GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9

Larger max_wal_size means fewer forced checkpoints during write bursts (imports, migrations), which reduces I/O spikes. Watch checkpoints_req versus checkpoints_timed in pg_stat_bgwriter (or pg_stat_checkpointer in PostgreSQL 17); frequent requested checkpoints mean WAL size is too small.

5. Timeouts that protect the app

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

These stop a runaway query, a forgotten transaction, or a lock wait from tying up connections. Migrations may need longer timeouts; set them per session in the migration process.

6. Autovacuum

Django tables with frequent updates (sessions, counters, task results) bloat if autovacuum lags. Raise its throughput on modern hardware:

autovacuum_vacuum_cost_limit = 2000
autovacuum_max_workers = 4

and tune hot tables individually; see Tune PostgreSQL Autovacuum for Django.

Verification check: after a restart (needed for shared_buffers), SHOW shared_buffers; shows the new value, the buffer cache hit ratio in pg_stat_database is above 99% for typical workloads, top queries in pg_stat_statements are faster, and checkpoint warnings disappear from the log.

Explanation

PostgreSQL relies on two caches (its shared buffers and the OS page cache), a cost-based planner that chooses between index and sequential scans using configurable cost estimates, and background processes for checkpoints and vacuuming. Defaults target the smallest machines. Telling PostgreSQL how much memory it can use and how fast random I/O is lets the planner make better choices; capping connections and adding timeouts keeps the server stable under Django's many-process connection pattern. Beyond these, gains usually come from queries and indexes, not configuration.

Edge cases / notes

• Managed services (RDS, Cloud SQL, Azure) pick sensible memory defaults by instance size; change settings through parameter groups or flags.
• Huge pages (huge_pages = try) help large shared_buffers on Linux; configure the OS accordingly.
• Restart vs reload. shared_buffers and max_connections need a restart; most others reload with SELECT pg_reload_conf().
• Tools like PGTune generate starting configs; treat them as a baseline, not a final answer.

Next steps

• Find what to optimise with Find Slow Django Queries With pg_stat_statements.
• Review the rest of the Django PostgreSQL Production Checklist.

FAQ

What shared_buffers should I use for Django?

About 25% of RAM on a dedicated database server is a good starting point.

Should I increase max_connections for more Gunicorn workers?

Usually not. Use a connection pooler so many app workers share fewer database connections.

What work_mem is safe?

Modest values (8-32 MB) globally, with higher values for specific reporting roles.

Do these settings apply to RDS or Cloud SQL?

Yes, through parameter groups or database flags, although many defaults are already sized for the instance.