Busca de texto completo no PostgreSQL: quando bem feita, é rápida (desfazendo o mito de que é lenta)

• Existe a percepção de que a Full-Text Search (FTS) nativa do PostgreSQL é lenta, mas, com a otimização adequada, ela pode ser muito rápida
• No blog da Neon, foi feita uma comparação entre a extensão pg_search, baseada em Rust, e a FTS nativa, defendendo que a segunda seria lenta
• Porém, é muito provável que essa comparação tenha sido feita sem otimizações básicas indispensáveis para a FTS do PostgreSQL
• Este artigo demonstra com números que, aplicando apenas otimizações simples à configuração padrão da FTS, é possível obter um ganho de desempenho de 50x

Visão geral da configuração do benchmark

• O teste foi realizado com base em uma tabela com 10 milhões de registros de log

  CREATE TABLE benchmark_logs (  
      id SERIAL PRIMARY KEY,  
      message TEXT,  
      country VARCHAR(255),  
      severity INTEGER,  
      timestamp TIMESTAMP,  
      metadata JSONB  
  );  
  

• Estrutura da consulta problemática:

  SELECT country, COUNT(*)  
  FROM benchmark_logs  
  WHERE to_tsvector('english', message) @@ to_tsquery('english', 'research')  
  GROUP BY country  
  ORDER BY country;  
  

  • Executar to_tsvector() dentro da consulta → muito ineficiente
  • Mesmo com índice GIN, ele não é aproveitado corretamente

Ambiente de teste (reprodução da configuração base)

• Instância EC2 i7ie.xlarge com SSD NVMe local
• 4 vCPUs, PostgreSQL 16 (Docker)
• Principais configurações do PostgreSQL:

  -c shared_buffers=8GB  
  -c maintenance_work_mem=8GB  
  -c max_parallel_workers=4  
  -c max_worker_processes=4  
  

• Limite de paralelismo: max_parallel_workers_per_gather = 2 (a Neon usou 8)

Fator 1 de perda de desempenho: cálculo de tsvector em tempo real

• Ao executar to_tsvector() dentro da consulta:
• O parsing do texto, a análise morfológica etc. são refeitos a cada vez
• Não é possível aproveitar o índice de forma alguma

• Solução: pré-gerar a coluna tsvector e indexá-la

  • 1. Adicionar a coluna tsvector

  ALTER TABLE benchmark_logs ADD COLUMN message_tsvector tsvector;  
  

  • 2. Preencher os dados

    UPDATE benchmark_logs SET message_tsvector = to_tsvector('english', message);  
    

  • 3. Criar o índice (com fastupdate desativado)

    CREATE INDEX idx_gin_logs_message_tsvector  
    ON benchmark_logs USING GIN (message_tsvector)  
    WITH (fastupdate = off);  
    

  • 4. Ajustar a consulta

    SELECT country, COUNT(*)  
    FROM benchmark_logs  
    WHERE message_tsvector @@ to_tsquery('english', 'research')  
    GROUP BY country  
    ORDER BY country;  
    

Fator 2 de perda de desempenho: configuração fastupdate=on no índice GIN

• fastupdate=on ajuda no desempenho de escrita, mas prejudica a performance de busca
• Para conjuntos de dados somente leitura ou orientados a busca, fastupdate=off é essencial
• O índice fica menor e mais rápido, sem necessidade de processar a pending list

• Como criar um índice GIN otimizado

  CREATE INDEX idx_gin_logs_message_tsvector  
  ON benchmark_logs USING GIN (message_tsvector)  
  WITH (fastupdate = off);  
  

Ganho de desempenho: melhoria de mais de 50x

• Antes da otimização: cerca de 41,3 segundos (41.301 ms)
• Depois da otimização: cerca de 0,88 segundo (877 ms)
• Isso representa um ganho de desempenho de aproximadamente 50x
• Esse resultado foi obtido mesmo em um ambiente com pouco paralelismo

O desempenho de ts_rank pode realmente ser lento

• ts_rank ou ts_rank_cd podem ser relativamente lentos, pois avaliam todos os resultados antes de ordenar
• Especialmente com grandes volumes de resultados, a carga de CPU/IO pode ser alta

Recurso avançado de ranking: extensão VectorChord-BM25

• Quando precisão de ordenação e velocidade são importantes, usar uma extensão dedicada pode ser mais eficaz
• VectorChord-BM25 é uma extensão para PostgreSQL que oferece ranking baseado no algoritmo BM25
• Há relatos de que ela pode ser 3x mais rápida que o Elasticsearch

Vantagens do VectorChord-BM25

• Algoritmo BM25: algoritmo de ranking de busca mais avançado que TF-IDF
• Formato de índice dedicado: otimizado para buscas de alta velocidade, como Block WeakAnd
• Fornece o tipo bm25vector: armazena representações tokenizadas
• Melhora tanto a precisão quanto a velocidade da busca

Conclusão: a FTS nativa do PostgreSQL também é rápida o suficiente

• Com uma coluna tsvector e um índice GIN adequado (fastupdate=off), é possível ter buscas muito rápidas mesmo com a FTS nativa
• Comparações de desempenho devem ser feitas com uma base devidamente otimizada
• Se for necessário um recurso de ranking mais avançado, vale considerar extensões como VectorChord-BM25
• Mensagem principal: o problema pode não ser a ferramenta ser lenta, mas sim a configuração

[stadia]

Graças a isso, fiz o tuning da consulta.

[pcj9024]

Os comentários no Hacker News dão medo... "Dez milhões? Tá de brincadeira?"

[GN⁺]

Comentários do Hacker News

• Como mantenedor do pg_search, segundo a documentação do Postgres, tanto o artigo da Neon/ParadeDB quanto a estratégia usada aqui são apresentados como alternativas válidas

  • O problema do FTS do Postgres não é otimizar uma única consulta, mas oferecer desempenho no nível do Elastic para uma variedade de consultas reais
  • O pg_search foi projetado para resolver este segundo problema, e os benchmarks também refletem isso
  • O benchmark da Neon/ParadeDB inclui apenas 12 consultas no total, o que é irrealista para casos de uso do mundo real
  • O pg_search funciona com vários tipos de consulta no "estilo Elastic" e tipos do Postgres apenas com uma definição simples de índice

• Calcular tsvector em tempo real é um grande erro

  • Quando implementei Postgres FTS em um projeto pessoal, li a documentação e segui as orientações
  • A documentação explica com clareza como criar um caso básico não otimizado e depois otimizá-lo
  • Quem cometeu esse erro parece não ter lido a documentação ou ter a intenção de retratar o Postgres FTS de forma enganosa

• Não consigo entender a tendência de querer colocar tudo dentro do Postgres

• Fico feliz em ver mais implementações de busca full-text nativas do Postgres

  • Soluções alternativas (lucene/tantivy) foram projetadas para segmentos imutáveis e, quando combinadas com tabelas heap do Postgres, podem resultar em uma solução pior

• Sem o plano de execução, é difícil entender o que está acontecendo

  • Se a consulta usa o índice, a rechecagem do tsvector em tempo real se aplica apenas aos itens correspondentes, e como a consulta do benchmark usa LIMIT 10, há pouca rechecagem
  • Como a condição da consulta tem filtros em dois índices GIN, parece que o otimizador está rechecando primeiro todos os itens correspondentes

• Há alguns anos, eu queria usar FTS nativo, mas fracassei

  • Em uma tabela com milhares de inserções por segundo, atualizações completas ficaram lentas e as transações estouravam o tempo limite
  • Adicionei índices, mas quando o segundo índice terminou, o sistema passou a sofrer timeouts
  • Tive de remover os índices novamente e nunca cheguei a ter a chance de testar o desempenho real do FTS

• Empacotei extensões RPM/DEB para pg_search e vchord_bm25

  • Estou compartilhando o link para quem quiser fazer benchmarks por conta própria

• Já vi muitas equipes irem direto para Elasticsearch ou Meilisearch

  • Se usado corretamente, dá para extrair bastante desempenho do FTS nativo do PG
  • Fico curioso se seria possível obter desempenho semelhante no navegador usando SQLite + FTS5 + Wasm

• 10 milhões de registros é um dataset de brinquedo

  • Grandes datasets de texto, como toda a Wikipedia ou os comentários do Reddit até 2022, seriam mais adequados para benchmark

• Usei full text do pg pela primeira vez por volta de 2008

  • O problema da busca full-text do Postgres não é que ela seja lenta demais, e sim que não é flexível o suficiente
  • Ela é boa para adicionar uma busca simples, mas insuficiente para ajustar a busca com mais precisão
  • Solr e Elasticsearch permitem configurar indexação e processamento de busca complexos
  • O Postgres poderia adotar esses recursos, mas no momento não oferece nada disso
  • O Postgres divide com base em espaços em branco, e é possível usar manualmente stop words e stemming
  • Não é possível atribuir pontuação de busca com base em pesos de campos
  • Em comparação com as alternativas, é um sistema de brinquedo