BT

Disseminando conhecimento e inovação em desenvolvimento de software corporativo.

Contribuir

Tópicos

Escolha a região

Início Artigos Busca Textual no PostgreSQL é boa o suficiente

Busca Textual no PostgreSQL é boa o suficiente

Favoritos

A Busca Textual tornou-se uma característica importante para muitos tipos de negócios. A comprovação desta necessidade é o aumento na popularidade de ferramentas como o Elasticsearch e Solr onde ambas são baseadas no Lucene. Solr e Elasticsearch são ótimas ferramentas, mas antes de caminharmos para este lado da busca textual, devemos pensar um pouco mais e talvez decidir por algo mais leve e que seja bom o suficiente.

Este artigo foi originalmente publicado por Rachid Belaid em seu blog pessoal e com algumas atualizações está sendo publicado com a permissão do autor em Português pelo InfoQ Brasil.

Ao expressar o termo 'bom o suficiente', estamos falando de um motor de busca com as seguintes características:

Felizmente, o PostgreSQL suporta todas essas características.

Este artigo é destinado a solução dos seguintes problemas:

  • Para aqueles que fazem uso do PostgreSQL e não desejam instalar uma dependência extra para o seu motor de busca.
  • Utiliza um banco de dados alternativo (por exemplo: MySQL, SQL Server) e têm a necessidade de melhores recursos para busca textual(FTS - Full Text Search).

Vamos ilustrar progressivamente alguns dos recursos de busca de texto completo utilizando o PostgreSQL com base nas seguintes tabelas e dados:

pg_fts_db_map.png

CREATE TABLE author(
   id SERIAL PRIMARY KEY,
   name TEXT NOT NULL
);

CREATE TABLE post(
   id SERIAL PRIMARY KEY,
   title TEXT NOT NULL,
   content TEXT NOT NULL,
   author_id INT NOT NULL references author(id)
);

CREATE TABLE tag(
   id SERIAL PRIMARY KEY,
   name TEXT NOT NULL
);

CREATE TABLE posts_tags(
   post_id INT NOT NULL references post(id),
   tag_id INT NOT NULL references tag(id)
 );

INSERT INTO author (id, name)
VALUES (1, 'Pete Graham'),
   	(2, 'Rachid Belaid'),
   	(3, 'Robert Berry');

INSERT INTO tag (id, name)
VALUES (1, 'scifi'),
   	(2, 'politics'),
   	(3, 'science');

INSERT INTO post (id, title, content, author_id)
VALUES (1, 'Endangered species',
    	'Pandas are an endangered species', 1 ),
   	(2, 'Freedom of Speech',
    	'Freedom of speech is a necessary right', 2),
   	(3, 'Star Wars vs Star Trek',
    	'Few words from a big fan', 3);

INSERT INTO posts_tags (post_id, tag_id)
VALUES (1, 3),
   	(2, 2),
   	(3, 1);

Trata-se da estrutura em banco de dados de uma aplicação de blog tradicional com objetos postados e que possui um título e conteúdo. A publicação está associada a um autor por meio de uma chave estrangeira. Uma publicação pode ter várias tags.

O que é Full Text Search

Segundo a Wikipédia:

Em tratando-se de recuperação de informação, Full Text Search refere-se as técnicas para procurar em um único documento armazenado em computador ou a uma coleção em um banco de dados de texto completo. Full Text Search é diferente de pesquisas com base em metadados ou em partes dos textos originais representados em bases de dados (tais como títulos, resumos, seções selecionadas, ou referências bibliográficas).

Esta definição apresenta o conceito para um único documento porém, quando executamos uma pesquisa sobre dados, estamos olhando para entidades significativas para o qual desejamos pesquisar, estes são os documentos! A documentação do PostgreSQL explica este conceito da seguinte forma.

Um documento é a unidade de busca completa em um sistema de busca textual como por exemplo, um artigo de revista ou uma mensagem de e-mail.

Este documento pode ser composto de várias tabelas e representa a entidade lógica que desejamos procurar.

Construindo o documento para busca

