PostgreSQL Tuning for Django in Production: Memory Settings, Autovacuum, Bloat and pg_stat_statements

Out of the box, PostgreSQL is configured to start on almost any machine, not to run well on yours. On a 16 GB server it uses 128 MB of shared buffers, assumes your NVMe drive is a spinning disk, and waits for 20% of a 50-million-row table to become dead before vacuuming it. It also keeps no record of which queries use the time. Django hides all of this until p95 latency starts climbing. This guide sets the values that matter, explains why each one is what it is, and shows how to confirm each change took effect.

Memory and planner settings

PostgreSQL caches data in two places: its own shared_buffers, allocated at startup, and the OS page cache. A page evicted from shared buffers is often still in the OS cache, so re-reading it costs a memory copy, not disk I/O. That is why the usual starting point is about 25% of RAM. At 50% or more, hot pages get cached twice, the OS cache is squeezed, and checkpoints have more dirty pages to write.

• shared_buffers: 25% of RAM on a dedicated database host. Use 15–20% if gunicorn, Celery and Redis run on the same box, since they need real memory as well. Changing it requires a restart.
• effective_cache_size: allocates nothing. It is a planner hint about how much data is cached across shared buffers and the OS cache together. Set it to 50–75% of RAM. If it is too low, the planner prefers sequential scans over index scans that would actually be served from memory.
• maintenance_work_mem: memory for VACUUM, CREATE INDEX and adding foreign keys. Few of these run at once, so 512 MB–1 GB is fine, and it makes index-building migrations noticeably faster. Each autovacuum worker may also use this much, unless you cap it with autovacuum_work_mem.
• random_page_cost: the default of 4.0 models spinning disks. On SSD or NVMe, set it to 1.1. With the default, the planner skips good indexes on large tables, which is one of the most common causes of "my index isn't used".

Don't tune these on a staging box with 2 GB of data. A setting that makes no difference there can flip plans once production tables are larger than RAM.

work_mem: the multiplication problem

work_mem is not a per-connection limit. It is the memory each sort or hash operation in a plan may use before spilling to temporary files. Several things multiply it:

• Per node: a queryset with .annotate(Count(...)) across a join, followed by .order_by(), easily has a hash join, a hash aggregate and a sort. That is three allowances in a single query.
• Per parallel worker: with max_parallel_workers_per_gather = 2, a parallel hash join runs in the leader and two workers, and each one gets its own allowance.
• Hash nodes get more: hash-based operations may use work_mem × hash_mem_multiplier. The multiplier defaults to 2.0 on current versions.
• Per connection: all of the above applies to every backend running such a query at the same moment.

A rough worst case is connections × nodes × work_mem. At 100 connections, three nodes and 64 MB, that is around 19 GB, more than the whole machine. A traffic spike on a heavy admin changelist can get close to that. The OOM killer then kills a backend, and the postmaster responds by resetting every connection, which amounts to a full database restart at peak traffic.

Keep the global value modest (8–32 MB for a typical Django OLTP workload) and raise it only where there is a reason:

-- One session: an ad-hoc report or a management command
SET work_mem = '256MB';

-- A dedicated role used only by reporting jobs
ALTER ROLE myproject_reports SET work_mem = '128MB';

-- Inside a single transaction only
BEGIN;
SET LOCAL work_mem = '512MB';
-- heavy aggregate here
COMMIT;

Raise it only when you have evidence. log_temp_files = 0 logs every temporary file along with its statement. In queryset.explain(analyze=True, buffers=True) output, Sort Method: external merge Disk: ... means the sort spilled to disk. quicksort Memory: ... means it fit, and more memory would not help that query.

max_connections vs pooling in Django

Each connection is a separate backend process. When you hit FATAL: sorry, too many clients already, the obvious fix is max_connections = 500. That removes the error and makes throughput worse. A 4-vCPU server can only run a handful of queries at once, and past a few dozen active backends the rest just compete for CPU, locks and cache. Keep max_connections around 100 (changing it requires a restart) and control concurrency on the client side.

Django gives you three tools, and which one fits depends on your worker model.

Persistent connections: CONN_MAX_AGE

CONN_MAX_AGE = 60 keeps each thread's connection open between requests, so you don't pay the connect and auth cost on every request. Pair it with CONN_HEALTH_CHECKS = True, which checks a reused connection at the start of each request so a connection broken by a restart or failover is replaced before your view runs. With sync gunicorn workers, this already gives you what a pool would.

