Recherche en texte intégral avec pg_textsearch

L'extension pg_textsearch dans Cloud SQL pour PostgreSQL fournit une recherche en texte intégral à l'aide de l'algorithme de scoring BM25 (Best Matching 25), une norme du secteur, pour fournir un score de pertinence très précis. L'extension est un projet Open Source disponible sur GitHub.

L'extension pg_textsearch nécessite PostgreSQL 17 ou version ultérieure.

Comme pg_textsearch est compatible avec la fréquence inverse du document, la saturation de la fréquence des termes et la normalisation de la longueur du document, il peut produire des résultats plus pertinents que la fonction ts_rank intégrée de PostgreSQL. De plus, comme il fonctionne directement sur les pages de stockage PostgreSQL standards, vous n'avez pas besoin d'installer un moteur externe comme Elasticsearch ni de vous soucier de la maintenance des doubles clusters et de la synchronisation des données. À l'aide de pg_textsearch, vous pouvez créer des expériences de recherche robustes, évolutives et très pertinentes sans quitter l'écosystème PostgreSQL.

Plusieurs options de recherche de texte sont disponibles dans Cloud SQL pour PostgreSQL :

  • Correspondance exacte : avec la recherche SQL standard, vous utilisez les instructions LIKE et ILIKE pour rechercher une séquence exacte de caractères. Par exemple, la requête ILIKE '%smart fitness watches%' permet de trouver des instances telles que :

    • "Découvrez nos nouvelles montres connectées pour la forme en promotion."
    • "Les montres connectées pour la forme sont d'excellents cadeaux."

    Toutefois, il ne trouverait pas :

    • "Les coureurs veulent une montre de fitness intelligente et étanche."
    • "Cette montre est particulièrement intelligente en matière de remise en forme."

  • Recherche en texte intégral : à l'aide de l'algorithme BM25 et de tsvector, la recherche en texte intégral divise le texte en mots racines, ignore les mots filtres et évalue la pertinence. Elle comprend les règles linguistiques, y compris le pluriel et la grammaire, et tient compte de la fréquence des mots. Une recherche en texte intégral pour smart AND fitness AND watches trouverait les instances que la requête de correspondance exacte de l'exemple manquerait, ainsi que des instances plus complexes comme celles-ci :

    • "Une montre connectée est idéale pour votre routine sportive quotidienne."
    • "Toutes les montres ne sont pas aussi intelligentes en matière de forme physique."

    Toutefois, il ne trouverait pas :

    • "Une montre intelligente qui tient compte de votre santé peut vous aider dans votre programme d'exercice."
    • "Ce bracelet intelligent de suivi d'activité est une aubaine."

  • Recherche sémantique : à l'aide de modèles d'IA, la recherche sémantique convertit le texte en vecteurs et mesure la distance de similarité. Il comprend la signification, l'intention, le contexte, les synonymes et les concepts associés. Une recherche sémantique pour smart fitness watches trouverait les exemples d'instances que les autres méthodes de recherche manqueraient. Elle comprendrait qu'une "montre intelligente axée sur la santé" est la même chose qu'une "montre de fitness intelligente", et qu'un "bracelet intelligent de suivi d'activité physique" pourrait également être pertinent. Il peut s'agir d'une erreur de correspondance avec un bracelet podomètre si la priorité n'a pas été suffisamment accordée à "montre".

Installer l'extension pg_textsearch

Pour installer et activer pg_textsearch, procédez comme suit :

  1. Définissez le flag cloudsql.enable_pg_textsearch sur on, comme décrit dans Configurer des options de base de données. Cela ajoute pg_textsearch à shared_preload_libraries.

  2. Installer l'extension pg_textsearch

      CREATE EXTENSION pg_textsearch;

  3. Vérifiez l'installation :

      SELECT extversion FROM pg_extension WHERE extname = 'pg_textsearch'

Rechercher avec pg_textsearch

Supposons que l'exemple de tableau suivant ait été rempli avec des données :

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

Avant d'utiliser la recherche en texte intégral, créez d'abord un index BM25 :

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

Vous pouvez ensuite effectuer une requête de recherche en texte intégral comme suit :

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

Effectuer des recherches hybrides à l'aide de pg_textsearch et vector

Vous pouvez utiliser pg_textsearch avec l'extension de recherche sémantique vector pour effectuer des recherches hybrides. Installez l'extension de recherche sémantique vector comme suit :

CREATE EXTENSION IF NOT EXISTS vector CASCADE;

Effectuez une requête de recherche hybride sémantique et en texte intégral comme celle-ci :

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;

Indicateurs pour configurer l'extension pg_textsearch

Utilisez les flags suivants pour configurer la recherche en texte intégral pg_textsearch :