Na seção anterior, introduzimos o conceito de documento. Neste contexto, um documento não está relacionado com um esquema composto por tabelas, mas a dados que em conjunto representam um objeto com algum tipo de significado. Com base no exemplo do esquema de dados proposto, o documento é composto de:

  • post.title (título da publicação)
  • post.content (conteúdo da publicação)
  • author.name (nome do autor da publicação)
  • tag.name (nome de todas as tags associadas à publicação)

Para criar o documento com base nestes critérios vamos imaginar a seguinte consulta SQL:

SELECT post.title || ' ' || post.content || ' ' ||
   	 author.name || ' ' ||
   	 coalesce((string_agg(tag.name, ' ')), '') as document
FROM post
   	 JOIN author ON author.id = post.author_id JOIN posts_tags ON posts_tags.post_id = posts_tags.tag_id
   	 JOIN tag ON tag.id = posts_tags.tag_id GROUP BY post.id, author.id;

Tem-se como resposta:

document
----------------------------------------------
 Endangered species Pandas are an endangered
 species Pete Graham politics
 Freedom of Speech Freedom of speech is a
 necessary right missing in many countries
 Rachid Belaid politics
 Star Wars vs Star Trek Few words from a big
 fan Robert Berry politics
(3 rows)

Como a consulta acima está realizando um agrupamento por tipo de publicação e autor, foi utilizado a função string_agg() como função de agregação porque segundo o esquema de relacionamento proposto, várias tags podem ser associadas a uma publicação.

Mesmo que o autor seja uma chave estrangeira, uma publicação não pode ter mais de um autor, e para que esta unicidade prevaleça, é necessário adicionar uma função de agregação para o autor ou adicionar o autor com um GROUP BY.

Como uma boa prática, pode-se utilizar coalesce() para os casos em que existam valores NULL evitando que a concatenação retorne valores NULL.

Até este momento, o documento criado não passa de uma simples e longa string de caracteres e não é muito útil. Para corrigir esta anomalia, é preciso transformar o documento para um formato adequado por meio da função to_tsvector().

SELECT to_tsvector(post.title) ||
    to_tsvector(post.content) ||
    to_tsvector(author.name) ||
    to_tsvector(coalesce((string_agg(tag.name, ' ')), '')) as document
FROM post
    JOIN author ON author.id = post.author_id JOIN posts_tags ON posts_tags.post_id = posts_tags.tag_id
    JOIN tag ON tag.id = posts_tags.tag_id
GROUP BY post.id, author.id;

O que retorna:

document
--------------------------------------------------
'endang':1,6 'graham':9 'panda':3 'pete':8
'polit':10 'speci':2,7
'belaid':16 'countri':14 'freedom':1,4
'mani':13 'miss':11 'necessari':9 'polit':17
'rachid':15 'right':10 'speech':3,6
'berri':13 'big':10 'fan':11 'polit':14
'robert':12 'star':1,4 'trek':5 'vs':3 'war':2
'word':7
(3 rows)

Esta consulta irá retornar o documento em formato de um tsvector, que é um tipo adequado para busca textual. Abaixo, um exemplo de como converter um conjunto de caracteres em um tipo tsvector.

SELECT to_tsvector('Try not to become a man of success, but rather try to become a man of value');

Que retornará o seguinte resultado:

to_tsvector
----------------------------------------------------------------------
'becom':4,13 'man':6,15 'rather':10 'success':8 'tri':1,11 'valu':17
(1 row)

Vejamos o que ocorreu com o resultado da consulta. Em primeiro lugar há menos palavras do que na sentença original, algumas das palavras são diferentes (try tornou-se tri) e todas elas estão seguidas por números. Por quê?

Um valor tsvector é uma lista ordenada de lexemas distintos o qual são palavras que foram normalizadas para fazer variações diferentes de uma mesma palavra e que são parecidas. Por exemplo, açoitar, açoite, abdome, abdômen o tsvector organiza estes resultados para que uma palavra que possua variações não seja contada/exibida mais de uma vez no resultado.

Por exemplo, a normalização inclui quase sempre converter letras maiúsculas para minúsculas e muitas vezes envolve a remoção de sufixos (como o 's', 'es' ou 'ing' em Inglês). Isto permite buscas para encontrar formas variadas da mesma palavra sem necessariamente envolver todas as variações possíveis existentes para a palavra.

Os números representam a localização do lexema na string original. Por exemplo, "man" está presente na posição 6 e 15. Conte as palavras no diagrama abaixo para comprovar.

