Búsqueda de texto completo

El buscador de una tienda tiene que encontrar "Audífonos inalámbricos" cuando el usuario escribe "audifono inalambrico", ordenar primero los resultados más relevantes y tolerar errores de dedo. Con LIKE '%texto%' eso es lento e impreciso. PostgreSQL trae un motor de búsqueda de texto completo integrado, con soporte para español, y la extensión pg_trgm para búsquedas difusas. En esta lección construirás un buscador de productos profesional sin servicios externos.

Concepto

LIKE compara caracteres. La búsqueda de texto completo compara palabras con significado:

Búsqueda: "audifonos inalambricos"	LIKE '%...%'	Texto completo
Encuentra "Audífonos Bluetooth inalámbricos"	No (acentos, orden)	Sí
Ignora palabras vacías ("de", "para", "los")	No	Sí
Entiende plurales y conjugaciones ("audífono" ≈ "audífonos")	No	Sí (raíces)
Ordena por relevancia	No	Sí
Usa índice	No (con % inicial)	Sí (GIN)

Funciona en dos pasos, como el índice temático de un libro:

1. Documento → tsvector (lista de raíces de palabras con posiciones)
   "Audífonos Bluetooth inalámbricos con 30 horas de batería"
        ↓ to_tsvector('spanish', ...)
   'audifon':1 'bateri':8 'bluetooth':2 'hor':6 'inalambr':3 '30':5

2. Búsqueda → tsquery (raíces combinadas con operadores)
   "audífono inalámbrico"
        ↓ websearch_to_tsquery('spanish', ...)
   'audifon' & 'inalambr'

3. ¿Coinciden?   tsvector @@ tsquery  →  true

(Las raíces exactas dependen del diccionario; los acentos se quitan con la extensión unaccent, que verás más abajo.)

Primeros pasos

-- ¿Cómo "ve" PostgreSQL un texto en español?
SELECT to_tsvector('spanish', 'Los audífonos inalámbricos tienen una batería increíble');
-- 'audifon':2 'bateri':6 'increibl':7 'inalambr':3 'tien':4
-- ("los" y "una" se descartan: son palabras vacías)

-- Una búsqueda
SELECT to_tsvector('spanish', 'Teclado mecánico para oficina') @@ to_tsquery('spanish', 'teclados');
-- true  ("teclados" y "teclado" comparten raíz)

Los acentos

El diccionario spanish reduce palabras a su raíz, pero no siempre elimina acentos: según la versión, "batería" puede quedar como 'bateri' o 'baterí', y "bateria" (sin acento, como escriben muchos usuarios) podría no coincidir. La solución robusta es combinar el diccionario con la extensión unaccent (abajo).

Construir consultas: tsquery

Función	Entrada del usuario	Resultado
to_tsquery	Sintaxis estricta: 'teclado & (gamer | oficina)'	Error si la sintaxis es inválida
plainto_tsquery	Texto normal: 'teclado gamer'	'teclad' & 'gam' (todas las palabras)
phraseto_tsquery	'teclado mecánico'	Palabras consecutivas (frase)
websearch_to_tsquery	Sintaxis tipo Google	Recomendada para buscadores

websearch_to_tsquery entiende lo que un usuario escribiría en Google y nunca lanza errores de sintaxis:

El usuario escribe	Significa
teclado gamer	Ambas palabras
teclado or monitor	Cualquiera de las dos
"teclado mecánico"	La frase exacta
audífonos -cable	Audífonos pero sin la palabra cable

SELECT websearch_to_tsquery('spanish', 'audífonos -cable "batería larga"');
-- 'audifon' & !'cabl' & 'bateri' <-> 'larg'

Preparar la tabla de productos

CREATE EXTENSION IF NOT EXISTS unaccent;

CREATE TABLE productos (
  id           INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  nombre       TEXT NOT NULL,
  descripcion  TEXT NOT NULL DEFAULT '',
  categoria    TEXT NOT NULL,
  precio       NUMERIC(10,2) NOT NULL
);

INSERT INTO productos (nombre, descripcion, categoria, precio) VALUES
  ('Audífonos Bluetooth', 'Audífonos inalámbricos con cancelación de ruido y 30 horas de batería', 'Electrónica', 899.00),
  ('Audífonos con cable', 'Audífonos alámbricos ligeros con micrófono para llamadas', 'Electrónica', 249.00),
  ('Teclado mecánico', 'Teclado mecánico gamer con switches rojos e iluminación RGB', 'Electrónica', 1250.00),
  ('Monitor 24 pulgadas', 'Monitor Full HD de 144 Hz ideal para gamers y oficina', 'Electrónica', 3499.00),
  ('Libro Aprende SQL', 'Libro práctico para aprender bases de datos desde cero', 'Libros', 349.00),
  ('Lámpara de escritorio', 'Lámpara LED con brazo flexible, ideal para leer y trabajar', 'Hogar', 459.00),
  ('Bocina portátil', 'Bocina inalámbrica resistente al agua con batería de 12 horas', 'Electrónica', 749.00);

Configuración de búsqueda en español sin acentos

