Nov 19, 2025

How to Tune Autovacuum in PostgreSQL for Fast-moving Performance

PostgreSQL uses the autovacuum process to automatically clean up dead tuples, reclaim storage, and update statistics for the query planner. Without it, table bloat and transaction ID wraparound could cause performance degradation or even bring the database to a halt.

For a database of 150 GB or more, tuning autovacuum parameters becomes critical — otherwise, you might find yourself running manual VACUUM FULL commands that lock tables and take more than 10 minutes to complete on entire tables in the database.

This post explores autovacuum-related parameters in postgresql.conf, their purpose, practical tuning tips, and how to monitor the effect of your tuning.

1. Manual Vacuum vs. Autovacuum: Which Is Better?

Autovacuum

Runs automatically in the background.
Non-blocking (no exclusive table lock except in rare cases like VACUUM FULL).
Works incrementally, avoiding long downtime.
Good for continuous maintenance without user intervention.

Manual VACUUM FULL

Manually triggered by the DBA.
Locks the entire table during the process.
Can reclaim more space than a regular vacuum but takes longer.
Best for extreme bloat situations, not for daily use.

Verdict:

Autovacuum is ideal for ongoing maintenance.
Manual VACUUM FULL is only for emergencies or periodic deep cleaning.
If the autovacuum is tuned well, you’ll rarely need a manual vacuum.

2. Key Autovacuum Parameters and Their Purpose

Below are the key parameters from your postgresql.conf and what they mean:

1. autovacuum

set autovacuum = on

Enables or disables the autovacuum subsystem.
Requires track_counts = on.
Recommendation: Always keep it on.

2. autovacuum_max_workers

set autovacuum_max_workers = 3

Number of parallel autovacuum processes.
Higher values help in large databases with many active tables.
Tuning Tip: For a 150 GB DB, increase to 4–6 if you have enough CPU/IO.

3. autovacuum_naptime

set autovacuum_naptime = 1min

Time between autovacuum checks for work.
Tuning Tip: For busy OLTP systems, reduce to 30s.

4. autovacuum_vacuum_threshold

set autovacuum_vacuum_threshold = 50

Minimum number of dead tuples before vacuum triggers.
Tuning Tip: Keep low for high-churn tables.

5. autovacuum_vacuum_insert_threshold

set autovacuum_vacuum_insert_threshold = 1000

Triggers vacuum based on inserts only.
Tuning Tip: Lower for append-heavy workloads.

6. autovacuum_analyze_threshold

set autovacuum_analyze_threshold = 50

Minimum number of changes before running ANALYZE.
Tuning Tip: Lower for small tables to keep planner stats fresh.

7. autovacuum_vacuum_scale_factor

set autovacuum_vacuum_scale_factor = 0.2

Fraction of table size that determines vacuum trigger.
Tuning Tip: Lower for large tables (e.g., 0.05) to avoid massive dead tuple build-up.

8. autovacuum_vacuum_insert_scale_factor

 Set autovacuum_vacuum_insert_scale_factor = 0.2

Works like autovacuum_vacuum_scale_factor but for inserts.

9. autovacuum_analyze_scale_factor

set autovacuum_analyze_scale_factor = 0.1

Fraction of table size that determines analyze trigger.
Lowering keeps stats fresher for large tables.

10. autovacuum_freeze_max_age

set autovacuum_freeze_max_age = 200000000

Prevents transaction ID wraparound by forcing a vacuum.
Don’t set too high — the database can enter read-only mode if exceeded.

11. autovacuum_multixact_freeze_max_age

set autovacuum_multixact_freeze_max_age = 400000000

Similar to freeze_max_age but for multixact IDs.

12. autovacuum_vacuum_cost_delay

set autovacuum_vacuum_cost_delay = 2ms

Throttles vacuum I/O to reduce impact on queries.
Tuning Tip: Reduce to 0 for aggressive cleanup during off-peak hours.

13. autovacuum_vacuum_cost_limit

set autovacuum_vacuum_cost_limit = -1

How much I/O work vacuum can do before pausing.
-1 means use vacuum_cost_limit.

3. Practical Tuning Strategy for a 150 GB Database

In case of a 150 gb sized database, a good starting point is:

autovacuum = on
autovacuum_max_workers = 5
autovacuum_naptime = 30s
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.05
autovacuum_vacuum_cost_delay = 1ms

Monitor using the below query:

SELECT relname, n_dead_tup, last_vacuum, last_autovacuum
FROM pg_stat_all_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC;

Adjust parameters per table with:

ALTER TABLE mytable
SET (autovacuum_vacuum_scale_factor = 0.05);

4. Monitoring the Effect of Autovacuum Tuning

Tuning is only effective if you can confirm the changes are working. PostgreSQL’s system views let you observe autovacuum in real time and track its history.

4.1 Checking Active Autovacuum Processes

SELECT *
FROM pg_stat_activity
WHERE wait_event = 'AutovacuumMain';

Example output:

-[ RECORD 1 ]----+---------------------------------
pid              | 1991
backend_start    | 2025-08-09 14:01:28.038249+05:30
wait_event_type  | Activity
wait_event       | AutovacuumMain
backend_type     | autovacuum launcher

What changes after tuning?

Increasing autovacuum_max_workers will show more rows here running in parallel.
Lowering naptime will make autovacuum appear more frequently.

4.2 Tracking Autovacuum History Per Table

SELECT relname,
       n_dead_tup,
       last_vacuum,
       last_autovacuum,
       vacuum_count,
       autovacuum_count
FROM pg_stat_all_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY n_dead_tup DESC;

After tuning, you should see:

Lower n_dead_tup for busy tables.
More recent last_autovacuum values.
Higher autovacuum_count as the vacuum runs more often.

4.3 Watching Transaction Freeze Progress

SELECT datname, age(datfrozenxid) AS tx_age
FROM pg_database
ORDER BY tx_age DESC;

Lowering freeze-related parameters keeps tx_age well below autovacuum_freeze_max_age, preventing emergency vacuums.

Extra Tip — Log Autovacuum Activity

SET log_autovacuum_min_duration = 0;

This logs every autovacuum action and its reason in postgresql.log, letting you verify parameter impact over time.

Think of Autovacuum as PostgreSQL’s built-in housekeeping service — when configured well, it works silently in the background to control table bloat, maintain healthy indexes, and ensure consistent query performance.

While the default settings work for smaller workloads, high-traffic or large databases often benefit from custom tuning of parameters like scale_factor, threshold, and naptime.

Monitoring views such as pg_stat_activity and pg_stat_all_tables let you validate your changes and fine-tune further. With proper tuning and monitoring, you minimize the need for disruptive manual maintenance and keep your database running smoothly.