Como monitorar PostgreSQL em produção: cache hit, autovacuum, idade de XID, bloat e pg_stat_statements

O PostgreSQL não tem um painel de saúde embutido: tudo o que você precisa saber está espalhado em views de estatísticas (pg_stat_*) e no catálogo. A boa notícia é que cinco sinais cobrem a maior parte dos incidentes de produção — e todos podem ser consultados com SQL puro. Os exemplos abaixo valem para PostgreSQL 13 a 17; onde há diferença entre versões, está indicado.

1. Cache hit ratio: o banco está lendo da memória ou do disco?

A pg_stat_database conta, por banco, quantos blocos foram encontrados no shared_buffers (blks_hit) e quantos precisaram ser lidos fora dele (blks_read). Atenção: blks_read inclui leituras que podem ter sido atendidas pelo cache do sistema operacional, então o número não é leitura física pura — mas a tendência é o que importa.

SELECT datname,
       blks_hit,
       blks_read,
       round(100.0 * blks_hit / NULLIF(blks_hit + blks_read, 0), 2) AS cache_hit_pct
FROM pg_stat_database
WHERE datname IS NOT NULL
ORDER BY blks_read DESC;

Regra prática bastante repetida na comunidade: em carga transacional (OLTP), manter o cache hit em 99% ou mais. É uma regra de bolso, não um limite oficial — um banco de relatórios que varre tabelas grandes de propósito pode ter um valor menor sem estar doente. O que deve acender o alerta é a queda em relação ao seu próprio histórico, e o contador é cumulativo desde o último reset de estatísticas, então compare janelas (amostras diferenciadas), não só o valor absoluto.

2. Autovacuum: linhas mortas acumulando

O MVCC do PostgreSQL não sobrescreve linhas: UPDATE e DELETE deixam versões antigas (dead tuples) que o autovacuum precisa limpar. Se ele não acompanha o ritmo de escrita, a tabela incha e as queries ficam mais lentas.

SELECT schemaname, relname,
       n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Procure tabelas grandes com dead_pct crescendo e last_autovacuum antigo ou nulo. O gatilho padrão do autovacuum é autovacuum_vacuum_threshold (50) mais autovacuum_vacuum_scale_factor (0,2, ou seja, 20% da tabela). Em tabelas de centenas de milhões de linhas, 20% é muito atraso — a correção comum é reduzir o scale_factor só naquela tabela, via parâmetro de armazenamento:

ALTER TABLE public.pedidos
  SET (autovacuum_vacuum_scale_factor = 0.02,
       autovacuum_vacuum_threshold = 1000);

Se o autovacuum roda mas as linhas mortas não diminuem, a causa costuma ser uma transação aberta há muito tempo (ou um slot de replicação parado) segurando o horizonte de limpeza — veja a seção de sessões mais abaixo.

3. Idade de XID: o alerta que ninguém quer ignorar

Os identificadores de transação (XID) do PostgreSQL têm 32 bits e são comparados de forma circular: só cerca de 2^31 (aproximadamente 2,1 bilhões) transações no "futuro" são distinguíveis. Se uma tabela não for "congelada" (freeze) a tempo, o banco para de aceitar escritas pra se proteger de perda de dados — o famoso transaction ID wraparound. O VACUUM de congelamento é o que impede isso.

SELECT datname,
       age(datfrozenxid) AS idade_xid,
       round(100.0 * age(datfrozenxid) / 2147483648, 1) AS pct_do_limite,
       round(100.0 * age(datfrozenxid)
             / current_setting('autovacuum_freeze_max_age')::bigint, 1) AS pct_do_gatilho_freeze
FROM pg_database
ORDER BY idade_xid DESC;

Duas referências: o autovacuum_freeze_max_age (padrão de 200 milhões) é o ponto em que o PostgreSQL força um autovacuum de congelamento mesmo com o autovacuum desligado; e 2^31 é o limite duro, quando as escritas são bloqueadas. Uma idade que sobe bem além do gatilho de freeze indica que algo está impedindo o VACUUM de avançar o datfrozenxid. Para achar a tabela responsável:

SELECT c.oid::regclass AS tabela,
       age(c.relfrozenxid) AS idade_xid
FROM pg_class c
WHERE c.relkind IN ('r', 'm', 't')
ORDER BY age(c.relfrozenxid) DESC
LIMIT 10;

Culpados típicos: transação aberta por horas (inclusive idle in transaction), slot de replicação lógica ou física parado, transação preparada (PREPARE TRANSACTION) esquecida, e autovacuum travado ou subdimensionado em tabela enorme.

4. Bloat: espaço ocupado que não guarda dado útil

Bloat é o espaço dentro de tabelas e índices ocupado por versões mortas ou vazio que ainda não foi reaproveitado. O VACUUM comum marca esse espaço como reutilizável, mas não devolve o arquivo ao sistema operacional — só VACUUM FULL ou ferramentas como pg_repack fazem isso (ambos com impacto de lock ou espaço extra, planeje a janela).

Há dois jeitos de medir. O barato é estimar a partir das estatísticas do planner; existem queries de estimativa conhecidas na comunidade (como a do projeto check_postgres), mas elas dependem de estatísticas atualizadas e erram em colunas de largura variável — trate o resultado como indício, não como medida. O exato é a extensão pgstattuple, que lê a tabela de verdade:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- Leitura completa da tabela (cara em tabelas grandes):
SELECT table_len, dead_tuple_percent, free_percent
FROM pgstattuple('public.pedidos');

-- Versão aproximada, bem mais leve (usa o visibility map):
SELECT approx_free_percent, dead_tuple_percent
FROM pgstattuple_approx('public.pedidos');

Prefira rodar a versão aproximada fora do horário de pico e em lote, tabela a tabela. Percentuais altos de espaço livre e linhas mortas numa tabela que não encolhe são o sinal pra agir — mas não existe um percentual universal de "bloat perigoso"; depende do tamanho da tabela e do custo da janela de manutenção.

5. pg_stat_statements: quais queries consomem o servidor

A extensão pg_stat_statements agrega estatísticas por query normalizada (sem os valores literais). Ela precisa ser carregada no startup e criada em cada banco onde você quer consultar:

-- postgresql.conf (exige reinício):
-- shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

A partir do PostgreSQL 13 as colunas de tempo foram separadas em total_exec_time e total_plan_time (e mean_exec_time, etc.). Em versões anteriores à 13 o nome era total_time. Para as queries que mais consomem tempo de execução:

SELECT left(query, 80) AS query,
       calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 2) AS media_ms,
       rows,
       shared_blks_read
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Ordene também por mean_exec_time (queries lentas individualmente) e por shared_blks_read (queries que mais forçam leitura fora do cache). Uma query barata que roda milhões de vezes aparece no topo por total_exec_time; uma query rara e pesada, por mean_exec_time — você precisa olhar os dois recortes. Lembre que os contadores são cumulativos: use pg_stat_statements_reset() com critério, ou compare amostras.

6. Sessões bloqueadas e transações abertas

A função pg_blocking_pids() devolve quem está bloqueando cada sessão, sem precisar cruzar pg_locks na mão:

SELECT pid,
       pg_blocking_pids(pid) AS bloqueada_por,
       wait_event_type, wait_event,
       state,
       now() - xact_start AS idade_transacao,
       left(query, 100) AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

E, para as transações abertas que seguram o horizonte de limpeza do autovacuum e do congelamento de XID:

SELECT pid, usename, state,
       now() - xact_start AS idade_transacao,
       left(query, 100) AS ultima_query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND state IN ('idle in transaction', 'active')
ORDER BY xact_start
LIMIT 10;

Uma sessão idle in transaction por muito tempo é um dos achados mais comuns por trás de autovacuum "ineficaz", bloat crescente e idade de XID subindo — as três coisas se conectam. Defina idle_in_transaction_session_timeout como rede de proteção.

Monitorar à mão funciona — até não escalar

Esses scripts resolvem o diagnóstico pontual, mas cumulativo não é histórico: sem coletar amostras de forma contínua você não sabe se o cache hit caiu ontem às 3h, nem recebe aviso antes de o XID chegar no limite. É aí que entra uma coleta recorrente com alerta.

O codeDB monitora esses sinais nativamente no PostgreSQL — cache hit, autovacuum pendente, idade de XID, bloat (medido com pgstattuple quando a extensão está instalada, estimado quando não está) e ranking de queries via pg_stat_statements — sem traduzir conceitos do SQL Server à força. Funciona em PostgreSQL self-hosted, Amazon RDS e Aurora e Azure Database for PostgreSQL Flexible Server, por meio de um agente local (instalador Windows, pacote .deb, tarball Linux ou imagem de container).