Mais Recursos
Nesta aula, iremos explorar mais detalhes sobre otimizações no PostgreSQL (Índices, views materializadas e subqueries).
Iremos utilizar o banco de dados da aula anterior, sfbikeshare
.
Exercício
Exercícios para Prática
Faça os seguintes exercícios para praticar e analisar o impacto do que foi aprendido na aula anterior.
Exercício
Answer
A primeira query filtra pela coluna category1
, enquanto a segunda filtra pela coluna category2
. Ambas as colunas são do tipo inteiro, da mesma tabela.
Exercício
Answer
A diferença na performance das duas queries após a criação dos índices ocorre devido à distribuição dos valores nas colunas category1
e category2
.
O índice será mais eficaz se uma coluna tem uma distribuição mais uniforme dos valores, permitindo que o PostgreSQL localize rapidamente as linhas relevantes.
Por outro lado, se uma coluna tem muitos valores repetidos (baixa cardinalidade), o índice pode não ser tão útil, pois muitas linhas podem corresponder ao mesmo valor, levando a um maior número de leituras de blocos.
Distribuição category1
:
SELECT s.category1,
COUNT(*) AS qtde_cat1
FROM public.status s
GROUP BY s.category1
ORDER BY s.category1 ASC;
Distribuição category2
:
Nem tudo se resolve com índices
Embora os índices sejam ferramentas poderosas para otimização de consultas, eles não são a solução para todos os problemas de performance.
Em alguns casos, outras estratégias podem ser mais eficazes, como a reestruturação de consultas, particionamento de tabelas ou até mesmo ajustes na configuração do banco de dados.
Vamos explorar um exemplo:
Exercício
Answer
A query retorna a média de bicicletas disponíveis (bikes_available
) para cada estação (station_id
) localizada na cidade de 'Redwood City'
.
Exercício
Exercício
Answer
Uma forma de reescrever a query é utilizando um JOIN
ao invés de uma subconsulta com IN
. Isso pode melhorar a performance, pois o otimizador de consultas do PostgreSQL pode gerar um plano de execução mais eficiente com JOINs
.
A query reescrita ficaria assim:
Exercício
Answer
No computador do professor, a query reescrita utilizando JOIN
executou 20 vezes mais rápido em comparação com a query original que utiliza uma subconsulta com IN
.
Views Materializadas
Exercício
Answer
Uma View é uma tabela virtual que resulta de uma consulta SQL. Ela não armazena dados fisicamente, mas sim a definição da consulta que gera os dados quando a View é acessada.
As Views são usadas para simplificar consultas complexas, encapsular lógica de negócios e fornecer uma camada de abstração sobre as tabelas subjacentes.
Já as Views materializadas são uma forma de armazenar o resultado de uma consulta para acesso rápido, evitando a necessidade de recalcular os dados toda vez que a consulta é executada.
Importante
Diferente de uma View comum, as Views materializadas armazenam fisicamente os dados resultantes da consulta, o que pode melhorar significativamente o desempenho para consultas complexas ou que envolvem grandes volumes de dados.
Assim, Views materializadas são particularmente úteis em cenários onde os dados não mudam com frequência, permitindo que consultas subsequentes sejam atendidas rapidamente a partir dos dados pré-calculados.
Atualização
Se os dados quase nunca mudam, diminui-se a necessidade de atualizar a View materializada com frequência, o que pode ser um processo custoso.
Vamos explorar o exemplo do último exercício.
Exercício
Exercício
Answer
A consulta na View materializada executa muito mais rápido do que tanto a query original quanto a query reescrita com JOIN
, porque os dados já estão pré-calculados e armazenados fisicamente.
Benefícios da IaC
Answer
Não, a View materializada não reflete automaticamente as mudanças nos dados subjacentes.
Ela precisa ser atualizada manualmente para incorporar quaisquer alterações feitas nas tabelas originais.
As View materializada precisam ser atualizadas para refletir mudanças nos dados. Isso pode ser feito com o comando:
Esse comando recalcula e atualiza os dados armazenados na View materializada. Este processo pode ser custoso dependendo do tamanho dos dados e da complexidade da consulta original.
Idealmente, as Views materializadas são atualizadas em horários específicos ou quando necessário, para equilibrar a necessidade de dados atualizados com o desempenho da consulta.
Dica
Pesquise sobre pg_cron
, uma extensão do PostgreSQL que permite agendar tarefas, como a atualização de Views materializadas, de forma automática e periódica.
Mas você também pode usar ferramentas externas para agendar essas atualizações, como o cron
do sistema operacional, ou ainda scripts que são parte de pipelines orquestrados.
Exercício
Exercícios
Vamos trabalhar com outra tabela.
Exercício
Exercício
Exercício
Exercício
Answer
Alguns cenários onde o uso de Views materializadas seria benéfico incluem:
-
Relatórios frequentes: Quando há necessidade de gerar relatórios complexos regularmente, como dashboards de negócios que agregam grandes volumes de dados.
-
Consultas complexas: Quando as consultas envolvem múltiplas junções, agregações ou cálculos que são custosos em termos de tempo de execução.
-
Dados estáticos ou pouco dinâmicos: Quando os dados subjacentes não mudam com frequência, permitindo que a View materializada seja atualizada periodicamente sem impactar a performance.
-
Redução da carga no banco de dados: Em sistemas com alta demanda de leitura, as Views materializadas podem aliviar a carga ao fornecer resultados pré-calculados.
Exemplo prático
Um sistema de análise de vendas que gera relatórios sobre o desempenho de produtos, onde os dados de vendas não mudam após o fechamento do dia e o dia atual não é considerado.
Neste caso, a View materializada pode ser atualizada de madrugada, sem sobrecarregar o sistema, permitindo que os relatórios sejam gerados rapidamente durante o dia seguinte.
Exercício
Answer
Alguns cenários onde o uso de Views materializadas não seria indicado incluem:
-
Dados altamente dinâmicos: Quando os dados subjacentes mudam frequentemente, exigindo atualizações constantes da View materializada, o que pode ser custoso e ineficiente.
-
Consultas simples: Quando as consultas são simples e rápidas de executar, o overhead de manter uma View materializada pode superar os benefícios.
-
Espaço de armazenamento limitado: Se o banco de dados tem restrições de espaço, armazenar Views materializadas pode não ser viável.
-
Necessidade de dados em tempo real: Em aplicações que exigem acesso a dados em tempo real, as Views materializadas podem introduzir latência devido à necessidade de atualização periódica.
Exemplo prático
Um sistema de monitoramento em tempo real que rastreia transações financeiras, onde os dados mudam constantemente e a precisão imediata é crucial.