Autovacuum PostgreSQL: Полное руководство по настройке и оптимизации

# Пороговые значения (отношение dead tuples к общему числу строк)
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.2

# Формула запуска: dead_tuples > vacuum_threshold + vacuum_scale_factor * total_tuples

# Для анализа (обновления статистики)
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.1

-- Просмотр статистики по таблицам
SELECT 
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC;

-- Наиболее "грязные" таблицы
SELECT 
    schemaname || '.' || relname AS table_name,
    n_dead_tup,
    n_live_tup,
    round(n_dead_tup::numeric * 100 / (n_live_tup + n_dead_tup), 2) AS dead_percent
FROM pg_stat_user_tables
WHERE n_live_tup > 0
ORDER BY dead_percent DESC
LIMIT 10;

# Основные настройки autovacuum в PostgreSQL
# В файле postgresql.conf

# Всегда включен в production
autovacuum = on

# Увеличиваем количество воркеров для параллельной обработки
autovacuum_max_workers = 5

# Увеличиваем лимит стоимости для более агрессивной очистки
autovacuum_vacuum_cost_limit = 1000

# Уменьшаем scale_factor для больших таблиц (глобально, но лучше настраивать таблицы отдельно)
autovacuum_vacuum_scale_factor = 0.05  # 5% вместо 20%
autovacuum_analyze_scale_factor = 0.02  # 2% вместо 10%

# Увеличиваем пороги для очень маленьких таблиц
autovacuum_vacuum_threshold = 100
autovacuum_analyze_threshold = 100

# Настройки для предотвращения wraparound
autovacuum_freeze_max_age = 200000000  # 200 миллионов транзакций

-- Для больших, часто обновляемых таблиц
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.01,  -- 1%
    autovacuum_vacuum_threshold = 1000,
    autovacuum_analyze_scale_factor = 0.005,  -- 0.5%
    autovacuum_vacuum_cost_delay = 2ms
);

-- Для маленьких справочников (редко обновляемых)
ALTER TABLE product_categories SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_vacuum_threshold = 50,
    autovacuum_enabled = false  -- можно отключить для статических таблиц
);

-- Для таблиц с высокой частотой UPDATE/DELETE
ALTER TABLE user_sessions SET (
    autovacuum_vacuum_scale_factor = 0.02,
    autovacuum_vacuum_threshold = 500,
    autovacuum_freeze_min_age = 5000000,
    toast.autovacuum_vacuum_scale_factor = 0.05
);

-- Настройка autovacuum для TOAST-таблицы
ALTER TABLE large_content_table 
SET (toast.autovacuum_vacuum_scale_factor = 0.05,
     toast.autovacuum_vacuum_threshold = 1000);

-- Текущие запущенные процессы autovacuum
SELECT 
    datname,
    usename,
    application_name,
    state,
    query,
    age(now(), query_start) AS duration
FROM pg_stat_activity 
WHERE application_name LIKE 'autovacuum%' 
   OR query LIKE 'autovacuum%';

-- Статистика по autovacuum за все время
SELECT 
    schemaname,
    relname,
    last_autovacuum,
    last_autoanalyze,
    autovacuum_count,
    autoanalyze_count
FROM pg_stat_user_tables
ORDER BY autovacuum_count DESC;

-- Таблицы, которые давно не очищались
SELECT 
    schemaname,
    relname,
    now() - last_autovacuum AS time_since_last_vacuum,
    now() - last_autoanalyze AS time_since_last_analyze,
    n_dead_tup
FROM pg_stat_user_tables
WHERE (now() - last_autovacuum) > interval '7 days'
   OR last_autovacuum IS NULL
ORDER BY n_dead_tup DESC;

# В postgresql.conf для отладки
log_autovacuum_min_duration = 0  # Логировать все autovacuum операции
# или
log_autovacuum_min_duration = 1000  # Логировать только медленные (>1 сек)

-- Проверка угрозы wraparound
SELECT 
    datname,
    age(datfrozenxid) AS frozen_age,
    current_setting('autovacuum_freeze_max_age') AS max_age
FROM pg_database
ORDER BY frozen_age DESC;

-- Таблицы с высоким риском
SELECT 
    schemaname,
    relname,
    age(relfrozenxid) AS xid_age,
    pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM pg_stat_user_tables
WHERE age(relfrozenxid) > 100000000  -- 100M транзакций
ORDER BY xid_age DESC;