Busca textual no PostgreSQL com exemplos

Uma coisa que me deixa frustrado é não encontrar o que desejo nos sites que visito.
Quase todo site hoje em dia possui um campo de busca inútil e limitado. Em uma época que existe tecnologias como Postgres, Elasticsearch e Solr, que possibilitam a criação de mecanismos de buscas modernos, eu ainda vejo um enorme desconhecimento sobre isso pelas empresas e desenvolvedores de software.

Nesse texto vou falar sobre a busca textual e mostrar um exemplo prático utilizando o Postgres. Se prepara que tem muita informação.

O que é?

Busca textual, ou Full Text Search em inglês, é uma técnica para procurar por palavras ou frases de maneira extremamente rápida e performática. Suas características são:

Suporte a múltiplos idiomas
Possibilidade de ignorar erros de ortografia
Suporte ao mapeamento de diferentes formas da mesma palavra. Também conhecido como Stemming
Classificar o resultado pelo ranking da palavra

O Postgres possui operadores de busca como ~ e LIKE que não oferecem as características acima e tem sérios problemas de performance e velocidade. Em uma tabela com 1 milhão de registros, uma busca usando LIKE pode levar alguns segundos, enquanto uma busca textual leva milésimos de segundo.

Termos utilizados

Quando falamos em busca textual no Postgres, precisamos entender os termos utilizados:

Document: documento é o texto pré-processado e indexado, utilizado na busca.
Tokens: são as palavras classificadas em um document. Essa classificação pode ser: string, número, endereço de email, palavra composta, etc.
Lexemes: lexemas são palavras, assim como tokens, mas normalizadas, ou seja, possuem as diferentes formas da mesma palavra. Essas formas podem ser maíusculas e minúsculas, singular e plural, etc.
Stop words: são palavras consideradas irrelevantes para a busca. Geralmente artigos e preposições. Ex.: a, de, do, para, etc.
Dicionários: é um programa que aceita um token como input e retorna um ou mais lexemas. Ex.: remover as stop words.

Dicionários

Dicionários são usados para eliminar palavras que não devem ser consideradas em uma pesquisa (stop words) e para normalizar palavras de forma que diferentes formas derivadas da mesma palavra sejam correspondidas. Uma palavra normalizada com sucesso é chamada de lexema.
A utilização de dicionários permite mapear sinômimos e variações de uma palavra ou mapear frases para uma única palavra.

O Postgres fornece dicionários para vários idiomas. Existem também templates predefinidos que podem ser usados para criar novos dicionários com parâmetros personalizados.

É possível utilizar mais de um dicionário de forma sequencial. Isso significa que o output de um dicionário vai ser o input do outro.
Veja abaixo os tipos de dicionários disponíveis:

Stop words

Palavras comuns que aparecem em quase todo documento e que não têm relevância para a busca. A lista padrão está em $SHAREDIR/tsearch_data/portuguese.stop.

Dicionário Simples

Converte a palavra para minúscula e verifica se ela existe no dicionário de stop words. Se não existir, retorna a palavra minúscula.

Sinônimos

Substitui uma palavra por outra. Boa opção para erros de ortografia.
Você precisa criar um arquivo contendo a palavra original e o sinônimo dela ao lado. Esse arquivo precisa ter a extensão .syn. Ex.:

echo "capassete capacete" | sudo tee -a $SHAREDIR/tsearch_data/brazilian.syn

Thesaurus

O dicionário Thesaurus é uma coleção de palavras que inclui informações sobre a relação de palavras e frases. No exemplo abaixo, a busca por “gerenciamento do negócio” ou “administração de empresas” pode retornar documentos que possuem a palavra “gestão”:

echo "
gerenciamento ? negócio  : gestão
administração ? empresas : gestão" | sudo tee -a $SHAREDIR/tsearch_data/brazilian.ths

O arquivo precisa ter a extensão .ths.

Ispell

Dicionário Ispell reduz a palavra ao infinitivo, reconhecendo um conjunto limitado de palavras e permitindo combinar conjugações de um termo de busca, como verbos irregulares, por exemplo. Ex.: o lexema das palavras afilhado, afilhada, afilhados é afilhar.

Esse dicionário deve ser seguidos por outro dicionário mais amplo, como um dicionário Snowball.

