PostgreSQL Autovacuum Error - Diagnosis and Resolution

# PostgreSQL Autovacuum Error - Diagnosis and Resolution

Autovacuum is PostgreSQL's automatic maintenance process that reclaims space from deleted or updated rows and prevents transaction ID wraparound. When autovacuum fails or runs incorrectly, tables become bloated and performance degrades significantly.

Understanding Autovacuum Errors

Autovacuum errors typically appear in the logs with specific messages:

```bash # Check for autovacuum-related errors sudo grep -i "autovacuum|vacuum" /var/log/postgresql/postgresql-*-main.log | tail -50

# Common messages: # - "autovacuum launcher started" # - "autovacuum worker processing" # - "cancelled autovacuum" # - "autovacuum launcher is shutting down" # - "WARNING: some databases have not been vacuumed" ```

Checking Autovacuum Status

```sql -- Check if autovacuum is running SELECT name, setting FROM pg_settings WHERE name LIKE '%autovacuum%';

-- View autovacuum activity SELECT pid, datname, relid::regclass AS table_name, state, query_start, EXTRACT(EPOCH FROM (now() - query_start)) AS seconds_running FROM pg_stat_activity WHERE query ILIKE '%autovacuum%';

-- Check autovacuum statistics per table SELECT schemaname, relname AS table_name, n_live_tup AS live_rows, n_dead_tup AS dead_rows, n_dead_tup::float / NULLIF(n_live_tup + n_dead_tup, 0) * 100 AS dead_ratio, last_vacuum, last_autovacuum, vacuum_count, autovacuum_count FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 20; ```

Autovacuum Not Running

If autovacuum isn't processing tables:

```bash # Check if autovacuum is enabled psql -U postgres -c "SHOW autovacuum;"

# Should return 'on' ```

```sql -- Check autovacuum worker processes SELECT count(*) AS autovacuum_workers FROM pg_stat_activity WHERE query ILIKE '%autovacuum%';

-- Check if workers are available SELECT name, setting FROM pg_settings WHERE name = 'autovacuum_max_workers';

-- Default is 3, if all busy, new tables wait ```

Why Autovacuum Might Not Trigger

-- Check if table meets vacuum threshold
SELECT 
    schemaname,
    relname,
    n_dead_tup,
    n_live_tup,
    relpages,
    current_setting('autovacuum_vacuum_threshold')::int AS threshold,
    (current_setting('autovacuum_vacuum_scale_factor')::float * n_live_tup + 
     current_setting('autovacuum_vacuum_threshold')::int) AS trigger_threshold,
    CASE 
        WHEN n_dead_tup > (current_setting('autovacuum_vacuum_scale_factor')::float * n_live_tup + 
                          current_setting('autovacuum_vacuum_threshold')::int)
        THEN 'NEEDS VACUUM'
        ELSE 'OK'
    END AS status
FROM pg_stat_user_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY n_dead_tup DESC
LIMIT 10;

Force Autovacuum on Specific Tables

```sql -- Manually trigger vacuum on a specific table VACUUM (VERBOSE) your_table_name;

-- For tables with many dead rows, use VACUUM FULL (locks table!) VACUUM FULL VERBOSE your_table_name;

-- Better: use pg_repack for online rebuild (requires extension) -- pg_repack --table=your_table_name your_database ```

Autovacuum Being Cancelled

Autovacuum workers can be cancelled by conflicting operations:

# Look for cancelled autovacuum messages
sudo grep "cancelled autovacuum" /var/log/postgresql/postgresql-*-main.log

-- Check for lock conflicts
SELECT 
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_statement,
    blocking_activity.query AS blocking_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

Reduce Autovacuum Disruption

```bash # Edit postgresql.conf sudo nano /etc/postgresql/16/main/postgresql.conf

# Make autovacuum less aggressive (less likely to conflict) autovacuum_vacuum_cost_delay = 20ms # Default 2ms, increase to slow down autovacuum_vacuum_cost_limit = 200 # Default -1 (use vacuum_cost_limit)

# These settings make autovacuum "nicer" but take longer # Good for busy systems where autovacuum gets cancelled frequently

# Reload sudo systemctl reload postgresql ```

Transaction ID Wraparound Prevention

The most critical autovacuum task is preventing transaction ID wraparound:

# Check for wraparound warnings
sudo grep -i "wraparound\|transaction id" /var/log/postgresql/postgresql-*-main.log

```sql -- Monitor transaction age per table SELECT c.oid::regclass AS table_name, pg_size_pretty(pg_total_relation_size(c.oid)) AS size, age(relfrozenxid) AS xid_age, mxid_age(relminmxid) AS mxid_age, current_setting('autovacuum_freeze_max_age')::bigint - age(relfrozenxid) AS xids_until_forced_vacuum FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE c.relkind IN ('r', 't', 'm') AND n.nspname NOT IN ('pg_catalog', 'information_schema') ORDER BY age(relfrozenxid) DESC LIMIT 20;

-- Tables with age > 150 million need attention -- Tables with age > 200 million trigger forced autovacuum -- Maximum is 2 billion, wraparound occurs at ~4 billion ```

Fixing High Transaction Age

```sql -- For tables approaching wraparound, force freeze VACUUM (FREEZE, VERBOSE) critical_table;

-- For entire database (runs longer but ensures freeze) VACUUM (FREEZE, ANALYZE, VERBOSE);

-- Or per table with monitoring DO $$ DECLARE r RECORD; BEGIN FOR r IN SELECT oid::regclass AS tbl FROM pg_class WHERE relkind = 'r' AND age(relfrozenxid) > 100000000 LOOP EXECUTE 'VACUUM (FREEZE, ANALYZE) ' || r.tbl; RAISE NOTICE 'Frozen table: %', r.tbl; END LOOP; END $$; ```

