Administração com PostgreSQL: desempenho e otimização

Otimização de consultas no PostgreSQL - Apresentação

Te damos as boas-vindas ao curso administração do PostgreSQL sobre desempenho e otimização**. O instrutor Victorino Vila vai nos acompanhar nessa jornada de aprendizado.

Audiodescrição: Victorino é uma pessoa de pele clara e olhos escuros. Seus cabelos são curtos, lisos e grisalhos. Usa barba e bigode e está com uma camiseta azul-marinho. Ao fundo, parede azul sem decorações.

Esse curso é parte da formação em PostgreSQL para administradores e é focado em elevar as suas habilidades e conhecimentos no gerenciamento e otimização de bancos de dados PostgreSQL.

O que vamos aprender nesse curso?

Nós estruturamos o conteúdo em cinco aulas principais, cada uma cuidadosamente desenvolvida para cobrir aspectos cruciais do desempenho e otimização do PostgreSQL.

O curso começa com os fundamentos da otimização de desempenho, incluindo a identificação de gargalos e o uso de ferramentas internas no PostgreSQL para monitorar a carga de trabalho.

Em seguida, focamos na importância dos índices, abordando como criá-los, gerenciá-los e utilizá-los de forma eficaz para melhorar a eficiência das consultas.

Na terceira aula, vamos nos aprofundar na análise e otimização dos chamados planos de execução, explorando algumas técnicas de reescrita das consultas e o uso estratégico das CTEs (Common Table Expressions, ou Expressões de Tabela Comum) e subqueries (subconsultas).

Na quarta aula, vamos discutir estratégias avançadas de modelagem, como normalização versus desnormalização, uso de chaves primárias e estrangeiras, particionamento de tabelas e a escolha adequada do tipo de dados.

Finalmente, na última aula, vamos oferecer uma aplicação prática de todas essas técnicas, destacando a importância de medir o impacto da otimização e selecionar a melhor estratégia mais eficaz para balancear desempenho, complexidade e manutenção do banco de dados.

Público-alvo

Esse curso é uma excelente oportunidade para quem deseja seguir na carreira como DBA (Database Administrator ou administrador de banco de dados) e, claro, quer se destacar no mercado.

O conhecimento adquirido será certamente um diferencial na sua trajetória profissional. Você será capaz para enfrentar desafios reais no gerenciamento de banco de dados PostgreSQL.

Aproveite ao máximo esse conteúdo, pratique e explore cada exemplo mostrado nos vídeos e não hesite em se aprofundar ainda mais em todos os temas que serão discutidos no curso.

Desejamos a você um excelente aprendizado! Até o próximo vídeo.

Otimização de consultas no PostgreSQL - Otimização de desempenho no PostgreSQL

Neste primeiro vídeo, abordaremos a importância do desempenho em banco de dados, com foco especial em ambientes de produção, onde consultas lentas podem impactar negativamente os resultados da empresa.

Utilizaremos como exemplo prático a base de dados FRUTALLY_VENDAS, da empresa fictícia Frutally. Esta é a mesma base de dados que estamos usando em todos os cursos desta formação, com alguns ajustes realizados nas primeiras atividades deste curso.

Por isso, é importante que você tenha cumprido os passos iniciais antes de começar a seguir os vídeos deste curso.

Otimização de desempenho no PostgreSQL

O conhecimento da base de dados é fundamental, pois um dos fatores básicos de configuração do desempenho é entender como ela está organizada.

A base de dados FRUTALLY_VENDAS possui duas tabelas que representam as vendas da empresa, notas fiscais e itens_notas_fiscais. Também tem duas tabelas de configuração de clientes, tabela_de_clientes e tabela_de_clientes_geo, cujo sufixo se refere ao georreferenciamento do endereço da clientela. Além disso, contamos com a tabela_de_produtos e a tabela_de_vendedores.

Precisamos garantir que o desempenho do acesso à informação dentro deste banco seja otimizado para que as pessoas usuárias possam obter um retorno eficiente dessas informações.

