Biscuit PostgreSQL: Tutorial Completo de Indexação para LIKE com Alta Performance

Se você trabalha com PostgreSQL, já deve ter enfrentado o desafio de tornar consultas com LIKE e curingas (% e _) realmente rápidas. Este tutorial completo vai te mostrar como alcançar uma performance excepcional em buscas textuais utilizando a extensão Biscuit PostgreSQL.

O Biscuit é uma extensão inovadora que implementa um método próprio de acesso a índices, projetado especificamente para padrões LIKE e ILIKE. Ele oferece ganhos significativos em relação ao tradicional pg_trgm. Vamos explorar tudo o que você precisa saber para implementá-lo.

O que é a extensão Biscuit?

O nome é um acrônimo para Bitmap Indexed Searching with Comprehensive Union and Intersection Techniques. Em vez de depender de trigramas (como faz o pg_trgm), o Biscuit constrói e consulta bitmaps posicionais para cada caractere de uma string.

Isso torna o processo determinístico: os resultados são exatos e não exigem uma etapa de verificação posterior na tabela (recheck no heap), que costuma ser a principal fonte de lentidão em outras abordagens.

Como Funciona a Indexação por Bitmaps?

Para cada string indexada, a extensão cria conjuntos estratégicos de bitmaps:

• Índice Positivo (Forward): Mapeia a posição de cada caractere a partir do início da string (Ex: em "Hello", cria-se H@0, e@1, l@2).
• Índice Negativo (Backward): Mapeia a posição de cada caractere a partir do final (Ex: o@-1 para o último, l@-2 para o penúltimo).
• Índices de Tamanho (Length):
  • length[5]: IDs de strings com exatamente 5 caracteres.
  • length_ge[3]: IDs de strings com 3 ou mais caracteres.

Para uma consulta como LIKE 'abc%def', o Biscuit executa operações lógicas AND entre os bitmaps:

-- 1. Encontra candidatos que começam com "abc" 
Candidatos = pos[a@0] ∩ pos[b@1] ∩ pos[c@2]

-- 2. Filtra os que terminam com "def" (usando índices negativos)
Candidatos = Candidatos ∩ neg[f@-1] ∩ neg[e@-2] ∩ neg[d@-3]

-- 3. Garante que a string tenha pelo menos 6 caracteres
Candidatos = Candidatos ∩ length_ge[6]

O resultado é um conjunto de IDs que correspondem exatamente ao padrão, sem a necessidade de ler a tabela original para confirmar.

Instalação

Pré-requisitos: PostgreSQL 16 (ou superior) e ferramentas de build (gcc, make, pg_config). É recomendável ter a biblioteca CRoaring para otimização das operações com bitmaps.

Método 1: Instalação via PGXN (Recomendado)

pgxn install biscuit
psql -d seu_banco_de_dados -c "CREATE EXTENSION biscuit;"

Método 2: Compilando do Código Fonte

git clone https://github.com/Crystallinecore/biscuit.git
cd biscuit
make
sudo make install
psql -d seu_banco_de_dados -c "CREATE EXTENSION biscuit;"

🚀 Guia Rápido de Uso

1. Criando um Índice Biscuit

A sintaxe é idêntica à de outros índices, utilizando USING biscuit.

-- Índice básico em uma coluna de texto
CREATE INDEX idx_users_name ON users USING biscuit(name);

-- Índice em múltiplas colunas
CREATE INDEX idx_products_search ON products USING biscuit(name, description, category);

2. Realizando Consultas

O otimizador do PostgreSQL passará a usar o índice automaticamente para padrões LIKE.

-- Busca por substring (cenário mais otimizado)
SELECT * FROM users WHERE name LIKE '%john%';

-- Busca por sufixo (utiliza os índices negativos)
SELECT * FROM logs WHERE message LIKE '%ERROR';

3. Operadores ILIKE e NOT LIKE

O Biscuit possui suporte nativo para buscas case-insensitive e negação de bitmaps de forma altamente eficiente.

-- Case-insensitive
SELECT * FROM users WHERE name ILIKE '%son';

-- Negação (excluindo padrões)
SELECT * FROM users WHERE name LIKE '%a%' AND name NOT LIKE '%3%';

4. Índices com Expressões

Você pode indexar expressões, como a função LOWER(), para forçar buscas case-insensitive manuais:

CREATE INDEX idx_users_lower_name ON users USING biscuit(LOWER(name));

Vantagens vs. Desvantagens

Biscuit ou pg_trgm: Qual Escolher?

Escolha o Biscuit se:

• Sua aplicação faz muitas buscas por substring ('%termo%').
• O banco tem alta taxa de leitura (SELECT) e memória RAM sobrando.
• Você precisa de tempos de resposta extremamente previsíveis e exatos.

Escolha o pg_trgm se:

• Há pouca memória RAM disponível no servidor.
• Você precisa de buscas por similaridade fonética ou expressões regulares.
• A tabela sofre operações de escrita intensas e constantes.

Exemplo de Benchmark

Em testes com 1 milhão de registros, o Biscuit apresentou vantagem clara em cenários de substring e consultas complexas:

Monitoramento e Boas Práticas

Para inspecionar o estado interno do índice (registros, tombstones e otimizações), utilize a função nativa:

SELECT biscuit_index_stats('idx_products_search'::regclass);

• Índices Parciais: Se suas consultas costumam ter filtros fixos (WHERE status = 'active'), crie índices parciais para economizar RAM.
• Reindexação: Em tabelas com muitos DELETEs, rode um REINDEX periodicamente para limpar os tombstones da memória.
• Homologação Primeiro: Por ser uma extensão em desenvolvimento ativo, teste sempre seu workload em um ambiente seguro antes de enviar para produção.

Referências

• Repositório Oficial no GitHub
• Página do Biscuit no PGXN

Continue aprendendo

Se você quer deixar a arquitetura do seu ecossistema de desenvolvimento ainda mais parruda e produtiva, dê um confere nestes artigos:

• Extensões PostgreSQL: Substitua Redis, MongoDB e Kafka com SQL Aumente a eficiência consolidando sua stack. Veja quando faz sentido e até onde o Postgres aguenta o tranco pesado na arquitetura do backend, lidando com busca vetorial, textual e filas diretamente no SQL.

• Como gerar tipos TypeScript direto do PostgreSQL com Kanel Esqueça aquele trabalho braçal de “sincronizador humano” de tipos. Automatize o tédio e garanta que sua base de dados seja a única fonte da verdade, com type safety estrito pro seu projeto não capotar.

• Como Otimizar PostgreSQL na Prática (Recomendação em Banco de Dados) Descubra os ajustes finos de infraestrutura e índices para extrair a máxima performance e reduzir o tempo de resposta das suas consultas pesadas.

• Problema N+1 em Queries SQL: Como Identificar e Resolver (Recomendação de Performance) Entenda o gargalo de performance mais comum no uso de ORMs e aprenda as estratégias definitivas para resolver as consultas N+1 no seu backend.