Diagnóstico e Tuning

Conceito

O Postgres não tem AWR como o Oracle. Ele tem algo diferente: um conjunto de views de estatística acumulada e um sistema de wait events, que juntos respondem a pergunta que importa num incidente:

O banco está esperando o quê?

Toda investigação segue a mesma ordem. Vale decorar:

1. Está esperando?         → pg_stat_activity (wait_event_type)
2. Está bloqueado?         → pg_locks / pg_blocking_pids()
3. Qual query custa mais?  → pg_stat_statements
4. Por que essa query?     → EXPLAIN (ANALYZE, BUFFERS)
5. É manutenção?           → pg_stat_user_tables (dead_tup, last_autovacuum)

Wait events: a taxonomia

pg_stat_activity.wait_event_type diz a categoria do bloqueio, e ela aponta direto para a causa raiz:

Um wait_event_type = 'Client' com state = 'idle in transaction' é o achado mais valioso: significa que a aplicação abriu transação e foi fazer outra coisa — e isso bloqueia VACUUM (4. MVCC, Tuplas e VACUUM) e segura locks.

pg_stat_statements: por onde o tempo vai

É a extensão mais importante do Postgres. Ela normaliza as queries (substitui literais por $1) e acumula tempo, chamadas, I/O e uso de cache por query id.

CREATE EXTENSION pg_stat_statements;   -- + shared_preload_libraries

-- o ranking que importa: tempo TOTAL, não tempo médio
SELECT round(total_exec_time::numeric / 1000, 1) AS total_s,
       calls,
       round(mean_exec_time::numeric, 2)         AS media_ms,
       round(100.0 * shared_blks_hit /
             nullif(shared_blks_hit + shared_blks_read, 0), 1) AS cache_pct,
       rows / nullif(calls, 0)                   AS linhas_por_call,
       left(query, 80)
  FROM pg_stat_statements
 ORDER BY total_exec_time DESC LIMIT 15;

Locks: quem está bloqueando quem

-- a árvore de bloqueio, direto ao ponto
SELECT bloqueado.pid       AS pid_bloqueado,
       bloqueado.query     AS query_bloqueada,
       bloqueador.pid      AS pid_bloqueador,
       bloqueador.query    AS query_bloqueadora,
       bloqueador.state    AS estado_bloqueador,
       now() - bloqueado.query_start AS esperando_ha
  FROM pg_stat_activity bloqueado
  JOIN pg_stat_activity bloqueador
    ON bloqueador.pid = ANY(pg_blocking_pids(bloqueado.pid))
 WHERE cardinality(pg_blocking_pids(bloqueado.pid)) > 0;

O padrão de incidente mais comum aparece assim: um ALTER TABLE esperando lock, e 300 SELECT esperando atrás dele. O Postgres enfileira locks de forma justa (FIFO), então um DDL que espera bloqueia tudo que chega depois, inclusive leituras que seriam compatíveis entre si.

-- a defesa, sempre, antes de DDL:
SET lock_timeout = '3s';
ALTER TABLE grande ADD COLUMN x int;   -- falha rápido em vez de travar o serviço

Trade-offs

Observabilidade custa alguma performance. track_io_timing = on, log_min_duration_statement baixo, auto_explain e pg_stat_statements com track = all têm overhead (pequeno, mas real). O overhead de não ter isso durante um incidente é muito maior — ligue todos, e apenas evite log_statement = 'all' em banco de alto volume.

Tuning de parâmetro raramente é a solução. A ordem de retorno sobre esforço é quase sempre: query/índice/schema ≫ manutenção (vacuum/estatística) ≫ parâmetros de memória ≫ hardware. Times gastam semanas em postgresql.conf quando um índice parcial resolveria em cinco minutos (5. Índices e o Planejador).

Reset de estatística apaga histórico. pg_stat_statements_reset() é útil para medir uma janela específica, e destrói a base de comparação. Colete em série temporal (Prometheus + postgres_exporter, pgwatch, pganalyze) em vez de olhar snapshots.

O trade-off central: o Postgres oferece dados crus e nenhuma conclusão. Isso o torna honesto e exige método: sem a sequência espera → bloqueio → query → plano, você fica mexendo em parâmetros no escuro.

Exemplo prático

Baseline de observabilidade

shared_preload_libraries = 'pg_stat_statements,auto_explain'
pg_stat_statements.max = 10000
pg_stat_statements.track = all         # inclui statements dentro de funções
track_io_timing = on
track_functions = pl

log_min_duration_statement = 1000      # loga query acima de 1s
log_lock_waits = on                    # loga quem espera lock > deadlock_timeout
log_autovacuum_min_duration = 0        # veja todo autovacuum
log_temp_files = 0                     # denuncia work_mem insuficiente
log_checkpoints = on
log_line_prefix = '%m [%p] %u@%d app=%a '   # aplicação no log = ouro em incidente

auto_explain.log_min_duration = 3000   # plano completo de queries > 3s
auto_explain.log_analyze = on
auto_explain.log_buffers = on

Playbook: "o banco está lento agora"

-- 1. panorama: quantos ativos, esperando o quê
SELECT state, wait_event_type, wait_event, count(*)
  FROM pg_stat_activity WHERE backend_type = 'client backend'
 GROUP BY 1,2,3 ORDER BY 4 DESC;

-- 2. as 10 queries mais longas rodando agora
SELECT pid, now() - query_start AS duracao, wait_event_type, wait_event,
       left(query, 100)
  FROM pg_stat_activity
 WHERE state = 'active' AND backend_type = 'client backend'
 ORDER BY query_start LIMIT 10;

-- 3. tem bloqueio? (query da seção anterior)

-- 4. matar o culpado, se necessário
SELECT pg_cancel_backend(12345);   -- tenta cancelar a query (educado)
SELECT pg_terminate_backend(12345);-- mata a conexão (último recurso)

As 8 métricas de dashboard

Como isso falha em produção

Aprofundamento aplicado

Separar causa imediata de causa que iniciou a fila

Uma consulta que normalmente leva 5 ms pode esperar 30 s por um lock. Otimizar seu índice não remove a transação bloqueadora. Comece localizando o tipo de espera e a cadeia de bloqueios; depois examine por que o bloqueador permanece aberto.

Exercício: crie uma sessão que atualiza uma linha e não confirma; faça outra tentar atualizá-la. Resultado esperado: identifique a primeira sessão como bloqueadora, mesmo que ela apareça sem executar SQL naquele instante. Só encerre sessões de estudo.

Uma boa comparação registra consulta, parâmetros, volume, concorrência e janela de medição. Comparar tempo com cache quente a tempo com cache frio confunde a conclusão. Referência: estatísticas e eventos de espera.

Relacionado

• 5. Índices e o Planejador
• 4. MVCC, Tuplas e VACUUM
• 2. Arquitetura de Processos e Memória
• 6. Particionamento e Pooling de Conexões
• SQL Tuning
• Monitoring
• 4. Otimizador, Planos e Bind Variables

---

Parte de MOC PostgreSQL · Monitoring (docs)