Índices BRIN no PostgreSQL: um guia para joias de desempenho ocultas

Quando falamos sobre PostgreSQL e otimização de consultas, a maioria das pessoas pensa imediatamente em índices B-Tree, GIN, GiST e assim por diante. Mas os índices BRIN muitas vezes permanecem nas sombras, apesar de serem incrivelmente úteis em determinados cenários. Eles podem aumentar drasticamente o desempenho, especialmente ao lidar com tabelas enormes e espaço em disco limitado. Hoje vou explicar como o BRIN funciona e quando ele brilha.

O que é BRIN?

BRIN significa Índice de intervalo de blocos. Embora os índices regulares tenham como objetivo armazenar informações detalhadas sobre cada linha, o BRIN adota uma abordagem diferente. Ele armazena informações resumidas para grupos de páginas, chamadas “zonas”, em vez de indexar cada linha individualmente.

Para simplificar, o BRIN divide uma tabela em blocos – cada zona normalmente cobre 32 páginas por padrão – e armazena os valores mínimo e máximo de uma coluna (ou outras características generalizadas dependendo da classe do operador) dentro dessa zona. Quando você consulta os dados, o BRIN identifica as zonas relevantes e ignora o restante.

É 100% preciso? Não exatamente – você ainda precisará verificar as linhas nas zonas selecionadas. No entanto, esta filtragem é significativamente mais rápida do que varrer a tabela inteira.

Quando o BRIN é uma boa escolha?

Aqui estão alguns cenários em que o BRIN se destaca:

1. Mesas enormes
   Ao lidar com tabelas contendo milhões ou bilhões de linhas, os índices B-Tree tradicionais podem ficar inchados e ocupar um espaço substancial. O BRIN, por outro lado, permanece leve e compacto.

2. Correlação Pure de Dados
   Se seus dados estiverem em ordem pure, o BRIN pode ser particularmente eficaz. Por exemplo, se você tiver um order_date coluna em um orders tabela, é provável que os pedidos mais antigos sejam armazenados no início da tabela e os mais recentes no remaining. Para consultas direcionadas a um mês específico, o BRIN restringe rapidamente as zonas relevantes.

3. Economia de espaço em disco
   Os índices BRIN são notavelmente compactos. Em vez de armazenar todos os valores individuais, utilizam metadados agregados por zona, o que reduz os requisitos de armazenamento.

Limitações do BRIN

Como qualquer ferramenta, o BRIN tem suas vantagens:

• Imprecisão: BRIN não identifica locais de dados. Indica apenas zonas onde os dados poder ser, exigindo filtragem adicional dentro dessas zonas.
• Dados dispersos: Se os valores das colunas estiverem espalhados aleatoriamente, o BRIN não será capaz de filtrar zonas irrelevantes de forma eficaz, tornando-o menos benéfico.

Criando um índice BRIN

Aqui está um exemplo rápido de como criar um índice BRIN:

CREATE INDEX idx_orders_date_brin ON orders
USING BRIN(order_date)
WITH (pages_per_range = 32);

O pages_per_range parâmetro determina o número de páginas em cada zona. O padrão é 128 páginas, mas você pode ajustá-lo. Um valor menor aumenta a precisão do índice, mas também aumenta o tamanho do índice. Experimentar esse parâmetro pode ajudar a encontrar o melhor equilíbrio para seus dados.

Automatizando Resumos

Você pode usar extensões como autosummarize para automatizar a atualização dos dados de resumo, eliminando a necessidade de chamar manualmente brin_summarize_new_values.

Lessons de Operadores BRIN: Minmax e Inclusão

BRIN usa lessons de operadores para definir como resume os dados. As duas abordagens principais são:

• Aulas mínimas: Armazene os valores mínimo e máximo em cada zona. Perfect para tipos ordenados como números, datas ou strings.
• Aulas de inclusão: Lide com tipos mais complexos, como geometrias, redes IP ou intervalos. Essas lessons armazenam representações “em massa”, como caixas delimitadoras ou uniões de intervalos, permitindo operações em estruturas de dados complexas.

Por que as aulas de inclusão são importantes

As lessons de inclusão podem processar dados complexos de forma eficiente. Por exemplo, eles podem identificar zonas contendo objetos que cruzam uma área específica, tornando-os úteis para casos de uso especializados, como dados espaciais.

Os índices BRIN são um recurso poderoso, mas subutilizado, no PostgreSQL. Embora não sejam adequados para todas as situações, eles podem melhorar significativamente o desempenho ao trabalhar com grandes conjuntos de dados e dados ordenados naturalmente. Experimente o BRIN para desbloquear seu potencial e otimizar suas consultas ao banco de dados.

Existem operações minmax para a maioria dos tipos e operadores de inclusão para estruturas mais exóticas. Ou seja, você pode indexar não apenas números e strings simples, mas também tipos complexos. Por exemplo, box_inclusion_ops permitirá que você pesquise objetos em uma determinada área geográfica, cortando rapidamente zonas onde exatamente não há objetos adequados.

Exemplo de otimização de amostra

Suponha que haja uma tabela de pedidos com várias linhas:

CREATE TABLE orders (
    id            BIGSERIAL PRIMARY KEY,
    order_date    DATE NOT NULL,
    customer_id   BIGINT NOT NULL,
    total_amount  NUMERIC(10, 2) NOT NULL
);

Inserindo um monte de dados:

INSERT INTO orders (order_date, customer_id, total_amount)
SELECT
    (DATE '2023-01-01' + (RANDOM()*365)::INT),
    (RANDOM()*1000000)::BIGINT,
    (RANDOM()*1000)::NUMERIC(10,2)
FROM generate_series(1,10000000) g;

Agora criamos o índice BRIN:

CREATE INDEX idx_orders_date_brin ON orders
USING BRIN(order_date date_minmax_ops)
WITH (pages_per_range = 64);

date_minmax_ops diz: armazenaremos an information mínima e máxima para cada zona de 64 páginas.

Ao consultar:

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE order_date BETWEEN '2023-06-01' AND '2023-06-15';

O plano mostrará que o PostgreSQL consultará primeiro o índice BRIN, determinará quais zonas podem conter as datas desejadas e ignorará o resto. Se os dados forem classificados aproximadamente por information (por exemplo, conforme inseridos), o número de zonas extras será minimizado.

Sutilezas dos parâmetros

pages_per_range

• Valores menores: fornece uma filtragem mais precisa (cada zona é menor, portanto, menos lixo), mas resulta em um tamanho de índice maior.
• Valores maiores: reduz o tamanho do índice, mas piora a precisão.
• Recomendação: Teste em dados reais para encontrar o equilíbrio ideally suited.

autovacuum e brin_summarize_new_values

Lessons de operadores personalizados

Se nenhuma classe de operador padrão atender às suas necessidades, você poderá escrever a sua própria. Esta é uma abordagem mais avançada, exigindo que você:

• Implemente funções C para descrever como:
  • Valores agregados,
  • Mesclar zonas e
  • Verifique interseções com consultas.
• Isso permite indexar estruturas de dados exclusivas ou exóticas, mas é uma tarefa complexa e altamente técnica.

Conclusão

Os índices BRIN não substituirão os índices B-Tree em todos os lugares. Entretanto, nos casos em que os dados estão organizados ou têm uma correlação pure com sua localização física, o BRIN pode oferecer um desempenho incrível com tamanho mínimo de índice.