Dicas PostgreSQL

Saulo Siqueira — Wed, 28 Jun 2023 17:30:49 +0000

🐘 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.

Excluindo Valores no PostgreSQL: Utilizando Expressões Regulares para Filtrar Dados

Saulo Siqueira — Wed, 28 Jun 2023 17:23:42 +0000

No PostgreSQL, o operador !~ é usado para verificar se uma string não corresponde a uma expressão regular. Ele retorna verdadeiro se a string não corresponder à expressão regular especificada.

Em termos mais simples, o operador !~ é usado para negar a correspondência de uma expressão regular. Se a expressão regular encontrar uma correspondência na string, o operador !~ retornará falso. Por outro lado, se a expressão regular não encontrar uma correspondência na string, o operador !~ retornará verdadeiro.

SELECT * FROM tabela WHERE coluna !~ '^(sup|age)'

No contexto da consulta SQL que forneci anteriormente, a cláusula WHERE coluna !~ '^(sup|age)' está excluindo os valores em que a coluna não corresponde ao padrão definido pela expressão regular '^(sup|age)', ou seja, valores que não começam com "sup" ou "age".

Essa é uma forma diferente de escrever em relação a forma tradicional abaixo:

SELECT * FROM tabela WHERE coluna !ILIKE 'sup%' AND coluna !ILIKE 'age%'

DEV Community: Saulo Siqueira

Dicas PostgreSQL

Excluindo Valores no PostgreSQL: Utilizando Expressões Regulares para Filtrar Dados