Creamos una configuración propia que primero quita acentos y luego aplica el diccionario español:

CREATE TEXT SEARCH CONFIGURATION es_sin_acentos (COPY = spanish);

ALTER TEXT SEARCH CONFIGURATION es_sin_acentos
  ALTER MAPPING FOR hword, hword_part, word
  WITH unaccent, spanish_stem;

-- Prueba
SELECT to_tsvector('es_sin_acentos', 'Batería inalámbrica') @@
       websearch_to_tsquery('es_sin_acentos', 'bateria inalambrica');
-- true

Columna tsvector generada con pesos

Calcular to_tsvector en cada búsqueda es costoso. Lo guardamos en una columna generada y le ponemos pesos: una coincidencia en el nombre vale más que en la descripción.

Peso	Uso típico
A	Título / nombre
B	Categoría, etiquetas
C	Descripción
D	Texto secundario

ALTER TABLE productos ADD COLUMN busqueda tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('es_sin_acentos', coalesce(nombre, '')), 'A') ||
    setweight(to_tsvector('es_sin_acentos', coalesce(categoria, '')), 'B') ||
    setweight(to_tsvector('es_sin_acentos', coalesce(descripcion, '')), 'C')
  ) STORED;

-- Índice GIN sobre la columna
CREATE INDEX idx_productos_busqueda ON productos USING GIN (busqueda);

¿Por qué funciona en una columna generada?

Las columnas generadas exigen expresiones IMMUTABLE. to_tsvector con el nombre de la configuración escrito literalmente ('es_sin_acentos') lo es. Si en tu versión unaccent da problemas de inmutabilidad, la alternativa es mantener la columna con un trigger.

Buscar y ordenar por relevancia

SELECT
  nombre,
  precio,
  ts_rank(busqueda, consulta) AS relevancia
FROM productos,
     websearch_to_tsquery('es_sin_acentos', 'audifonos inalambricos') AS consulta
WHERE busqueda @@ consulta
ORDER BY relevancia DESC;

nombre	precio	relevancia
Audífonos Bluetooth	899.00	0.4

Solo aparecen los audífonos inalámbricos: los "Audífonos con cable" no contienen "inalámbricos" (¡son alámbricos!). Con or:

SELECT nombre, round(ts_rank(busqueda, q)::numeric, 3) AS relevancia
FROM productos, websearch_to_tsquery('es_sin_acentos', 'audifonos or bocina') AS q
WHERE busqueda @@ q
ORDER BY relevancia DESC;

nombre	relevancia
Audífonos Bluetooth	0.669
Audífonos con cable	0.669
Bocina portátil	0.608

(Los valores exactos de relevancia varían; lo importante es el orden relativo.)

Función	Diferencia
ts_rank	Frecuencia de las palabras encontradas
ts_rank_cd	Además premia que las palabras estén cerca entre sí

Resaltar coincidencias

SELECT
  nombre,
  ts_headline('es_sin_acentos', descripcion, q,
              'StartSel=<mark>, StopSel=</mark>, MaxWords=15, MinWords=5') AS fragmento
FROM productos, websearch_to_tsquery('es_sin_acentos', 'bateria') AS q
WHERE busqueda @@ q;

nombre	fragmento
Audífonos Bluetooth	...cancelación de ruido y 30 horas de <mark>batería</mark>
Bocina portátil	...resistente al agua con <mark>batería</mark> de 12 horas

Tu frontend muestra el fragmento con la palabra resaltada (¡escapando el resto del HTML para evitar XSS!).

Búsqueda difusa con pg_trgm

La búsqueda de texto completo trabaja con palabras completas. No ayuda con errores de dedo ("audifnos", "tecaldo") ni con búsquedas por fragmentos ("lamp" mientras el usuario escribe). Para eso está pg_trgm, que divide el texto en trigramas (grupos de 3 letras):

"teclado" → "  t", " te", "tec", "ecl", "cla", "lad", "ado", "do "
"tecaldo" → "  t", " te", "tec", "eca", "cal", "ald", "ldo", "do "
             comparten 4 de 8 → similitud ≈ 0.33

CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Similitud entre 0 y 1
SELECT similarity('teclado', 'tecaldo');    -- 0.33
SELECT similarity('audifonos', 'audifnos'); -- 0.58

-- Índice GIN de trigramas (acelera LIKE/ILIKE '%...%', similarity y %)
CREATE INDEX idx_productos_nombre_trgm ON productos USING GIN (nombre gin_trgm_ops);

-- 1. ¡Ahora ILIKE con % inicial usa índice! (ideal para autocompletar)
SELECT nombre FROM productos WHERE nombre ILIKE '%lamp%';

-- 2. Tolerancia a errores: operador % (similitud mayor al umbral, 0.3 por defecto)
SELECT nombre, round(similarity(nombre, 'audifnos blutooth')::numeric, 2) AS sim
FROM productos
WHERE nombre % 'audifnos blutooth'
ORDER BY sim DESC;

nombre	sim
Audífonos Bluetooth	0.46

