Pesquisa de texto completo usando pg_textsearch

A extensão pg_textsearch no Cloud SQL para PostgreSQL oferece pesquisa de texto completo usando o algoritmo de pontuação BM25 (Best Matching 25), padrão do setor, para fornecer uma pontuação de relevância altamente precisa. A extensão é um projeto de código aberto disponível no GitHub.

A extensão pg_textsearch requer o PostgreSQL 17 ou mais recente.

Como o pg_textsearch é compatível com frequência inversa de documentos, saturação de frequência do termo e normalização de tamanho de documento, ele pode produzir resultados mais relevantes do que a função ts_rank integrada do PostgreSQL. Além disso, como ele opera diretamente em páginas de armazenamento padrão do PostgreSQL, não é necessário instalar um mecanismo externo, como o Elasticsearch, nem se preocupar com a manutenção de cluster duplo e a sincronização de dados. Com o pg_textsearch, é possível criar experiências de pesquisa robustas, escalonáveis e altamente relevantes sem sair do ecossistema do PostgreSQL.

Há várias opções de Text Search no Cloud SQL para PostgreSQL:

  • Correspondência exata: com a pesquisa SQL padrão, use as instruções LIKE e ILIKE para verificar uma sequência exata de caracteres. Um exemplo de consulta seria ILIKE '%smart fitness watches%', que encontraria instâncias como:

    • "Confira nossos novos relógios fitness inteligentes em promoção."
    • "Relógios fitness inteligentes são ótimos presentes."

    No entanto, ela não encontraria:

    • "Os corredores querem um relógio fitness inteligente e à prova d'água."
    • "Este relógio é particularmente inteligente em relação ao condicionamento físico."
  • Pesquisa de texto completo: usando o algoritmo BM25 e tsvector, a pesquisa de texto completo divide o texto em palavras-raiz, ignora palavras de filtro e pontua a relevância. Ele entende as regras de linguagem, incluindo plurais e gramática, e considera a frequência das palavras. Uma pesquisa de texto completo por smart AND fitness AND watches encontraria as instâncias que a consulta de correspondência exata do exemplo não encontraria, além de instâncias mais complicadas como estas:

    • "Um smartwatch é perfeito para sua rotina diária de atividades físicas."
    • "Nem todo relógio é tão inteligente quando o assunto é condicionamento físico."

    No entanto, ela não encontraria:

    • "Um relógio inteligente que monitora a saúde pode ajudar no seu programa de exercícios."
    • "Esta pulseira inteligente de monitoramento de atividade física é uma pechincha."
  • Pesquisa semântica: usando modelos de IA, a pesquisa semântica converte texto em vetores e mede a distância de similaridade. Ela entende o significado, a intenção, o contexto, os sinônimos e os conceitos relacionados. Uma pesquisa semântica de smart fitness watches encontraria as instâncias de exemplo que os outros métodos de pesquisa não encontrariam. Ela entenderia que um "relógio inteligente que monitora a saúde" é o mesmo que um "relógio fitness inteligente" e que uma "pulseira inteligente de monitoramento de exercícios" também pode ser relevante. Ele pode corresponder por engano a uma pulseira de pedômetro se não priorizar "relógio" o suficiente.

Instalar a extensão pg_textsearch

Siga estas etapas para instalar e ativar o pg_textsearch:

  1. Defina a flag cloudsql.enable_pg_textsearch como on, conforme descrito em Configurar flags de banco de dados. Isso adiciona pg_textsearch ao shared_preload_libraries.

  2. Instalar a extensão pg_textsearch

      CREATE EXTENSION pg_textsearch;
    
  3. Verifique a instalação:

      SELECT extversion FROM pg_extension WHERE extname = 'pg_textsearch'
    

Pesquisar usando o pg_textsearch

Suponha que a tabela de amostra a seguir tenha sido preenchida com dados:

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 a pesquisa de texto completo, crie um índice BM25:

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

Em seguida, faça uma consulta de pesquisa de texto completo como esta:

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

Realizar pesquisas híbridas usando pg_textsearch e vector

É possível usar pg_textsearch com a extensão de pesquisa semântica vector para realizar pesquisas híbridas. Instale a extensão de pesquisa semântica vector desta forma:

CREATE EXTENSION IF NOT EXISTS vector CASCADE;

Faça uma consulta de pesquisa híbrida semântica e 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;

Sinalizações para configurar a extensão pg_textsearch

Use as seguintes flags para configurar a pesquisa de texto completo do pg_textsearch: