Comandos principais de monitoramento no PostgreSQL — pg_stat_activity e pg_stat_user_tables

Monitorar banco de dados sem pg_stat_activity e pg_stat_user_tables é tipo cuidar da saúde só olhando a febre. Tu não consegue sacar onde tá o problema se olhar só o geralzão. Essas duas ferramentas chave do PostgreSQL vão te ajudar não só a observar, mas também analisar de verdade o que tá rolando no teu banco.

O que é pg_stat_activity?

pg_stat_activity é uma view do sistema do PostgreSQL que mostra info sobre todas as conexões no teu banco. Ela responde perguntas tipo: quem tá conectado, quais queries tão rolando agora e quais conexões tão “paradas” sem fazer nada. É tua ferramenta pra analisar o que tá pegando no servidor nesse momento.

Bora ver os campos principais que tem no pg_stat_activity. O campo datname mostra o nome do banco onde o cliente tá conectado, e usename mostra o usuário que fez a conexão. application_name mostra o nome do app usando a conexão, client_addr traz o IP do cliente conectado no servidor. backend_start mostra quando o cliente conectou, state mostra o estado atual da conexão (active, idle, idle in transaction), e query mostra a query que tá rodando ou rodou por último.

Exemplo 1: vendo todas as conexões ativas

Pra ver as conexões ativas, roda essa query:

SELECT datname, usename, client_addr, state, query
FROM pg_stat_activity
WHERE state = 'active';

Fica ligado no campo query. Ele mostra as queries que tão rodando agora. Se uma query tá demorando demais, pode ter algo errado com ela.

Exemplo 2: Analisando o estado das transações

Às vezes as conexões ficam “presas” no estado idle in transaction. Isso significa que uma transação foi iniciada mas não foi finalizada, e isso pode causar locks.

SELECT pid, usename, query, state
FROM pg_stat_activity
WHERE state = 'idle in transaction';

Como resolver isso? Se tu achou uma transação “travada”, pode finalizar ela com esse comando:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction';

Tem dev que se empolga demais com isso. Recomendo perguntar pro time antes se pode “matar” o processo. Opa, quis dizer — finalizar a conexão.

Monitorando uso das tabelas: a view pg_stat_user_tables

Se pg_stat_activity serve pra olhar as conexões, pg_stat_user_tables mostra como as tabelas tão performando. Com ela tu descobre: com que frequência as tabelas são lidas ou escritas, quais tabelas são mais usadas e onde pode ter gargalo de performance.

Aqui vão os campos principais do pg_stat_user_tables pra analisar as tabelas. relname é o nome da tabela, seq_scan mostra quantos scans sequenciais rolaram, idx_scan — quantos scans usando índice. n_tup_ins mostra quantas linhas foram inseridas, n_tup_upd — quantas linhas foram atualizadas, e n_tup_del — quantas linhas foram deletadas.

Exemplo 1: comparando uso de índices e scans sequenciais

Se o índice é pouco usado (idx_scan perto de zero), provavelmente dá pra otimizar as queries dessa tabela.

SELECT relname, seq_scan, idx_scan
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

Exemplo de resultado:

Se tu vê que a tabela orders tem um monte de scans sequenciais (seq_scan), pensa em criar um índice. Imagina a tabela orders com 3500 scans sequenciais e só 100 scans por índice, enquanto a tabela employees tem 50 scans sequenciais e 1000 por índice — isso é um sinal claro pra otimizar.

Exemplo 2: analisando quantidade de operações nas tabelas

Pra ver o quão “vivos” estão os dados nas tabelas, pega info de linhas inseridas, atualizadas e deletadas:

SELECT relname, n_tup_ins, n_tup_upd, n_tup_del
FROM pg_stat_user_tables
ORDER BY n_tup_ins DESC;

O que tu pode descobrir? Tabelas com muitos inserts (n_tup_ins) e deletes (n_tup_del) podem ser pontos quentes no teu banco. Isso quer dizer que a performance delas merece atenção especial.

Usando na prática pra analisar performance: combinando dados do pg_stat_activity e pg_stat_user_tables

Quando tu vai analisar a performance do banco, dá pra juntar info dessas duas views. Primeiro, acha as queries demoradas com pg_stat_activity, depois vê quais tabelas essas queries usam com pg_stat_user_tables. Se as queries demoram em tabelas com seq_scan alto, tenta otimizar as queries ou criar um índice.

Exemplo de query:

WITH active_queries AS (
    SELECT pid, query
    FROM pg_stat_activity
    WHERE state = 'active' AND query <> '<IDLE>'
)
SELECT a.pid, a.query, t.relname, t.seq_scan, t.idx_scan
FROM active_queries a
JOIN pg_stat_user_tables t ON a.query LIKE '%' || t.relname || '%';