🔍📚 ¿Qué es eso de "Full Text Search" en PostgreSQL? ¡Te lo explicamos con flow!

¿Cansado de hacer búsquedas con LIKE y que no encuentres ni el nombre de tu gato? 😿 Pues prepárate, porque llega la joya de la corona en el mundo de las búsquedas: ¡Full Text Search (FTS)! 🎯✨

Full Text Search es ese superpoder que permite encontrar documentos o registros que contienen ciertas palabras o frases, aunque no coincidan exactamente con lo que escribiste 🙃. Es como decirle a PostgreSQL: “🧠 piensa un poco y búscame esto bien, no seas literal…”

A diferencia de las búsquedas básicas (LIKE '%algo%'), FTS tiene en cuenta:

• 🧬 La morfología del lenguaje (sí, PostgreSQL no solo sabe SQL, también sabe idiomas 🤓).
• 🕳️ Las palabras vacías (esas como “el”, “de”, “y”, que no aportan mucho pero siempre están) (stop words).
• 🏆 La relevancia: ordena los resultados según qué tan bien encajan con lo que pediste, como un buen DJ ordenando temazos en una playlist 🎶.

Y lo mejor de todo… ¡esto ya viene incorporado en PostgreSQL! 🐘✨

Incluye:

• Tipos de datos especiales (tsvector y tsquery) 🔠
• Funciones mágicas como to_tsvector, to_tsquery, plainto_tsquery 🪄
• Y hasta índices GIN/GiST para que todo vaya como un cohete 🚀💨

Perfecto para:

• 📝 Blogs
• 🛍️ Tiendas online
• 📂 Documentos
• Y cualquier proyecto donde buscar texto no sea un drama sino una experiencia ✨

🏆 Componentes principales:

• 📌 tsvector: es una estructura que almacena el texto en forma de léxemas (tokens), teniendo en cuenta sus posiciones. Por ejemplo, el texto "PostgreSQL es la mejor base de datos" se transforma en: 'bas':5 'dat':7 'mejor':4 'postgresql':1. Es básicamente un diccionario indexado que contiene todas las palabras en su forma normalizada, excluyendo las llamadas palabras vacías (preposiciones, conjunciones, etc., según el idioma elegido).

• 📌 tsquery: representa la consulta de búsqueda que se comparan contra tsvector.

• 📌 Operador @@: compara un tsvector con un tsquery para ver si hay coincidencia.

• 📌 ts_rank: calcula la relevancia de la coincidencia. Se puede usar para ordenar los resultados (ORDER BY rank DESC).

• 📌 ts_rank_cd: similar a ts_rank, pero también tiene en cuenta la densidad, es decir, cuán cerca están las palabras del término buscado dentro del texto. Es útil cuando importa que las palabras estén próximas entre sí.

Para acelerar FTS(Full Text Search), se pueden usar los índices GIN o GiST.

1️⃣ Configurar Full Text Search:

Creamos una tabla con datos de texto:

CREATE TABLE articles (
    article_id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT,
    content_tsvector TSVECTOR
    publication_date DATE
);

content_tsvector almacenará la versión procesada del contenido para búsquedas rápidas

Datos de ejemplo:

INSERT INTO articles (title, content, publication_date) VALUES
('Introducción a PostgreSQL', 'PostgreSQL es una base de datos relacional de código abierto, ideal para proyectos pequeños y grandes.', '2024-12-15'),
('Guía avanzada de búsqueda de texto completo', 'La búsqueda de texto completo permite encontrar información relevante en grandes volúmenes de texto.', '2025-01-10'),
('PostgreSQL y su integración con otras herramientas', 'Descubre cómo PostgreSQL se integra con herramientas modernas de análisis y visualización.', '2024-11-05'),
('Optimización de consultas SQL', 'Aprende a optimizar tus consultas SQL para mejorar el rendimiento en bases de datos grandes.', '2023-09-01'),
('Toguapo y el mundo del desarrollo backend', 'Toguapo comparte su experiencia con PostgreSQL y cómo mejorar el backend de tus aplicaciones.', '2025-02-18'),
('Tutorial: Full Text Search con PostgreSQL', 'Este tutorial muestra paso a paso cómo implementar búsquedas rápidas y precisas usando FTS.', '2025-01-20'),
('Comparativa entre Elasticsearch y PostgreSQL FTS', 'Aunque PostgreSQL tiene FTS integrado, Elasticsearch ofrece más flexibilidad en algunos escenarios.', '2023-12-01'),
('Cómo hacer búsquedas más precisas en contenido textual', 'Usar ts_rank y ts_headline mejora la experiencia de búsqueda y permite ordenar por relevancia.', '2024-06-11'),
('Gestión documental con PostgreSQL', 'Una solución práctica para gestionar documentos, aplicar búsquedas por texto y clasificar resultados.', '2025-02-01'),
('Novedades en PostgreSQL 16', 'La versión 16 de PostgreSQL incluye mejoras en rendimiento, FTS y más funciones analíticas.', '2024-10-10'),
('Viaje gastronómico por España', 'Exploramos los sabores auténticos del norte de España, desde pintxos hasta mariscos.', '2025-01-22'),
('Cómo cuidar tus plantas en casa', 'Consejos prácticos para mantener tus plantas saludables todo el año.', '2023-08-15'),
('Tendencias de moda 2025', 'Descubre los colores, estilos y tejidos que marcarán tendencia este año.', '2025-01-05'),
('Guía básica de fotografía para principiantes', 'Aprende sobre composición, luz y enfoque en tus primeras fotos.', '2024-05-30');

