Fundamentos de Búsqueda de Texto Completo

PostgreSQL incluye búsqueda de texto completo nativa con tsvector, tsquery e índices GIN. Para muchos productos, reemplaza un clúster de Elasticsearch independiente cuando el volumen de datos, las necesidades de clasificación y la dotación de personal operativo favorecen una única base de datos.

Receta

-- Tabla mínima de documentos buscables
CREATE TABLE articles (
  id          bigserial PRIMARY KEY,
  title       text NOT NULL,
  body        text NOT NULL,
  published   timestamptz DEFAULT now()
);
 
-- Columna tsvector generada (PostgreSQL 12+)
ALTER TABLE articles
  ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body, '')), 'B')
  ) STORED;
 
CREATE INDEX articles_search_idx ON articles USING gin (search_vector);
 
-- Búsqueda
SELECT id, title, ts_rank(search_vector, query) AS rank
FROM articles, plainto_tsquery('english', 'postgresql replication') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;

Cuándo usar esto: Búsqueda de texto completo sobre datos relacionales, tamaño de corpus moderado (millones de filas) y el equipo desea consistencia transaccional sin escritura doble a un motor de búsqueda.

Ejemplo de Trabajo

INSERT INTO articles (title, body) VALUES
  ('Streaming Replication', 'Physical WAL shipping to standbys for HA.'),
  ('Logical Replication', 'Row-level changes for upgrades and fan-out.'),
  ('Full-Text Search', 'Built-in tsvector ranking with GIN indexes.');
 
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title,
       ts_rank_cd(search_vector, query) AS rank,
       ts_headline('english', body, query,
         'MaxFragments=2, MaxWords=20, MinWords=8') AS snippet
FROM articles,
     websearch_to_tsquery('english', 'replication OR "full text"') query
WHERE search_vector @@ query
ORDER BY rank DESC;

Qué demuestra esto:

• Campos ponderados (título A, cuerpo B) influyen en la clasificación.
• websearch_to_tsquery acepta sintaxis de búsqueda fácil de usar.
• ts_headline crea fragmentos de resultados en SQL.
• EXPLAIN confirma el uso del índice GIN en @@.

Profundización

FTS Integrado vs. Búsqueda Externa

Predeterminado de ADR: Comienza con FTS de Postgres cuando el corpus cabe en una instancia, las consultas son de palabras clave + ligeras y difusas, y el tamaño del equipo no puede encargarse de Elasticsearch.

Utiliza OpenSearch cuando: búsqueda sub-100ms a gran escala, analizadores complejos por idioma, fragmentación pesada de autocompletado o equipo SRE de búsqueda dedicado.

Objetos Principales

• tsvector: Bolsa de lemas normalizada con pesos/posiciones opcionales.
• tsquery: Expresión de búsqueda analizada con operadores (&, |, !, <-> frase).
• Operador de coincidencia @@: Prueba booleana de coincidencia; el índice GIN la acelera.
• Diccionarios: Stemming, palabras vacías, sinónimos (ver artículo tsvector).

Configuración de Idioma

SELECT cfgname FROM pg_ts_config;
-- común: english, simple, french
SELECT to_tsvector('simple', 'Running runs RUN');
-- simple: sin stemming, bueno para SKUs y códigos de producto

Usa simple para identificadores; usa english (o específico de la región) para prosa.

Trampas

• Sin índice en to_tsvector() en WHERE: La expresión no almacenada significa escaneo secuencial. Solución: Columna generada almacenada o tsvector mantenido por disparador.
• Consultas OR explotan los resultados: websearch_to_tsquery con muchos términos OR. Solución: Requerir un umbral de clasificación mínimo o valores predeterminados con mucho AND.
• Mayúsculas y diacríticos: FTS normaliza las mayúsculas; los acentos dependen del diccionario. Solución: Extensión unaccent o intercalación ICU para búsqueda sensible a la región.
• Búsqueda de subcadenas: FTS coincide con lemas, no con LIKE '%foo%'. Solución: pg_trgm para subcadenas; combinar en consultas híbridas.
• Vectores obsoletos después de carga masiva: Las columnas generadas se actualizan automáticamente; las basadas en disparadores pueden no hacerlo. Solución: Verificar la ruta de mantenimiento después de ETL.
• Envidia de Elasticsearch: Reconstrucción del índice invertido para cada ajuste de esquema. Solución: Iterar la clasificación en SQL antes de adoptar un segundo sistema.

Alternativas

Preguntas Frecuentes

Relacionado

• tsvector y tsquery - diccionarios y clasificación
• Índices GIN para FTS - construcción y mantenimiento de índices
• Búsqueda Híbrida - combinar con pgvector
• GIN y GiST - descripción general del método de acceso
• Mejores Prácticas de Búsqueda de Texto Completo - lista de verificación operativa

Versiones de la pila: Esta página fue escrita para PostgreSQL 18.4 (estable 18, mantenimiento 17), pgvector 0.8+, PostGIS 3.5+, pgbouncer 1.x, y Patroni 3.x.