Tuning Postgres: The Scale Factor

The "Too Late" Problem

Autovacuum decides whether to clean a table using a simple formula:

dead_tuples > autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor × table_size)

With defaults of autovacuum_vacuum_threshold = 50 and autovacuum_vacuum_scale_factor = 0.2, autovacuum triggers when 20% of the table's rows are dead. This sounds reasonable until you apply it to large tables:

By the time autovacuum wakes up on a 1 TB table, there are 200 GB of dead tuples to process. That cleanup job takes hours. During those hours, queries slow down, disk fills up, and the planner works from stale statistics. This is not a theoretical problem — it is the most common cause of PostgreSQL performance degradation in production.

The Strategy: Tune per Table

The correct approach is to lower the scale factor for large tables so that autovacuum triggers more frequently, cleaning smaller amounts of garbage each time. You configure this at the table level using ALTER TABLE ... SET (...), which overrides the global defaults for that specific table — for a large, high-write table you'd typically drop autovacuum_vacuum_scale_factor to around 0.01 (triggering at 1% dead tuples instead of 20%), tighten autovacuum_analyze_scale_factor similarly, and set a minimum autovacuum_vacuum_threshold floor.

For extremely large tables (hundreds of GB to TB range), some teams go further and set autovacuum_vacuum_scale_factor = 0 and rely entirely on autovacuum_vacuum_threshold with a fixed row count. This gives predictable, time-based triggering regardless of table size.

You can inspect the current dead tuple count and last autovacuum time for all tables via pg_stat_all_tables — comparing n_dead_tup against n_live_tup for a dead-tuple percentage, alongside each table's last_autovacuum/last_autoanalyze timestamps. Any table with that percentage above 10–20% and a last_autovacuum timestamp more than a few hours old is a candidate for per-table tuning.