Introdução ao pg_stat_statements: instalação e configuração da extensão

A extensão pg_stat_statements no PostgreSQL é uma ferramenta pra coletar estatísticas das queries. Ela deixa você ver quais queries são executadas com mais frequência, quais demoram mais tempo e como os recursos do banco estão sendo usados. Em vez de analisar cada query manualmente com EXPLAIN, dá pra ter uma visão geral da performance do banco.

Vantagens de usar o pg_stat_statements:

Monitoramento em tempo real: você consegue ver quais queries estão pesando no banco agora mesmo.

Análise da performance do sistema inteiro: as informações ficam disponíveis pra todas as queries do banco, não só pras que você decidiu analisar manualmente.

Encontrar queries lentas: fica fácil descobrir quais queries estão gastando mais tempo.

Identificar queries repetidas: ajuda a otimizar o cache e criar índices pras queries mais populares.

Instalando e configurando o pg_stat_statements

Agora que você já entendeu pra que serve o pg_stat_statements, bora ver como instalar e configurar ele passo a passo.

1. Checando se o PostgreSQL está pronto. Confere se seu PostgreSQL suporta a extensão pg_stat_statements. Essa extensão já vem no pacote padrão a partir do PostgreSQL 9.2. Pra checar se ela tá disponível, roda:

SELECT extname FROM pg_extension;

Se pg_stat_statements não aparecer na lista, pode ser que o admin do banco não instalou ela ainda.

Assim que a extensão estiver instalada e ativada, vai ficar assim:

2. Adicionando a extensão.

Pra ativar o pg_stat_statements, você precisa colocar ele na lista de bibliotecas que o PostgreSQL carrega na inicialização. Isso é feito no arquivo de configuração postgresql.conf.

Passos:

1. Acha o arquivo postgresql.conf. Normalmente ele fica na pasta de dados do PostgreSQL.
2. Abre ele pra editar.
3. Adiciona ou altera a linha:

   shared_preload_libraries = 'pg_stat_statements'

Por que precisa disso? Porque o pg_stat_statements tem que ser carregado antes, já que ele monitora as queries no nível do sistema.

4. Salva as mudanças e reinicia o servidor PostgreSQL pra ativar. No Linux, o comando é:

   sudo systemctl restart postgresql
   

Se você tá desenvolvendo ou testando localmente, só reiniciar o servidor já resolve.

3. Criando a extensão no banco de dados. Depois que o servidor PostgreSQL reiniciou, dá pra criar a extensão pg_stat_statements no banco que você quiser. Conecta no banco pelo psql ou outro client e roda:

CREATE EXTENSION pg_stat_statements;

Se tudo der certo, o comando termina sem erro. Agora o pg_stat_statements tá ativado no seu banco.

4. Configurando os parâmetros do pg_stat_statements.

Depois de instalar a extensão, é bom ajustar os parâmetros dela pra coletar as estatísticas do jeito certo. Os principais parâmetros ficam no arquivo postgresql.conf.

Parâmetros principais

• pg_stat_statements.track
• Diz quais queries vão ser monitoradas.
• Valores:
  • all — monitora todas as queries (recomendado pra debug e análise).
  • top — monitora só as queries de nível superior.
  • none — desliga o monitoramento.
• Exemplo de configuração:

pg_stat_statements.track = 'all'

• pg_stat_statements.max

  • Diz o número máximo de queries que vão ser salvas nas estatísticas.
  • Padrão: 5000.
  • Se seu sistema tem muita query, aumenta esse valor, tipo:

    pg_stat_statements.max = 10000
    

• pg_stat_statements.save

  • Diz se as estatísticas vão ser salvas entre os restarts do servidor.
  • Valores: on ou off.
  • Recomendo deixar on:

    pg_stat_statements.save = on
    

Depois de mudar os parâmetros, reinicia o servidor PostgreSQL de novo.

Testando o pg_stat_statements

Agora que a extensão tá instalada e configurada, bora testar se tá funcionando. Pra ver as estatísticas das queries, roda essa query:

SELECT
    queryid,        -- Identificador único da query
    query,          -- Texto da query
    calls,          -- Quantidade de execuções da query
    total_time,     -- Tempo total de execução (em milissegundos)
    rows            -- Quantidade de linhas retornadas pela query
FROM pg_stat_statements
ORDER BY total_time DESC;

O que significam as colunas?

• queryid: identificador único da query, útil pra achar queries iguais com parâmetros diferentes.
• query: o texto da query SQL que foi executada.
• calls: quantas vezes a query foi chamada.
• total_time: tempo total (soma do tempo de todas as execuções da query).
• rows: total de linhas retornadas pela query.

Por exemplo, se você ver que uma query com calls = 100 e total_time = 50000 (50 segundos) tá consumindo a maior parte do tempo do sistema, é sinal de que precisa otimizar ela.

Cenários comuns de uso do pg_stat_statements

1. Encontrar as queries mais lentas. Pra achar as queries que gastam mais tempo, ordena os resultados por total_time:

SELECT query, total_time, calls
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;

2. Descobrir as queries mais ativas. Pra achar as queries que rodam mais vezes, ordena por calls:

SELECT query, calls, total_time
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 5;

3. Analisar o uso de índices. Se você ver muita query lenta, confere se tá usando índice. Por exemplo, queries com filtro (WHERE) sem índice costumam ser lentas.

Limpando os dados do pg_stat_statements

Às vezes você vai querer zerar as estatísticas pra começar a analisar do zero. Dá pra fazer isso com o comando:

SELECT pg_stat_statements_reset();

Depois de resetar, todas as estatísticas são limpas e a coleta começa de novo.

Dicas práticas

Limita o tamanho das estatísticas: se você tá num sistema com muita carga e milhões de queries, deixa o pg_stat_statements.max num valor razoável pra não sobrecarregar o banco.

Limpa as estatísticas de vez em quando: é bom fazer isso antes de analisar a performance, pra não misturar dados antigos com novos.

Fica de olho nas queries lentas: mesmo que rodem pouco, uma query lenta pode pesar bastante no seu banco.

Agora você já sabe como instalar, configurar e usar a extensão pg_stat_statements pra analisar a performance das queries. Na próxima aula, a gente vai ver como encontrar queries lentas com ela e otimizar a execução delas.