Tuning de performance do PostgreSQL

# postgresql.conf — configurações por RAM disponível

# === VPS 2 GB RAM ===
shared_buffers = 512MB
effective_cache_size = 1536MB
work_mem = 16MB
maintenance_work_mem = 128MB
max_connections = 50

# === VPS 4 GB RAM ===
shared_buffers = 1GB
effective_cache_size = 3GB
work_mem = 32MB
maintenance_work_mem = 256MB
max_connections = 100

# === VPS 8 GB RAM ===
shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 64MB
maintenance_work_mem = 512MB
max_connections = 200

# === Parâmetros comuns para todos os tamanhos ===
wal_buffers = 16MB
checkpoint_completion_target = 0.9
default_statistics_target = 100
random_page_cost = 1.1          # SSD: 1.1, HDD: 4.0
effective_io_concurrency = 200  # SSD: 200, HDD: 2

# Aplicar sem reiniciar (para parâmetros que suportam):
SELECT pg_reload_conf();

-- Ver tabelas com muitos seq scans (candidatas a índice):
SELECT schemaname, tablename, seq_scan, seq_tup_read,
       idx_scan, idx_tup_fetch,
       n_live_tup AS linhas
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_scan DESC
LIMIT 20;

-- Ver índices que nunca são usados (candidatos a remoção):
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE 'pg_%'
ORDER BY tablename;

-- EXPLAIN ANALYZE para ver o plano de execução:
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM pedidos
WHERE usuario_id = 123
  AND status = 'pendente'
ORDER BY criado_em DESC
LIMIT 10;

-- Se mostrar Seq Scan, criar índice:
CREATE INDEX CONCURRENTLY idx_pedidos_usuario_status
  ON pedidos (usuario_id, status, criado_em DESC);
-- CONCURRENTLY cria o índice sem bloquear a tabela

-- Executar e interpretar o plano:
EXPLAIN (ANALYZE, BUFFERS)
SELECT u.nome, count(p.id) AS total_pedidos
FROM usuarios u
LEFT JOIN pedidos p ON p.usuario_id = u.id
WHERE u.criado_em > '2025-01-01'
GROUP BY u.id
ORDER BY total_pedidos DESC
LIMIT 100;

-- O que observar:
-- "Seq Scan" em tabelas grandes: falta índice
-- "actual time=X" alto: operação lenta
-- "rows=100 / actual rows=10000": estatísticas desatualizadas
--   → resolver com: ANALYZE nome_da_tabela;
-- "Buffers: shared hit=X read=Y": X = cache, Y = disco
--   → Y alto = falta memória (aumentar shared_buffers)
-- "Hash Join" vs "Nested Loop": Hash melhor para tabelas grandes

-- Usar pgMustard ou explain.dalibo.com para análise visual
-- (cole o resultado do EXPLAIN no site)

-- Verificar estado do autovacuum nas tabelas:
SELECT schemaname, relname,
       n_live_tup, n_dead_tup,
       last_autovacuum, last_autoanalyze,
       autovacuum_count, autoanalyze_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

-- Tabela com muitos dead tuples: forçar VACUUM manual
VACUUM ANALYZE pedidos;

-- Para tabelas com alta taxa de UPDATE/DELETE — ajustar autovacuum:
ALTER TABLE pedidos SET (
  autovacuum_vacuum_scale_factor = 0.01,    -- 1% (padrão: 20%)
  autovacuum_analyze_scale_factor = 0.005,  -- 0.5%
  autovacuum_vacuum_cost_delay = 2          -- ms (padrão: 2ms)
);

-- postgresql.conf — configurações globais de autovacuum
autovacuum_max_workers = 3
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02
autovacuum_vacuum_cost_delay = 2ms

-- Habilitar pg_stat_statements (postgresql.conf):
-- shared_preload_libraries = 'pg_stat_statements'
-- pg_stat_statements.max = 10000
-- pg_stat_statements.track = all
-- Reiniciar: sudo systemctl restart postgresql

-- Criar extensão no banco:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 10 queries por tempo total:
SELECT round(total_exec_time::numeric / 1000, 2) AS total_seg,
       calls,
       round(mean_exec_time::numeric, 2) AS media_ms,
       round(stddev_exec_time::numeric, 2) AS stddev_ms,
       query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Resetar estatísticas (após otimizações):
SELECT pg_stat_statements_reset();

-- Queries lentas no log (postgresql.conf):
log_min_duration_statement = 500   # logar queries > 500ms