Rellenamos el tsvector usando la función to_tsvector que convierte texto a tsvector, haciéndolo buscable.

Ejemplo:

UPDATE articles
SET content_tsvector = to_tsvector('spanish', content);

Aquí, 'spanish' indica que se usa la morfología del idioma eh!pañol, procesa el contenido y lo convierte en un tsvector buscable.

Para mejorar el rendimiento de búsqueda, creamos un índice GIN sobre la columna tsvector.

Ejemplo:

CREATE INDEX idx_content_tsvector ON articles USING gin(content_tsvector);

Este índice mejora el rendimiento de las búsquedas, especialmente con grandes volúmenes de datos.

¡Y listo! Ya podemos usar FTS en nuestra tabla 😁

Ejemplos de consultas

✅ Buscar una palabra:

SELECT title
FROM articles
WHERE content_tsvector @@ to_tsquery('spanish', 'postgresql');

Esta consulta busca el término "postgresql" en el campo content_tsvector.

✅ Buscar una frase

SELECT title, content 
FROM articles 
WHERE content_tsvector @@ to_tsquery('spanish', 'postgresql & toguapo');

Este ejemplo busca artículos que contengan tanto "postgresql" como "toguapo".

✅ Resaltar Resultados de Búsqueda:

La función ts_headline resalta los términos buscados en los resultados.

Ejemplo:

SELECT title, ts_headline('spanish', content, to_tsquery('full & text & search')) AS preview
FROM articles
WHERE content_tsvector @@ to_tsquery('spanish', 'full & text & search');

Esto resalta las ocurrencias de "full text search" en una vista previa del contenido.

✅ Clasificar Resultados por Relevancia:

Usa ts_rank o ts_rank_cd para clasificar los resultados según su relevancia con respecto a la consulta.

Ejemplo:

SELECT title, ts_rank(content_tsvector, to_tsquery('postgresql')) AS rank
FROM articles
WHERE content_tsvector @@ to_tsquery('postgresql')
ORDER BY rank DESC;

Esto ordena los artículos según su relevancia con el término "postgresql".

✅ Combinar Full Text Search con Filtros SQL:
Puedes combinar búsqueda de texto completo con otras condiciones SQL.

Ejemplo:

SELECT title
FROM articles
WHERE content_tsvector @@ to_tsquery('postgresql')
AND publication_date > '2025-01-01';

Esto busca artículos que contengan "database" y que hayan sido publicados después del 1 de enero de 2025.

✅ Búsqueda de Frases con plainto_tsquery:
Si deseas que un término de búsqueda sea tratado como una frase completa, plainto_tsquery lo convierte en un tsquery.

Ejemplo:

SELECT title
FROM articles
WHERE content_tsvector @@ plainto_tsquery('spanish', 'full text search');

Esta consulta busca la frase exacta "full text search".

✅ Agregar Triggers para Actualización Automática:

Para mantener content_tsvector actualizado al cambiar el contenido, se puede usar un trigger.

CREATE FUNCTION update_tsvector() RETURNS TRIGGER AS $$
BEGIN
    NEW.content_tsvector := to_tsvector('spanish', NEW.content);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tsvectorupdate 
BEFORE INSERT OR UPDATE ON articles 
FOR EACH ROW EXECUTE FUNCTION update_tsvector();

Este trigger actualiza automáticamente content_tsvector cuando el contenido cambia.