Dicas PostgreSQL

🐘 Consultas Úteis para Monitorar e Diagnosticar Bancos PostgreSQL

Manter um banco de dados PostgreSQL saudável vai muito além de apenas armazenar dados. É fundamental monitorar o crescimento das tabelas, detectar consultas lentas, identificar índices ausentes e, quando necessário, encerrar sessões problemáticas.

Neste artigo, reuni alguns comandos SQL essenciais para diagnosticar o comportamento do banco e manter a performance em dia.

🔍 Verificando o tamanho das tabelas

Para saber quais tabelas estão ocupando mais espaço no seu banco de dados, use a consulta abaixo.
Ela retorna o tamanho individual e total (incluindo índices) de cada tabela, ordenadas da maior para a menor:

SELECT esquema, tabela,
pg_size_pretty(pg_relation_size(esq_tab)) AS tamanho,
pg_size_pretty(pg_total_relation_size(esq_tab)) AS tamanho_total
FROM (SELECT tablename AS tabela,
schemaname AS esquema,
schemaname||'.'||tablename AS esq_tab
FROM pg_catalog.pg_tables
WHERE schemaname NOT
IN ('pg_catalog', 'information_schema', 'pg_toast') ) AS x
ORDER BY pg_total_relation_size(esq_tab) DESC;

💡 Dica: Ideal para identificar tabelas que podem precisar de manutenção, particionamento ou limpeza de dados antigos.

🛑 Cancelando instruções SQL

Em alguns casos, consultas travadas ou ociosas podem estar consumindo recursos do banco.
Use o comando abaixo para encerrar sessões que estão em estado IDLE:

SELECT
pg_terminate_backend(procpid)
FROM
pg_stat_activity
WHERE
current_query = '<IDLE>';

⚠️ Atenção:
Encerrar processos pode impactar conexões de aplicações ativas.
Sempre verifique antes com um SELECT * FROM pg_stat_activity;

📊 Encontrando tabelas sem índices adequados

Consultas que fazem full table scans (varredura completa) podem indicar a ausência de índices.
Use a consulta abaixo para identificar possíveis pontos de melhoria:

SELECT
relname AS TableName,
seq_scan - idx_scan AS TotalSeqScan,
CASE WHEN seq_scan - idx_scan > 0
THEN 'Missing Index Found'
ELSE 'Missing Index Not Found'
END AS MissingIndex,
pg_size_pretty(pg_relation_size(relname::regclass)) AS TableSize,
idx_scan AS TotalIndexScan
FROM pg_stat_all_tables
WHERE schemaname = 'public'
AND pg_relation_size(relname::regclass) > 100000
ORDER BY 2 DESC;

📈 Resultado:
Você verá quais tabelas estão sendo mais varridas sem uso de índice — ótimos candidatos para otimização.

⏱️ Consultas que estão rodando há mais de 1 minuto

Para visualizar queries longas em execução, rode:
SELECT
CURRENT_TIMESTAMP - query_start AS runtime,
datname,
usename,
query
FROM
pg_stat_activity
WHERE
state = 'active'
AND CURRENT_TIMESTAMP - query_start > '1 min'
ORDER BY
1 DESC;

🧠 Uso prático:
Ajuda a identificar gargalos, deadlocks ou consultas que precisam ser otimizadas.

💬 Conclusão

Essas consultas são extremamente úteis para:

• Auditar o consumo de espaço no banco;
• Diagnosticar lentidão em consultas;
• Detectar índices ausentes;
• Controlar sessões problemáticas.

Manter essas práticas no seu dia a dia de DBA ou DevOps pode evitar grandes dores de cabeça e garantir uma operação mais estável e performática no PostgreSQL. 🚀

✍️ Autor: Saulo José Eduardo de Siqueira
💻 Gerente de Projetos & Desenvolvedor PHP/Laravel apaixonado por DevOps e performance de sistemas.