Volltextsuche mit pg_textsearch

Die Erweiterung pg_textsearch in Cloud SQL for PostgreSQL bietet eine Volltextsuche mit dem branchenüblichen BM25-Bewertungsalgorithmus (Best Matching 25), um eine sehr genaue Relevanzbewertung zu ermöglichen. Die Erweiterung ist ein Open-Source-Projekt, das auf GitHub verfügbar ist.

Für die Erweiterung pg_textsearch ist PostgreSQL 17 oder höher erforderlich.

Da pg_textsearch die inverse Dokumenthäufigkeit, die Sättigung der Begriffshäufigkeit und die Normalisierung der Dokumentlänge unterstützt, kann sie relevantere Ergebnisse als die integrierte ts_rank-Funktion von PostgreSQL liefern. Da die Erweiterung direkt auf Standard-PostgreSQL-Speicherseiten ausgeführt wird, müssen Sie keine externe Engine wie Elasticsearch installieren und sich auch nicht um die Wartung von zwei Clustern und die Datensynchronisierung kümmern. Mit pg_textsearch können Sie robuste, skalierbare und hochrelevante Suchfunktionen erstellen, ohne die PostgreSQL-Umgebung zu verlassen.

Es gibt mehrere Optionen für die Textsuche in Cloud SQL for PostgreSQL:

  • Genaue Übereinstimmung: Bei der Standard-SQL-Suche verwenden Sie die Anweisungen LIKE und ILIKE, um nach einer genauen Zeichenfolge zu suchen. Ein Beispiel für eine Anfrage wäre ILIKE '%smart fitness watches%'. Damit würden Instanzen wie die folgenden gefunden:

    • „Entdecke unsere neuen Smartwatches für Sport und Fitness im Angebot.“
    • Smartwatches für Fitness sind tolle Geschenke.

    Folgendes würde jedoch nicht gefunden:

    • „Läufer möchten eine smarte wasserdichte Fitness-Smartwatch.“
    • „Diese Smartwatch ist besonders smart, wenn es um Fitness geht.“

  • Volltextsuche: Bei der Volltextsuche wird der BM25-Algorithmus und tsvector verwendet. Dabei wird Text in Stammwörter unterteilt, Filterwörter werden ignoriert und die Relevanz wird bewertet. Sie versteht Sprachregeln wie Pluralformen und Grammatik und berücksichtigt die Häufigkeit von Wörtern. Bei einer Volltextsuche nach smart AND fitness AND watches würden die Instanzen gefunden, die bei der Beispielabfrage mit genauem Abgleich nicht berücksichtigt würden, sowie komplexere Instanzen wie diese:

    • „Eine Smartwatch ist perfekt für dein tägliches Fitnesstraining.“
    • „Nicht jede Smartwatch ist so smart, wenn es um Fitness geht.“

    Allerdings würde die Suche Folgendes nicht finden:

    • „Eine intelligente Smartwatch kann dir bei deinem Trainingsprogramm helfen.“
    • „Dieses intelligente Fitness-Tracker-Armband ist ein Schnäppchen.“

  • Semantische Suche: Bei der semantischen Suche werden KI-Modelle verwendet, um Text in Vektoren umzuwandeln und den Ähnlichkeitsabstand zu messen. Sie versteht Bedeutung, Absicht, Kontext, Synonyme und verwandte Konzepte. Bei einer semantischen Suche nach smart fitness watches würden die Beispielinstanzen gefunden, die bei den anderen Suchmethoden nicht berücksichtigt würden. Es würde erkannt, dass eine „intelligente, gesundheitsbewusste Smartwatch“ dasselbe ist wie eine „Fitness-Smartwatch“ und dass ein „intelligentes Armband zur Trainingsaufzeichnung“ ebenfalls relevant sein könnte. Möglicherweise wird fälschlicherweise ein Schrittzählerarmband erkannt, wenn „Smartwatch“ nicht ausreichend priorisiert wurde.

pg_textsearch-Erweiterung installieren

So installieren und aktivieren Sie pg_textsearch:

  1. Legen Sie das Flag cloudsql.enable_pg_textsearch auf on fest, wie unter Datenbank-Flags konfigurieren beschrieben. Dadurch wird pg_textsearch der shared_preload_libraries hinzugefügt.

  2. pg_textsearch-Erweiterung installieren

      CREATE EXTENSION pg_textsearch;

  3. Installation prüfen:

      SELECT extversion FROM pg_extension WHERE extname = 'pg_textsearch'

Mit pg_textsearch suchen

Angenommen, die folgende Beispieltabelle wurde mit Daten gefüllt:

CREATE TABLE documents (
    doc_id TEXT PRIMARY KEY,
    content TEXT,
    text_embedding vector(3072)
    GENERATED ALWAYS AS (embedding('gemini-embedding-001', content)) STORED 
  );

Bevor Sie die Volltextsuche verwenden, müssen Sie zuerst einen BM25-Index erstellen:

CREATE INDEX idx_docs_bm25
      ON documents
      USING bm25 (content)
      WITH (text_config = 'english');

Anschließend können Sie eine Volltextsuchanfrage wie diese stellen:

SELECT doc_id, content, content <@> 'database system'
      AS score FROM documents
      ORDER BY content <@> 'database system'
      ASC LIMIT 5;

Hybridsuchen mit pg_textsearch und vector durchführen

Sie können pg_textsearch zusammen mit der Erweiterung für die semantische Suche vector verwenden, um hybride Suchanfragen auszuführen. So installieren Sie die Erweiterung für die semantische Suche vector:

CREATE EXTENSION IF NOT EXISTS vector CASCADE;

So stellen Sie eine hybride semantische und Volltextsuchanfrage:

WITH
  -- Semantic search results
  vector_search AS (
    SELECT doc_id,
      RANK () OVER (ORDER BY text_embedding <=>
                             google_ml.embedding('gemini-embedding-001',
                                                 'database')::VECTOR ) AS rank
      FROM documents
      ORDER BY text_embedding <=> google_ml.embedding('gemini-embedding-001',
                                                      'database')::VECTOR
    LIMIT 10
  ),
  -- Full text search results
  text_search AS (
    SELECT doc_id,
      RANK () OVER (ORDER BY content <@> 'database' ASC) AS rank
    FROM documents
    ORDER BY content <@> 'database' ASC
    LIMIT 10
  )
  -- RRF combining both semantic and full text search results
  SELECT
    COALESCE(vector_search.doc_id, text_search.doc_id) AS id,
    COALESCE(1.0 / (60 + vector_search.rank), 0.0) +
    COALESCE(1.0 / (60 + text_search.rank), 0.0) AS rrf_score
  FROM vector_search
    FULL OUTER JOIN text_search ON vector_search.doc_id = text_search.doc_id
  ORDER BY rrf_score DESC
  LIMIT 5;

Flags zum Konfigurieren der pg_textsearch-Erweiterung

Verwenden Sie die folgenden Flags, um die Volltextsuche für pg_textsearch zu konfigurieren: