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.
Diferentes formas de pesquisar
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
LIKEeILIKEpara verificar uma sequência exata de caracteres. Um exemplo de consulta seriaILIKE '%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 porsmart AND fitness AND watchesencontraria 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 watchesencontraria 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:
Defina a flag
cloudsql.enable_pg_textsearchcomoon, conforme descrito em Configurar flags de banco de dados. Isso adicionapg_textsearchaoshared_preload_libraries.Instalar a extensão
pg_textsearchCREATE EXTENSION pg_textsearch;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:
pg_textsearch.bulk_load_threshold: termos por transação antes do transbordamento automático. Defina como 0 para desativar. Padrão: 100.000.pg_textsearch.compress_segments: compacta blocos de postagem em novos segmentos. Padrão:on.pg_textsearch.default_limit: o número máximo de documentos pontuados quando nenhuma cláusulaLIMITestá presente. Padrão: 1.000.pg_textsearch.memtable_pages_threshold: número de páginas a serem encadeadas antes do transbordamento automático. Defina como 0 para desativar. Padrão: 64.pg_textsearch.segments_per_level: segmentos por nível antes da compactação automática. Intervalo: 2-64. Padrão: 8.