Diagnosticando Banco

Diagnosticando Banco — Health Check PostgreSQL

Skill para diagnosticos de saude, performance e otimizacao do banco PostgreSQL.

ESCOPO: Esta skill executa checks read-only no banco. Nao modifica dados nem estrutura. Para consultas de dados de negocio, use consultando-sql. Para metricas de infra (CPU, memoria, HTTP), use mcp__render__get_metrics.

Quando NAO Usar Esta Skill

DECISION TREE — Qual Check Usar?

Combinando Checks

# Checks especificos combinados
health_check_banco.py --check unused_indexes cache_hit_rate connections

# Todos com resumo executivo
health_check_banco.py --all --resumo

Modos de Execucao

Modo 1: Script Local (dev/staging)

source .venv/bin/activate && \
python .claude/skills/diagnosticando-banco/scripts/health_check_banco.py --all --resumo

Modo 2: Render MCP (producao)

Para producao, executar as SQLs diretamente via mcp__render__query_render_postgres com postgresId = "dpg-d13m38vfte5s738t6p50-a".

SQLs prontas para uso direto — ver secao "SQL Templates para Render MCP" abaixo.

SQL Templates para Render MCP

Quando nao puder executar o script (ex: producao via agente web), usar estas queries diretamente:

Indices Nao Usados

SELECT s.relname AS tabela, s.indexrelname AS indice,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS tamanho,
       s.idx_scan AS scans
FROM pg_stat_user_indexes s
JOIN pg_index i ON s.indexrelid = i.indexrelid
WHERE s.idx_scan = 0 AND NOT i.indisunique AND NOT i.indisprimary
  AND s.schemaname = 'public'
ORDER BY pg_relation_size(s.indexrelid) DESC LIMIT 20;

Cache Hit Rate

SELECT round(sum(heap_blks_hit)::numeric /
  NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0) * 100, 2) AS hit_rate_pct
FROM pg_statio_user_tables;

Top Queries Lentas (requer pg_stat_statements)

SELECT substring(query, 1, 200) AS query, calls,
       round(total_exec_time::numeric, 2) AS tempo_total_ms,
       round(mean_exec_time::numeric, 2) AS tempo_medio_ms, rows
FROM pg_stat_statements
WHERE query NOT LIKE 'SET %' AND query NOT LIKE 'BEGIN%'
  AND userid = (SELECT usesysid FROM pg_user WHERE usename = current_user)
ORDER BY total_exec_time DESC LIMIT 10;

Conexoes Ativas

SELECT state, count(*) AS quantidade,
  max(extract(epoch from (now() - state_change)))::integer AS max_duracao_seg
FROM pg_stat_activity WHERE pid <> pg_backend_pid()
GROUP BY state ORDER BY quantidade DESC;

Maiores Tabelas

SELECT relname AS tabela,
  pg_size_pretty(pg_total_relation_size(relid)) AS tamanho_total,
  n_live_tup AS linhas_vivas, n_dead_tup AS linhas_mortas
FROM pg_stat_user_tables WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(relid) DESC LIMIT 20;

Sequences (Risco de Overflow INTEGER)

SELECT sequencename, last_value,
  round(last_value::numeric / 2147483647 * 100, 4) AS uso_pct
FROM pg_sequences WHERE schemaname = 'public' AND last_value IS NOT NULL
ORDER BY last_value DESC;

Nota: Query assume INTEGER (2.1B limite). No Render MCP, data_type causa erro de casting — usar versao simplificada acima.

Interpretacao de Resultados

Cache Hit Rate

Sequences

Vacuum

Script Principal

health_check_banco.py

source .venv/bin/activate && \
python .claude/skills/diagnosticando-banco/scripts/health_check_banco.py [opcoes]

Para parametros completos, retornos JSON e exemplos: LER SCRIPTS.md

Pre-requisitos

Referencia Cruzada