Otimização de funções para trabalhar com grandes volumes de dados

Quando a gente fala de otimizar funções no PostgreSQL, normalmente estamos pensando em dois pontos principais: indexação e particionamento. Essas duas técnicas ajudam a processar grandes volumes de dados mais rápido, evitando cálculos desnecessários e acessando os dados "na mosca". Bora entender isso melhor.

Índices no mundo dos bancos de dados funcionam igualzinho aos índices de livros. Quando você procura uma informação num livro, não lê todas as páginas uma por uma. Você vai no índice, acha o tema e já pula pra página certa. É basicamente isso que os índices fazem no PostgreSQL.

Criando índices

Índices são criados com o comando CREATE INDEX. Olha um exemplo simples:

-- Criando um índice na coluna id da tabela users pra acelerar as buscas
CREATE INDEX idx_users_id ON users (id);

Agora, se você rodar uma query tipo:

SELECT * FROM users WHERE id = 42;

O PostgreSQL vai usar o índice criado pra achar rapidinho a linha certa.

Exemplo: Otimizando função usando índices

Imagina que a gente tem uma função que pega dados dos pedidos na tabela orders por usuário:

CREATE OR REPLACE FUNCTION get_user_orders(user_id INT)
RETURNS TABLE(order_id INT, order_date DATE) AS $$
BEGIN
    RETURN QUERY 
    SELECT id, order_date 
    FROM orders 
    WHERE user_id = user_id;
END; 
$$ LANGUAGE plpgsql;

Se a tabela orders tiver milhões de linhas, essa função vai rodar devagar. Solução? Criar um índice em user_id:

CREATE INDEX idx_orders_user_id ON orders (user_id);

Agora a query dentro da função vai ficar bem mais rápida, porque o PostgreSQL vai usar o índice pra buscar as linhas.

Tipos de índices

O PostgreSQL suporta vários tipos de índices, mas os mais usados são B-TREE e GIN. Olha um resumo:

Se quiser se aprofundar em índices, dá uma olhada na documentação oficial do PostgreSQL.

Particionamento de dados

Se índices aceleram a busca, particionamento é uma técnica que ajuda a dividir uma tabela em "pedaços" menores (partições). Isso é útil quando você tem uma quantidade gigante de dados numa tabela só.

Imagina que você tem uma tabela orders que guarda pedidos dos últimos 10 anos. Se você faz uma query pra pegar pedidos do último mês, o PostgreSQL ainda vai olhar a tabela toda, o que é pesado. O particionamento resolve isso, dividindo os dados, por exemplo, por ano.

Criando uma tabela particionada

Olha como dá pra criar uma tabela particionada:

-- Criando a tabela orders como partição mãe
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_date DATE NOT NULL,
    user_id INT NOT NULL
) PARTITION BY RANGE (order_date);

-- Criando tabelas filhas pra cada ano
CREATE TABLE orders_2023 PARTITION OF orders FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE orders_2022 PARTITION OF orders FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

Agora, quando você rodar uma query tipo:

SELECT * FROM orders WHERE order_date >= '2023-01-01' AND order_date < '2023-02-01';

O PostgreSQL já saca que só precisa procurar na tabela orders_2023, sem ter que olhar tudo.

Usando particionamento em funções

Imagina que a gente tem uma função que pega pedidos de um ano específico. Com particionamento, as queries dentro da função vão ser mais rápidas, porque o PostgreSQL vai trabalhar só com a tabela filha certa.

CREATE OR REPLACE FUNCTION get_orders_by_year(year INT)
RETURNS TABLE(order_id INT, order_date DATE) AS $$
BEGIN
    RETURN QUERY 
    SELECT id, order_date 
    FROM orders 
    WHERE order_date >= make_date(year, 1, 1) 
      AND order_date < make_date(year + 1, 1, 1);
END;
$$ LANGUAGE plpgsql;

Casos práticos

1. Casos de indexação

Busca por strings: se você tem uma tabela de produtos e sempre procura produtos pelo nome, cria um índice no campo name:

CREATE INDEX idx_products_name ON products (name);

Acelerando ordenação: se suas queries sempre ordenam por data, cria um índice:

CREATE INDEX idx_orders_date ON orders (order_date);

2. Casos de particionamento

Dados históricos: se a tabela tem dados com timestamp, particionar por dia, mês ou ano vai deixar as queries muito mais rápidas.

Dados geográficos: se a tabela tem dados por país, cria partições pra cada país.

Erros comuns e como resolver

Muita gente erra criando índice demais. Isso deixa as inserções e updates mais lentos, porque o PostgreSQL tem que atualizar todos os índices sempre que a tabela muda. Dica: só cria índice nos campos que você realmente usa pra filtrar ou ordenar.

Outro erro clássico é particionar errado. Se você cria partição demais (tipo, uma pra cada dia em vez de cada mês), pode acabar tendo um monte de trabalho só pra gerenciar essas tabelas.