lexema_1.png

Por padrão, o PostgreSQL usa o idioma 'Inglês' como configuração para busca textual utilizando a função to_tsvector o que também faz com que as chamadas stop words sejam ignoradas para este idioma.

Esta particularidade explica o motivo pelo qual os resultados da função tsvector possuem menos elementos do que a sentença de exemplo. Neste artigo, mais a frente, será detalhado um pouco mais sobre idiomas e a configuração de buscas textuais no PostgreSQL.

Realizando consultas

No seção anterior, foi discutido como construir um documento, mas o objetivo deste artigo é encontrar o documento. Para executar uma consulta em um tipo tsvector podemos usar o operador @@ conforme documentado no site do PostgreSQL. A seguir, são apresentados alguns exemplos sobre como consultar o documento criado.

> select to_tsvector('If you can dream it, you can do it') @@ 'dream';
 ?column?
----------
 t
(1 row)

> select to_tsvector('It''s kind of fun to do the impossible') @@ 'impossible';

 ?column?
----------
 f
(1 row)

A segunda consulta retorna false porque é preciso construir uma consulta do tipo tsquery que criará os mesmos lexemas e que ao utilizar o operador @@, transformará(cast) a string em um formato tsquery. O exemplo a seguir exibe a diferença entre o uso de transformação(cast) e uso da função to_tsquery().

>SELECT 'impossible'::tsquery, to_tsquery('impossible');
   tsquery    | to_tsquery
--------------+------------
 'impossible' | 'imposs'
(1 row)

Mas no caso da palavra 'dream' o fonema é igual a palavra.

SELECT 'dream'::tsquery, to_tsquery('dream');
   tsquery	| to_tsquery
--------------+------------
 'dream'  	| 'dream'
(1 row)

Deste ponto em diante, é possível utilizar a função to_tsquery para consultar documentos.

>SELECT to_tsvector('It''s kind of fun to do the impossible') @@ to_tsquery('impossible');

 ?column?
----------
 t
(1 row)

O tsquery armazena o valor dos lexemas que estão sendo buscados, e combina-os respeitando os operadores booleanos / (AND), | (OR), e ! (NÃO). Os parênteses podem ser utilizados para aplicar agrupamento nos operadores.