Native psycopg pool (Django 5.1+)

With psycopg 3 and the pool extra installed (psycopg[pool]), Django can keep a real connection pool per process. Set OPTIONS["pool"] to True for the defaults, or to a dict of psycopg_pool.ConnectionPool arguments. CONN_MAX_AGE must be 0 when the pool is on, and Django raises ImproperlyConfigured if it isn't.

# settings/production.py
DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "NAME": "myproject",
        "USER": "myproject",
        "PASSWORD": env("DB_PASSWORD"),
        "HOST": "127.0.0.1",
        "PORT": "5432",
        "CONN_MAX_AGE": 0,  # must be 0 when the pool is enabled
        "OPTIONS": {
            "pool": {
                "min_size": 2,
                "max_size": 8,
                "timeout": 10,        # seconds to wait for a free connection
                "max_lifetime": 1800, # recycle connections every 30 minutes
            },
        },
    },
}

The pool pays off with gthread workers and ASGI, where many requests in a process share a few connections. timeout gives you back-pressure: when the pool is exhausted, requests fail fast instead of piling onto a database that is already saturated. max_lifetime recycles connections, which matters because role settings only reach new connections.

External pooler

When many hosts share one database, per-process pools add up fast. A transaction-mode pooler such as PgBouncer caps the server-side total. Django works with it if you set DISABLE_SERVER_SIDE_CURSORS = True, because server-side cursors from .iterator() cannot move between server connections. Session-level SET and advisory locks also stop behaving as you would expect.

Doing the arithmetic

Write the budget down: 4 gunicorn processes × 8 = 32, plus 4 Celery processes × 2 = 8, plus beat, cron and your psql session during an incident. That comes to about 50. It fits under 100, with room for superuser_reserved_connections and for a deploy, when old and new workers briefly overlap.

Worked example: a 16 GB VPS, and how to apply it

The target is a dedicated database VPS with 16 GB RAM, 4 vCPUs and NVMe storage, running PostgreSQL 16 on Ubuntu 24.04. The application is a Django shop with an order pipeline: mostly short OLTP queries, some admin reporting and a nightly export.

-- Requires restart
ALTER SYSTEM SET shared_buffers = '4GB';
ALTER SYSTEM SET max_connections = 100;

-- Reload is enough
ALTER SYSTEM SET effective_cache_size = '12GB';
ALTER SYSTEM SET work_mem = '16MB';
ALTER SYSTEM SET maintenance_work_mem = '1GB';
ALTER SYSTEM SET autovacuum_work_mem = '512MB';
ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET effective_io_concurrency = 200;
ALTER SYSTEM SET max_parallel_workers_per_gather = 2;
ALTER SYSTEM SET max_wal_size = '4GB';
ALTER SYSTEM SET min_wal_size = '1GB';
ALTER SYSTEM SET checkpoint_timeout = '15min';
ALTER SYSTEM SET checkpoint_completion_target = 0.9;
ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 2000;

The budget: 4 GB of shared buffers, up to 1.5 GB for three autovacuum workers, and under 2 GB of work_mem for about 50 connections in practice. That leaves 8–9 GB of OS cache, which is what effective_cache_size = 12GB assumes. If Django shares the host, use 3 GB and 8 GB instead.

Reload vs restart

ALTER SYSTEM writes to postgresql.auto.conf, which overrides postgresql.conf. Nothing changes until you reload. In pg_settings.context, postmaster means a restart is needed, while sighup and user apply on reload.

SELECT pg_reload_conf();

-- What still needs a restart?
SELECT name, setting, unit, context
FROM pg_settings
WHERE pending_restart;

-- Where did each value come from?
SELECT name, setting, source, sourcefile
FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'random_page_cost');

sudo systemctl restart postgresql@16-main
sudo journalctl -u postgresql@16-main --since "5 minutes ago"

Restart in a quiet period and watch the log: if the server can't allocate shared buffers, it will not start. Keep each setting in one place, and use ALTER SYSTEM RESET name; to remove an override.

WAL and checkpoints

A checkpoint writes every dirty buffer to disk. After each checkpoint, the first change to a page writes a full image of that page into WAL. Frequent checkpoints therefore mean both I/O bursts and much more WAL, which in turn means more replication traffic and bigger backups.

