Monitoramento e métricas essenciais para PostgreSQL

Os principais indicadores que você deve acompanhar em produção.

Por que monitorar é crítico

Você não pode gerenciar o que não mede. Monitoramento eficaz do PostgreSQL permite detectar problemas antes dos usuários, otimizar recursos e planejar crescimento com dados reais.

Consequências de não monitorar

• ⚠️ Downtime inesperado - Disco cheio, memória esgotada
• ⚠️ Performance degradada - Queries lentas não identificadas
• ⚠️ Custos desnecessários - Recursos mal dimensionados
• ⚠️ Incidentes não rastreados - Impossível fazer postmortem
• ⚠️ Escalabilidade comprometida - Não sabe quando escalar

1. Métricas de sistema (Infrastructure)

CPU

-- PostgreSQL consumindo CPU
SELECT 
  pid,
  usename,
  application_name,
  state,
  query,
  (now() - query_start) as duration
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY query_start
LIMIT 10;

# Sistema operacional
top -bn1 | grep postgres
# Alerta: CPU > 80% consistente

Memória

-- Configurações de memória PostgreSQL
SHOW shared_buffers;
SHOW work_mem;
SHOW maintenance_work_mem;
SHOW effective_cache_size;

# Sistema operacional
free -h
# Alerta: Memória disponível < 10%
# Alerta: Swap usage > 0 (indica falta de RAM)

Disco

-- Tamanho do banco
SELECT 
  pg_database.datname,
  pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database
ORDER BY pg_database_size(pg_database.datname) DESC;

-- Tamanho por tabela
SELECT 
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 20;

# Sistema operacional
df -h
# Alerta crítico: Disco > 85%
# Alerta warning: Disco > 70%

I/O (leitura/escrita)

-- I/O por tabela
SELECT 
  schemaname,
  tablename,
  heap_blks_read,
  heap_blks_hit,
  CASE 
    WHEN heap_blks_hit + heap_blks_read = 0 THEN 0
    ELSE ROUND(100.0 * heap_blks_hit / (heap_blks_hit + heap_blks_read), 2)
  END AS cache_hit_ratio
FROM pg_statio_user_tables
ORDER BY heap_blks_read DESC
LIMIT 20;

# Alerta: cache_hit_ratio < 90% (pode precisar mais memória)

2. Métricas de banco de dados

Conexões

-- Conexões ativas
SELECT 
  count(*) as total,
  count(*) FILTER (WHERE state = 'active') as active,
  count(*) FILTER (WHERE state = 'idle') as idle,
  count(*) FILTER (WHERE state = 'idle in transaction') as idle_in_transaction
FROM pg_stat_activity
WHERE pid <> pg_backend_pid();

-- Limite de conexões
SHOW max_connections;

-- Conexões por banco
SELECT 
  datname,
  count(*) as connections
FROM pg_stat_activity
GROUP BY datname
ORDER BY connections DESC;

# Alertas:
# - Conexões > 80% do max_connections
# - idle_in_transaction > 10 (locks desnecessários)

Transactions

-- Estatísticas de transações
SELECT 
  datname,
  xact_commit,
  xact_rollback,
  blks_read,
  blks_hit,
  tup_returned,
  tup_fetched,
  tup_inserted,
  tup_updated,
  tup_deleted
FROM pg_stat_database
WHERE datname NOT IN ('template0', 'template1', 'postgres');

-- Taxa de commit vs rollback
SELECT 
  datname,
  xact_commit,
  xact_rollback,
  ROUND(100.0 * xact_rollback / NULLIF(xact_commit + xact_rollback, 0), 2) as rollback_ratio
FROM pg_stat_database
WHERE datname = 'meudb';

# Alerta: rollback_ratio > 5% (possível problema na aplicação)

Cache Hit Ratio

-- Cache hit ratio global (deve ser > 99%)
SELECT 
  sum(heap_blks_read) as heap_read,
  sum(heap_blks_hit) as heap_hit,
  ROUND(100.0 * sum(heap_blks_hit) / NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0), 2) AS ratio