Postgres suporta diferentes formatos de dicionário Ispell, como por exemplo o Hunspell ou Aspell. Você precisa instalar o dicionário na mesma máquina onde o Postgres está instalado, já que ele não vem com nenhum dicionário Ispell.

Exemplo usando o Hunspell:

# Exemplo instalando no Ubuntu
sudo apt install hunspell-pt-br

# Usa o iconv para converter para UTF-8
iconv -f latin1 -t UTF-8 /usr/share/hunspell/pt_BR.aff | sudo tee $SHAREDIR/tsearch_data/brazilian.affix
iconv -f latin1 -t UTF-8 /usr/share/hunspell/pt_BR.dic | sudo tee $SHAREDIR/tsearch_data/brazilian.dict

Os arquivos do Hunspell devem ser convertidos para UTF-8 e possuir a extensão .affix e .dict.

Snowball

O dicionário Snowball fornece um algoritmo de stemming que entende como reduzir as formas variantes comuns de palavras à uma base ou radical de grafia em seu idioma. Esse dicionário reconhece tudo, mesmo que não seja capaz de simplificar a palavra, por isso deve ser colocado no final da lista de dicionários.

No idioma inglês, ele consegue mapear as palavras connection, connections, connective, connected, e connecting para connect. Assim, uma busca pela palavra “connected” vai retornar documentos que contém também as outras formas da palavra. Já no idioma português, ele não é tão eficiente assim.

Sintaxe

Toda busca textual no Postgres é feita em um documento. Esse documento, apesar de ser um texto, não é do tipo VARCHAR ou TEXT, mas sim do tipo TSVECTOR.
O termo usado na busca, mesmo sendo uma string, deve ser convertido para o tipo TSQUERY.
Já o operador utilizado para verificar se o termo existe no documento, são dois arrobas: @@. Exemplo:

SELECT 'o rato roeu a roupa do rei de roma'::tsvector @@ 'roupa'::tsquery;

As principais funções para trabalhar com busca textual são:

to_tsvector: retorna lista de lexemas com suas posições no documento
to_tsquery: converte string para lexemas e permite adicionar operadores booleanos
plainto_tsquery: transforma uma string em lexema, removendo as stop words e usando o operador boleano & para cada palavra
ts_headline: retorna a parte do documento relacionada com o termo buscado. É usado para marcar um trecho do resultado.

É possível utilizar a funcão to_tsvector para criar um documento a partir de um campo de texto, no momento da execução da query. Esse é um processo mais lento, indicado apenas para tabelas com poucos registros.
Para fins de performance, é criado um campo do tipo TSVECTOR na tabela que contém o texto onde a busca será realizada e uma trigger que converte o texto para documento quando há uma operação de insert ou update na tabela. Vou mostrar isso daqui a pouco.

Índices

Um índice é um estrutura de dados que melhora a velocidade das consultas realizadas em uma tabela.
A criação de um índice em um campo de uma tabela cria uma estrutura de dados que contém o valor do campo e uma referência para o registro ao qual está relacionado. Essa estrutura de índice é então classificada, permitindo que buscas binárias sejam realizadas nela.

Postgres fornece os índices do tipo B-tree, hash, GiST, SP-GiST, GIN e BRIN. Ao criar um índice, se não especificado o tipo, é utilizado por padrão o B-tree.

A busca textual geralmente é feita em um campo do tipo TSVECTOR. Os índices mais apropriados para busca textual são:

GiST: Escrita rápida e ocupa pouco espaço em disco. Bom para tabelas pequenas ou valores atualizados com frequência.
GIN: Mais lento que o GiST em escritas, boa performance para buscas e escalabilidade. A velocidade de escrita pode melhorar ao modificar a propriedade maintenance_work_mem no arquivo de configuração. Indicado para a maioria dos casos.
RUM: Uma evolução do GIN. Lento para escritas, ocupa um espaço maior em disco, mais rápido para buscas e não precisa de uma coluna TSVECTOR. Ideal para buscas de frases e ordernação por TS_RANK ou TIMESTAMP. É um módulo de terceiros que não acompanha a instalação padrão do Postgres.

O GIN é indicado para a maioria dos casos. Mas vale a pena você testar as outras opções de acordo com o cenário do seu software.

Mão na massa: Catálogo de produtos e serviços

