Autovacuum is a core PostgreSQL background worker that regularly cleans up dead row versions created by MVCC and refreshes planner statistics. By proper PostgreSQL autovacuum tuning, it can keep table and index bloat under control and it helps the planner make better choices, which directly improves performance.

In this guide from PerLod Hosting, you will learn how autovacuum impacts performance and the best strategies for tuning it safely in large databases.

What is Autovacuum and Why PostgreSQL Needs It?

Autovacuum is a background service process that automatically performs database maintenance tasks, which are essential for optimal PostgreSQL performance.

PostgreSQL uses Multi-Version Concurrency Control (MVCC) to handle concurrent transactions without locking. When you update or delete a row, PostgreSQL doesn’t immediately remove the old version; it creates a new row version and marks the old one as dead.

This way allows multiple transactions to read and write data simultaneously without blocking each other, but it creates dead row versions over time and wastes space.

Autovacuum solves this by performing three maintenance operations, including:

• Removing dead row version: It frees up space from old data so it can be reused.
• Updating table statistics: It gathers data statistics to help the query planner choose the best execution plans.
• Preventing transaction ID wraparound: Prevents database shutdown by freezing old transaction IDs before they reach the 2 billion limit.

Without autovacuum, your database would continuously grow in size, queries would slow down because of scanning unnecessary data, and finally, the database would stop accepting new transactions.

How Autovacuum Runs?

Autovacuum doesn’t run constantly. It monitors tables and runs cleanup based on a formula that considers both the table size and the number of changed rows.

The formula looks like this:

Vacuum trigger = autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor × number of rows)

The default settings include:

• utovacuum_vacuum_threshold: 50 rows
• autovacuum_vacuum_scale_factor: 0.2 (20%)

This means a table with 10,000 rows will run autovacuum when 2,050 rows have been updated or deleted:

50 + (0.2 × 10,000) = 2,050

For analysis, which updates statistics, you can apply this formula:

Analyze trigger = autovacuum_analyze_threshold + (autovacuum_analyze_scale_factor × number of rows)

With a 50 threshold and a 0.1 scale factor, analysis runs more frequently than vacuuming.

The Performance Impact of Autovacuum

Many people mistakenly believe autovacuum slows down the database because it uses disk I/O. In reality, a well-tuned autovacuum actually improves performance by keeping tables clean and statistics up to date.

When autovacuum doesn’t run frequently enough or runs too slowly, several performance issues happen:

• Slower sequential scans
• Bloated indexes
• Wasted disk space
• Poor query plans

Without proper autovacuum, tables can quadruple in size, and fast queries can slow down significantly. It must run frequently enough to match your write workload; if performance suffers, the solution is tuning it to run faster or more often, never disabling it.

Key Autovacuum Configuration Parameters

Understanding the core autovacuum parameters is essential for effective tuning. These settings control when autovacuum runs, how aggressively it works, and how many system resources it consumes.

1. Trigger Frequency Parameters: These settings control when autovacuum runs.

• autovacuum: Master switch. Always keep it enabled.
• autovacuum_vacuum_scale_factor: Runs vacuum when 20% (0.2) of rows change.
• ​autovacuum_vacuum_threshold: Minimum changed rows required to start vacuum (50).
• autovacuum_analyze_scale_factor: Runs statistics updates when 10% (0.1) of rows change.
• autovacuum_analyze_threshold: Minimum changed rows required to start analysis (50).

2. Speed and Resource Parameters: These control how fast autovacuum runs and how much I/O it uses.

• autovacuum_vacuum_cost_delay: Sleep time between work cycles (default 2ms). Lower values speed up vacuuming but increase I/O usage.
• autovacuum_vacuum_cost_limit: Cost budget before sleeping. Default is -1, which uses vacuum_cost_limit of 200. Higher values let vacuuming run longer before sleeping.
• ​autovacuum_max_workers: Maximum simultaneous vacuum processes (default 3), each using its own CPU and I/O.
• ​autovacuum_naptime: Minimum wait time between autovacuum runs (default 1 minute).
• autovacuum_work_mem: Memory dedicated to each worker(default -1). Increasing this reduces expensive index scans.

3. Wraparound Protection Parameters: These prevent transaction ID exhaustion.

• autovacuum_freeze_max_age: The transaction age that forces an emergency vacuum(default 200 million). With monitoring, this can be safely raised to 500 million or 1 billion.
• ​vacuum_freeze_min_age: The minimum age a row must reach before its transaction ID is frozen (default 50 million).

Check Current Autovacuum Settings

To view all autovacuum-related settings on your PostgreSQL shell, you can run the commands below:

SELECT name, setting, unit, context, short_desc
FROM pg_settings
WHERE name LIKE '%autovacuum%'
ORDER BY name;

This query shows each parameter’s current value, whether it requires a restart to change, and a brief description.

To verify autovacuum is enabled, you can use the command below:

SHOW autovacuum;

This should return on. If it returns off, you need to enable it immediately to prevent serious performance and stability issues.

Also, check if the statistics collector is enabled, which autovacuum requires to function:

SHOW track_counts;

This must be on for autovacuum to work properly.

Understand PostgreSQL Cost Model

PostgreSQL uses a cost-based system to stop autovacuum from slowing down your server. Each I/O operation during vacuum has an associated cost:​

• Reading a page from disk: cost of 1.
• Reading a page from cache: cost of 1 if hit.
• Dirtying a page: cost of 20 by default.

When autovacuum spends its budget (autovacuum_vacuum_cost_limit), it must sleep for a short time (autovacuum_vacuum_cost_delay). While this protects performance, if the budget is too low, autovacuum runs too slowly to clean up data effectively.

Now that you have undrestand the PostgreSQL autovacuum tuning basics, proceed to the next step to find the issues.

Discover PostgreSQL Autovacuum Issues

Even with autovacuum enabled, many databases suffer from poor performance due to improper tuning. Identifying these PostgreSQL autovacuum tuning issues early and understanding why they happen is essential for keeping your database healthy.

Table Bloat and Wasted Space

Table bloat happens when dead rows gather faster than autovacuum cleans them, usually because default settings are too slow for large tables.

For example, an 800-million row table with default settings won’t vacuum until 160 million rows change, which wastes massive amounts of space. This gets worse as tables grow. A small table is cleaned often, but a huge table waits for millions of changes before autovacuum even starts.

To check dead rows in your tables, you can use the following query:

SELECT schemaname, 
       relname, 
       n_live_tup, 
       n_dead_tup,
       n_dead_tup::float / NULLIF(n_live_tup, 0) AS dead_ratio,
       last_autovacuum, 
       last_vacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC
LIMIT 20;

Tables with high n_dead_tup counts or high dead_ratio values require faster autovacuum settings. If the last_autovacuum is old or NULL, it means autovacuum is failing to keep up with that table.

For more detailed analysis, you can install and use the pgstattuple extension:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT table_len,
       tuple_count,
       dead_tuple_count,
       dead_tuple_len,
       dead_tuple_len::float / NULLIF(table_len, 0) * 100 AS bloat_pct
FROM pgstattuple('schema_name.table_name');

This provides an exact measurement of the table’s total physical size compared to the actual useful data it contains.

Autovacuum Running Too Slowly

Sometimes autovacuum runs too slowly to keep up,leading to processes that last hours or days while dead rows continue to gather. In production, a single table vacuum can take over 8 hours, which causes query latency to spike from 100ms to 1000ms.

To check if autovacuum is currently running and how long it’s been active, you can use the query below:

SELECT pid,
       now() - xact_start AS duration,
       state,
       query
FROM pg_stat_activity
WHERE query LIKE 'autovacuum:%'
ORDER BY xact_start;

Also, you can monitor detailed progress of active vacuum operations using this query:

SELECT p.pid,
       a.query,
       p.datname,
       p.relid::regclass AS table_name,
       p.phase,
       p.heap_blks_total,
       p.heap_blks_scanned,
       p.heap_blks_vacuumed,
       ROUND(100.0 * p.heap_blks_scanned / NULLIF(p.heap_blks_total, 0), 2) AS pct_scanned,
       ROUND(100.0 * p.heap_blks_vacuumed / NULLIF(p.heap_blks_total, 0), 2) AS pct_vacuumed,
       p.index_vacuum_count,
       p.max_dead_tuples,
       p.num_dead_tuples
FROM pg_stat_progress_vacuum p
JOIN pg_stat_activity a ON p.pid = a.pid;

