Criando um sistema de busca usando PostgreSQL

Hoje em dia todas as plataformas modernas contam com sistema de busca e agora até o TabNews, para quem não sabe um sistema de busca consiste em encontrar as melhores correspondências para determinada palavra, frase ou até mesmo imagem. Por trás desses sistemas existem algoritmos bastantes complexos como o PageRank do Google, mas também existem diversas ferramentas que abstraem esses algoritmos e disponibilizam APIs simples para uso como por exemplo Apache Lucene, biblioteca usada no Elastic Search e no Apache Solr, Meilisearch e OpenSearch são bons exemplos.

O que acontece caso não queira utilizar nenhuma outra ferramenta, ao não ser meu banco de dados

Hoje isso já é possível em diversos bancos de dados SQL como o PostgreSQL, Mysql, Oracle e também NoSQL como Redis e MongoDB através da técnica de full text search que essas ferramentas disponibilizam, capaz de identificar linguagem natural nos documentos que satisfaça a busca e organizar por relevância. Essa área de algoritmos vem crescendo bastante graças a popularização dos grandes modelos de inteligência artificial, um uso por exemplo é um chat de dúvidas quando o usuário faz uma pergunta, você primeiro busca no seu banco de dados textos relacionados a pergunta usando full text search e alimenta o contexto da inteligência artificial com os resultados, assim ela responde de uma melhor forma para seu chat.

Implementação no PostgreSQL

O full text search está presente no postgresql desde a versão 11 e é necessário fazer algumas mudanças na sua tabela para funcionar com uma melhor performance.

Pequeno exemplo do funcionamento:

SELECT 'tabnews é a plataforma mais legal que existe'::tsvector @@ 'legal & plataforma'::tsquery;

Esse exemplo me retorna se legal e plataforma existem na frase, nesse caso t de true(verdadeiro).

Mas chega de enrolação e vamos ver em uma tabela de verdade, o primeiro passo é adicionar um nova coluna com o tipo de tsvector que é gerada toda vez que a coluna do conteúdo que você quer buscar é alterada, também pode ser mais de uma coluna. Segue o exemplo para uma tabela posts que já possui as colunas title e content:

ALTER TABLE posts ADD COLUMN text_search tsvector GENERATED ALWAYS AS (to_tsvector('portuguese', title || ' ' || content )) STORED;

Para melhorar as perfomance das buscas podemos adicionar um índice para esse coluna:

CREATE INDEX posts_search ON posts USING GIN (text_search);

E finalmente realizar uma busca:

SELECT * FROM posts WHERE text_search @@ websearch_to_tsquery("tabnews");

Esse exemplo simula uma busca usada comumente na web filtra todos os posts que correnspondem a busca, usamos a função websearch_to_tsquery que recebe um texto e transforma em um query, existem outras funções veja na documentação.

Podemos também listar os posts com base no rank da busca, realmente como um sistema de busca ocorre:

SELECT title, content, ts_rank_cd(text_search,query) as rank 
FROM posts, websearch_to_tsquery("tabnews") as query
WHERE query @@ text_search
ORDER BY rank DESC;

Aprenda mais na documentação.

Estou a procura de um emprego, meu GitHub ❤️.