Vamos utilizar dados de uma empresa da área de offshore que presta serviços e vende produtos.
O termo utilizado na busca deve verificar os campos document e keywords.

Criando os dicionários

A primeira coisa a ser feita é criar os dicionários personalizados.
Não precisa modificar o dicionário de stop words, podemos utilizar o padrão do Postgres. Você pode começar pelo dicionário de sinônimos ou Thesaurus.

O caminho do diretório tsearch_data do Postgres varia de acordo com o sistema operacional. No Ubuntu 21.04 está localizado em /usr/share/postgresql/13. Vamos chamar esse diretório de $SHAREDIR.
Você pode procurar usando o comando find / -name tsearch_data no Linux ou MacOS.

export SHAREDIR=/usr/share/postgresql/13

echo "capassete capacete" | sudo tee -a $SHAREDIR/tsearch_data/brazilian.syn
echo "gerenciamento gestão" | sudo tee -a $SHAREDIR/tsearch_data/brazilian.syn

Esse comando cria o dicionário brazilian.syn com 2 linhas, cada uma com a palavra e sua substituição ao lado.
Você pode salvar os termos que os usuários utilizaram e não tiveram resultado para depois adiciona-los ao dicionário de sinônimos.

O dicionário Thesaurus é similar ao dicionário de sinômimos, mas com suporte a frases. O sinal de interrogação é para ignorar as stop words.
Os Thesaurus é usado durante a indexação, portanto, qualquer alteração nos parâmetros do dicionário exige uma reindexação do documento.

echo "gerenciamento ? negócios : gestão
administração ? empresas : gestão
protetor ? cabeça : capacete
capassete : capacete" | sudo tee -a $SHAREDIR/tsearch_data/brazilian.ths

Em seguida instalar o Hunspell, do mesmo jeito que foi mostrado anteriormente.

sudo apt install hunspell-pt-br
iconv -f latin1 -t UTF-8 /usr/share/hunspell/pt_BR.aff | sudo tee $SHAREDIR/tsearch_data/brazilian.affix
iconv -f latin1 -t UTF-8 /usr/share/hunspell/pt_BR.dic | sudo tee $SHAREDIR/tsearch_data/brazilian.dict

Pronto. Agora temos que *reiniciar o serviço e criar os dicionários dentro do Postgres:

sudo systemctl restart postgresql

Queries para criar os dicionários:

DROP TEXT SEARCH DICTIONARY IF EXISTS dict_simple_ptbr CASCADE;
CREATE TEXT SEARCH DICTIONARY public.dict_simple_ptbr (
    TEMPLATE = pg_catalog.simple,
    STOPWORDS = portuguese,
    Accept = false
);

-- Comentado porque vou utilizar o Thesaurus ao invés desse
-- DROP TEXT SEARCH DICTIONARY IF EXISTS dict_synonyms_ptbr CASCADE;
-- CREATE TEXT SEARCH DICTIONARY dict_synonyms_ptbr (
--   TEMPLATE = synonym, 
--   SYNONYMS = brazilian
--);

DROP TEXT SEARCH DICTIONARY IF EXISTS dict_thesaurus_ptbr CASCADE;
CREATE TEXT SEARCH DICTIONARY dict_thesaurus_ptbr (
  TEMPLATE = thesaurus, 
  DictFile = brazilian, 
  Dictionary = pg_catalog.portuguese_stem
);

DROP TEXT SEARCH DICTIONARY IF EXISTS dict_ispell_ptbr CASCADE;
CREATE TEXT SEARCH DICTIONARY dict_ispell_ptbr (
  TEMPLATE = ispell,
  DictFile = brazilian,
  AffFile = brazilian,
  StopWords = portuguese
);

DROP TEXT SEARCH DICTIONARY IF EXISTS dict_snowball_ptbr CASCADE;
CREATE TEXT SEARCH DICTIONARY dict_snowball_ptbr (
  TEMPLATE = snowball,
  Language = portuguese,
  StopWords = portuguese
);

Criando uma configuração de busca textual

Vamos criar um novo text search configuration para utilizar os dicionários personalizados.
O comando para criar essa configuração é o CREATE TEXT SEARCH CONFIGURATION. Vamos criar uma configuração chamada ecommerce no schema public, que é o padrão do Postgres.