A checkpoint starts when checkpoint_timeout expires or when WAL approaches max_wal_size. The 1 GB default is easily exceeded by a data migration that touches every row, and that forces requested checkpoints every minute or two. Raising max_wal_size to 4–8 GB lets the timer drive checkpoints instead. The cost is more WAL on disk and a longer crash recovery. checkpoint_completion_target = 0.9 (the default since PostgreSQL 14) spreads the writes across the interval.

-- PostgreSQL 17
SELECT num_timed, num_requested, write_time, sync_time
FROM pg_stat_checkpointer;

-- PostgreSQL 16
SELECT checkpoints_timed, checkpoints_req
FROM pg_stat_bgwriter;

Requested checkpoints should be a small minority. A checkpoints are occurring too frequently warning in the log during your nightly import means you should raise max_wal_size again.

Autovacuum, dead tuples and per-table settings

Under MVCC, an UPDATE writes a new row version and a DELETE only marks the row. The old versions, called dead tuples, stay in the table until no transaction can still see them. Vacuum makes their space reusable, maintains the visibility map that index-only scans depend on, and freezes old transaction IDs so the counter can wrap around safely.

Autovacuum starts on a table when dead tuples exceed autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples, which defaults to 50 + 20%. On a 50-million-row orders_order table, that means waiting for 10 million dead rows, and then a vacuum that runs for hours. Hot, large tables need what is effectively a fixed row threshold. Set it in a migration:

# orders/migrations/0042_autovacuum_tuning.py
from django.db import migrations

OPTIONS = (
    "autovacuum_vacuum_scale_factor",
    "autovacuum_vacuum_threshold",
    "autovacuum_analyze_scale_factor",
)


class Migration(migrations.Migration):
    dependencies = [("orders", "0041_order_status_index")]

    operations = [
        migrations.RunSQL(
            sql=(
                "ALTER TABLE orders_order SET ("
                "autovacuum_vacuum_scale_factor = 0.01, "
                "autovacuum_vacuum_threshold = 5000, "
                "autovacuum_analyze_scale_factor = 0.02);"
            ),
            reverse_sql=f"ALTER TABLE orders_order RESET ({', '.join(OPTIONS)});",
        ),
    ]

At 1% plus 5,000 rows, the orders table is vacuumed after about 505,000 dead rows, so each run is short. Analysing at 2% keeps row estimates current as the status distribution changes. Insert-only tables, such as audit logs, are governed by autovacuum_vacuum_insert_scale_factor instead.

When vacuum runs but cannot keep up

Autovacuum is throttled on purpose. Workers pause after spending autovacuum_vacuum_cost_limit worth of page cost points. That setting falls back to vacuum_cost_limit, which is 200, and the budget is shared across all active workers. Adding more autovacuum_max_workers therefore changes almost nothing on its own. On SSD, a cost limit of 1000–2000 is safe and makes a large difference.

HOT updates and fillfactor

If an update changes no indexed column and the new row version fits on the same page, PostgreSQL does a heap-only tuple (HOT) update. It writes no index entries, and the old version is pruned cheaply. An indexed updated_at = DateTimeField(auto_now=True) prevents HOT on every save, and so do fully packed pages. Compare n_tup_hot_upd with n_tup_upd. On update-heavy tables, ALTER TABLE ... SET (fillfactor = 85) leaves room on each newly written page.

Monitoring

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,
       n_tup_upd,
       n_tup_hot_upd,
       last_autovacuum,
       last_autoanalyze,
       autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 15;

-- Transaction ID age: autovacuum forces an aggressive vacuum at 200 million by default
SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;

If n_dead_tup stays high even though last_autovacuum is recent, vacuum is running but cannot remove anything. Something is holding back the xmin horizon, the oldest snapshot still in use. The usual causes are a transaction left open, a replication slot whose consumer has gone away, or a forgotten prepared transaction:

SELECT pid, usename, application_name, state,
       now() - xact_start AS xact_age,
       left(query, 80) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 10;

SELECT slot_name, active, xmin, catalog_xmin FROM pg_replication_slots;
SELECT gid, prepared, owner FROM pg_prepared_xacts;

Detecting and fixing bloat

