Otimização de funções analíticas para grandes volumes de dados: indexação e particionamento

Quando os dados começam a ficar gigantescos (tipo aquelas mensagens de deadline no chat da firma), as consultas pra buscar e processar começam a ficar lentas. Olha só os principais motivos:

1. Falta de índices. Quando o PostgreSQL precisa escanear a tabela inteira pra rodar uma query (isso é chamado de "Seq Scan" — varredura sequencial), a consulta pode demorar bem mais.
2. Consultas SQL ineficientes. Se as queries não são pensadas pra performance, mesmo com índices você pode sofrer no ambiente de produção. Esqueceu de usar condições chave no WHERE? Se prepara pra esperar sentado.
3. Grandes volumes de dados numa tabela só. Tipo quando você tenta analisar vendas de todos os anos de uma vez, nem índice salva.

Mas relaxa, tem dois jeitos clássicos de lidar com isso: Indexação e Particionamento.

Usando índices pra acelerar consultas

Olha um exemplo simples de como criar um índice:

CREATE INDEX idx_sales_date ON sales(transaction_date);

• Aqui idx_sales_date é o nome do índice (você pode chamar do jeito que quiser, mas é melhor dar um nome que faça sentido).
• ON sales(transaction_date) — diz pra qual tabela e coluna o índice vai ser criado.

Esse índice é especialmente útil se você sempre filtra as queries pelo campo transaction_date.

Exemplo de consulta que vai ficar mais rápida com esse índice:

SELECT *
FROM sales
WHERE transaction_date BETWEEN '2023-01-01' AND '2023-12-31';

Indexação de chaves compostas

Se suas queries usam várias colunas juntas, tipo region e product_id, pensa em criar um índice composto:

CREATE INDEX idx_sales_region_product ON sales(region, product_id);

Agora consultas desse tipo vão voar:

SELECT *
FROM sales
WHERE region = 'North America' AND product_id = 42;

Usando índices únicos

Índices únicos não só aceleram buscas, mas também garantem que os valores na coluna sejam únicos. Tipo assim:

CREATE UNIQUE INDEX idx_unique_customer_email ON customers(email);

Agora não tem como cadastrar dois clientes com o mesmo e-mail sem querer.

Indexação pra funções analíticas

Algumas funções de análise de dados, tipo SUM, COUNT ou AVG, podem usar o índice pra calcular mais rápido. Olha só:

CREATE INDEX idx_sales_amount ON sales(amount);

Consulta:

SELECT SUM(amount)
FROM sales 
WHERE transaction_date >= '2023-01-01';

vai rodar mais rápido por causa do índice.

Particionamento de tabelas pra lidar com grandes volumes de dados

Particionamento de tabelas é o processo de dividir uma tabela grandona em partes menores e lógicas, chamadas de partições. Por exemplo, você pode dividir a tabela sales em partições por ano: sales_2021, sales_2022 e por aí vai.

Parece complicado? Na real, o PostgreSQL facilita muito isso.

Tipos de particionamento

1. Particionamento por intervalo (Range Partitioning). Os dados são divididos por intervalos, tipo por data.
2. Particionamento por lista (List Partitioning). Os dados são divididos por valores exatos, tipo por região.
3. Particionamento por hash (Hash Partitioning). Usa função hash pra dividir os dados (raro de usar na mão).

Criando uma tabela particionada

Bora criar uma tabela de vendas particionada por ano.

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    transaction_date DATE NOT NULL,
    amount NUMERIC,
    region TEXT
) PARTITION BY RANGE (transaction_date);

Agora vamos criar as partições pros anos diferentes:

CREATE TABLE sales_2021 PARTITION OF sales
FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');

CREATE TABLE sales_2022 PARTITION OF sales
FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

Consultas que filtram por data vão automaticamente acessar só a partição certa. Dá pra conferir isso usando o comando EXPLAIN.

Exemplo com particionamento

Assim ficaria uma consulta pra somar vendas só de 2021:

SELECT SUM(amount)
FROM sales
WHERE transaction_date BETWEEN '2021-01-01' AND '2021-12-31';

Como você vê, o PostgreSQL só mexe na partição sales_2021 e não escaneia a tabela toda.

Exemplo: otimizando cálculo de métricas por região

Imagina que você quer calcular o total de vendas por região. Sem índices e partições, isso demora uma vida. Primeiro, cria um índice pra coluna region:

CREATE INDEX idx_sales_region ON sales(region);

Sua consulta:

SELECT region, SUM(amount)
FROM sales
GROUP BY region;

Agora o processamento acelera por causa do índice.

Exemplo: particionando dados temporais

Pra dados temporais, tipo transações ou logs, crie partições por mês. Exemplo:

CREATE TABLE sales_monthly PARTITION BY RANGE (transaction_date);

CREATE TABLE sales_jan_2023 PARTITION OF sales_monthly
FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');

Consulta:

SELECT SUM(amount)
FROM sales_monthly
WHERE transaction_date >= '2023-01-01' AND transaction_date < '2023-02-01';

vai rodar mais rápido porque o PostgreSQL lê só a partição sales_jan_2023.

Exemplo: juntando indexação e particionamento

Dá pra combinar indexação e particionamento pra tirar o máximo de performance. Por exemplo, você pode criar índices dentro de cada partição. Olha só:

CREATE INDEX idx_sales_amount_jan_2023 ON sales_jan_2023(amount);

Como evitar erros comuns

Muitos problemas de performance vêm do uso errado de índices e particionamento. Tipo assim:

• Ter índice demais pode deixar as operações de insert lentas.
• As partições têm que ser bem distribuídas; partições muito pequenas ou muito grandes pioram a performance.
• Esquecer de analisar a performance (EXPLAIN ANALYZE) antes de otimizar é tipo tentar consertar o carro sem abrir o capô.

Sempre confira se suas otimizações realmente aceleram as coisas e não tenha medo de testar.