DROP TEXT SEARCH CONFIGURATION IF EXISTS ecommerce CASCADE;
CREATE TEXT SEARCH CONFIGURATION ecommerce (COPY=pg_catalog.portuguese);
ALTER TEXT SEARCH CONFIGURATION ecommerce ALTER MAPPING FOR asciiword, asciihword, hword_asciipart, word, hword, hword_part
    WITH unaccent, dict_thesaurus_ptbr, portuguese_stem, dict_simple_ptbr, dict_ispell_ptbr, dict_snowball_ptbr;

O comando acima faz o seguinte:

Remove a configuração se existir;
Cria uma nova configuração copiando a configuração de texto padrão do idioma português;
Altera configuração adicionando parsers que identificam o tipo do token;
Adiciona os dicionários na ordem começando pelo unaccent e terminando no dict_snowball_ptbr.

Com isso, toda configuração necessária para utilizar busca textual com dicionários personalizados foi finalizada.

Criando tabela e funções

Vamos criar a tabela items com um índice GIN para armazenar os produtos e serviços da empresa.

CREATE TABLE items (
    id SERIAL PRIMARY KEY,
    name TEXT,
    description TEXT,
    keywords TEXT,
    document TSVECTOR
);
CREATE INDEX idx_items_document ON items USING GIN(document);

A busca textual é feita no campo do tipo TSVECTOR. Então vamos criar uma trigger que atualiza o campo document com a junção do conteúdo dos campos que fazem sentido para a busca.
Uma trigger é uma função executada quando algum evento ocorre. Nesse caso, quando ocorrer um evento de INSERT ou UPDATE na tabela items.
Já a extensão unaccent do Postgres permite remover acentos das palavras.

CREATE EXTENSION IF NOT EXISTS "unaccent";
CREATE OR REPLACE FUNCTION update_items_document() RETURNS trigger AS 
$$
BEGIN
  new.document := 
    setweight(to_tsvector('public.ecommerce', unaccent(coalesce(new.name, ''))), 'A') ||
    setweight(to_tsvector('public.ecommerce', unaccent(coalesce(new.description, ''))), 'B') ||
    setweight(to_tsvector('public.ecommerce', unaccent(coalesce(new.keywords, ''))), 'C');
  RETURN new;
END
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS tg_update_items_document ON items;
CREATE TRIGGER tg_update_items_document BEFORE INSERT OR UPDATE ON items FOR EACH ROW EXECUTE PROCEDURE update_items_document();

O que essa função update_items_document() faz? Ela atualiza o campo document da tabela adicionando os tokens dos campos name, description e keywords, atribuindo um peso que varia de A, mais importante, até D, menos importante.
Isso significa que o termo buscado vai levar em consideração, primeiro, se ele existe em name, depois em description e por fim em keywords.

Essa trigger só faz sentido quando se está usando o mesmo banco de dados para informações de negócio e busca textual.
Há casos em que é utilizado um banco de dados separado apenas para busca textual. Sendo assim, algum agente externo ao Postgres (seu aplicação, por exemplo) vai precisar se conectar ao banco e fazer um típico INSERT ou UPDATE, convertendo os dados de input usando a função to_tsvector.

Toda busca textual é feita comparando TSVECTOR com TSQUERY. O termo utilizado para busca deve ser convertido usando a função nativa to_tsquery ou plainto_tsquery. Essa segunda função é usada quando se deseja buscar por todas as palavras fornecidas. Então criei uma função que remove os espaços do termo a ser buscado e retorna um TSQUERY.

CREATE OR REPLACE FUNCTION ecommerce_tsquery(word text) RETURNS tsquery AS 
$$
BEGIN
  RETURN plainto_tsquery('public.ecommerce', unaccent(trim(word)));
END
$$ LANGUAGE plpgsql;

Executando a busca textual

Vamos cadastrar alguns produtos e serviços na tabela items:

