Full-text search en PostgreSQL y MySQL

Por qué LIKE no basta

SELECT * FROM articulos WHERE titulo LIKE '%bases de datos%';

Tres problemas:

1. Lento: el LIKE '%...%' no puede usar un índice B-tree (el patrón abierto por la izquierda lo anula). Seq Scan sobre millones de filas.
2. No entiende morfología: "base" no encuentra "bases", "programar" no encuentra "programación", "SQL" no encuentra "sql".
3. No rankea: todas las coincidencias salen iguales, no las más relevantes primero.

Full-text search resuelve los tres con tokenización, lematización (reducción a raíces) y índices GIN/GiST.

PostgreSQL: tsvector y tsquery

Convertir texto a tsvector

Un tsvector es el texto procesado: tokenizado, con stopwords eliminadas y palabras lematizadas a su raíz.

SELECT to_tsvector('spanish', 'Las bases de datos modernas soportan búsquedas rápidas');
-- 'bas':2 'busqued':6 'dat':4 'modern':5 'rap':7

Observa:

• "Las", "de" se eliminaron (stopwords).
• "bases" → "bas", "búsquedas" → "busqued", "rápidas" → "rap" (stems del español).
• Las tildes se normalizan.
• Los números representan la posición de cada token (útil para ranking).

Construir la query

SELECT to_tsquery('spanish', 'bases & datos');
-- 'bas' & 'dat'

SELECT plainto_tsquery('spanish', 'bases de datos');
-- 'bas' & 'dat'   (automaticamente en AND)

SELECT websearch_to_tsquery('spanish', '"bases de datos" -lentas');
-- 'bas' <-> 'dat' & !'lent'   (estilo Google: frase exacta y exclusion)

Matching con @@

SELECT * FROM articulos
WHERE to_tsvector('spanish', titulo || ' ' || contenido) @@ plainto_tsquery('spanish', 'bases de datos');

Sin índice, esto es lento. Hay que precalcular y indexar.

Indexar con GIN

Opción 1: columna tsvector materializada

ALTER TABLE articulos ADD COLUMN busqueda tsvector;

-- Popular con trigger
CREATE OR REPLACE FUNCTION actualizar_busqueda() RETURNS trigger AS $$
BEGIN
    NEW.busqueda := to_tsvector('spanish', coalesce(NEW.titulo, '') || ' ' || coalesce(NEW.contenido, ''));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_busqueda
BEFORE INSERT OR UPDATE ON articulos
FOR EACH ROW EXECUTE FUNCTION actualizar_busqueda();

CREATE INDEX idx_articulos_busqueda ON articulos USING GIN(busqueda);

-- Consultas rapidas ahora:
SELECT id, titulo FROM articulos
WHERE busqueda @@ plainto_tsquery('spanish', 'bases de datos');

Opción 2: columna generada (PostgreSQL 12+)

Más limpio, sin trigger:

ALTER TABLE articulos ADD COLUMN busqueda tsvector
    GENERATED ALWAYS AS (
        to_tsvector('spanish', coalesce(titulo, '') || ' ' || coalesce(contenido, ''))
    ) STORED;

CREATE INDEX idx_articulos_busqueda ON articulos USING GIN(busqueda);

Opción 3: índice funcional directo

CREATE INDEX idx_articulos_fts
ON articulos USING GIN(to_tsvector('spanish', titulo || ' ' || contenido));

-- Atencion: la query debe usar la misma expresion exacta
SELECT * FROM articulos
WHERE to_tsvector('spanish', titulo || ' ' || contenido) @@ plainto_tsquery('spanish', 'SQL');

Ranking por relevancia

ts_rank o ts_rank_cd calculan un score. Para ordenar resultados:

SELECT id, titulo,
       ts_rank(busqueda, query) AS rank
FROM articulos,
     plainto_tsquery('spanish', 'bases de datos modernas') AS query
WHERE busqueda @@ query
ORDER BY rank DESC
LIMIT 10;

ts_rank_cd tiene en cuenta la proximidad de los términos (cover density); ts_rank solo la frecuencia.

Ponderar títulos frente a cuerpo

setweight permite dar más peso a ciertos campos:

busqueda := setweight(to_tsvector('spanish', coalesce(titulo, '')), 'A') ||
            setweight(to_tsvector('spanish', coalesce(subtitulo, '')), 'B') ||
            setweight(to_tsvector('spanish', coalesce(contenido, '')), 'D');

-- Ranking con pesos por defecto: A=1, B=0.4, C=0.2, D=0.1
SELECT ts_rank(busqueda, query, 0) FROM ...

Un match en el título puntúa 10x más que en el cuerpo.

Resaltar coincidencias con ts_headline

Devuelve un extracto con las coincidencias resaltadas:

SELECT titulo,
       ts_headline('spanish', contenido, query,
                   'StartSel=<b>, StopSel=</b>, MaxWords=30, MinWords=15') AS snippet
FROM articulos,
     plainto_tsquery('spanish', 'bases de datos') AS query
WHERE busqueda @@ query
LIMIT 5;

Útil para mostrar resultados en una UI estilo Google.

Query avanzadas

Operadores booleanos

-- bases AND datos
to_tsquery('spanish', 'bases & datos')