Vacuum makes dead space reusable, but apart from empty pages at the end of the file it does not return that space to the OS. After a backfill or a big purge, the table stays large. That extra size is bloat: scans read it, it takes up cache, and backups copy it. Indexes often bloat worse than tables. Measure it with the pgstattuple contrib extension, which ships with the Ubuntu packages:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- Fast estimate using the visibility map
SELECT table_len, approx_free_percent, dead_tuple_percent
FROM pgstattuple_approx('public.orders_order');

-- Exact, but reads the entire table: run it off-peak
SELECT table_len, tuple_percent, dead_tuple_percent, free_percent
FROM pgstattuple('public.orders_order');

-- B-tree index health: a fresh index sits near 90% leaf density
SELECT avg_leaf_density, leaf_fragmentation
FROM pgstatindex('orders_order_status_created_idx');

20–30% free space in an updated table is healthy, because future updates reuse it. Act when free space stays above about 50%, or when leaf density is far below the fillfactor. Use the least disruptive fix that works:

While VACUUM FULL runs, every request that touches the table waits, reads included. On a large table, that is an outage. pg_repack (an extension plus a matching client binary) builds a copy in the background, tracks changes made in the meantime with a trigger, and swaps it in at the end: pg_repack -d myproject -t orders_order. Both need free disk space roughly equal to the table plus its indexes.

Bloat comes back unless you fix the cause. That is almost always a long-lived transaction, thresholds that are too loose, or a single statement that updates millions of rows. Batch large .update() and .delete() calls by primary-key range.

pg_stat_statements, slow query logging and auto_explain

pg_stat_statements aggregates every normalised query the server runs, with call counts, timings and buffer usage. Its overhead is low. It must be preloaded, which needs a restart. Use a conf.d file, which the Ubuntu config already includes, rather than ALTER SYSTEM SET shared_preload_libraries, which replaces the existing list:

# /etc/postgresql/16/main/conf.d/90-observability.conf
shared_preload_libraries = 'pg_stat_statements,auto_explain'

pg_stat_statements.max = 10000
pg_stat_statements.track = top

auto_explain.log_min_duration = '1s'
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_timing = off
auto_explain.log_nested_statements = on
auto_explain.sample_rate = 1.0

sudo systemctl restart postgresql@16-main
sudo -u postgres psql -d myproject -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"

Reading pg_stat_statements

Ordering by total time often puts a 2 ms query called millions of times at the top, typically an N+1 in a serializer. Fixing it with select_related or prefetch_related frees more capacity than any config change.

SELECT round(total_exec_time::numeric) AS total_ms,
       calls,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 1) AS pct,
       rows,
       left(query, 120) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;

Ordering by mean time finds individually slow queries, the ones users notice as a slow page. Filter out rare statements so a one-off migration or a manual query does not dominate the list:

SELECT round(mean_exec_time::numeric, 1) AS mean_ms,
       round(stddev_exec_time::numeric, 1) AS stddev_ms,
       calls,
       shared_blks_hit,
       shared_blks_read,
       temp_blks_written,
       left(query, 120) AS query
FROM pg_stat_statements
WHERE calls > 50
ORDER BY mean_exec_time DESC
LIMIT 15;

A high shared_blks_read means the query is reading from disk. A non-zero temp_blks_written is work_mem evidence for that specific query. A large stddev_exec_time suggests the plan depends on the parameters. After you deploy a fix, run SELECT pg_stat_statements_reset();.

Slow query log

Aggregates tell you which statement is slow. The log records the concrete occurrences, including parameters and timing. These settings apply on reload:

ALTER SYSTEM SET log_min_duration_statement = '250ms';
ALTER SYSTEM SET log_lock_waits = on;
ALTER SYSTEM SET log_temp_files = 0;
ALTER SYSTEM SET log_autovacuum_min_duration = '1s';
SELECT pg_reload_conf();

log_lock_waits logs lock waits longer than deadlock_timeout (1s by default), which exposes the select_for_update() hot spots. Autovacuum log lines report dead tuples that could not be removed yet, which is direct evidence that the xmin horizon is pinned.

auto_explain

The auto_explain settings above log the actual plan of any statement slower than one second, so you don't have to reproduce it later. With log_analyze on, every statement is instrumented, and per-node timing is the expensive part, which is why log_timing is off. Row counts and buffers per node are still logged. On a very busy server, lower sample_rate as well. Never set log_min_duration_statement = 0 in production.

statement_timeout and idle_in_transaction_session_timeout

