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 oferecer 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 exige o PostgreSQL 17 ou versões mais recentes.

Como o pg_textsearch oferece suporte à frequência inversa de documentos, à saturação de frequência de termos e à normalização do comprimento de documentos, 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 para pesquisar texto no Cloud SQL para PostgreSQL:

  • Correspondência exata: com a pesquisa SQL padrão, você usa as instruções LIKE e ILIKE para procurar 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."
    • "Smartwatches de fitness são ótimos presentes."

    No entanto, não seria possível encontrar:

    • Os corredores querem um relógio fitness inteligente e à prova d'água.
    • "Este relógio é especialmente inteligente para fitness."

  • 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 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 relógio inteligente é perfeito para sua rotina diária de fitness."
    • "Nem todo relógio é tão inteligente quando se trata de fitness."

    No entanto, não seria possível encontrar:

    • "Um relógio inteligente que monitora a saúde pode ajudar no seu programa de exercícios."
    • "Essa pulseira inteligente de monitoramento de exercícios é uma pechincha."

  • Pesquisa semântica: usando modelos de IA, a pesquisa semântica converte texto em vetores e mede a distância de similaridade. Ele entende o significado, a intenção, o contexto, os sinônimos e os conceitos relacionados. Uma pesquisa semântica por 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 com monitoramento de saúde" é o mesmo que um "relógio inteligente de fitness", 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 do 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 pg_textsearch

Suponha que a seguinte tabela de amostra 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, você pode fazer uma consulta de pesquisa de texto completo assim:

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

Fazer pesquisas híbridas usando pg_textsearch e vector

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

CREATE EXTENSION IF NOT EXISTS vector CASCADE;

Faça uma consulta de pesquisa semântica e de texto completo híbrida assim:

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 para configurar a extensão pg_textsearch

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