The index_vacuum_count is essential; a high value (10+) means that autovacuum_work_mem is too small, which forces vacuum to repeatedly rescan indexes because it can’t hold all dead rows references in memory.

Emergency Wraparound Vacuum

One of the worst performance issues is the emergency wraparound vacuum, which occurs when tables near the 2 billion transaction ID limit. Because PostgreSQL uses 32-bit IDs, it must freeze old ones to prevent corruption; if the age exceeds autovacuum_freeze_max_age (default 200 million), the system forces an aggressive vacuum.

These emergency vacuums run even if autovacuum is disabled and are very I/O heavy because they scan every page. If multiple large tables hit this limit at once, it can severely decrease database performance.

Database logs will show messages like this:

autovacuum: VACUUM table_name (to prevent wraparound)

If the database gets too close to the 2 billion transaction limit without freezing old IDs, PostgreSQL will stop accepting all new writes to prevent data corruption. This results in complete downtime until the emergency vacuum finishes.

To check which tables are closest to the wraparound limit, you can use the following query:

SELECT oid::regclass::text AS table_name,
       age(relfrozenxid) AS xid_age,
       (SELECT setting::int FROM pg_settings WHERE name = 'autovacuum_freeze_max_age')
         - age(relfrozenxid) AS tx_before_wraparound_vacuum,
       pg_size_pretty(pg_total_relation_size(oid)) AS size
FROM pg_class
WHERE relfrozenxid != 0
  AND relkind IN ('r', 't')
ORDER BY age(relfrozenxid) DESC
LIMIT 20;

Tables with tx_before_wraparound_vacuum values below 50 million are near to emergency vacuum state.

Long-Running Transactions Blocking Cleanup

A common problem is when autovacuum runs but can’t remove dead rows because a long-running transaction is holding an old database snapshot. PostgreSQL cannot delete data that might still be visible to any open transaction, even a read-only one.

This results in autovacuum finishing quickly but leaving n_dead_tup high, often logging warnings that dead tuples are not yet removable.

To find long-running transactions blocking cleanup, you can use this query:

SELECT pid,
       now() - xact_start AS duration,
       state,
       wait_event,
       query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND state != 'idle'
ORDER BY xact_start
LIMIT 10;

Transactions that run for hours or days block autovacuum from freeing space, even if they seem idle. Other common causes of dead tuples not yet removable include:

• Abandoned replication slots: Unused but still registered logical replication slots.
• Prepared transactions: Two-phase commits that were never finished or rolled back.
• Orphaned temporary tables: Leftover tables from crashed sessions.

Poor Monitoring and Logging

Many autovacuum issues go unnoticed because logging is often disabled or set too high (default 10min), which means only very long vacuums are recorded. This hides essential details like which tables are being vacuumed, frequency, and whether cleanup is keeping pace.

Without proper logging, you can’t see which tables consume the most time, how many dead rows are removed, if vacuum finishes before new bloat accumulates, or if locks are skipping maintenance.

Incorrect Settings for Large Tables

Default autovacuum settings are too traditional for large databases, which leads to problems as tables grow to billions of rows.

For example, a 1 billion row table waits for 200 million changes before vacuuming.

This delay causes massive bloat, forces vacuums to run for hours, allows more dead tuples to gather during the process, and kills query performance.

The percentage-based scale_factor just doesn’t work for huge tables.

Autovacuum Disabled on Specific Tables

In some cases, developers or DBAs disable autovacuum on specific tables because they think it will improve performance. This is not the right solution and will cause serious problems over time.

To check if autovacuum is disabled on any tables, you can use this query:

SELECT schemaname, 
       relname, 
       reloptions
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
LEFT JOIN pg_stat_user_tables s ON c.oid = s.relid
WHERE relkind = 'r'
  AND reloptions::text LIKE '%autovacuum_enabled=false%'
ORDER BY schemaname, relname;

Investigate any table with autovacuum_enabled=false; unless it’s a specific case like a regularly truncated staging table, autovacuum should be re-enabled immediately.

Insufficient Workers or Poor Scheduling

With only 3 default workers, databases with many tables can easily fall behind; for example, 500 tables taking 10 minutes each means most will go unvacuumed for long periods.

Also, if autovacuum_naptime (default 1 minute) is too slow, the system won’t even check for dirty tables often enough.

To see how many autovacuum workers are currently active, you can run the query below:

SELECT count(*) AS active_workers
FROM pg_stat_activity
WHERE query LIKE 'autovacuum:%';

If the active worker count consistently hits your autovacuum_max_workers limit, your system is saturated and failing to keep up with maintenance.

Optimize Performance with PostgreSQL Autovacuum Tuning Solutions

At this point, you can implement the PostgreSQL autovacuum tuning best solutions to optimize performance in production databases. The key is tuning autovacuum to match your specific workload characteristics rather than relying on default settings.

1. Adjust PostgreSQL Cost Parameters

When autovacuum is falling because it runs too slowly, the most effective solution is to increase its speed by adjusting the PsotgreSQL cost parameters.

Increase Cost limit: The autovacuum_vacuum_cost_limit sets the work budget before autovacuum pauses; increasing it allows more I/O operations before it sleeps:

-- In postgresql.conf or via ALTER SYSTEM
ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 2000;
SELECT pg_reload_conf();

Raising to 2000 or higher allows autovacuum to do 10x more work before pausing, which speeds it up. Just be sure to monitor system I/O to avoid overwhelming your disks.

Decrease cost limit: Alternatively, you can reduce the autovacuum_vacuum_cost_delay to shorten the pause between work cycles:

ALTER SYSTEM SET autovacuum_vacuum_cost_delay = 1;  -- Down from default 2ms
SELECT pg_reload_conf();

Setting this to 0 removes the delay entirely, which allows autovacuum to run at maximum speed:

ALTER SYSTEM SET autovacuum_vacuum_cost_delay = 0;
SELECT pg_reload_conf();

This setting is suitable for systems with fast SSDs or NVMe drives where I/O is not a bottleneck.

2. PostgreSQL Table Speed Optimization

For specific tables that need faster vacuuming without affecting the entire database, you can use table settings:

ALTER TABLE large_busy_table SET (
  autovacuum_vacuum_cost_delay = 0,
  autovacuum_vacuum_cost_limit = 10000
);

This forces autovacuum to process that specific table as fast as possible, while keeping safer, slower settings for the rest of the database.

3. Increase Memory for Autovacuum

Autovacuum needs memory to store dead row references; if autovacuum_work_mem is too small, it forces expensive multiple passes over indexes.

The default (-1) uses maintenance_work_mem, which is often too low. Increase memory for autovacuum (1GB) lets workers store more references, reducing index scans and speeding up cleanup.

ALTER SYSTEM SET autovacuum_work_mem = '1GB';
SELECT pg_reload_conf();

If pg_stat_progress_vacuum shows a high index_vacuum_count (10+), adding memory will help.

4. Make Autovacuum Run More Frequently

For large tables that gather dead rows fast, the default 20% scale factor is too high. The best solution is to run autovacuum much more frequently.

Global adjustments: To improve autovacuum frequency across the entire database, you can modify the global settings:

ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.05;  -- 5% instead of 20%
ALTER SYSTEM SET autovacuum_vacuum_threshold = 1000;      -- 1000 instead of 50
ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.02;  -- 2% instead of 10%
ALTER SYSTEM SET autovacuum_analyze_threshold = 500;      -- 500 instead of 50
SELECT pg_reload_conf();

Table Tuning: For large tables, stop using the scale factor completely and switch to a fixed threshold:

-- For a table with 800 million rows that needs daily vacuuming
ALTER TABLE large_table SET (
  autovacuum_vacuum_scale_factor = 0.0,
  autovacuum_vacuum_threshold = 400000,
  autovacuum_analyze_scale_factor = 0.0,
  autovacuum_analyze_threshold = 100000
);

For extremely high-write tables, you can run more aggressively:

ALTER TABLE high_update_table SET (
  autovacuum_vacuum_scale_factor = 0.0,
  autovacuum_vacuum_threshold = 1000
);

Decrease Naptime: If you have many tables requiring frequent attention, you can reduce autovacuum_naptime so the launcher checks tables more often:

ALTER SYSTEM SET autovacuum_naptime = 15; -- 15 seconds instead of 60
SELECT pg_reload_conf();

Increase Autovacuum Workers: When you have many tables or tables that take hours to vacuum, you can add more workers to process tables:

-- Requires database restart in PostgreSQL versions before 18
ALTER SYSTEM SET autovacuum_max_workers = 6;

Be careful, as each worker uses CPU, memory, and I/O, start with 3 to 8 workers and monitor resources.

Note that in PostgreSQL 18 and above, you can change autovacuum_max_workers immediately without a restart.

For production databases requiring full control over autovacuum tuning, dedicated servers provide the flexibility to adjust system-level settings without restrictions.

5. Prevent PostgreSQL Transaction ID Wraparound Issues

To prevent disruptive emergency wraparound vacuums, you can safely increase autovacuum_freeze_max_age with appropriate monitoring:​

ALTER SYSTEM SET autovacuum_freeze_max_age = 500000000;  -- 500 million transactions

This change requires a restart. Raising the limit up to 1 billion gives autovacuum more time to freeze data naturally, which avoids aggressive emergency scans.

Important note: Only do this if you actively monitor transaction ages to ensure autovacuum keeps up. Use this query to track age:

SELECT datname,
       age(datfrozenxid) AS age_in_transactions,
       (SELECT setting::int FROM pg_settings WHERE name = 'autovacuum_freeze_max_age')
         - age(datfrozenxid) AS transactions_remaining
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

Set up alerts when transactions_remaining drops below 100 million.

6. Identify and Terminate PostgreSQL Long Running Transactions

PostgreSQL Long running transactions are a common cause of autovacuum being unable to clean up dead rows. The solution is to identify and terminate problematic transactions.

Find transactions that have been open for extended periods with the following query:

SELECT pid,
       now() - xact_start AS duration,
       state,
       wait_event,
       query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND (now() - xact_start) > interval '1 hour'
ORDER BY xact_start;

Terminate any transactions that have been running for hours or days without a valid reason:

SELECT pg_terminate_backend(pid);

Replace pid with the process ID from the previous query.

Application-Level Solutions: The better solution is preventing long-running transactions at the application level:

• Keep transaction blocks short and focused.
• Avoid performing long operations inside database transactions.
• Use connection poolers with transaction timeout settings.
• Review ORM configurations that may keep transactions open unnecessarily.

Ensure prepared transactions (two-phase commits) are promptly committed or rolled back:

-- List prepared transactions
SELECT * FROM pg_prepared_xacts;

-- Commit or rollback as appropriate
COMMIT PREPARED 'transaction_id';
-- or
ROLLBACK PREPARED 'transaction_id';

7. Improve Autovacuum Monitoring and Logging

You can enable comprehensive autovacuum logging to access visibility into vacuum operations:

ALTER SYSTEM SET log_autovacuum_min_duration = 0;  -- Log all autovacuum actions
SELECT pg_reload_conf();

This logs every autovacuum operation with details about rows removed, pages processed, and execution time. These logs appear in your PostgreSQL log file and are essential for identifying issues.

For a good balance that captures important activity without spamming your logs, log only vacuums that take longer than 1 second:

ALTER SYSTEM SET log_autovacuum_min_duration = 1000;  -- Log vacuums taking over 1 second
SELECT pg_reload_conf();

This gives the most important information inclusing which tables are vacuumed most often, which take the longest, and where locks are blocking progress.

Here are the most important things that you must not to do:

• Never disable autovacuum globally: This causes massive bloat and database shutdown due to wraparound.
• Don’t rely on VACUUM FULL: It locks tables completely; use it only during scheduled maintenance, not as a routine fix.
• ​Don’t disable autovacuum on specific tables: Only do this if you have a manual maintenance plan in place.
• ​Don’t ignore wraparound warnings: If you see warnings about transaction ID wraparound, act immediately to prevent data loss.

FAQs

Conclusion

Effective PostgreSQL autovacuum tuning ensures your database keeps optimal performance at scale. By understanding how autovacuum works, recognizing common issues, and applying best fixes, you can prevent bloat, avoid emergency vacuums, and keep queries running fast even as your data grows to billions of rows.

We hope you enjoy PostgreSQL autovacuum tuning guide. Subscribe to our X and Facebook channel to get the latest updates and articles.

For further reading:

iptables vs nftables in Linux

How to Fix MySQL Error 1040 Too Many Connections