-- Cambiar el umbral para la sesión
SET pg_trgm.similarity_threshold = 0.2;

-- word_similarity: busca el término dentro de un texto más largo (mejor para frases)
SELECT nombre FROM productos WHERE 'tecldo' <% nombre;

Para que pg_trgm ignore acentos, compara textos pasados por unaccent (requiere un índice de expresión con una función unaccent envuelta como IMMUTABLE) o guarda una columna normalizada.

El buscador completo

Combinamos lo mejor de ambos: texto completo para relevancia y trigramas como respaldo cuando no hay resultados (errores de dedo).

CREATE OR REPLACE FUNCTION buscar_productos(p_texto TEXT, p_limite INT DEFAULT 20)
RETURNS TABLE (id INT, nombre TEXT, precio NUMERIC, puntaje REAL)
LANGUAGE sql
STABLE
AS $$
  WITH q AS (
    SELECT websearch_to_tsquery('es_sin_acentos', p_texto) AS consulta
  ),
  texto_completo AS (
    SELECT p.id, p.nombre, p.precio, ts_rank_cd(p.busqueda, q.consulta) AS puntaje
    FROM productos p, q
    WHERE p.busqueda @@ q.consulta
  ),
  difusa AS (
    SELECT p.id, p.nombre, p.precio, similarity(p.nombre, p_texto) AS puntaje
    FROM productos p
    WHERE p.nombre % p_texto
      AND NOT EXISTS (SELECT 1 FROM texto_completo)   -- solo si no hubo resultados exactos
  )
  SELECT * FROM texto_completo
  UNION ALL
  SELECT * FROM difusa
  ORDER BY puntaje DESC
  LIMIT p_limite;
$$;

SELECT * FROM buscar_productos('bocina inalambrica');   -- texto completo
SELECT * FROM buscar_productos('tecaldo mecanico');     -- difusa: Teclado mecánico

Desde Node sería tan simple como:

const { rows } = await pool.query('SELECT * FROM buscar_productos($1, $2)', [textoUsuario, 20]);

¿Cuándo usar algo externo?

La búsqueda de PostgreSQL es suficiente para la gran mayoría de tiendas, blogs y sistemas internos (cientos de miles o algunos millones de documentos). Considera Meilisearch, Typesense, Elasticsearch/OpenSearch o Algolia si necesitas:

• Facetas complejas y filtros instantáneos al teclear sobre millones de productos.
• Sinónimos y tolerancia a errores avanzados sin configurar nada.
• Búsqueda en muchos idiomas a la vez con analíticas de búsqueda.

Y para búsqueda semántica (por significado, con IA), PostgreSQL tiene la extensión pgvector.

Ejercicios

1. Muestra el tsvector en español del texto "Las lámparas LED ahorran energía".
2. Busca los productos que contengan "gamer" pero no "monitor" usando websearch_to_tsquery.
3. Agrega una columna etiquetas TEXT[] a productos e inclúyela con peso B en la búsqueda (pista: array_to_string).
4. Crea la consulta de autocompletado: mientras el usuario escribe "lib", devuelve hasta 5 nombres que contengan ese texto, usando el índice de trigramas.

Ver solución

-- 1
SELECT to_tsvector('spanish', 'Las lámparas LED ahorran energía');
-- 'ahorr':4 'energ':5 'lamp':2 'led':3   (las raíces exactas pueden variar)

-- 2
SELECT nombre
FROM productos, websearch_to_tsquery('es_sin_acentos', 'gamer -monitor') AS q
WHERE busqueda @@ q;
-- Teclado mecánico

-- 3 (hay que recrear la columna generada)
ALTER TABLE productos ADD COLUMN etiquetas TEXT[] NOT NULL DEFAULT '{}';
ALTER TABLE productos DROP COLUMN busqueda;
ALTER TABLE productos ADD COLUMN busqueda tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('es_sin_acentos', coalesce(nombre, '')), 'A') ||
    setweight(to_tsvector('es_sin_acentos', array_to_string(etiquetas, ' ')), 'B') ||
    setweight(to_tsvector('es_sin_acentos', coalesce(categoria, '')), 'B') ||
    setweight(to_tsvector('es_sin_acentos', coalesce(descripcion, '')), 'C')
  ) STORED;
CREATE INDEX idx_productos_busqueda ON productos USING GIN (busqueda);

-- 4
SELECT nombre
FROM productos
WHERE nombre ILIKE '%' || 'lib' || '%'
ORDER BY similarity(nombre, 'lib') DESC
LIMIT 5;
-- Libro Aprende SQL

Resumen

• La búsqueda de texto completo convierte documentos en tsvector y búsquedas en tsquery, y los compara con @@.
• Usa la configuración spanish combinada con unaccent para ignorar acentos.
• websearch_to_tsquery acepta la sintaxis que escriben los usuarios (comillas, or, -).
• Guarda el tsvector en una columna generada con pesos (A nombre, C descripción) e indexa con GIN.
• Ordena con ts_rank / ts_rank_cd y resalta con ts_headline.
• pg_trgm cubre errores de dedo, autocompletado e ILIKE '%...%' indexado.