Tune PostgreSQL Autovacuum for Django

Problem statement

A Django app gets slower over months even though traffic and data volume barely changed. Tables like django_session, a frequently updated Order or Inventory table, or a Celery results table have grown far beyond their row count; queries that used to take milliseconds now scan bloated pages; disk usage climbs.

PostgreSQL never overwrites rows in place: updates and deletes leave dead row versions behind, and VACUUM reclaims them. Autovacuum does this automatically, but its defaults are tuned for moderate tables. On large, heavily updated tables it runs too rarely and too gently, and long-running transactions can stop it from cleaning anything. This guide measures the problem and tunes autovacuum where it matters.

1. Check dead tuples and last vacuum times in pg_stat_user_tables for your largest and busiest tables.
2. Find anything holding back cleanup: long or idle-in-transaction sessions, stale replication slots, prepared transactions.
3. For large, frequently updated tables, lower autovacuum_vacuum_scale_factor per table (for example to 0.01 to 0.05) with a migration.
4. Raise autovacuum_vacuum_cost_limit (or lower the cost delay) so vacuum keeps up on modern disks.
5. Make sure analyze also runs often enough on those tables so query plans stay accurate.
6. For tables already badly bloated, rebuild them online (for example with pg_repack) after fixing the cause.

Step-by-step solution

1. Measure dead tuples and vacuum activity

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,
       autovacuum_count,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 15;

Warning signs:

• dead_pct persistently above 10 to 20 percent on a large table;
• last_autovacuum days old on a table with constant updates;
• total_size much larger than the live row count justifies.

Typical Django candidates: django_session (rows written and deleted constantly), Celery or django_celery_results tables, status fields updated many times per row (orders, jobs), counters, and last_login-style timestamp updates on user tables.

2. Remove what blocks cleanup

Vacuum can only remove row versions that no running transaction might still need. One old transaction holds back cleanup for the whole database:

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

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

• Transactions open for hours (a forgotten psql session, an idle-in-transaction worker, a very long report) must be fixed first; tuning autovacuum cannot help while they exist.
• Inactive replication slots and orphaned prepared transactions hold back cleanup the same way.
• On replicas, hot_standby_feedback = on makes long replica queries hold back vacuum on the primary.

Set idle_in_transaction_session_timeout to stop abandoned transactions from accumulating.

3. Understand the trigger

Autovacuum vacuums a table when:

dead tuples > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × live tuples

With the defaults (threshold 50, scale factor 0.2), a table with 10 million rows waits for 2 million dead tuples before being vacuumed. By then, bloat and slow scans are already visible.

4. Tune hot tables per table

Change settings only where needed, with a migration so they are versioned and applied in every environment:

from django.db import migrations


class Migration(migrations.Migration):
    dependencies = [("orders", "0042_previous")]

    operations = [
        migrations.RunSQL(
            sql="""
                ALTER TABLE orders_order SET (
                    autovacuum_vacuum_scale_factor = 0.02,
                    autovacuum_vacuum_threshold = 1000,
                    autovacuum_analyze_scale_factor = 0.02
                );
            """,
            reverse_sql="""
                ALTER TABLE orders_order RESET (
                    autovacuum_vacuum_scale_factor,
                    autovacuum_vacuum_threshold,
                    autovacuum_analyze_scale_factor
                );
            """,
        ),
    ]

Now a 10-million-row table is vacuumed after about 200,000 dead tuples. The analyze setting keeps planner statistics current, which matters for tables whose data distribution shifts (status columns, dates).

Append-mostly tables (event logs, audit tables) benefit from vacuum too, for index-only scans and to freeze old rows. PostgreSQL 13 and newer trigger vacuum on inserts as well (autovacuum_vacuum_insert_scale_factor); tune it similarly for very large insert-only tables.

5. Let vacuum do enough work

Autovacuum throttles itself with a cost budget. Defaults were set for slow disks; on SSDs, vacuum often finishes too slowly to keep up.

# postgresql.conf
autovacuum_vacuum_cost_limit = 1000     # default -1 uses vacuum_cost_limit (200)
autovacuum_max_workers = 4               # default 3; needs restart
autovacuum_naptime = 30s                 # default 1min

The cost limit is shared among running workers, so raising the number of workers alone does not make each one faster. Change these gradually and watch disk I/O during business hours. On managed services, set them through parameter groups or flags.

6. Fix existing bloat

Vacuum makes dead space reusable but does not shrink files. A table that is already several times its necessary size stays large until rebuilt:

• VACUUM FULL table rewrites it compactly but takes an exclusive lock for the whole operation, blocking all access.
• pg_repack (an extension plus a client tool) rebuilds tables and indexes online with only brief locks. It needs free disk space roughly equal to the table size.
• REINDEX INDEX CONCURRENTLY rebuilds bloated indexes without blocking writes (PostgreSQL 12 and newer).

Rebuild only after fixing the cause, or the bloat returns.

Verification check:

SELECT relname, n_dead_tup, last_autovacuum, autovacuum_count
FROM pg_stat_user_tables
WHERE relname IN ('orders_order', 'django_session');

SELECT relname, reloptions FROM pg_class WHERE relname = 'orders_order';

Over the following days, autovacuum_count should increase regularly and n_dead_tup should stay a small fraction of live rows. Query latency on those tables should stabilise.

Explanation

PostgreSQL's multi-version concurrency control keeps old row versions so concurrent transactions see consistent snapshots. Vacuum removes versions no snapshot can see anymore, and analyze refreshes the statistics the query planner relies on. Both run automatically, triggered by thresholds proportional to table size, and throttled to limit I/O impact. For busy tables in a Django app, proportional thresholds are too coarse and the throttle too tight, while long transactions can block the process entirely. Per-table thresholds, a realistic cost budget, and no stale transactions keep tables compact and plans accurate.

Edge cases / notes

Transaction ID wraparound

Vacuum also freezes old rows to prevent transaction ID wraparound. If autovacuum cannot keep up for a long time, PostgreSQL runs aggressive anti-wraparound vacuums and, in the extreme, stops accepting writes. Monitor age(datfrozenxid) per database.

Deleting lots of rows

Mass deletes (cleanup of old sessions or logs) create large numbers of dead tuples at once. Delete in batches, and consider partitioning append-heavy tables by time so old data can be dropped as whole partitions.

When to automate this

Ship per-table settings as migrations, include the dead tuple query in your monitoring with alerts on growth, and alert on transactions older than a threshold. Those three measures prevent most bloat incidents.

Next steps

• Review related settings with the PostgreSQL Production Checklist for Django.
• Eliminate the most common vacuum blocker with Fix Idle in Transaction Connections from Django.
• Find queries suffering from bloat with Find Slow Django Queries with pg_stat_statements.
• If bloat has already filled the disk, see Fix a Full Disk on a Django Production Server.

FAQ

What does autovacuum do in PostgreSQL?

It removes dead row versions left by updates and deletes, updates planner statistics, and freezes old rows, automatically based on table activity.

Why is my Django table much larger than its data?

Dead tuples are not being vacuumed fast enough, often because of default thresholds on a large table or long-running transactions blocking cleanup.

Can I set autovacuum settings from Django?

Yes. Use a migration with RunSQL running ALTER TABLE ... SET (autovacuum_...), with a reverse RESET.

Should I run VACUUM FULL on production tables?

Only in a maintenance window, because it locks the table exclusively. Prefer pg_repack for online rebuilds.