PostgreSQL Maintenance: Autovacuum

This document describes how to verify and monitor the autovacuum process in PostgreSQL. Autovacuum is essential for preventing data fragmentation (bloat) and maintaining query performance by updating table statistics.

By default, PostgreSQL has autovacuum enabled. To confirm that the settings are correct, run the following SQL queries:

-- Check if the main daemon is enabled
SELECT name, setting FROM pg_settings WHERE name = 'autovacuum';

-- Check if the statistics collector is enabled (required for autovacuum)
SELECT name, setting FROM pg_settings WHERE name = 'track_counts';

If the database feels slow, you can check if there is a vacuum process running right now and see its progress.

SELECT pid, query, state, backend_start
FROM pg_stat_activity
WHERE query LIKE 'autovacuum%';

This query shows exactly which table is being processed, the current phase, and how long it has been running:

SELECT
p.pid,
now() - a.xact_start AS duration,
coalesce(wait_event_type ||': '|| wait_event, 'none') AS waiting,
CASE WHEN a.query LIKE 'autovacuum%' THEN 'Autovacuum' ELSE 'Manual' END AS type,
p.relid::regclass AS table_name,
p.phase
FROM pg_stat_progress_vacuum p
JOIN pg_stat_activity a ON p.pid = a.pid;

To see if autovacuum is working efficiently, check the last run date and the number of “dead tuples” (rows that are deleted/obsolete but still taking up space).

SELECT
relname AS table_name,
n_dead_tup,
n_live_tup,
last_autovacuum,
last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

Note: If the n_dead_tup column has a very high value and last_autovacuum is old (or NULL), the process might be failing to clean that specific table.

Long-running transactions: A transaction left open (IDLE in transaction) prevents autovacuum from removing old rows. Exclusive Locks: Commands like ALTER TABLE or VACUUM FULL block autovacuum from accessing the table. Resource Limits: On very large databases, you might need to increase the number of worker processes (autovacuum_max_workers).

If you do not have access to an SQL console, you can check the worker process directly from the Linux terminal:

ps -axww | grep autovacuum

Official Documentation: PostgreSQL Automatic Vacuuming