FROM pg_statio_user_tables;

# Alerta: ratio < 90% (considere aumentar shared_buffers)

3. Métricas de performance

Queries lentas

-- Habilite pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 20 queries mais lentas (tempo médio)
SELECT 
  ROUND(mean_exec_time::numeric, 2) AS avg_ms,
  ROUND(total_exec_time::numeric, 2) AS total_ms,
  calls,
  ROUND((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct,
  LEFT(query, 80) AS query
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat_statements%'
ORDER BY mean_exec_time DESC
LIMIT 20;

-- Queries com mais chamadas
SELECT 
  calls,
  ROUND(mean_exec_time::numeric, 2) AS avg_ms,
  LEFT(query, 80) AS query
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat_statements%'
ORDER BY calls DESC
LIMIT 20;

# Alerta: Queries com avg_ms > 1000ms (1 segundo)

Bloat (desperdício de espaço)

-- Tabelas com bloat
SELECT 
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size,
  n_dead_tup,
  n_live_tup,
  ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC
LIMIT 20;

# Alerta: dead_ratio > 20% (execute VACUUM)
# Solução: VACUUM ANALYZE nome_da_tabela;

Índices não utilizados

-- Índices que nunca foram usados
SELECT 
  schemaname,
  tablename,
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'  -- Ignora primary keys
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

# Considere remover índices não usados (economiza espaço e I/O)

Table scans

-- Tabelas com muitos sequential scans (faltam índices?)
SELECT 
  schemaname,
  tablename,
  seq_scan,
  seq_tup_read,
  idx_scan,
  ROUND(100.0 * seq_scan / NULLIF(seq_scan + idx_scan, 0), 2) AS seq_scan_pct,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_scan DESC
LIMIT 20;

# Alerta: Tabelas grandes (>1GB) com seq_scan_pct > 50%

4. Métricas de locks e contention

Locks ativos

-- Locks bloqueando queries
SELECT 
  blocked_locks.pid AS blocked_pid,
  blocked_activity.usename AS blocked_user,
  blocking_locks.pid AS blocking_pid,
  blocking_activity.usename AS blocking_user,
  blocked_activity.query AS blocked_statement,
  blocking_activity.query AS blocking_statement,
  blocked_activity.application_name AS blocked_application
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks 
  ON blocking_locks.locktype = blocked_locks.locktype
  AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
  AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
  AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
  AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
  AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
  AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
  AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
  AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
  AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
  AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

# Alerta: Locks bloqueando por > 30 segundos

Long-running queries

-- Queries rodando há muito tempo
SELECT 
  pid,
  now() - query_start AS duration,
  usename,
  application_name,
  state,
  LEFT(query, 100) AS query
FROM pg_stat_activity
WHERE state != 'idle'
  AND query NOT LIKE '%pg_stat_activity%'
ORDER BY query_start
LIMIT 20;

# Alerta crítico: Queries > 5 minutos (investigar)

5. Métricas de replicação

Lag de replicação

-- Em uma replica (standby)
SELECT 
  now() - pg_last_xact_replay_timestamp() AS replication_lag,
  pg_is_in_recovery() AS is_replica;

-- No master
SELECT 
  client_addr,
  application_name,
  state,
  sync_state,
  pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS send_lag,
  pg_wal_lsn_diff(sent_lsn, write_lsn) AS write_lag,
  pg_wal_lsn_diff(write_lsn, flush_lsn) AS flush_lag,
  pg_wal_lsn_diff(flush_lsn, replay_lsn) AS replay_lag
FROM pg_stat_replication;

# Alerta: replication_lag > 10 segundos

6. Scripts de monitoramento automatizado

Script completo de health check

#!/bin/bash
# pg-health-check.sh

DB_HOST="localhost"
DB_NAME="meudb"
DB_USER="monitor"
ALERT_EMAIL="admin@empresa.com"

# 1. Disk usage
DISK_USAGE=\$(df -h /var/lib/postgresql | awk 'NR==2 {print \$5}' | sed 's/%//')
if [ "\$DISK_USAGE" -gt 85 ]; then
    echo "🔴 CRÍTICO: Disco em \${DISK_USAGE}%" | mail -s "ALERTA PostgreSQL" \$ALERT_EMAIL
fi

# 2. Conexões
CONNECTIONS=\$(psql -h \$DB_HOST -U \$DB_USER -d \$DB_NAME -t -c "SELECT count(*) FROM pg_stat_activity;")
MAX_CONN=\$(psql -h \$DB_HOST -U \$DB_USER -d \$DB_NAME -t -c "SHOW max_connections;")
CONN_PCT=\$((100 * CONNECTIONS / MAX_CONN))
if [ "\$CONN_PCT" -gt 80 ]; then
    echo "🟡 WARNING: \${CONNECTIONS}/\${MAX_CONN} conexões (\${CONN_PCT}%)"
fi

# 3. Replication lag
LAG=\$(psql -h \$DB_HOST -U \$DB_USER -d \$DB_NAME -t -c "SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::int;")
if [ "\$LAG" -gt 10 ]; then
    echo "🔴 CRÍTICO: Lag de replicação = \${LAG}s"
fi

# 4. Long queries
LONG_QUERIES=\$(psql -h \$DB_HOST -U \$DB_USER -d \$DB_NAME -t -c "
    SELECT count(*) FROM pg_stat_activity 
    WHERE state = 'active' 
    AND now() - query_start > interval '5 minutes';
")
if [ "\$LONG_QUERIES" -gt 0 ]; then
    echo "🟡 WARNING: \${LONG_QUERIES} queries rodando > 5min"
fi

echo "✅ Health check completo"

7. Dashboards e ferramentas

Ferramentas open-source

• pgAdmin 4 - Interface visual com dashboard
• pgBadger - Análise de logs
• pg_stat_monitor - Melhor que pg_stat_statements
• Prometheus + Grafana - Monitoramento profissional
• Datadog / New Relic - Soluções comerciais

Exportador Prometheus

# Instale postgres_exporter
docker run -d \\
  --name postgres_exporter \\
  -p 9187:9187 \\
  -e DATA_SOURCE_NAME="postgresql://monitor:senha@localhost:5432/meudb?sslmode=disable" \\
  quay.io/prometheuscommunity/postgres-exporter

# Métricas em http://localhost:9187/metrics

8. CloudPG Monitoring

No CloudPG, monitoramento é automático e visual desde o primeiro minuto:

• ✅ Dashboard em tempo real - CPU, memória, disco, I/O
• ✅ Query analytics - Top queries lentas automaticamente
• ✅ Alertas inteligentes - Email/Slack quando algo está errado
• ✅ Métricas históricas - 30 dias de dados (PRO: 90 dias)
• ✅ Logs centralizados - Busca e filtros poderosos
• ✅ Connection pooling - PgBouncer integrado e monitorado
• ✅ Replication lag - Monitoramento automático de réplicas
• ✅ API de métricas - Integre com suas ferramentas

Alertas pré-configurados

• 🔴 Disco > 85%
• 🔴 CPU > 90% por 5min
• 🔴 Conexões > 90% do limite
• 🔴 Replication lag > 30s
• 🟡 Cache hit ratio < 90%
• 🟡 Queries > 5s de duração
• 🟡 Bloat > 30%

Conclusão

Monitoramento eficaz do PostgreSQL requer acompanhar:

1. ✅ Infraestrutura - CPU, RAM, disco, I/O
2. ✅ Banco - Conexões, transações, cache
3. ✅ Performance - Queries lentas, índices, bloat
4. ✅ Replicação - Lag e sincronização
5. ✅ Alertas - Notificações proativas

Com CloudPG, todo esse monitoramento vem configurado e funcionando automaticamente.

Monitore desde o primeiro minuto

Crie seu cluster PostgreSQL com dashboard de monitoramento completo e alertas inteligentes inclusos.