Bancos de dados mal otimizados podem resultar em consultas SQL lentas, além de consumir excessivamente recursos de hardware e, consequentemente, impactar negativamente a experiência da pessoa usuária.

Vamos realizar alguns testes para entender o que é desempenho. Antes de executar qualquer comando, podemos dizer intuitivamente que desempenho é o tempo que a pessoa usuária espera entre a execução de uma consulta e a visualização da resposta.

Por exemplo, vamos executar o comando SQL para selecionar todos os clientes, cujo BAIRRO seja "Jardins".

SELECT * FROM tabela_de_clientes WHERE BAIRRO = 'Jardins';

Successfully run. Total query runtime: 61 msec. 3 rows affected.

Ao executar esta consulta em nossa máquina local com o atalho "F5", o tempo de execução será de 0,061 segundos para devolver 3 registros - uma resposta muito rápida. Isso ocorre porque a comunicação entre o servidor e o cliente está na mesma máquina, tornando o tempo de resposta muito rápido.

Para entender como isso funciona, mostraremos um exemplo que não poderá ser reproduzido em sua máquina local, pois acessaremos outro servidor PostgreSQL na nuvem.

Em outra máquina, nosso servidor está em um IP diferente, não no localhost. Nesse caso, entre o cliente e o servidor, temos a internet.

Para consultar o endereço do servidor, clicamos com o botão direito no servidor e escolhemos a opção "Properties". Na aba "Conenection", o IP estará especificado no campo "Host name/address".

Se executamos a mesma consulta nesse servidor na nuvem, o tempo de resposta será de 1,097 segundos.

SELECT * FROM tabela_de_clientes WHERE BAIRRO = 'Jardins';

Successfully run. Total query runtime: 1 sec 97 msec. 3 rows affected.

Embora seja um tempo bom, comparado ao da máquina local, a diferença é significativa. No entanto, essa diferença é imperceptível para a pessoa usuária.

Antes de mostrar outro exemplo, precisamos modificar uma configuração no pgAdmin. No menu superior, vamos acessar a opção "File > Preferences". Na lateral esquerda da janela de preferências, vamos clicar em "Query Tools > Results Grid".

Em seguida, alteramos o parâmetro "On Demand Record Count" de 1000 para 20000. Após isso, clicamos no botão "Save" para salvar as modificações.

Agora, vamos executar outra consulta para trazer todos os registros da tabela_de_clientes.

SELECT * from tabela_de_clientes;

Successfully run. Total query runtime: 1 sec 282 msec. 20016 rows affected.

No servidor local, a execução demorou 1,282 segundos, um tempo maior em relação à primeira consulta, pois retornou 20 mil linhas. O tráfego de dados entre o servidor e o cliente foi rápido, pois ambos estão locais.

Porém, ao rodar a mesma consulta no servidor da internet, o tempo de resposta aumenta significativamente, chegando a 13 segundos.

SELECT * from tabela_de_clientes;

Successfully run. Total query runtime: 13 secs 559 msec. 20016 rows affected.

Isso demonstra que a conectividade entre o servidor e o cliente é fundamental. Um tempo de 13 segundos pode ser problemático para a pessoa usuária.

Conclusão

Desempenho em banco de dados não se resume a boas consultas SQL ou a um servidor bem configurado. Outros fatores são importantes, como:

• ajustes de parâmetros de configuração internos do PostgreSQL;
• estrutura das tabelas do banco de dados;
• uso de índices apropriados;
• construção das consultas SQL;
• manutenção da base de dados.

Problemas de conexão, servidores pouco potentes ou poucos índices criados podem afetar o desempenho. Como DBA, é necessário entender e diagnosticar o ambiente para identificar ações que melhorem o desempenho.

Continuaremos discutindo este assunto no próximo vídeo. Até mais!

Otimização de consultas no PostgreSQL - Identificação dos gargalos

