Volltextsuche mit pg_textsearch

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

Die Erweiterung pg_textsearch erfordert PostgreSQL 17 oder höher.

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

Für die Textsuche in Cloud SQL for PostgreSQL gibt es mehrere Optionen:

  • Genau passend: Bei der Standard-SQL-Suche verwenden Sie die Anweisungen LIKE und ILIKE, um nach einer genauen Zeichenfolge zu suchen. Eine Beispielabfrage wäre ILIKE '%smart fitness watches%', die Instanzen wie die folgenden finden würde:

    • „Entdecke unsere neuen Fitness-Smartwatches im Angebot.“
    • Fitness-Smartwatches sind tolle Geschenke.

    Folgendes würde jedoch nicht gefunden werden:

    • „Läufer wollen eine intelligente, wasserdichte Fitness-Smartwatch.“
    • „Diese Smartwatch ist besonders smart, wenn es um Fitness geht.“
  • Volltextsuche: Bei der Volltextsuche mit dem BM25-Algorithmus und tsvector wird Text in Stammwörter zerlegt, Filterwörter werden ignoriert und die Relevanz wird bewertet. Es versteht Sprachregeln, einschließlich Pluralformen und Grammatik, und berücksichtigt die Worthäufigkeit. Bei einer Volltextsuche nach smart AND fitness AND watches werden die Instanzen gefunden, die bei der Beispielabfrage mit genauer Übereinstimmung fehlen, sowie komplexere Instanzen wie diese:

    • „Eine Smartwatch ist perfekt für deine tägliche Fitnessroutine.“
    • „Nicht jede Smartwatch ist so smart, wenn es um Fitness geht.“

    Folgendes würde jedoch nicht gefunden werden:

    • „Eine intelligente Smartwatch, die deine Gesundheit im Blick behält, kann dir bei deinem Trainingsprogramm helfen.“
    • „Dieses intelligente Fitnessarmband ist ein Schnäppchen.“
  • Semantische Suche: Bei der semantischen Suche wird Text mithilfe von KI-Modellen in Vektoren umgewandelt und die Ähnlichkeitsdistanz gemessen. Es versteht Bedeutung, Absicht, Kontext, Synonyme und verwandte Konzepte. Eine semantische Suche nach smart fitness watches würde die Beispielinstanzen finden, die die anderen Suchmethoden nicht finden würden. Sie würde verstehen, dass eine „intelligente gesundheitsbewusste Uhr“ dasselbe ist wie eine „intelligente Fitnessuhr“ und dass ein „intelligentes Trainingsarmband“ ebenfalls relevant sein könnte. Wenn „Uhr“ nicht ausreichend priorisiert wird, könnte fälschlicherweise ein Schrittzählerband gefunden werden.

pg_textsearch-Erweiterung installieren

So installieren und aktivieren Sie pg_textsearch:

  1. Setzen Sie das Flag cloudsql.enable_pg_textsearch auf on, wie unter Datenbank-Flags konfigurieren beschrieben. Dadurch wird pg_textsearch zu 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 können, müssen Sie einen BM25-Index erstellen:

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

Anschließend können Sie eine Volltextsuche wie diese ausführen:

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 semantischen Sucherweiterung vector verwenden, um hybride Suchen durchzuführen. So installieren Sie die semantische Sucherweiterung vector:

CREATE EXTENSION IF NOT EXISTS vector CASCADE;

Stellen Sie eine hybride semantische und Volltextsuchanfrage wie diese:

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 in pg_textsearch zu konfigurieren: