====== 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.

===== 1. Configuration Check =====

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

<code>
-- 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';
</code>

^ Parameter ^ Optimal Value ^ Description ^
| autovacuum | ''on'' | Starts the automatic vacuuming daemon. |
| track_counts | ''on'' | Allows the collector to gather statistics on row modifications. |

===== 2. Monitoring Current Activity =====

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

==== Active Processes ====
<code>
SELECT pid, query, state, backend_start
FROM pg_stat_activity
WHERE query LIKE 'autovacuum%';
</code>

==== Detailed Progress (PostgreSQL 10+) ====
This query shows exactly which table is being processed, the current phase, and how long it has been running:
<code>
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;
</code>

===== 3. Table Statistics (History) =====

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).

<code>
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;
</code>

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.

===== 4. Common Issues =====

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'').
===== 5. Useful Commands (Terminal) =====

If you do not have access to an SQL console, you can check the worker process directly from the Linux terminal:
<code>
ps -axww | grep autovacuum
</code>

Official Documentation: [[https://www.postgresql.org/docs/current/routine-vacuuming.html#AUTOVACUUM|PostgreSQL Automatic Vacuuming]]