Two failure modes cause most Django database incidents. One is a runaway query, such as an admin search using icontains on an unindexed column, which holds a worker and a connection for minutes. The other is an idle open transaction: an atomic() block waiting on a slow payment API, or a forgotten manage.py shell. The idle transaction holds its locks, so the next migration queues behind it and all traffic queues behind the migration. It also pins the xmin horizon, which undoes your autovacuum tuning.

Set limits on the application role, so they apply however the code connects:

ALTER ROLE myproject IN DATABASE myproject SET statement_timeout = '30s';
ALTER ROLE myproject IN DATABASE myproject SET idle_in_transaction_session_timeout = '60s';
ALTER ROLE myproject IN DATABASE myproject SET lock_timeout = '10s';

-- Migration role: long statements allowed, but never wait long for a lock
ALTER ROLE myproject_migrate IN DATABASE myproject SET statement_timeout = 0;
ALTER ROLE myproject_migrate IN DATABASE myproject SET lock_timeout = '5s';

-- Inspect what is set
SELECT r.rolname, s.setconfig
FROM pg_db_role_setting s
JOIN pg_roles r ON r.oid = s.setrole;

A few details matter here:

• Only new sessions pick these up. Persistent and pooled connections keep their old values. Restart gunicorn and Celery after changing a role setting, or rely on max_lifetime in the pool to recycle connections over time.
• Use a separate migration role. CREATE INDEX CONCURRENTLY can legitimately run for twenty minutes, so it cannot be held to the 30-second cap. Keep a short lock_timeout on this role, though. An ALTER TABLE that can't get its lock then fails fast and you retry, instead of it sitting in the queue with every request stuck behind it.
• Allow exceptions locally. A nightly export that really needs five minutes should run SET LOCAL statement_timeout = '5min' inside its own transaction, rather than loosening the limit for the whole role.
• Do not use idle_session_timeout with a pool. A pool keeps idle connections on purpose, and having the server kill them just turns into connection errors.

A timeout reaches Django as OperationalError: canceling statement due to statement timeout. Each one usually means either a missing index or work that belongs in a background job.

Common mistakes

• Treating work_mem as a per-connection limit. A global 256 MB with 100 connections on 16 GB invites the OOM killer. Raise it per role, per session or per transaction instead.
• Raising max_connections instead of pooling. Throughput falls once you pass a few dozen active backends. Count processes × pool size first.
• Running VACUUM FULL on a live table. Its ACCESS EXCLUSIVE lock blocks even plain SELECTs for the whole rewrite. Use REINDEX CONCURRENTLY for index bloat and pg_repack for table bloat.
• Tuning autovacuum while a transaction has been open for three days. No threshold or cost limit can remove tuples that an old snapshot can still see. Check pg_stat_activity, replication slots and prepared transactions first.
• Overwriting shared_preload_libraries with ALTER SYSTEM. This replaces the whole list, and a misspelt library name stops the server from starting. Use a conf.d file and restart while you are watching the log.
• Expecting ALTER ROLE ... SET to apply straight away. Existing pooled and persistent connections keep the old values until they reconnect.

Verify it

sudo -u postgres psql -c "SELECT name, setting, unit, source FROM pg_settings WHERE name IN ('shared_buffers','effective_cache_size','work_mem','maintenance_work_mem','random_page_cost','max_wal_size');"
sudo -u postgres psql -c "SELECT name FROM pg_settings WHERE pending_restart;"
sudo -u postgres psql -c "SHOW shared_preload_libraries;"
sudo -u postgres psql -d myproject -c "SELECT count(*) FROM pg_stat_statements;"
sudo -u postgres psql -d myproject -c "SELECT relname, reloptions FROM pg_class WHERE reloptions IS NOT NULL;"

# Role settings on a fresh connection, then a timeout that must fire
python manage.py shell -c "from django.db import connection; c = connection.cursor(); c.execute('SHOW statement_timeout'); print(c.fetchone())"
python manage.py shell -c "from django.db import connection; connection.cursor().execute('SELECT pg_sleep(35)')"

After a day of traffic, check three things. Timed checkpoints should far outnumber requested ones. n_dead_tup on your hot tables should stay small, with recent last_autovacuum timestamps. The top of pg_stat_statements should list queries you recognise and can act on. If all three hold, the database fits its hardware, keeps itself clean, and tells you which Django query to fix next.