Nesta aula, vamos continuar nosso estudo sobre o desempenho do PostgreSQL, focando na identificação de gargalos de desempenho. Entender onde estão os gargalos é fundamental para melhorar a performance do banco de dados, especialmente em ambientes de produção.

Um gargalo de desempenho é um ponto do sistema onde a capacidade de processamento é insuficiente para lidar com a carga de trabalho, causando lentidão.

Esses gargalos podem surgir em diferentes áreas do banco de dados, como no processamento das consultas, no acesso a disco ou até mesmo na alocação de memória, podendo causar uma degradação significativa do sistema como um todo.

Na demonstração do vídeo passado, o gargalo estava no tráfego da conexão, especificamente no tráfego de internet. Identificamos isso executando a consulta no servidor local e na nuvem, comparando os tempos.

Configurando pg_stat_statements

Existem duas ferramentas que podemos usar para monitorar os gargalos.

Nesse vídeo, vamos falar sobre uma delas, que é a pg_stat_statements. Esta é uma extensão poderosa do PostgreSQL que coleta estatísticas sobre todas as consultas executadas. Através do acesso às tabelas geradas por essa extensão, podemos visualizar a performance das consultas SQL e o consumo de recursos.

Outra forma de verificar possíveis gargalos é a pg_stat_activity, que oferece uma visão da atividade atual do banco de dados, incluindo quais consultas estão sendo executadas, quais conexões estão abertas e por quanto tempo, além de identificar o usuário que está executando a conexão.

Para trabalhar com boa conectividade e performance do banco de dados, precisamos configurar algumas variáveis de ambiente do PostgreSQL. Dentre elas, destacamos quatro variáveis:

-- shared_preload_libraries = 'pg_stat_statements'
-- pg_stat_statements.track = all
-- pg_stat_statements.max = 10000
-- pg_stat_statements.track_utility = on

Vamos configurar esses parâmetros e comentar sobre cada um deles. O arquivo de configuração do PostgreSQL está no diretório C:\Arquivo de Programas\PostgreSQL\[versão]\data, e o nome do arquivo é postgresql.conf.

Clicando com o botão direito, vamos abrir esse arquivo com um editor de texto e buscar a primeira variável, shared_preload_libraries. No Notepad++, podemos clicar em "Localizar" no menu superior e escolher a opção "Localizar" (ou atalho "Ctrl + F") para buscar pela variável.

Este parâmetro indica quais bibliotecas podem ser inicializadas quando o PostgreSQL reinicia. Vamos configurá-lo para carregar a pg_stat_statements, que coleta estatísticas dos SQLs.

postgresql.conf:

# - Shared Library Preloading -

#local_preload_libraries = ''
#session_preload_libraries = ''
shared_preload_libraries = 'pg_stat_statements'    # (change requires restart)
#jit_provider = 'llvmjit'          # JIT library to use

A segunda variável é pg_stat_statements.track. Se não encontramos essa variável no arquivo, podemos inicializá-la abaixo da shared_preload_libraries.

Esta variável define quais comandos SQL serão rastreados. A opção all indica que todos os comandos serão rastreados pelo pg_stat_statements.

A terceira variável é pg_stat_statements.max, que define o número máximo de instruções SQL diferentes que serão rastreadas pela extensão pg_stat_statements.

Nesse caso, vamos iniciá-la para que até 10 mil instruções sejam rastreadas. Se o número exceder esse valor, as estatísticas mais antigas ou menos usadas serão descartadas para liberar espaço.

Por isso, é importante definir um valor apropriado de acordo com a quantidade de acessos e usos do banco de dados.

A quarta variável é pg_stat_statements.track_utility, que determina se alguns comandos executados pelo banco de dados para otimizar as estatísticas internas serão acompanhados pelo pg_stat_statements. Por exemplo, os comandos VACUUM, ANALYZE e COPY.

Configuramos o parâmetro como on para rastrear essas consultas.

# - Shared Library Preloading -

