Monitorar PostgreSQL: queries lentas e estatísticas

# postgresql.conf — ativar a extensão:
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000      # manter até 10k queries distintas
pg_stat_statements.track = all      # rastrear queries de clientes e funções
pg_stat_statements.track_utility = on  # rastrear COPY, CREATE, etc.

# Reiniciar o PostgreSQL:
sudo systemctl restart postgresql

# Criar a extensão no banco:
psql -U postgres -d producao -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"

# Verificar que está ativa:
psql -U postgres -d producao -c "SELECT * FROM pg_extension WHERE extname='pg_stat_statements';"

# Conceder acesso ao usuário da aplicação para monitoramento:
psql -U postgres -c "GRANT pg_monitor TO app_user;"

-- Top 10 queries por tempo total (candidatas a otimização):
SELECT
  left(query, 100) AS query_resumida,
  calls,
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2) AS media_ms,
  round(stddev_exec_time::numeric, 2) AS desvio_ms,
  rows,
  round(100.0 * total_exec_time / sum(total_exec_time) OVER (), 2) AS pct_total
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat%'
ORDER BY total_exec_time DESC
LIMIT 10;

-- Top 10 queries por tempo médio (as mais lentas por execução):
SELECT
  left(query, 100) AS query_resumida,
  calls,
  round(mean_exec_time::numeric, 2) AS media_ms,
  round(max_exec_time::numeric, 2) AS max_ms
FROM pg_stat_statements
WHERE calls > 10  -- ignorar queries executadas poucas vezes
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Queries com maior uso de I/O (leituras de bloco):
SELECT
  left(query, 100) AS query_resumida,
  calls,
  blk_read_time + blk_write_time AS io_time_ms,
  shared_blks_read,
  shared_blks_hit,
  round(100.0 * shared_blks_hit /
    nullif(shared_blks_hit + shared_blks_read, 0), 1) AS cache_hit_pct
FROM pg_stat_statements
ORDER BY blk_read_time + blk_write_time DESC
LIMIT 10;

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

# postgresql.conf — ativar slow query log:
log_min_duration_statement = 1000   # logar queries > 1000ms (1 segundo)
log_duration = off                  # não logar TODAS as durações (muito ruído)
log_statement = 'none'              # não logar todos os statements

# Para depuração temporária (não em produção permanente):
# log_min_duration_statement = 100  # todas as queries > 100ms

# Aplicar sem reiniciar (apenas reload):
sudo -u postgres psql -c "SELECT pg_reload_conf();"
# ou: sudo systemctl reload postgresql

# Verificar no log:
sudo tail -f /var/log/postgresql/postgresql-*.log | grep "duration:"

# Exemplo de saída:
# 2026-06-08 02:15:43 UTC [1234] app_user@producao LOG:
#   duration: 2341.123 ms  statement: SELECT * FROM pedidos WHERE status = 'pendente'

# Parsear slow queries do log (resumo por tipo):
sudo grep "duration:" /var/log/postgresql/postgresql-*.log |   grep -oP "duration: d+.d+" |   awk '{sum+=$2; count++} END {print "Total:", count, "queries, Média:", sum/count, "ms"}'

-- Analisar como o PostgreSQL executa a query:
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT p.id, p.titulo, u.nome
FROM pedidos p
JOIN usuarios u ON u.id = p.usuario_id
WHERE p.status = 'pendente'
  AND p.criado_em > NOW() - INTERVAL '7 days'
ORDER BY p.criado_em DESC
LIMIT 20;

-- O que procurar na saída:
-- "Seq Scan" em tabela grande: precisa de índice!
-- "cost=0.00..999999.00": custo estimado alto
-- "actual time=5000..5001": tempo real alto
-- "rows=1000000 loops=3": muitas linhas sendo processadas
-- "Buffers: shared hit=5 read=99999": muitas leituras de disco

-- Adicionar índice para a query acima:
CREATE INDEX CONCURRENTLY idx_pedidos_status_criado
  ON pedidos (status, criado_em DESC)
  WHERE status = 'pendente';  -- partial index: só indexa pendentes

-- Rodar EXPLAIN ANALYZE novamente para comparar:
-- Antes: Seq Scan, cost=50000, actual time=800ms
-- Depois: Index Scan, cost=50, actual time=2ms

-- Ferramenta visual: explain.depesz.com ou explain.tensor.ru
-- Cole a saída do EXPLAIN para visualização gráfica

# docker-compose.yml — adicionar postgres_exporter:
services:
  postgres-exporter:
    image: prometheuscommunity/postgres-exporter:latest
    restart: always
    ports:
      - "127.0.0.1:9187:9187"
    environment:
      DATA_SOURCE_NAME: "postgresql://postgres:SENHA@localhost:5432/postgres?sslmode=disable"
    extra_hosts:
      - "host.docker.internal:host-gateway"

# prometheus.yml — adicionar scrape:
# - job_name: postgres
#   static_configs:
#     - targets: ['localhost:9187']

# Métricas importantes do PostgreSQL via postgres_exporter:
# pg_up                          — banco está respondendo?
# pg_database_size_bytes         — tamanho de cada banco
# pg_stat_user_tables_*          — estatísticas de tabelas
# pg_stat_activity_count         — conexões ativas
# pg_locks_count                 — locks no banco
# pg_replication_lag             — lag de replicação (se tiver réplica)
# pg_stat_bgwriter_*             — atividade do background writer

# Alertas no Alertmanager:
# pg_up == 0                     → banco fora do ar
# pg_stat_activity_count{state="idle in transaction"} > 5 → transações travadas
# pg_database_size_bytes > 50GB  → banco crescendo demais
# rate(pg_stat_user_tables_seq_scan[5m]) > 100 → sequential scans (sem índice)