Búsqueda en el texto completo con pg_textsearch

La extensión pg_textsearch en Cloud SQL para PostgreSQL proporciona búsqueda de texto completo con el algoritmo de puntuación BM25 (Best Matching 25) estándar de la industria para ofrecer una puntuación de relevancia muy precisa. La extensión es un proyecto de código abierto disponible en GitHub.

La extensión pg_textsearch requiere PostgreSQL 17 o una versión posterior.

Dado que pg_textsearch admite la frecuencia inversa del documento, la saturación de la frecuencia del término y la normalización de la longitud del documento, puede producir resultados más relevantes que la función ts_rank integrada de PostgreSQL. Además, como opera directamente en las páginas de almacenamiento estándar de PostgreSQL, no tienes que instalar un motor externo como Elasticsearch ni preocuparte por el mantenimiento de clústeres dobles ni la sincronización de datos. Con pg_textsearch, puedes crear experiencias de búsqueda sólidas, escalables y muy relevantes sin salir del ecosistema de PostgreSQL.

Existen varias opciones para la búsqueda de texto en Cloud SQL para PostgreSQL:

  • Coincidencia exacta: Con la búsqueda de SQL estándar, usas las instrucciones LIKE y ILIKE para buscar una secuencia exacta de caracteres. Un ejemplo de búsqueda sería ILIKE '%smart fitness watches%', que encontraría instancias como las siguientes:

    • "Explora nuestros nuevos relojes inteligentes de fitness en oferta".
    • "Los relojes inteligentes para actividad física son regalos excelentes".

    Sin embargo, no podría encontrar lo siguiente:

    • "Los corredores quieren un reloj inteligente sumergible para acondicionamiento físico".
    • "Este reloj es particularmente inteligente en cuanto al fitness".

  • Búsqueda de texto completo: Con el algoritmo BM25 y tsvector, la búsqueda de texto completo divide el texto en palabras raíz, ignora las palabras de filtro y califica la relevancia. Comprende las reglas del idioma, incluidos los plurales y la gramática, y tiene en cuenta la frecuencia de las palabras. Una búsqueda en el texto completo de smart AND fitness AND watches encontraría las instancias que la consulta de coincidencia exacta de ejemplo no encontraría, y también instancias más complicadas como las siguientes:

    • "Un reloj inteligente es perfecto para tu rutina diaria de fitness".
    • "No todos los relojes son tan inteligentes cuando se trata de actividad física".

    Sin embargo, no podría encontrar lo siguiente:

    • "Un reloj inteligente que monitorea tu salud puede ayudarte con tu programa de ejercicios".
    • "Esta inteligente correa de monitoreo de ejercicios es una ganga".

  • Búsqueda semántica: Con modelos de IA, la búsqueda semántica convierte el texto en vectores y mide la distancia de similitud. Comprende el significado, la intención, el contexto, los sinónimos y los conceptos relacionados. Una búsqueda semántica de smart fitness watches encontraría las instancias de ejemplo que los otros métodos de búsqueda no encontrarían. Comprendería que un "reloj inteligente que monitorea la salud" es lo mismo que un "reloj inteligente de fitness", y que una "banda inteligente de monitoreo de ejercicios" también podría ser pertinente. Podría coincidir erróneamente con una banda de podómetro si no priorizó "reloj" lo suficiente.

Instala la extensión de pg_textsearch

Sigue estos pasos para instalar y habilitar pg_textsearch:

  1. Establece la marca cloudsql.enable_pg_textsearch en on como se describe en Configura marcas de bases de datos. Esto agrega pg_textsearch a shared_preload_libraries.

  2. Instala la extensión de pg_textsearch

      CREATE EXTENSION pg_textsearch;

  3. Verifica la instalación:

      SELECT extversion FROM pg_extension WHERE extname = 'pg_textsearch'

Cómo buscar con pg_textsearch

Supongamos que la siguiente tabla de muestra se completó con datos:

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

Antes de usar la búsqueda en el texto completo, primero crea un índice BM25:

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

Luego, puedes realizar una consulta de búsqueda en el texto completo de la siguiente manera:

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

Realiza búsquedas híbridas con pg_textsearch y vector

Puedes usar pg_textsearch junto con la extensión de búsqueda semántica vector para realizar búsquedas híbridas. Instala la extensión de búsqueda semántica vector de la siguiente manera:

CREATE EXTENSION IF NOT EXISTS vector CASCADE;

Realiza una consulta de búsqueda híbrida semántica y de texto completo como esta:

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;

Marcas para configurar la extensión pg_textsearch

Usa las siguientes marcas para configurar la búsqueda en el texto completo de pg_textsearch: