PostgreSQL Volltextsuche

Einführung

In diesem Lab lernen Sie, wie Sie Volltextsuche in PostgreSQL implementieren. Volltextsuche bietet die Möglichkeit, in natürlichsprachlichen Dokumenten nach bestimmten Wörtern oder Phrasen zu suchen. Sie beginnen mit der Erstellung einer articles-Tabelle und dem Hinzufügen von Beispieldaten. Anschließend fügen Sie eine search_vector-Spalte vom Typ tsvector hinzu, einem spezialisierten Datentyp zur Speicherung von verarbeitetem Text. Sie erstellen einen Index, um Suchen zu beschleunigen, und führen dann Abfragen mit tsquery durch. Schließlich richten Sie einen Trigger ein, um die Suchdaten automatisch zu aktualisieren, wenn der Quelltext geändert wird, um sicherzustellen, dass Ihr Suchindex aktuell bleibt.

Tabelle erstellen und Beispieldaten einfügen

In diesem Schritt verbinden Sie sich mit der PostgreSQL-Datenbank, erstellen eine articles-Tabelle und fügen einige Beispieldaten ein. Diese Tabelle dient als Grundlage für die Implementierung und das Testen der Volltextsuche.

Öffnen Sie zunächst ein Terminal und verbinden Sie sich als Benutzer postgres mit der interaktiven PostgreSQL-Shell (psql). Alle nachfolgenden SQL-Befehle in diesem Lab werden innerhalb dieser Shell ausgeführt.

sudo -u postgres psql

Sie sehen nun die PostgreSQL-Eingabeaufforderung, die wie folgt aussieht: postgres=#.

Erstellen Sie als Nächstes die articles-Tabelle. Diese Tabelle speichert eine ID, einen Titel und den Hauptinhalt für jeden Artikel.

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT
);

Fügen Sie nun drei Beispielartikel in die articles-Tabelle ein. Diese Daten werden in den folgenden Schritten für Ihre Suchanfragen verwendet.

INSERT INTO articles (title, content) VALUES
('PostgreSQL Tutorial', 'This is a comprehensive tutorial on PostgreSQL.'),
('Full Text Search in PostgreSQL', 'Learn how to implement full text search using TSVECTOR in PostgreSQL.'),
('PostgreSQL Performance Tuning', 'Tips and tricks to improve the performance of your PostgreSQL database.');

Um zu bestätigen, dass die Daten korrekt eingefügt wurden, führen Sie eine SELECT-Abfrage aus, um alle Datensätze in der Tabelle anzuzeigen.

SELECT * FROM articles;

Sie sollten die drei gerade eingefügten Zeilen sehen, ähnlich der folgenden Ausgabe:

 id |             title              |                                       content
----+----------------------------------+-----------------------------------------------------------------------
  1 | PostgreSQL Tutorial            | This is a comprehensive tutorial on PostgreSQL.
  2 | Full Text Search in PostgreSQL | Learn how to implement full text search using TSVECTOR in PostgreSQL.
  3 | PostgreSQL Performance Tuning  | Tips and tricks to improve the performance of your PostgreSQL database.
(3 rows)

TSVECTOR-Spalte und GIN-Index hinzufügen

Um Volltextsuche durchzuführen, benötigen Sie eine Spalte vom Typ tsvector. Ein tsvector-Wert ist eine sortierte Liste von eindeutigen Wörtern (Lexemen), die normalisiert wurden, um verschiedene Varianten desselben Wortes zusammenzuführen. In diesem Schritt fügen Sie eine tsvector-Spalte hinzu und erstellen einen Index darauf, um Suchanfragen zu beschleunigen.

Fügen Sie zunächst eine neue Spalte namens search_vector vom Typ TSVECTOR zur articles-Tabelle hinzu.

ALTER TABLE articles ADD COLUMN search_vector TSVECTOR;

Befüllen Sie als Nächstes diese neue Spalte, indem Sie den title und content jedes Artikels in einen tsvector konvertieren. Die Funktion to_tsvector wird für diese Konvertierung verwendet. Sie verarbeitet den Text, indem sie gängige Wörter (Stoppwörter) entfernt und Wörter auf ihre Grundform reduziert (Stemming).

UPDATE articles
SET search_vector = to_tsvector('english', title || ' ' || content);

In diesem Befehl gibt 'english' die Textsuchkonfiguration an, und || ist der Verkettungsoperator, um Titel und Inhalt zu kombinieren.

Um Volltextsuchanfragen erheblich zu beschleunigen, erstellen Sie einen GIN (Generalized Inverted Index) auf der search_vector-Spalte. GIN-Indizes sind sehr effektiv für die Indizierung von zusammengesetzten Werten wie tsvector.

CREATE INDEX articles_search_idx ON articles USING GIN (search_vector);

Sie können überprüfen, ob die Spalte und der Index hinzugefügt wurden, indem Sie die Tabellenstruktur mit dem Befehl \d untersuchen.

\d articles

Die Ausgabe sollte nun die search_vector-Spalte und den articles_search_idx GIN-Index enthalten.

                                     Table "public.articles"
    Column     |          Type          | Collation | Nullable |                 Default
---------------+------------------------+-----------+----------+-----------------------------------------
 id            | integer                |           | not null | nextval('articles_id_seq'::regclass)
 title         | character varying(255) |           | not null |
 content       | text                   |           |          |
 search_vector | tsvector               |           |          |
Indexes:
    "articles_pkey" PRIMARY KEY, btree (id)
    "articles_search_idx" gin (search_vector)

Volltextsuche durchführen

Mit der tsvector-Spalte und dem GIN-Index können Sie nun effiziente Volltextsuchanfragen durchführen. Abfragen werden mit dem Datentyp tsquery konstruiert, der eine Textabfrage darstellt. Der Operator @@ wird verwendet, um einen tsquery mit einem tsvector abzugleichen.

Suchen wir zunächst nach Artikeln, die das Wort search enthalten. Die Funktion to_tsquery konvertiert die Suchzeichenkette in einen tsquery.

SELECT title, content FROM articles WHERE search_vector @@ to_tsquery('english', 'search');

Diese Abfrage gibt den Artikel über "Full Text Search" zurück.

             title              |                                       content
----------------------------------+-----------------------------------------------------------------------
 Full Text Search in PostgreSQL | Learn how to implement full text search using TSVECTOR in PostgreSQL.
(1 row)

Sie können auch Operatoren innerhalb Ihrer Abfrage verwenden. Der Operator & (AND) findet Dokumente, die alle angegebenen Begriffe enthalten. Suchen wir nach Artikeln, die sowohl PostgreSQL als auch performance enthalten.

SELECT title FROM articles WHERE search_vector @@ to_tsquery('english', 'PostgreSQL & performance');

Dies gibt den Artikel über Performance Tuning zurück.

             title
-------------------------------
 PostgreSQL Performance Tuning
(1 row)

Der Operator | (OR) findet Dokumente, die mindestens einen der angegebenen Begriffe enthalten. Suchen wir nach Artikeln, die entweder tutorial oder tuning enthalten.

SELECT title FROM articles WHERE search_vector @@ to_tsquery('english', 'tutorial | tuning');

Diese Abfrage gibt zwei Artikel zurück.

             title
-------------------------------
 PostgreSQL Tutorial
 PostgreSQL Performance Tuning
(2 rows)

Updates mit einem Trigger automatisieren

Derzeit ist die search_vector-Spalte statisch. Wenn Sie den Titel oder Inhalt eines Artikels aktualisieren, wird der search_vector die Änderung nicht automatisch widerspiegeln. Um dies zu lösen, können Sie einen Trigger erstellen, der die search_vector-Spalte vor jeder INSERT- oder UPDATE-Operation auf der articles-Tabelle automatisch aktualisiert.

Definieren Sie zunächst eine Funktion, die vom Trigger ausgeführt wird. Diese Funktion aktualisiert den search_vector für die neue oder aktualisierte Zeile.

CREATE OR REPLACE FUNCTION articles_tsvector_update() RETURNS TRIGGER AS $$
BEGIN
    NEW.search_vector :=
        to_tsvector('pg_catalog.english', NEW.title || ' ' || NEW.content);
    RETURN NEW;
END
$$ LANGUAGE plpgsql;

Die Variable NEW in der Funktion bezieht sich auf die Zeile, die eingefügt oder aktualisiert wird.

Erstellen Sie als Nächstes den Trigger selbst. Dieser Trigger ruft die Funktion articles_tsvector_update vor jeder Zeileneinfügung oder -aktualisierung auf.

CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
ON articles FOR EACH ROW EXECUTE FUNCTION articles_tsvector_update();

Testen wir nun den Trigger. Fügen Sie zunächst einen neuen Artikel über Datenbankindizierung ein.

INSERT INTO articles (title, content) VALUES ('Database Indexing', 'Learn about B-Tree and GIN indexes.');

Aufgrund des Triggers wurde der search_vector für diesen neuen Artikel automatisch befüllt. Sie können dies überprüfen, indem Sie nach dem Wort indexes suchen.

SELECT title FROM articles WHERE search_vector @@ to_tsquery('indexes');

Der neue Artikel sollte in den Ergebnissen erscheinen.

        title
---------------------
 Database Indexing
(1 row)

Dies bestätigt, dass Ihr Trigger korrekt funktioniert und sicherstellt, dass Ihr Volltextsuchindex mit Ihren Daten synchron bleibt.

Zusammenfassung

In diesem Lab haben Sie die Grundlagen der Implementierung von Volltextsuche in PostgreSQL gelernt. Sie haben erfolgreich eine Tabelle erstellt, diese mit Daten gefüllt und für die Volltextsuche konfiguriert, indem Sie eine tsvector-Spalte hinzugefügt haben. Sie haben gelernt, wie Sie die Funktion to_tsvector zur Textverarbeitung und einen GIN-Index zur Optimierung der Suchleistung verwenden. Außerdem haben Sie das Durchführen von Suchen mit tsquery unter Verwendung verschiedener Operatoren geübt. Schließlich haben Sie einen Trigger implementiert, um den Prozess der Synchronisierung des Suchindexes mit Ihren Daten zu automatisieren, was ein entscheidender Schritt zur Aufrechterhaltung eines zuverlässigen Suchsystems ist.