Volltextsuche mit PostgreSQL und Spring (Teil I)
„Postgres till we see what breaks“ ist ein Ansatz für viele Probleme in der modernen Softwareentwicklung. Eines davon ist die Volltextsuche, denn auch hier bietet PostgreSQL, seit Version 12, von Haus aus nützliche Funktionen.
Stemming
Die Volltextsuche arbeitet mit der sogenannten Stammformreduktion (eng. Stemming). Hierbei werden Wörter auf ihre Stammform reduziert und in einem Vektor gespeichert. Dieser Vektor kann dann deutlich performanter durchsucht werden als der Volltext. Meist werden auch Füllwörter (eng. stop words) entfernt. Um den korrekten Wortstamm ermitteln zu können, muss zwingend die Sprache des Textes mit angegeben werden.
Als Beispiel schauen wir uns folgenden Satz an:
“Eine Liste die viele Wörter enthält.”
PostgreSQL erzeugt mit Hilfe des deutschen Wörterbuchs folgenden Vektor:
SELECT * FROM to_tsvector('german','Eine Liste die viele Wörter enthält.');
> 'enthalt':6 'list':2 'viel':4 'wort':5Aus dem Text wurden die beiden Füllwörter “Eine” und “die” entfernt, sowie der Punkt am Ende.
Aus “Wörter” wurde “wort”. Somit würde es auch bei eine Suche nach “Worte” oder “Wort” gefunden werden.
Die Zahl hinter jedem Wortstamm gibt an, an welcher Position im Text das Ursprungswort steht.
Diese Information wird für die erweiterten Suchmöglichkeiten benötigt (z.B. Wort A und B stehen direkt hintereinander, haben also aufeinanderfolgende Indices).
Spalte und Index
Für unser Beispiel wollen wir uns eine kleine Tabelle anlegen, die Artikel für einen Blog speichert. Zunächst legen wir die Tabelle ganz normal mit unseren gewünschten Feldern an. Zusätzlich legen wir eine Spalte für den Vektor an, diese muss vom Typ “tsvector” sein.
CREATE TABLE articles
(
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
timestamp timestamp with time zone,
titel text,
content text,
ts tsvector
);Wir haben nun zwei Möglichkeiten, um uns einen Vektor für die Volltextsuche generieren zu lassen.
1. Manuell befüllen
Zum einen, können wir die Spalte manuell befüllen. Somit müssen wir bei jedem Update unseres Titels oder Textes auch den entsprechenden Suchvektor aktualisieren.
UPDATE articles SET ts = to_tsvector('german', titel || ' ' || content);2. Automatisch befüllen
Die angenehmere Variante (welche auch deutlich eleganter mit Spring Data zusammen arbeitet) ist, dass wir eine generierte Spalte nutzen. (ebenfalls seit PostgreSQL 12 möglich)
ALTER TABLE articles
ADD COLUMN ts tsvector
GENERATED ALWAYS AS (to_tsvector('german', titel || ' ' || content)) STORED;Hiermit haben wir eine Spalte `ts`, welche automatisch aus dem Vektor für den String aus ‚titel‘ und ‚content‘ generiert wird. Wir müssen uns also nicht um das aktuell halten des Vektors kümmern, auch braucht Spring Data von dieser Spalte nichts wissen.
Um in dieser generierten Spalte nun effizient Suchen zu können, legen wir noch einen Index an. PostgreSQL empfiehlt hier eine GIN Index.
CREATE INDEX articles_ts_idx ON articles USING GIN (ts);Falls die Inhalte der verwendeten Spalten nullable sind, sollte mit COALESCE gearbeitet werden um Nullwerte zu vermeiden:
UPDATE articles SET ts = to_tsvector('german', titel || ' ' || coalesce(content, ' ' ));Query
Nun, da wir unsere Daten als Vektor in der Tabelle abgelegt haben, wollen wir darin auch suchen können. PostgreSQL hat hierfür eine eigene Syntax und stellt verschiedene Hilfsmethoden zur Verfügung.
Da das Stemming von der Sprache abhängt, müssen wir auch beim Query immer die Sprache mit angeben. Unterscheiden sich die Sprachen, funktioniert das Verfahren nicht.
Um in einem Vektor suchen zu können, gibt es in PostgreSQL das @@-Keyword. Die Funktion to_tsquery wandelt den Querytext in ein Query um.
Der Querytext kann verschiedene Operatoren enthalten, um die einzelnen Wörter zu verbinden.
- & (AND)
- | (OR)
- ! (NOT)
- <-> Gefolgt von
Wörter und Operatoren können durch die Verwendung von Klammern auch gruppiert werden.
SELECT * FROM articles WHERE ts @@ to_tsquery('german','grün & auto');So findet das obige Query alle Vektoren, in denen die Stammform von “grün” und “auto” enthalten ist. Die Reihenfolge spielt dabei keine Rolle.
Wollen wir wirklich auch ‚grüne Autos‘ finden und nicht ‚rote Autos in grünen Häusern‘, so müssen wir angeben, dass die beiden Worte direkt aufeinander folgen sollen.
SELECT * FROM articles WHERE ts @@ to_tsquery('german','grün<->auto');So lassen sich mit den verfügbaren Operatoren auch komplexe Abfragen abbilden.
PostgreSQL bietet uns hier auch noch ein paar nützliche Hilfsfunktionen um übliche Fälle abzudecken.
- plainto_tsquery → Stopwörter werden entfernt und alle verbleibenden Wörter mit & verknüpft
- phraseto_tsquery → Verhält sich wie plainto_tsquery, nur dass die Wörter mit <-> verknüpft werden
- websearch_to_tsquery → Ist die komplexeste Hilfsfunktion. Sie bildet das Verhalten üblicher Websuchen nach.
Im Details sind diese und weitere Funktionen in der PostgreSQL Dokumentation beschrieben.
In der Praxis wünscht man sich vielleicht auch eine etwas unschärfere Suche. Hierfür kann mit ‘:*’ z.B. eine Wildcard am Ende eines Wortes eingefügt werden. So würde das folgende Query bei einer Suche in Kundendaten Daniel Müller und Dani Maier finden.
SELECT * FROM customers WHERE ts @@ plainto_tsquery('german','dan:*&m:*');Wie bereits erwähnt, erlaubt PostgreSQL es einem, sehr komplexe Abfragen zu modellieren.
Oft werden in der Praxis größere Mengen an Datensätzen durchsucht, daher soll hier abschließend noch auf das Ranking von Ergebnissen eingegangen werden.
Hierzu bietet PostgreSQL zwei Methoden an ts_rank und ts_rank_cd. Für die genaue Funktionsweise sei hier wieder auf die PostgreSQL Dokumentation verwiesen.
Ein einfaches Anwendungsbeispiel, um die Zehn besten Treffer zu einer Suche abzufragen ist folgendes:
SELECT content, ts_rank_cd(ts, query) AS rank
FROM articles, plainto_tsquery('artikel datenschutz') query
WHERE query @@ ts
ORDER BY rank DESC
LIMIT 10;Fazit
Man sieht also, PostgreSQL bietet hier bereits viele nützliche Funktionen, um eine kleine Volltextsuche, zum Beispiel für eine Backoffice Anwendung, umzusetzen.
Im zweiten Teil wollen wir uns dann damit beschäftigen, wie man die Volltextsuche mit Spring Data in eine Anwendung integriert.
Kommentare