Wyszukiwanie pełnotekstowe w postgresql

Postgresql jest bardzo popularną bazą danych. Oprócz tego, że w większości przypadków jest wykorzystywany jako baza relacyjna, to również posiada różne funkcje, które sprawiają, że nie koniecznie trzeba go tak traktować. Jedną z nich jest wyszukiwanie pełnotekstowe.

Dlaczego

Najważniejszym pytaniem, które możesz sobie zadać to dlaczego masz używać do wyszukiwanie pełnotekstowego postgresa, skoro istnieje elasticsearch. Po pierwsze, jeśli nie jest on dodany do projektu, to jest to kolejna technologia. Niestety z każda dodatkową technologią projekt co raz bardziej się komplikuje. Nie możesz, zapomnieć o tym, że ktoś musi elasticsearch zainstalować i skonfigurować. Oprócz, tego ważną kwestią jest synchronizacja danych między postgresem a elasticsearchem.
Innym powodem jest brak wiedzy w zespole jak go używać. Również zdarzają się projekty, w których stack technologiczny nie może być zmieniany i nie jesteś w stanie łatwo dodać nowego narzędzia. Kolejnym powodem jest to, że ilość danych jest zbyt mała aby dodawać coś bardziej zaawansowanego. Innym czynnikiem jest posiadanie danych, które zanim będą w pełni gotowe do wyszukiwania muszą mieć wiele updatów.

Teoria

Gdy już wiesz dlaczego użyć postgresa do wyszukiwania pełnotekstowego, to najwyższa pora abyś poznał trochę teorii. Po pierwsze wyszukiwane frazy muszą zostać stokenizowane. Służy do tego funkcja to_tsvector(). np

SELECT to_tsvector('The quick brown fox jumped over the lazy dog.');
--zwraca
'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2

Defaultowo funkcja wykorzystuje locale z setingsów postgesa, jednak zawsze możesz przekazać inne, jeśli chcesz wykorzystać wyszukiwane pełnotekstowe dla innych języków. Skoro już wiesz jak stokenizować dane to na pewno chciałbyś po nich szukać. Z pomocą przychodzi funkcja to_tsquery(). Dla powyższego przykładu to zapytanie wyszuka dane:

SELECT to_tsvector('The quick brown fox jumped over the lazy dog') @@ to_tsquery('jumping');

Funkcję to_tsquery możesz zmodyfikować i użyć w niej operatorów logicznych.

and – to_tsquery(‘fox & dog’)
or – to_tsquery(‘fox | cat’)
negacji – to_tsquery(‘!cat’)

Dodatkowo możesz szukać po całej frazie to_tsquery(‘brown<->fox’), gdzie <-> oznacza, że obie frazy, muszą występować obok siebie w tej kolejności. W razie potrzeby możesz zwiększyć odległość od siebie poprzez użycie <2>

to_tsquery('brown<2>jump')

Praktyka

Trochę teorii już za Tobą, więc teraz pora na praktyczne użycie tej wiedzy. Załóż, że w bazie są dwie tabele: article, która zawiera id i content oraz comment z następującymi polami: id, article_id, content. Twoim zadaniem jest wyszukanie pełnotekstowo po treści artykułu i komentarzach. Na samym początku możesz przygotować widok dla tych danych.

Create or replace view searchable_article
As select a.id, 
(Select (to_tsvector(a.content::text) || to_tsvector(c.content:text))) as to_search
from article a join comment c on a.id = c.artilce_id;

Następnie już w prosty sposób szukasz poprzez np:

Select * from searchable_article where to_search @@ to_tsquery('wyszukiwanie<->pełnotekstowe');

Jooq

Jooq pozwala generowac kod javowy na podstawie bazy a później tworzyć zapytania z wykorzystaniem fluent api. Niestety nie ma on wbudowanego typu ts_vector, jednak nic nie stoi na przeszkodzie aby napisać query, które będzie obsługiwać wyszukiwanie pełnotekstowe. Dla powyższego widoku, może to być coś w stylu:

DSL.condition("{0} @@ to_tsquery({1})", SEARCHABLE_ARTICLE.TO_SEARCH, phrase);