#local_preload_libraries = ''
#session_preload_libraries = ''
shared_preload_libraries = 'pg_stat_statements'    # (change requires restart)
pg_stat_statements.track = all
pg_stat_statements.max = 10000
pg_stat_statements.track_utility = on
#jit_provider = 'llvmjit'          # JIT library to use

Após salvar o arquivo com as variáveis de ambiente do PostgreSQL, precisamos reiniciar o serviço do PostgreSQL para que as alterações sejam carregadas.

Por meio da caixa de pesquisa, abrimos os "Serviços" do Windows e buscamos pelo serviço chamado postgresqlx-64-16. Depois, clicamos no botão de "Reiniciar" para parar e subir o serviço novamente.

Identificando gargalos de desempenho

Abrindo o pgAdmin e conectando com o banco de dados novamente, vamos executar a seguinte consulta três vezes:

SELECT * FROM tabela_de_clientes WHERE BAIRRO = 'Jardins';

Successfully run. Total query runtime: 125 msec. 3 rows affected.

Successfully run. Total query runtime: 54 msec. 3 rows affected.

Successfully run. Total query runtime: 55 msec. 3 rows affected.

Para buscar as estatísticas das três consultas, precisamos carregar a extensão pg_stat_statements:

CREATE EXTENSION pg_stat_statements;

Query returned successfully in 99 msec.

Em seguida, vamos fazer um SELECT de seis colunas da tabela pg_stat_statements, onde a nossa consulta é igual a SELECT * FROM tabela_de_clientes. Assim, poderemos acompanhar o desempenho dessa consulta dentro do banco.

SELECT query, calls, total_exec_time, min_exec_time, max_exec_time, mean_exec_time
FROM pg_stat_statements WHERE query LIKE 'SELECT * FROM tabela_de_clientes%';

Cada coluna dessa consulta tem um significado:

• query: instrução SQL executada que queremos acompanhar;
• calls: número de vezes que a consulta foi executada;
• total_exec_time: tempo total somado das consultas (em milissegundos);
• min_exec_time: tempo da consulta mais rápida (em milissegundos);
• max_exec_time: tempo da consulta mais lenta (em milissegundos);
• mean_exec_time: tempo médio das consultas (em milissegundos).

Ao ter uma noção entre o tempo mínimo e máximo das consultas, podemos tentar entender, por exemplo, como o horário afeta a consulta.

Vamos executar a consulta da tabela de estatísticas de queries e conferir o resultado:

Na coluna "query", obtemos a consulta e a condição WHERE BAIRRO = $1. Isso significa que se executarmos uma consulta passando outro bairro, seus dados também entrarão nessa estatística.

Também descobrimos que essa consulta foi executada 3 vezes com um tempo total de execução de 10 milissegundos. A consulta mais rápida foi executada em 2.9 milissegundos, enquanto a mais lenta demorou 3.7 milissegundos. Na média, a consulta é resolvida em 3.33 milissegundos.

Conclusão

Ao acompanhar a tabela pg_stat_statements, a pessoa administradora do banco terá uma noção do tempo das consultas SQL executadas no banco, identificar quais estão demorando mais ou menos e diagnosticar possíveis gargalos.

No próximo vídeo, falaremos sobre análise de carga. Até mais!

Sobre o curso Administração com PostgreSQL: desempenho e otimização

O curso Administração com PostgreSQL: desempenho e otimização possui 209 minutos de vídeos, em um total de 62 atividades. Gostou? Conheça nossos outros cursos de SQL e Banco de Dados em Data Science, ou leia nossos artigos de Data Science.

Matricule-se e comece a estudar com a gente hoje! Conheça outros tópicos abordados durante o curso:

• Otimização de consultas no PostgreSQL
• Índices e suas aplicações para otimização
• Análise e otimização de consultas avançadas
• Estruturação e manutenção de Tabelas para desempenho
• Otimização prática de consultas SQL no PostgreSQL