INSERT INTO items (name, description, keywords) VALUES
('Inteligência de mercado', 'Estudos de Inserção Estudos de viabilidade técnica e econômica Estudos de Benchmarking -Análise da concorrência -Panorama Regional -Estudos de atração de investimentos', 'estudo econômico análise estratégia investimentos'),
('Gestão e Inovação', 'Modelagem e Plano de Negócios gerenciamento -Planejamento Estratégico -Reestruturação Organizacional Estrutura Comercial -Mapeamento de Processos -KPIs e painéis -Gestão CRC e outros cadastros', 'plano de negócios estrutura organizacional processos customer'),
('Eventos', 'Rodadas de Negócios entre compradoras e fornecedoras presencial ou online -Sessões de negócios entre fornecedoras presencial ou online', 'apresentações power point'),
('Capacete', 'Desenhado para proteger a cabeça contra impacto de objetos em queda livre. ✓ Possui 8 tiras de fixação em 8 pontos de ancoragem. ✓ Design inteligente que permite ancoragem de diversos sistemas', 'proteção para cabeça'),
('Treinamentos', 'Treinamentos in company relacionados a inovação e gestão, como: - Design Thinking - Cadastro de fornecedores - Inovação', 'elearning ead informática computação');

Repare que após esse cadastro, a trigger já atualizou o campo document:

SELECT id, document FROM items LIMIT 1;

Execute as queries abaixo e veja os resultados:

-- busca ignorando acentuação
SELECT * FROM items WHERE document @@ ecommerce_tsquery('inovacao');

-- busca por sinônimo
SELECT * FROM items WHERE document @@ ecommerce_tsquery('capassete');

-- busca por sinônimo usando frase
SELECT * FROM items WHERE document @@ ecommerce_tsquery('protetor de cabeça');

-- busca retorna apenas com o ranking A ou B
SELECT * FROM items WHERE ts_filter(document, '{a,b}') @@ ecommerce_tsquery('capacete');

-- retorna o termo achado com marcação negrito <b></b> do HTML
SELECT *, ts_headline('public.ecommerce', description, ecommerce_tsquery('protege a cabeça'), 'StartSel = <b>,StopSel =</b>')
FROM items
WHERE document @@ ecommerce_tsquery('protege a cabeça');

-- busca por treinamentos e company podendo ter no mínimo 2 palavras de distância
SELECT * FROM items WHERE document @@ to_tsquery('public.ecommerce', 'treinamentos <2> company');

-- orderna pelo resultado mais relevante
SELECT id, name, ts_rank_cd(document, ecommerce_tsquery('gestão')) AS ranking
FROM items
WHERE document @@ ecommerce_tsquery('gestão')
ORDER BY ts_rank_cd(document, ecommerce_tsquery('gestão')) DESC;

Teste e debug

O comportamento de uma configuração de busca textual personalizada pode se tornar confuso. Existem funções úteis para testar busca textual.
Você pode testar uma configuração completa ou testar analisadores e dicionários separadamente.

ts_debug

Retorna os tokens do documento produzidos pelos parses e processados pelos dicionários:

SELECT * FROM ts_debug('public.ecommerce', 'Desenhado para proteger a cabeça contra impacto de objetos em queda livre. ✓ Possui 8 tiras de fixação em 8 pontos de ancoragem. ✓ Design inteligente que permite ancoragem de diversos sistemas');

ts_lexize

Facilita o teste de dicionários. Espera uma palavra como argumento, não uma frase. Por isso não serve para dicionário Thesaurus.

SELECT ts_lexize('public.dict_ispell_ptbr', 'gerenciamento');

Conclusão

Se você chegou até aqui, parabéns!

Eu escolhi mostrar como utilizar no Postgres porque, na minha opinião, é uma plataforma adequada para a maioria das aplicações web modernas. É um banco de dados estável, robusto e confiável, usado para armazenar todo tipo de dado.

Tecnologias como Elasticsearch e Solr requerem um servidor (ou cluster) dedicado, trazendo mais demanda de monitoramento, verificação de segurança, gerenciamento de usuários, etc. Você pode utilizar SaaS específicos, como da Amazon por exemplo, mas não deixa de trazer mais complexidade para a organização. Estou pensando em pequenas e médias empresas, é claro.

Falando em SaaS e Amazon, se for utilizar o Postgres no RDS e Aurora, ou alguma outra opção que você não tenha acesso ao sistema de arquivos da máquina, não será possível criar dicionários personalizados como sinônimos, Thesaurus ou Ispell.

Isso foi só um gostinho sobre busca textual. Mas suficiente para seu sistema possuir uma busca moderna e inteligente.