> SELECT to_tsvector('If the facts don't fit the theory, change the facts') @@ to_tsquery('! fact');

 ?column?
----------
 f
(1 row)

> SELECT to_tsvector('If the facts don''t fit the theory, change the facts') @@ to_tsquery('theory & !fact');

 ?column?
----------
 f
(1 row)

> SELECT to_tsvector('If the facts don''t fit the theory, change the facts.') @@ to_tsquery('fiction | theory');

 ?column?
----------
 t
(1 row)

É possível também utilizar consultas que comecem com algum coringa fazendo uso de :*. como no exemplo a seguir:

>SELECT to_tsvector('If the facts don''t fit the theory, change the facts.') @@ to_tsquery('theo:*');

 ?column?
----------
 t
(1 row)

Agora que foi exemplificado como fazer buscas do tipo full-text, vamos voltar para a tabela e esquema criados no início deste artigo e fazer consultas no documento.

SELECT pid, p_title
FROM (SELECT post.id as pid,
         	post.title as p_title,
         	to_tsvector(post.title) ||
         	to_tsvector(post.content) ||
         	to_tsvector(author.name) ||
         	to_tsvector(coalesce(string_agg(tag.name, ' '))) as document
  	FROM post
  	JOIN author ON author.id = post.author_id
  	JOIN posts_tags ON posts_tags.post_id = posts_tags.tag_id
  	JOIN tag ON tag.id = posts_tags.tag_id
  	GROUP BY post.id, author.id) p_search
WHERE p_search.document @@ to_tsquery('Endangered & Species');

 pid |  	p_title
-----+--------------------
   1 | Endangered species
(1 row)

O resultado da consulta vai retornar o documento que contém como título 'Endangered species' ou lexemas que estejam o suficientemente próximos ao procurado.

Suporte a idiomas

O PostgreSQL possui internamente opções de busca textual para vários idiomas: Dinamarquês, Holandês, Inglês, Finlandês, Francês, Alemão, Húngaro, Italiano, Norueguês, Português, Romeno, Russo, Espanhol, Sueco, Turco.

SELECT to_tsvector('english', 'We are running');
 to_tsvector
-------------
 'run':3
(1 row)

SELECT to_tsvector('french', 'We are running');
    	to_tsvector
----------------------------
 'are':2 'running':3 'we':1
(1 row)

Um nome de coluna pode ser utilizado para criar um tsvector com base no modelo inicial utilizado neste artigo.

Supondo que o post(publicação) possa ser escrito em diferentes idiomas e que o post(publicação) contenha uma coluna do tipo idioma.

ALTER TABLE post ADD language text NOT NULL DEFAULT('english');

Desta forma é possível reconstruir o documento para usar a nova coluna de idioma.

SELECT to_tsvector(post.language::regconfig, post.title) ||
   	to_tsvector(post.language::regconfig, post.content) ||
   	to_tsvector('simple', author.name) ||
   	to_tsvector('simple', coalesce((string_agg(tag.name, ' ')), '')) as document
FROM post
JOIN author ON author.id = post.author_id
JOIN posts_tags ON posts_tags.post_id = posts_tags.tag_id
JOIN tag ON tag.id = posts_tags.tag_id
GROUP BY post.id, author.id;

É importante observar que sem a devida transformação(cast) utilizando ::regconfig a consulta lançará uma exceção com erro:

ERROR:  function to_tsvector(text, text) does not exist

O tipo regconfig é um identificador de objeto que representa a configuração de pesquisa de texto existente no PostgreSQL e está mais detalhado na documentação sobre Tipos de Identificadores de Objetos.

Com esta alteração, os lexemas do documento que foram criados, utilizarão a linguagem correta e identificada baseando-se na variável post.language da configuração do PostgreSQL.

É possível também fazer uso do dicionário simple, que é uma das funcionalidades embutidas de configuração para busca textual que o PostgreSQL oferece. O dicionário simple não ignora stop words e não tenta encontrar fonemas(stem) de uma palavra. Com simple cada grupo de caracteres separados por um espaço é um lexema. A configuração do dicionário simple para busca textual é eficiente para dados como por exemplo o nome de uma pessoa para o qual não precisamos identificar o fonema(stem) do nome desta pessoa.

SELECT to_tsvector('simple', 'We are running');
    	to_tsvector
----------------------------
 'are':2 'running':3 'we':1
(1 row)

Trabalhando com caracteres acentuados

Ao construir um motor de busca que suporte muitos idiomas é preciso se preocupar com o problema da acentuação. Em muitos idiomas, acentos são muito importantes e podem alterar o significado de uma palavra. O PostgreSQL possui uma extensão de nome unaccent que é útil para remover a acentuação de textos.

CREATE EXTENSION unaccent;
SELECT unaccent('èéêë');

 unaccent
----------
 eeee
(1 row)

Como exemplo, pode-se adicionar algum conteúdo acentuado à tabela de publicações.

INSERT INTO post (id, title, content, author_id, language)
VALUES (4, 'il était une fois', 'il était une fois un hôtel ...', 2,'french')

Para ignorar os acentos ao construir um documento, é possível simplesmente fazer a seguinte consulta SQL:

SELECT to_tsvector(post.language, unaccent(post.title)) ||
   	to_tsvector(post.language, unaccent(post.content)) ||
   	to_tsvector('simple', unaccent(author.name)) ||
   	to_tsvector('simple', unaccent(coalesce(string_agg(tag.name, ' '))))
FROM post
JOIN author ON author.id = post.author_id
JOIN posts_tags ON posts_tags.post_id = posts_tags.tag_id
JOIN tag ON author.id = post.author_id
GROUP BY p.id

Esta técnica funciona, mas consome muitos recursos do servidor e dá margem para erros. A parte boa é que construir uma nova configuração de busca textual com suporte a caracteres não acentuados é possível e simplificado como no exemplo abaixo:

CREATE TEXT SEARCH CONFIGURATION fr ( COPY = french );
ALTER TEXT SEARCH CONFIGURATION fr ALTER MAPPING
FOR hword, hword_part, word WITH unaccent, french_stem;

Ao utilizar esta nova configuração de busca textual, é possível visualizar os lexemas

SELECT to_tsvector('french', 'il était une fois');
 to_tsvector
-------------
 'fois':4
(1 row)

SELECT to_tsvector('fr', 'il était une fois');
	to_tsvector
--------------------
 'etait':2 'fois':4
(1 row)

Esta nova configuração devolve o mesmo resultado de quando é aplicado a extensão unaccent na primeira vez e constrói o tsvector do resultado.

SELECT to_tsvector('french', unaccent('il était une fois'));
	to_tsvector
--------------------
 'etait':2 'fois':4
(1 row)

O número de lexemas é diferente porque il était une são stop words (palavras irrelevantes) em francês. Seria um problema manter estas stop words no documento de exemplo? É um caso a se pensar, uma vez que etait não é realmente uma stop word pois está escrito de forma incorreta.

SELECT to_tsvector('fr', 'Hôtel') @@ to_tsquery('hotels') as result;
 result
--------
 t
(1 row)

Ao criar uma configuração de busca não acentuada para cada idioma que uma publicação(post) possa ser escrito e ao manter este valor em post.language, então pode-se manter a mesma consulta do documento anterior.

SELECT to_tsvector(post.language, post.title) ||
   	to_tsvector(post.language, post.content) ||
   	to_tsvector('simple', author.name) ||
   	to_tsvector('simple', coalesce(string_agg(tag.name, ' ')))
FROM post
JOIN author ON author.id = post.author_id
JOIN posts_tags ON posts_tags.post_id = posts_tags.tag_id
JOIN tag ON author.id = post.author_id
GROUP BY p.id

Caso seja necessário criar uma configuração para se obter palavras não acentuadas de busca textual para cada idioma suportado pelo PostgreSQL, é possível fazer este tipo de funcionalidade com os recursos disponíveis neste repositório do gist.

O documento criado provavelmente sofrerá aumento no tamanho, pois agora pode incluir stop words(palavras irrelevantes) não acentuadas, mas é possível realizar consultas sem se preocupar com caracteres acentuados. Este exemplo, pode ser útil nos casos em que se tem um teclado em Inglês e deseja-se fazer buscas por conteúdo em Francês.

Classificação de documentos

Quando um motor de busca é construído, um dos desejos é que este recurso seja capaz de obter os resultados ordenados por relevância. A classificação de um documento é baseada em muitos fatores que são explicados com detalhes na documentação do PostgreSQL.

Para ordenar os resultados por relevância, o PostgreSQL fornece algumas funções, mas nos exemplos deste artigo serão apresentados apenas 2 deles: ts_rank() e setweight().

A função setweight permite atribuir um valor de peso para um tsvector. O valor pode ser 'A', 'B', 'C' ou 'D'.

SELECT pid, p_title
FROM (SELECT post.id as pid,
         	post.title as p_title,
         	setweight(to_tsvector(post.language::regconfig, post.title), 'A') ||
         	setweight(to_tsvector(post.language::regconfig, post.content), 'B') ||
         	setweight(to_tsvector('simple', author.name), 'C') ||
         	setweight(to_tsvector('simple', coalesce(string_agg(tag.name, ' '))), 'B') as document
  	FROM post
  	JOIN author ON author.id = post.author_id
  	JOIN posts_tags ON posts_tags.post_id = posts_tags.tag_id
  	JOIN tag ON tag.id = posts_tags.tag_id
  	GROUP BY post.id, author.id) p_search
WHERE p_search.document @@ to_tsquery('english', 'Endangered & Species')
ORDER BY ts_rank(p_search.document, to_tsquery('english', 'Endangered & Species')) DESC;

Na consulta acima, são atribuídos diferentes pesos para as diferentes áreas de um documento. No exemplo, post.title é mais importante que o post.content e tão importante quanto a tag associada. O menos importante é o atributo author.name.

Isto significa que, ao procurar o termo 'Alice', um documento que contenha este termo em seu título seria devolvido antes de um documento que contenha o termo em seu conteúdo que por sua vez seria retornado antes dos documentos que tenham este termo como autor e por último documentos que contenham o nome seriam devolvidos.

Com base nos pesos atribuídos às partes do documento de exemplo, a função ts_rank() retorna um número flutuante que representa a relevância deste documento em relação a consulta.

SELECT ts_rank(to_tsvector('This is an example of document'),
           	to_tsquery('example | document')) as relevancy;
 relevancy
-----------
 0.0607927
(1 row)


SELECT ts_rank(to_tsvector('This is an example of document'),
           	to_tsquery('example ')) as relevancy;
 relevancy
-----------
 0.0607927
(1 row)


SELECT ts_rank(to_tsvector('This is an example of document'),
           	to_tsquery('example | unkown')) as relevancy;
 relevancy
-----------
 0.0303964
(1 row)


SELECT ts_rank(to_tsvector('This is an example of document'),
           	to_tsquery('example & document')) as relevancy;
 relevancy
-----------
 0.0985009
(1 row)


SELECT ts_rank(to_tsvector('This is an example of document'),
           	to_tsquery('example & unknown')) as relevancy;
 relevancy
-----------
 1e-20
(1 row)

No entanto, o conceito de relevância é vago e muito específico ao contexto da aplicação. Diferentes aplicativos podem exigir informações adicionais para classificação, por exemplo, o tempo de modificação do documento. Funções de classificação nativas, como ts_rank são apenas exemplos. É possível escrever funções de classificação personalizadas e/ou combinar seus resultados com fatores adicionais para atender a necessidades específicas.

Para ilustrar o parágrafo anterior, ao promover as publicações mais recentes em relação às mais antigas, é possível dividir o valor da função ts_rank pela idade do documento +1 (evitando assim a divisão por zero).

Otimização e indexação

Otimizar a busca em uma tabela é o objetivo principal apresentado neste artigo. O PostgreSQL suporta a utilização de funções baseadas em índice, desta forma, é possível criar um índice do tipo GIN em torno de uma função tsvector().

CREATE INDEX idx_fts_post ON post
USING gin(setweight(to_tsvector(language, title),'A') ||
   	setweight(to_tsvector(language, content), 'B'));

Índices do tipo GIN ou GiST? Estes dois tipos de índices poderiam ser objeto de um estudo a parte. GiST pode produzir falsos positivos e para evitar isto é necessário a utilização de uma tabela extra de pesquisa para confirmar o match. Por outro lado, índices GIN são mais rápidos para consultar, mas são maiores e mais lentos para construí-los.

Como regra geral, índices GIN são melhores para dados estáticos pois as pesquisas são mais rápidas. Para dados dinâmicos, índices GiST são mais rápidos na atualização.

Especificamente, índices GiST são muito bons para dados dinâmicos e bastante rápidos se o número de palavras únicas (lexemas) encontram-se em torno de 100.000, enquanto que índices GIN podem facilmente trabalhar com mais de 100.000 lexemas de uma forma melhor, porém são mais lentos para atualizar.

No exemplo tratado neste artigo, optou-se por utilizar índices do tipo GIN, porém a escolha pode ser discutida e a decisão do que utilizar precisa ser analisada baseado no tipo de informação o qual se vai utilizar.

Ainda relacionado ao exemplo tratado neste artigo, há um problema típico no esquema de exemplo, o documento encontra-se distribuído entre várias tabelas que possuem diferentes pesos. Para um melhor desempenho, é necessário desnormalizar os dados via triggers ou visões materializadas.

Nem sempre é necessário desnormalizar e, em alguns casos, é possível adicionar um índice baseado em função, como apresentado anteriormente. Alternativamente, é possível desnormalizar dados de uma mesma tabela por meio de funções tsvector_update_trigger() ou tsvector_update_trigger_column() do PostgreSQL.

Se for aceitável que se tenha algum atraso antes que um documento possa ser encontrado em uma busca, então este pode ser um bom caso de uso para uma visão materializada e com isto, seja possível construir um índice extra sobre esta visão.

CREATE MATERIALIZED VIEW search_index AS
SELECT post.id,
   	post.title,
   	setweight(to_tsvector(post.language::regconfig, post.title), 'A') ||
   	setweight(to_tsvector(post.language::regconfig, post.content), 'B') ||
   	setweight(to_tsvector('simple', author.name), 'C') ||
   	setweight(to_tsvector('simple', coalesce(string_agg(tag.name, ' '))), 'A') as document
FROM post
JOIN author ON author.id = post.author_id
JOIN posts_tags ON posts_tags.post_id = posts_tags.tag_id
JOIN tag ON tag.id = posts_tags.tag_id
GROUP BY post.id, author.id

Neste caso, a reindexação do motor de busca vai ser tão simples como executar periodicamente o comando REFRESH MATERIALIZED VIEW search_index;.

Diante disto, é possível adicionar um índice na visão materializada.

CREATE INDEX idx_fts_search ON search_index USING gin(document);

E a consulta vai se tornar muito mais simples conforme o exemplo abaixo:

SELECT id as post_id, title
FROM search_index
WHERE document @@ to_tsquery('english', 'Endangered & Species')
ORDER BY ts_rank(p_search.document, to_tsquery('english', 'Endangered & Species')) DESC;

Caso não exista margem para suportar algum tipo de atraso, será necessário realizar um estudo mais detalhado e investigar um método alternativo fazendo uso de trigger.

Não existe um caminho único para se construir a estrutura de documentos a serem consultados, vai depender do que compreende o documento: uma tabela simples, várias tabelas, vários idiomas, quantidade de dados e quaisquer fatores que sejam relevantes para a busca.

Erros de ortografia

O PostgreSQL possui uma extensão de nome pg_trgm.

CREATE EXTENSION pg_trgm;

A extensão pg_trgm fornece suporte para trigram que nada mais é que um N-gram com N == 3. N-grams são úteis porque permitem encontrar strings com caracteres semelhantes e, em essência, é o que representa um erro de ortografia - uma palavra que é parecida, mas não igual.

SELECT similarity('Something', 'something');
 similarity
------------
 	1
(1 row)

SELECT similarity('Something', 'samething');
 similarity
------------
  0.538462
(1 row)

SELECT similarity('Something', 'unrelated');
 similarity
------------
 	0
(1 row)

SELECT similarity('Something', 'everything');
 similarity                                     	 
------------
   0.235294
(1 row)

SELECT similarity('Something', 'omething');
 similarity
------------
   0.583333
(1 row)

Com os exemplos anteriores pode-se verificar que a semelhança retorna um número flutuante para representar a similaridade entre duas strings. Para detectar erro de ortografia podemos recolher os lexemas utilizados pelos documentos e comparar as semelhanças com a entrada da pesquisa. Pode-se constatar que 0,5 seja um bom número para testar a similaridade de erro de ortografia. Para isto, primeiramente é preciso criar uma lista de lexemas exclusivos usados pelos documentos.

CREATE MATERIALIZED VIEW unique_lexeme AS
SELECT word FROM ts_stat(
'SELECT to_tsvector('simple', post.title) ||
	to_tsvector('simple', post.content) ||
	to_tsvector('simple', author.name) ||
	to_tsvector('simple', coalesce(string_agg(tag.name, ' ')))
FROM post
JOIN author ON author.id = post.author_id
JOIN posts_tags ON posts_tags.post_id = posts_tags.tag_id
JOIN tag ON tag.id = posts_tags.tag_id
GROUP BY post.id, author.id');

A consulta acima constrói uma visão materializada com uma coluna chamada palavra que recebe todos os lexemas exclusivos dos documentos existentes. Novamente foi utilizado a diretiva simple porque o conteúdo do documento pode estar em vários idiomas. Uma vez que esta visão materializada foi criada, é necessário adicionar um índice para fazer a consulta de similaridade de forma mais rápida.

CREATE INDEX words_idx ON search_words USING gin(word gin_trgm_ops);

Felizmente, lexemas exclusivos usados em motores de busca não são algo que seja alterado constantemente e por este motivo, não será necessário atualizar a view materializada com muita freqüência. Caso seja necessário uma atualização, pode-se utilizar o comando:

REFRESH MATERIALIZED VIEW unique_lexeme;

Uma vez que o objetivo de construir esta tabela era o de localizar a correspondência mais próxima para o texto, a busca se torna muito simples.

SELECT word
WHERE similarity(word, 'samething') > 0.5
ORDER BY word <-> 'samething'
LIMIT 1;

Esta consulta retorna um lexema o qual é similar o suficiente (> 0,5) com a entrada samething ordenada pelo mais próximo em primeiro lugar. O operador <-> retorna a "distância" entre os argumentos, que é um valor menor que o valor da função similarity().

Ao decidir tratar erros de ortografia em uma busca, é possível decidir por não retornar resultados com erros ortográficos a cada consulta. Além disso, é possível decidir por retornar resultados com erros ortográficos apenas quando a busca não retornar resultados e usar os resultados dessa consulta para fornecer algumas sugestões para o usuário.

Também é possível que os dados possam conter erros ortográficos, como por exemplo, nos casos em que se tratar de alguma fonte de comunicação informal tal como uma rede social, caso em que poderá obter bons resultados, anexando o lexema similar ao tsquery.

O artigo Super Fuzzy Searching on PostgreSQL é uma boa referência sobre o uso de trigrams para o tratamento de erros de ortografia e pesquisa com o PostgreSQL.

No exemplo apresentado neste artigo, a tabela de lexemas únicos nunca foi maior que 2000 linhas, mas se o conjunto de dados a ser utilizado possuir mais de 1 milhão de lexemas únicos usados em todo o documento, é possível que ocorram problemas de performance com esta técnica.

Foreign Data Wrapper (FDW)

Foreign Data Wrapper (FDW) é uma das grandes características que vieram com PostgreSQL 9.1. De forma simplificada, este recurso permite que dados externos ao PostgreSQL sejam acessados como se fossem uma tabela regular no banco de dados.

PostgreSQL FDW é uma implementação que possui cerca de uma década de idade baseado no padrão SQL/MED (gerenciamento de dados externos) e que contém informações sobre a forma de permissão de acesso à fontes de dados externas por bancos de dados - arquivos de texto, web services, etc… - como se fossem tabelas locais, fornecendo fácil acesso a estas informações utilizando comandos SQL.

Imagine uma situação em que seja necessário realizar alguma análise de dados de informações que estejam armazenados em um banco de dados externo ao PostgreSQL. No lugar de se ter que escrever scripts para atualizar os dados de um banco para o outro, graças ao FDW, é possível configurar tabelas estrangeiras (foreign tables) e manter estas tabelas atualizadas.

Lenley Hensarling recentemente publicou um artigo no InfoQ (em inglês) sobre como quebrar silos utilizando Foreign Data Wrapper do PostgreSQL.

Existem várias implementações e descrições de uso para esta tecnologia que possibilita a análise de dados por meio dos recursos do PostgreSQL totalmente viáveis.

Foreign Data Wrappers permitem que os dados por exemplo, estejam armazenados em um banco de dados MySQL enquanto as informações para análise são acessadas a partir do PostgreSQL que pode fornecer capacidades relacionais e analíticas diferenciadas conforme apresentado neste artigo.

O PostgreSQL possui bibliotecas para os mais variados bancos de dados atuais, incluindo Oracle, MySQL, SQL Server e MongoDB.

FDW.png

Conclusão

É fundamental que uma análise para compreensão das necessidades seja realizada para entender que caminho tomar.

Muitas vezes, com a falta de informação, algumas aplicações são levadas a utilizar ferramentas muito avançadas para suas reais necessidades sem levar em conta que muitas tecnologias de código aberto existentes atualmente possuem recursos suficientes para atender a demanda, com qualidade nada a desejar comparada a ferramentas pagas e a um custo muito menor.

A versão 9.5 do PostgreSQL deverá ser lançada a qualquer momento nos próximos meses e juntamente com esta nova versão, mais recursos voltados a análise de informações serão lançados ou melhorados.

Sobre o autor

Marcelo_oculos.JPG Marcelo Costa (LinkedIn, Twitter) é pós-graduado em Engenharia de Software pela UNICAMP. Atua em sistemas de alta complexidade desde 2002, coordenando equipes multidisciplinares no desenvolvimento de soluções de software nas áreas de educação, saúde, finanças e logística. Especializa-se na coleta inteligente de informações na internet e de conteúdo eletronicamente disponível; atualmente é Arquiteto de Soluções na EMBRAER. Possui experiência com PHP, Java, HTML5, Lean, Kanban, Scrum, SOA, ALM, Oracle, PostgreSQL e Shell Script.

Avalie esse artigo

Relevância
Estilo/Redação

Conteúdo educacional

BT