Table Bloat from Ineffective Autovacuum

```sql -- Estimate table bloat using pgstattuple extension CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size, pgstattuple_approx(schemaname || '.' || tablename) AS tuple_info FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema') ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC LIMIT 10; ```

Simple Bloat Estimate Query

-- Estimate bloat without extensions
WITH bloat_info AS (
    SELECT 
        schemaname,
        tablename,
        pg_total_relation_size(schemaname || '.' || tablename) AS total_size,
        seq_scan,
        seq_tup_read,
        n_live_tup,
        n_dead_tup
    FROM pg_stat_user_tables
)
SELECT 
    schemaname || '.' || tablename AS table_name,
    pg_size_pretty(total_size) AS size,
    n_live_tup AS live_rows,
    n_dead_tup AS dead_rows,
    CASE 
        WHEN n_live_tup > 0 
        THEN round(n_dead_tup::numeric / n_live_tup * 100, 2)
        ELSE 0 
    END AS dead_pct,
    seq_scan
FROM bloat_info
WHERE total_size > 1024 * 1024  -- Tables larger than 1MB
ORDER BY total_size DESC
LIMIT 20;

Fixing Bloat

```sql -- Option 1: Regular vacuum (if dead tuples not yet reused) VACUUM (VERBOSE, ANALYZE) bloated_table;

-- Option 2: VACUUM FULL (locks table, rebuilds completely) VACUUM FULL bloated_table;

-- Option 3: pg_repack (online rebuild, no locks) -- Requires pg_repack extension and command-line tool -- pg_repack --table=bloated_table your_database

-- Option 4: CLUSTER (reorganizes table, locks during operation) CLUSTER bloated_table USING bloated_table_pkey; ```

Tuning Autovacuum for Your Workload

# Edit postgresql.conf
sudo nano /etc/postgresql/16/main/postgresql.conf

```ini # Basic autovacuum settings autovacuum = on # Must be on! autovacuum_max_workers = 3 # Number of worker processes autovacuum_naptime = 1min # Time between worker launches

# When to trigger autovacuum autovacuum_vacuum_threshold = 50 # Base threshold autovacuum_vacuum_scale_factor = 0.2 # 20% dead tuples triggers autovacuum_analyze_threshold = 50 # Base threshold for analyze autovacuum_analyze_scale_factor = 0.1 # 10% changes triggers analyze

# How fast autovacuum works autovacuum_vacuum_cost_delay = 2ms # Sleep time per cost unit autovacuum_vacuum_cost_limit = -1 # -1 uses vacuum_cost_limit

# Freeze settings autovacuum_freeze_max_age = 200000000 # Force freeze at this age autovacuum_multixact_freeze_max_age = 400000000

# Per-table overrides (set on specific tables) # ALTER TABLE busy_table SET (autovacuum_vacuum_scale_factor = 0.1); # ALTER TABLE busy_table SET (autovacuum_vacuum_cost_limit = 1000); ```

Apply Per-Table Settings

```sql -- For frequently updated tables ALTER TABLE high_activity_table SET (autovacuum_vacuum_scale_factor = 0.05); -- Vacuum at 5% dead tuples

-- For large tables that need more aggressive vacuum ALTER TABLE large_table SET ( autovacuum_vacuum_scale_factor = 0.1, autovacuum_vacuum_cost_limit = 2000 -- Allow faster vacuum );

-- For tables where vacuum was cancelled frequently ALTER TABLE problem_table SET (autovacuum_vacuum_cost_delay = 10ms); -- Slower but less disruptive

-- Reset to defaults ALTER TABLE some_table RESET (autovacuum_vacuum_scale_factor); ```

Monitoring Autovacuum Health

```sql -- Create monitoring view CREATE OR REPLACE VIEW autovacuum_health AS SELECT schemaname || '.' || relname AS table_name, pg_size_pretty(pg_total_relation_size(schemaname || '.' || relname)) AS size, n_live_tup AS live, n_dead_tup AS dead, round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct, last_autovacuum, last_autoanalyze, age(relfrozenxid) AS xid_age, (current_setting('autovacuum_freeze_max_age')::bigint - age(relfrozenxid)) AS xids_remaining FROM pg_stat_user_tables t JOIN pg_class c ON c.oid = (schemaname || '.' || relname)::regclass WHERE n_dead_tup > 1000 ORDER BY n_dead_tup DESC;

-- Query for health check SELECT * FROM autovacuum_health LIMIT 20; ```

Common Autovacuum Error Patterns

1. "autovacuum launcher is shutting down"

# PostgreSQL is stopping, wait for restart
sudo systemctl status postgresql

2. "could not open file" during autovacuum

```bash # Check file permissions ls -la /var/lib/postgresql/16/main/base/

# Fix if needed sudo chown -R postgres:postgres /var/lib/postgresql/16/main ```

3. "out of memory" during autovacuum

```sql -- Reduce maintenance work memory ALTER SYSTEM SET maintenance_work_mem = '256MB'; SELECT pg_reload_conf();

-- Or set per-session during manual vacuum SET maintenance_work_mem = '512MB'; VACUUM VERBOSE large_table; ```

When autovacuum issues occur, the key is identifying whether it's a configuration problem, a resource constraint, or a workload-specific issue. Regular monitoring of dead tuples and transaction age prevents most autovacuum emergencies.