Tuning Postgres: The Cost Limit

The Throttling Problem

Even after autovacuum triggers at the right time, it may still fall behind because of deliberate throttling. PostgreSQL uses a cost-based delay system to prevent autovacuum from monopolizing disk I/O and starving user queries.

The system works like a budget:
- Every page autovacuum reads from disk costs 1 credit (a "hit").
- Every page it reads that was not in the buffer cache costs 10 credits (a "miss").
- Every page it writes (dirtied) costs 20 credits.
- When the total credits spent reach autovacuum_vacuum_cost_limit (default: 200), autovacuum sleeps for autovacuum_vacuum_cost_delay (default: 2ms) before continuing.

On a spinning disk from 2008, this made sense. On a modern NVMe SSD capable of 1 million IOPS, a cost limit of 200 means autovacuum is sleeping the vast majority of the time. It's like hiring a janitor and then making them take a 20-minute break after every 5 minutes of work.

The Strategy: Unleash the Janitor

On modern hardware, increase the cost limit significantly. The right value depends on your hardware and workload, but a 10x increase is a safe starting point — raising the global autovacuum_vacuum_cost_limit from its default of 200 to something like 2000, optionally lowering autovacuum_vacuum_cost_delay below its 2ms default, and adding more autovacuum_max_workers for large databases. You can also tune the cost limit per table via the same ALTER TABLE ... SET (...) mechanism used for scale factor, which is useful when you want to be aggressive about one critical table without affecting the rest.

The key insight is that these two knobs — scale factor and cost limit — address different failure modes. Scale factor controls when autovacuum wakes up. Cost limit controls how fast it works once awake. A table can be suffering from either problem independently, or both simultaneously.