Antes de responder à questão, vamos entender um pouco sobre a extensão pg_stat_statments.

No artigo de hoje vamos falar sobre uma das ferramentas mais poderosas que o PostgreSQL nos oferece para poder entender o que está acontecendo com o nosso banco de dados. Ela pode ser vista como um detetive que nos ajuda a encontrar os vilões da performance.

Vamos conversar sobre a extensão pg_stat_statements, uma ferramenta essencial para quem quer entender e melhorar o desempenho das consultas SQL no PostgreSQL. Ela coleta estatísticas detalhadas sobre as consultas que o banco executa.

O que é e por que vocês deveriam se importar?

Imagine que uma aplicação esteja lenta, gerando muitas reclamações e você precisa descobrir onde está o problema. Muitas vezes, a culpa é de uma ou duas consultas SQL que estão consumindo muito mais recursos do que deveriam. A questão aqui é: como descobrir quais são elas?

É muito comum pensar que a consulta vilã será aquela mais complexa, cheia de JOINs. Mas, na realidade, a maior culpada pode ser um SELECT supersimples que está sendo executado um zilhão de vezes por hora.

A pg_stat_statements nos ajuda a resolver essa questão. Ela rastreia as estatísticas de execução de todas as consultas que rodam no seu banco de dados e nos permite responder perguntas como:

• Quais são as consultas que, somadas, mais consomem tempo do meu cluster?
• Quais são as consultas executadas com mais frequência?
• Qual consulta, em média, é a mais lenta?
• Quais consultas estão lendo mais dados do disco em vez da memória cache (lembrem-se que o acesso a disco é muito mais lento).

Usando pg_stat_statements para Análises Avançadas no PostgreSQL: Um Guia Passo a Passo

Ativação e Configuração

Para configurar a pg_stat_statements são necessárias duas etapas principais:

Etapa 1: Configurar o Servidor PostgreSQL

Esta extensão precisa ser pré-carregada junto com a inicialização do serviço PostgreSQL. Para isso, precisamos editar o arquivo de configuração postgresql.conf.

1. Edite o arquivo e procure pelo parâmetro shared_preload_libraries.

2. Adicione 'pg_stat_statements' a esse parâmetro.

               Ex.:   shared_preload_libraries = 'pg_stat_statements'

Obs.: Como esse parâmetro é do tipo enumerado, pode acontecer de já haver outras libraries indicadas. Assim, adicione à string, após a última existente, separando-a com uma vírgula.

                        Ex.: shared_preload_libraries = 'extensão A, extensão B, pg_stat_statements'

3. Importante: Este parâmetro é do contexto postmaster, ou seja, afeta a instância em sua totalidade. Portanto, é necessária a reinicialização do serviço para que possa ser aplicado. Sem esta ação, não haverá coleta de dados.

Depois de reiniciar, o pg_stat_statements começará a registrar as estatísticas de todas as consultas executadas a partir do momento que for criada dentro do Data Base a ser inspecionado.

Etapa 2: Habilitar a Extensão no Banco de Dados

É necessária a criação para cada database a ser monitorado. Conecte-se ao banco de dados e execute:

=# CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Pronto! Já podemos dar início às análises:

Consultando Estatísticas

É preciso que a aplicação rode por um tempo, horas ou dias, para podermos ter dados relevantes à análise. Após isso, podemos executar consultas na view criada pela extensão.

Essa view possui diversas colunas, a serem utilizadas de acordo com nossa necessidade.

As colunas mais relevantes são:

• query: O sql da consulta (os parâmetros são substituídos por ? ou $1).
• calls: O número de vezes que a consulta foi executada.
• total_exec_time: O tempo total, em milissegundos, gasto na execução da consulta.
• min_exec_time, max_exec_time, mean_exec_time: tempos de execução, mínimo, máximo e médio (total_exec_time / calls).
• rows: O número total de linhas retornadas ou afetadas pela consulta, em todas as chamadas.
• shared_blks_read: Total de blocos de dados a consulta precisou ler do disco.
• shared_blks_hit: Total de blocos de dados a consulta leu da memória.
• shared_blks_written: Total de blocos de dados escritos pela consulta.

Exemplos:

Encontrando as 5 consultas que mais consumiram tempo no total:

SELECT total_exec_time, mean_exec_time, calls, rows, query

FROM pg_stat_statements

ORDER BY total_exec_time DESC

LIMIT 5;

Encontrando as consultas que mais fazem leitura e escrita em disco:

SELECT query, shared_blks_read, shared_blks_written

FROM pg_stat_statements

ORDER BY (shared_blks_read + shared_blks_written) DESC

LIMIT 10;

O que fazer, a partir dos resultados obtidos?

1. Use EXPLAIN (ANALYZE, BUFFERS) para examinar o plano de execução de forma detalhada.
2. Verifique se há “sequential scans” desnecessários, joins ineficientes ou falta de índices.
3. Reescreva a consulta ou crie índices para reduzir custos.
4. Monitore novamente com pg_stat_statements para confirmar se obteve melhoras nos tempos de execução.

Se precisar, limpe as estatísticas para começar uma nova medição:

=# SELECT pg_stat_statements_reset();

Não esquecemos da resposta à questão que te trouxe aqui.

Resposta: Depende!

Se sua distribuição linux for da família Fedora, essa extensão se encontra dentro do pacote complementar postgresql-contrib. Assim, a primeira coisa a ser feita é a instalação do pacote postgresql-contrib em seu sistema operacional!

Mas, se a sua distro for da família Debian, essa extensão já faz parte do pacote principal postgresql- VERSÃO_do_PG. Sendo assim, a primeira coisa a ser feita é a alteração no parâmetro shared_preload_libraries!