Autovacuum Tuning and Maintenance

Intermediate12 min

Configure and monitor PostgreSQL autovacuum to reclaim dead tuples, prevent table bloat, and avoid transaction ID wraparound.

Prerequisites

-PostgreSQL database with write activity
-Superuser access for configuration changes

Steps

Check current autovacuum settings

View the running autovacuum configuration parameters.

$ psql -U postgres -c "SELECT name, setting, short_desc FROM pg_settings WHERE name LIKE 'autovacuum%' ORDER BY name;"

Find tables needing vacuum

Identify tables with the most dead tuples that are waiting for vacuum.

$ psql -U postgres -d mydb -c "SELECT relname, n_live_tup, n_dead_tup, round(n_dead_tup::numeric / GREATEST(n_live_tup, 1) * 100, 2) AS dead_pct, last_autovacuum FROM pg_stat_user_tables WHERE n_dead_tup > 1000 ORDER BY n_dead_tup DESC LIMIT 10;"

Run manual vacuum with verbose output

Manually vacuum a specific table and see detailed statistics.

$ psql -U postgres -d mydb -c "VACUUM (VERBOSE, ANALYZE) orders;"

ANALYZE updates table statistics for the query planner. Always combine VACUUM with ANALYZE.

Check table bloat

Estimate wasted space from dead rows in frequently updated tables.

$ psql -U postgres -d mydb -c "SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS total_size, n_dead_tup, last_autovacuum, last_autoanalyze FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;"

Tune autovacuum for a heavy-write table

Override autovacuum settings at the table level for high-throughput tables.

$ psql -U postgres -d mydb -c "ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.05, autovacuum_analyze_scale_factor = 0.02, autovacuum_vacuum_cost_delay = 10);"

Lower scale factors trigger vacuum sooner. Default is 0.2 (20% dead tuples). Set 0.05 for active tables.

Full Script

full-script.sh

#!/bin/bash
# PostgreSQL Vacuum Maintenance Report
set -euo pipefail

DB="mydb"
USER="postgres"

echo "=== Autovacuum Configuration ==="
psql -U "$USER" -c "
SELECT name, setting
FROM pg_settings
WHERE name LIKE 'autovacuum%'
ORDER BY name;
"

echo ""
echo "=== Tables with most dead tuples ==="
psql -U "$USER" -d "$DB" -c "
SELECT relname AS table_name,
       n_live_tup AS live_rows,
       n_dead_tup AS dead_rows,
       round(n_dead_tup::numeric / GREATEST(n_live_tup, 1) * 100, 2) AS dead_pct,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
       last_autovacuum,
       last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 15;
"

echo ""
echo "=== Transaction ID age (wraparound risk) ==="
psql -U "$USER" -d "$DB" -c "
SELECT relname,
       age(relfrozenxid) AS xid_age,
       pg_size_pretty(pg_total_relation_size(oid)) AS size
FROM pg_class
WHERE relkind = 'r' AND relnamespace = 'public'::regnamespace
ORDER BY age(relfrozenxid) DESC
LIMIT 10;
"

echo ""
echo "=== Currently running vacuums ==="
psql -U "$USER" -c "
SELECT pid, datname, query, state, now() - query_start AS duration
FROM pg_stat_activity
WHERE query LIKE '%VACUUM%' OR query LIKE '%vacuum%';
"

FAQ

Discussion

Loading comments...

#!/bin/bash # PostgreSQL Vacuum Maintenance Report set -euo pipefail DB="mydb" USER="postgres" echo "=== Autovacuum Configuration ===" psql -U "$USER" -c " SELECT name, setting FROM pg_settings WHERE name LIKE 'autovacuum%' ORDER BY name; " echo "" echo "=== Tables with most dead tuples ===" psql -U "$USER" -d "$DB" -c " SELECT relname AS table_name, n_live_tup AS live_rows, n_dead_tup AS dead_rows, round(n_dead_tup::numeric / GREATEST(n_live_tup, 1) * 100, 2) AS dead_pct, pg_size_pretty(pg_total_relation_size(relid)) AS total_size, last_autovacuum, last_autoanalyze FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 15; " echo "" echo "=== Transaction ID age (wraparound risk) ===" psql -U "$USER" -d "$DB" -c " SELECT relname, age(relfrozenxid) AS xid_age, pg_size_pretty(pg_total_relation_size(oid)) AS size FROM pg_class WHERE relkind = 'r' AND relnamespace = 'public'::regnamespace ORDER BY age(relfrozenxid) DESC LIMIT 10; " echo "" echo "=== Currently running vacuums ===" psql -U "$USER" -c " SELECT pid, datname, query, state, now() - query_start AS duration FROM pg_stat_activity WHERE query LIKE '%VACUUM%' OR query LIKE '%vacuum%'; "