Full-text search no PostgreSQL

-- tsvector: texto processado (palavras normalizadas + posições):
SELECT to_tsvector('portuguese', 'O servidor está executando com alta disponibilidade');
-- 'alt':3 'disponibilidad':5 'execut':3 'servidor':2

-- Configuração "portuguese" faz:
-- 1. Remove stop words (o, a, de, com...)
-- 2. Aplica stemming (executando → execut, alta → alt)
-- 3. Registra posição de cada palavra no texto

-- tsquery: padrão de busca:
SELECT to_tsquery('portuguese', 'servidor & disponibilidade');
-- 'servidor' & 'disponibilid'

-- Operadores de busca:
-- &  (AND): ambas as palavras
-- |  (OR): qualquer uma
-- !  (NOT): excluir palavra
-- <-> (FOLLOWED BY): palavra A seguida por B
-- <2> (DISTANCE): palavras com distância máxima

-- Verificar se documento corresponde à query:
SELECT to_tsvector('portuguese', 'alta disponibilidade do servidor')
  @@ to_tsquery('portuguese', 'servidor & disponibilidade');
-- t (true = encontrou)

-- plainto_tsquery: aceita texto livre (não precisa de & | !):
SELECT plainto_tsquery('portuguese', 'servidor alta disponibilidade');
-- 'servidor' & 'alt' & 'disponibilid'

-- websearch_to_tsquery: sintaxe do Google (mais amigável):
SELECT websearch_to_tsquery('portuguese', '"alta disponibilidade" OR backup');

-- Adicionar coluna de busca a uma tabela de artigos:
ALTER TABLE artigos ADD COLUMN busca tsvector;

-- Preencher a coluna (pesos: A=maior, B, C, D=menor):
UPDATE artigos SET busca =
  setweight(to_tsvector('portuguese', coalesce(titulo, '')), 'A') ||
  setweight(to_tsvector('portuguese', coalesce(subtitulo, '')), 'B') ||
  setweight(to_tsvector('portuguese', coalesce(conteudo, '')), 'C');

-- Criar índice GIN para buscas rápidas:
CREATE INDEX CONCURRENTLY idx_artigos_busca ON artigos USING GIN(busca);

-- Manter a coluna atualizada automaticamente com trigger:
CREATE FUNCTION atualizar_busca_artigos() RETURNS trigger AS $
BEGIN
  NEW.busca =
    setweight(to_tsvector('portuguese', coalesce(NEW.titulo, '')), 'A') ||
    setweight(to_tsvector('portuguese', coalesce(NEW.subtitulo, '')), 'B') ||
    setweight(to_tsvector('portuguese', coalesce(NEW.conteudo, '')), 'C');
  RETURN NEW;
END;
$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_busca_artigos
  BEFORE INSERT OR UPDATE ON artigos
  FOR EACH ROW EXECUTE FUNCTION atualizar_busca_artigos();

-- Buscar com ranking por relevância:
SELECT
  id, titulo,
  ts_rank(busca, query) AS relevancia,
  ts_headline('portuguese', conteudo, query,
    'MaxFragments=2, MaxWords=20, MinWords=10') AS trecho
FROM artigos, websearch_to_tsquery('portuguese', 'deploy docker') query
WHERE busca @@ query
ORDER BY relevancia DESC
LIMIT 10;

-- Busca ad-hoc sem coluna tsvector (mais flexível, mais lento):
SELECT id, nome, descricao,
  ts_rank(
    to_tsvector('portuguese', nome || ' ' || coalesce(descricao, '')),
    plainto_tsquery('portuguese', 'configuração nginx')
  ) AS relevancia
FROM produtos
WHERE to_tsvector('portuguese', nome || ' ' || coalesce(descricao, ''))
  @@ plainto_tsquery('portuguese', 'configuração nginx')
ORDER BY relevancia DESC;

-- Índice de expressão (sem coluna extra):
CREATE INDEX CONCURRENTLY idx_produtos_fts
  ON produtos USING GIN(
    to_tsvector('portuguese', nome || ' ' || coalesce(descricao, ''))
  );

-- API Node.js — busca full-text com parâmetro:
async function buscarProdutos(termo: string, limite = 10) {
  const result = await db.query(
    `SELECT id, nome, descricao,
       ts_rank(busca, websearch_to_tsquery('portuguese', $1)) AS relevancia,
       ts_headline('portuguese', descricao, websearch_to_tsquery('portuguese', $1),
         'MaxFragments=1, MaxWords=15') AS trecho
     FROM produtos
     WHERE busca @@ websearch_to_tsquery('portuguese', $1)
     ORDER BY relevancia DESC
     LIMIT $2`,
    [termo, limite]
  )
  return result.rows
}

-- Verificar configurações de texto disponíveis:
SELECT cfgname FROM pg_ts_config;
-- portuguese já vem instalado na maioria das distros

-- Testar stemming em português:
SELECT ts_lexize('portuguese_stem', 'configurações');
-- {configur}  ← stem correto

SELECT ts_lexize('portuguese_stem', 'executando');
-- {execut}

-- Adicionar sinônimos customizados (ex: abreviaturas técnicas):
-- Criar arquivo /usr/share/postgresql/16/tsearch_data/meus_sinonimos.syn:
-- nginx nginx web-server webserver
-- k8s kubernetes

-- Criar dicionário de sinônimos:
CREATE TEXT SEARCH DICTIONARY sinonimos_tech (
  TEMPLATE = synonym,
  SYNONYMS = meus_sinonimos
);

-- Criar configuração customizada baseada no português:
CREATE TEXT SEARCH CONFIGURATION pt_tech (COPY = portuguese);
ALTER TEXT SEARCH CONFIGURATION pt_tech
  ALTER MAPPING FOR asciiword, word, numword
  WITH sinonimos_tech, portuguese_stem;

-- Usar a configuração customizada:
SELECT to_tsvector('pt_tech', 'configuração nginx k8s deploy');
-- 'configur':1 'deploy':4 'kubernetes':3 'nginx':2

-- PostgreSQL FTS é suficiente quando:
-- ✓ Volume de documentos < 10 milhões
-- ✓ Buscas simples (palavras, frases, pesos por campo)
-- ✓ Você já usa PostgreSQL (zero infraestrutura extra)
-- ✓ Consistência transacional é importante (busca sempre reflete o banco)
-- ✓ Time pequeno sem dedicação para manter Elasticsearch

-- Elasticsearch faz mais sentido quando:
-- ✓ > 10-50 milhões de documentos com buscas frequentes
-- ✓ Análise de texto muito sofisticada (fuzzy search, sinônimos complexos)
-- ✓ Agregações de analytics em tempo real sobre logs/eventos
-- ✓ Buscas geoespaciais complexas
-- ✓ Você tem infraestrutura e equipe para manter

-- Benchmarks típicos para buscas simples:
-- PostgreSQL FTS com GIN: 5-50ms para 1M documentos
-- Elasticsearch: 1-10ms para qualquer volume, mas +8GB RAM

-- Opção intermediária: pg_trgm (trigrams) para busca fuzzy:
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_produtos_trgm ON produtos USING GIN(nome gin_trgm_ops);

-- Busca fuzzy (tolera erros de digitação):
SELECT nome, similarity(nome, 'ngnix') AS sim
FROM produtos
WHERE nome % 'ngnix'   -- % = similaridade > pg_trgm.similarity_threshold
ORDER BY sim DESC;