-- bases OR datos
to_tsquery('spanish', 'bases | datos')

-- bases AND NOT relacional
to_tsquery('spanish', 'bases & !relacional')

-- Frase exacta (proximidad)
to_tsquery('spanish', 'bases <-> datos')  -- "bases" inmediatamente antes de "datos"

-- N palabras entre
to_tsquery('spanish', 'bases <3> datos')  -- hasta 3 palabras de distancia

websearch_to_tsquery

Soporta sintaxis Google-like (comillas para frase, - para exclusión, OR):

SELECT * FROM articulos
WHERE busqueda @@ websearch_to_tsquery('spanish', '"SQL avanzado" -oracle');

MySQL: FULLTEXT index y MATCH AGAINST

MySQL tiene su propio sistema de FTS con sintaxis distinta:

-- Crear indice FULLTEXT (MySQL 5.6+ con InnoDB)
CREATE TABLE articulos (
    id INT PRIMARY KEY AUTO_INCREMENT,
    titulo VARCHAR(500),
    contenido TEXT,
    FULLTEXT KEY idx_fts (titulo, contenido) WITH PARSER ngram
);

-- Modo NATURAL LANGUAGE (por defecto)
SELECT id, titulo,
       MATCH(titulo, contenido) AGAINST('bases de datos') AS score
FROM articulos
WHERE MATCH(titulo, contenido) AGAINST('bases de datos')
ORDER BY score DESC;

-- Modo BOOLEAN (operadores +required, -excluded, *prefix)
SELECT * FROM articulos
WHERE MATCH(titulo, contenido)
      AGAINST('+SQL +avanzado -oracle' IN BOOLEAN MODE);

-- Modo QUERY EXPANSION (busqueda iterativa)
SELECT * FROM articulos
WHERE MATCH(titulo, contenido)
      AGAINST('bases datos' WITH QUERY EXPANSION);

Diferencias clave con PostgreSQL:

| Aspecto | PostgreSQL | MySQL | |---------|-----------|-------| | Tipo indexado | tsvector | VARCHAR/TEXT | | Operador | @@ | MATCH ... AGAINST | | Lematización | to_tsvector con config | Depende del parser (ngram para CJK) | | Ranking | ts_rank, ts_rank_cd | Score implícito del MATCH | | Multi-idioma | Configs por idioma (spanish, english, ...) | Depende del idioma del servidor | | Stopwords | Configurable por dictionary | Lista interna + innodb_ft_server_stopword_table |

Diccionarios y configuración

Cada lenguaje tiene reglas propias de stemming y stopwords. Lista los disponibles:

SELECT cfgname FROM pg_ts_config;
-- spanish, english, french, german, italian, portuguese, russian, ...

Personalizar: puedes crear diccionarios propios que ignoren cierto vocabulario, sinónimos, etc. Para la mayoría de casos, spanish y english bastan.

Caso real: buscador de artículos del blog

-- Tabla completa
CREATE TABLE blog_posts (
    id SERIAL PRIMARY KEY,
    titulo TEXT NOT NULL,
    subtitulo TEXT,
    contenido TEXT NOT NULL,
    tags TEXT[],
    publicado_en TIMESTAMPTZ,
    busqueda tsvector
);

-- Trigger para mantener busqueda actualizada con pesos
CREATE OR REPLACE FUNCTION actualizar_blog_busqueda() RETURNS trigger AS $$
BEGIN
    NEW.busqueda :=
        setweight(to_tsvector('spanish', coalesce(NEW.titulo, '')), 'A') ||
        setweight(to_tsvector('spanish', coalesce(NEW.subtitulo, '')), 'B') ||
        setweight(to_tsvector('spanish', array_to_string(coalesce(NEW.tags, '{}'), ' ')), 'C') ||
        setweight(to_tsvector('spanish', coalesce(NEW.contenido, '')), 'D');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_blog_busqueda
BEFORE INSERT OR UPDATE ON blog_posts
FOR EACH ROW EXECUTE FUNCTION actualizar_blog_busqueda();

CREATE INDEX idx_blog_busqueda ON blog_posts USING GIN(busqueda);

-- Funcion de busqueda para la API
CREATE OR REPLACE FUNCTION buscar_posts(q TEXT, pagina INT DEFAULT 0, tam INT DEFAULT 10)
RETURNS TABLE(id INT, titulo TEXT, snippet TEXT, rank REAL) AS $$
    SELECT p.id, p.titulo,
           ts_headline('spanish', p.contenido, query, 'MaxWords=30, MinWords=15') AS snippet,
           ts_rank_cd(p.busqueda, query) AS rank
    FROM blog_posts p,
         websearch_to_tsquery('spanish', q) AS query
    WHERE p.busqueda @@ query
    ORDER BY rank DESC
    LIMIT tam OFFSET pagina * tam;
$$ LANGUAGE SQL STABLE;

-- Llamada desde la app
SELECT * FROM buscar_posts('"SQL avanzado" -oracle', 0, 10);

Este patrón cubre el 90% de buscadores internos. Para casos más complejos (sinónimos, typo-tolerance, multi-idioma a la vez), considera Elasticsearch o MeiliSearch, pero para muchos productos, PostgreSQL FTS es más que suficiente y ahorra un servicio extra.