VACUUM e autovacuum no PostgreSQL

-- Entender o problema:
-- No PostgreSQL, UPDATE cria uma NOVA versão da linha (MVCC)
-- A versão antiga fica como "dead tuple" — ocupa espaço mas é invisível

-- Simular bloat:
CREATE TABLE teste (id serial, valor text);
INSERT INTO teste SELECT generate_series(1, 100000), 'dados iniciais';
UPDATE teste SET valor = 'atualizado';  -- cria 100k dead tuples!

-- Ver tamanho real vs tamanho útil da tabela:
SELECT
  relname AS tabela,
  pg_size_pretty(pg_relation_size(oid)) AS tamanho_total,
  pg_size_pretty(pg_relation_size(oid) - (reltuples * 100)) AS estimativa_bloat,
  n_dead_tup AS dead_tuples,
  n_live_tup AS live_tuples,
  round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS pct_mortas
FROM pg_stat_user_tables
WHERE relname = 'teste';

-- Verificar quando o autovacuum rodou por último:
SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

-- VACUUM simples: limpa dead tuples mas NÃO devolve espaço ao SO:
VACUUM tabela_grande;

-- VACUUM ANALYZE: limpa + atualiza estatísticas do planejador:
VACUUM ANALYZE tabela_grande;

-- VACUUM FULL: limpa + reescreve a tabela compactada (devolve espaço ao SO):
-- ATENÇÃO: bloqueia a tabela! Não usar em produção sem manutenção
VACUUM FULL tabela_grande;

-- VACUUM VERBOSE: mostrar progresso detalhado:
VACUUM VERBOSE ANALYZE tabela_grande;

-- Para desfragmentar sem bloquear: usar pg_repack (extensão):
-- apt install postgresql-16-repack
-- pg_repack -d producao -t tabela_grande

-- Ver progresso do VACUUM em execução (PostgreSQL 9.6+):
SELECT phase, heap_blks_scanned, heap_blks_total,
       round(100.0 * heap_blks_scanned / nullif(heap_blks_total, 0), 1) AS pct
FROM pg_stat_progress_vacuum;

-- Executar VACUUM em todo o banco (apenas para manutenção):
-- vacuumdb -U postgres -d producao --analyze --verbose

-- Ver configuração atual do autovacuum:
SELECT name, setting, unit FROM pg_settings WHERE name LIKE '%autovacuum%';

-- Configurações globais no postgresql.conf:
autovacuum = on                          # nunca desativar!
autovacuum_max_workers = 3               # workers paralelos (default: 3)
autovacuum_naptime = 1min                # intervalo entre verificações

# Quando acionar VACUUM (por tabela):
autovacuum_vacuum_threshold = 50         # mínimo de dead tuples
autovacuum_vacuum_scale_factor = 0.2     # 20% da tabela com dead tuples

# Quando acionar ANALYZE:
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.1   # 10% da tabela modificada

# Throttle para não sobrecarregar I/O:
autovacuum_vacuum_cost_delay = 2ms       # pausa entre páginas (default: 2ms)
autovacuum_vacuum_cost_limit = 200       # custo por ciclo antes de pausar

-- Configuração POR TABELA (mais granular):
-- Para tabelas com muitos UPDATEs (sessions, eventos):
ALTER TABLE sessoes_usuario SET (
  autovacuum_vacuum_scale_factor = 0.01,  -- acionar com 1% de dead tuples
  autovacuum_vacuum_threshold = 100,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_vacuum_cost_delay = 1        -- mais agressivo no I/O
);

-- Para tabelas mostly read-only (quase sem updates):
ALTER TABLE produtos_catalogo SET (
  autovacuum_vacuum_scale_factor = 0.5,   -- acionar só com 50% de dead tuples
  autovacuum_naptime = 3600               -- verificar a cada hora
);

-- O PostgreSQL usa Transaction IDs (XID) de 32 bits — máximo: 2 bilhões
-- Quando o XID se aproxima do limite: PostgreSQL PARA de aceitar writes
-- para evitar corrupção (transaction wraparound)

-- Monitorar XID age de cada banco (CRÍTICO):
SELECT
  datname,
  age(datfrozenxid) AS xid_age,
  2147483648 - age(datfrozenxid) AS transacoes_restantes,
  round(100.0 * age(datfrozenxid) / 2147483648, 1) AS pct_consumido
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

-- Monitorar por tabela:
SELECT
  relname,
  age(relfrozenxid) AS xid_age,
  round(100.0 * age(relfrozenxid) / 2147483648, 1) AS pct
FROM pg_class
WHERE relkind = 'r'
ORDER BY age(relfrozenxid) DESC
LIMIT 20;

-- Alerta: se xid_age > 1.5 bilhões → executar VACUUM FREEZE urgente
-- postgresql.conf — acionar VACUUM FREEZE preventivo:
vacuum_freeze_min_age = 50000000          -- congelar XIDs com mais de 50M transações
vacuum_freeze_table_age = 150000000       -- forçar VACUUM FREEZE na tabela inteira
autovacuum_freeze_max_age = 200000000     -- VACUMM FREEZE preventivo automático

-- Se o banco estiver próximo do limite (acima de 1.9B):
-- VACUUM FREEZE producao;  (pode levar horas, mas evita shutdown)

-- Query para detectar bloat em tabelas:
SELECT
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS tamanho_total,
  n_dead_tup,
  n_live_tup,
  round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS pct_bloat
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

-- Detectar bloat em índices:
SELECT
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS tamanho_indice,
  idx_scan AS leituras
FROM pg_stat_user_indexes
JOIN pg_class ON pg_class.oid = indexrelid
WHERE pg_relation_size(indexrelid) > 10 * 1024 * 1024  -- > 10 MB
ORDER BY pg_relation_size(indexrelid) DESC;

-- Reconstruir índice inchado sem bloquear:
REINDEX INDEX CONCURRENTLY idx_meu_indice_grande;
-- ou todos os índices de uma tabela:
REINDEX TABLE CONCURRENTLY minha_tabela;

-- pg_repack: compactar tabela sem lock (melhor que VACUUM FULL):
-- pg_repack -d producao --table minha_tabela --no-order
-- (requer extensão: CREATE EXTENSION pg_repack;)