PostgreSQL Autovacuum Tuning Principles for Write-Heavy Tables

Quick answer: autovacuum removes dead row versions so tables and indexes do not bloat, and the default settings are tuned for modest tables. For large, write-heavy tables, lower the per-table scale factor so vacuum starts earlier, check that cost throttling does not make it too slow, and make sure long transactions are not blocking cleanup. Never disable it.

Why Does PostgreSQL Need Autovacuum?

PostgreSQL uses multiversion concurrency control. An UPDATE writes a new row version and a DELETE only marks the old one as removed, so readers are never blocked by writers. The price is dead tuples: old versions that no transaction can see anymore but that still occupy space in the table and its indexes until vacuum reclaims it.

If dead tuples pile up, tables and indexes grow, scans read more pages than needed, and cache efficiency drops. Autovacuum also updates visibility information that enables index-only scans, refreshes statistics when combined with analyze, and prevents transaction ID wraparound, a serious condition that can force the database into protective shutdown behavior if ignored.

For these reasons, disabling autovacuum is almost never a good idea. If it causes load spikes, the right answer is to tune it so it works steadily in small increments, not to turn it off and face a large, disruptive cleanup later.

How Does Autovacuum Decide When to Run?

For each table, autovacuum compares the number of dead tuples with a threshold computed from a base value plus a fraction of the table size. The defaults are a base threshold of 50 rows and a scale factor of 0.2, which means vacuum starts after roughly 20 percent of the rows are dead. Confirm the defaults for your version in the documentation.

That fraction is fine for small tables but too generous for big ones. On a table with 500 million rows, waiting for 100 million dead rows means massive bloat and a very long vacuum when it finally starts. A write-heavy table needs a much smaller scale factor so vacuum triggers often and each run stays short.

ALTER TABLE events SET (
    autovacuum_vacuum_scale_factor = 0.02,
    autovacuum_vacuum_threshold = 1000,
    autovacuum_analyze_scale_factor = 0.02
);

Per-table settings let you tune hot tables without changing behavior for the whole database. Pick values from observation, and revisit them as the table grows.

What Else Slows Down or Blocks Cleanup?

Even a well-tuned autovacuum cannot remove tuples that an open transaction might still need. Several things hold back the cleanup horizon and cause bloat despite frequent vacuums:

• Long-running transactions, including sessions left idle in transaction by the application.
• Replication slots that are inactive or lagging, which retain old data.
• Long-running queries on standbys when feedback to the primary is enabled.
• Prepared transactions that were never committed or rolled back.

Autovacuum is also throttled by cost-based settings that make it pause after doing a certain amount of work, so it does not overwhelm storage. On fast storage, the default limits can make vacuum too slow to keep up with writes. Raising the cost limit or lowering the delay lets it work faster, at the price of more I/O, and the right balance depends on your hardware.

The number of autovacuum workers and the memory they use are also limits. Many busy tables and few workers produce queues, so check whether tables are waiting. Review the documentation for the parameters and defaults in your version before changing them.

What Should You Monitor?

Tuning without measurement is guesswork. Watch dead tuple counts and the time of the last autovacuum for hot tables, along with table and index size over time. Statistics views show these counts, and alerting on a growing dead tuple ratio catches problems early. Log autovacuum activity with a duration threshold to see how long runs take and whether they are being cancelled by conflicting locks.

When bloat has already happened, a regular vacuum frees space for reuse but may not shrink files. Rewriting commands can reclaim disk space but take stronger locks, so plan them carefully and consult the documentation for options that reduce locking.

Frequently Asked Questions

Is it safe to disable autovacuum on a busy table?

Not recommended. You risk bloat, stale statistics, and wraparound problems. Tune thresholds and throttling instead so vacuum runs in smaller, steadier passes.

What scale factor should I use for large tables?

A much smaller value than the default, often in the low single-digit percent range or less. Start conservatively, measure dead tuples and run times, and adjust per table.

Why does vacuum not reduce my table size?

Standard vacuum marks space reusable inside the file but usually does not return it to the operating system. Reclaiming disk space requires a table rewrite approach.

How do long transactions affect vacuum?

They keep old row versions visible, so vacuum cannot remove them. Find and fix idle-in-transaction sessions and slow queries to restore effective cleanup.

Summary

Autovacuum is essential to MVCC, and write-heavy tables need earlier triggers, enough throughput, and a clear cleanup horizon. Tune per table, watch dead tuples, and hunt down long transactions and stale replication slots. Check defaults and parameter details in the PostgreSQL documentation for your version.