How Do You Tune Autovacuum in PostgreSQL?
Learn how to tune PostgreSQL autovacuum thresholds and cost settings to keep busy tables from bloating.
Expected Interview Answer
Tuning autovacuum means adjusting how aggressively and how frequently PostgreSQL's background autovacuum workers scan and clean each table, primarily by lowering the scale-factor and threshold settings and increasing the cost limit so cleanup keeps pace with write volume on busy tables.
By default, autovacuum triggers on a table once dead tuples exceed autovacuum_vacuum_scale_factor (a percentage of table size, default 20%) plus autovacuum_vacuum_threshold (a fixed row count, default 50), which is far too lax for very large or very hot tables that generate millions of dead tuples per day. Tuning typically means setting per-table storage parameters with a lower scale factor, raising autovacuum_vacuum_cost_limit or lowering autovacuum_vacuum_cost_delay so each vacuum run does more work per cycle, and increasing autovacuum_max_workers so more tables can be vacuumed concurrently. The goal is to keep dead-tuple accumulation low enough that bloat, index degradation, and wraparound risk never build up between runs.
- Keeps table and index bloat under control on high-churn tables
- Reduces the chance of autovacuum falling permanently behind
- Avoids emergency wraparound-prevention vacuums that can block writes
- Improves query planner accuracy through fresher statistics
AI Mentor Explanation
Think of ground staff who only mow the outfield once it has grown past a fixed shaggy threshold, which works fine for a rarely used practice pitch but leaves the main stadium pitch overgrown between international matches held every few days. Tuning autovacuum is like giving the main pitch its own lower mowing threshold and a bigger crew, so it gets attention proportional to how much it is actually used. Leave every ground on the same default schedule and the busiest pitch is always the one falling behind.
Step-by-Step Explanation
Step 1
Identify hot tables
Use pg_stat_user_tables to find tables with high n_dead_tup counts or long gaps since last_autovacuum.
Step 2
Lower the scale factor and threshold
Set per-table autovacuum_vacuum_scale_factor and autovacuum_vacuum_threshold lower via ALTER TABLE ... SET so cleanup triggers sooner.
Step 3
Raise the cost limit or lower the delay
Increase autovacuum_vacuum_cost_limit or reduce autovacuum_vacuum_cost_delay so each run does more work before throttling.
Step 4
Increase worker capacity and monitor
Raise autovacuum_max_workers if many tables need cleanup concurrently, then monitor pg_stat_user_tables to confirm dead tuples stay bounded.
What Interviewer Expects
- Knowledge of the default scale-factor/threshold formula and why it does not fit every table
- Understanding of per-table storage parameter overrides via ALTER TABLE
- Awareness of cost-based throttling (cost limit vs cost delay) and its effect on vacuum speed
- Ability to name monitoring views like pg_stat_user_tables used to diagnose the problem
Common Mistakes
- Disabling autovacuum entirely instead of tuning it
- Tuning global settings only, ignoring per-table overrides for hot tables
- Not monitoring after tuning to confirm dead tuples actually stay bounded
- Confusing cost_limit and cost_delay and their inverse relationship to aggressiveness
Best Answer (HR Friendly)
“Tuning autovacuum means telling PostgreSQL to clean up busy tables more often and more aggressively than the defaults allow, usually by lowering the percentage-of-table threshold that triggers a cleanup and letting each cleanup run do more work before pausing. It matters most on high-write tables, where the default settings are too relaxed and dead rows can pile up faster than the default schedule clears them.”
Code Example
-- Make a hot table trigger autovacuum sooner and work harder
ALTER TABLE orders SET (
autovacuum_vacuum_scale_factor = 0.02,
autovacuum_vacuum_threshold = 500,
autovacuum_vacuum_cost_limit = 2000
);
-- Monitor dead tuples and last autovacuum run per table
SELECT relname, n_dead_tup, n_live_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;Follow-up Questions
- What is the difference between autovacuum_vacuum_cost_limit and autovacuum_vacuum_cost_delay?
- When would you set per-table autovacuum parameters instead of global ones?
- How does autovacuum_max_workers interact with many bloated tables at once?
- What signs in pg_stat_user_tables indicate autovacuum is falling behind?
MCQ Practice
1. What does lowering autovacuum_vacuum_scale_factor on a table do?
A lower scale factor means autovacuum fires after a smaller percentage of dead tuples, cleaning more frequently.
2. Which view is commonly used to diagnose autovacuum falling behind on a table?
pg_stat_user_tables exposes n_dead_tup, n_live_tup, and last_autovacuum timing needed to diagnose lagging cleanup.
3. Raising autovacuum_vacuum_cost_limit primarily has what effect?
A higher cost limit raises the throttling ceiling, letting each vacuum run process more pages before pausing.
Flash Cards
What triggers autovacuum on a table by default? — Dead tuples exceeding autovacuum_vacuum_scale_factor (percentage) plus autovacuum_vacuum_threshold (fixed count).
How do you tune a single hot table? — Use ALTER TABLE ... SET to override autovacuum storage parameters for just that table.
What does autovacuum_vacuum_cost_limit control? — How much work a vacuum run does before pausing due to cost-based throttling.
Where do you check if autovacuum is keeping up? — pg_stat_user_tables, looking at n_dead_tup and last_autovacuum.