Búsquedas de cadenas en PostgreSQL

Búsquedas de cadenas en PostgreSQL

Estos días que he estado escribiendo activamente y metiendo mucho código a Deeditt, he decidido investigar un poco sobre las diferentes técnicas de búsqueda en PostgreSQL y aunque finalmente he llegado a utilizar una mezcla de los posibles métodos, es que decidí crear un artículo donde se enumeran éstas técnicas, sus pros y constras, al igual que algunos ejemplos. Estoy seguro ya seas un principiante o un desarrollador experimentado, esta guía bastante amplia tiene algo para ti.

Prerrequisitos

Antes de sumergirnos en los detalles, asegúrate de tener instalado PostgreSQL y de haber configurado una base de datos para las pruebas. Es útil tener un conocimiento básico de los conceptos de SQL.

Configuración de la base de datos de muestra

Para ilustrar las diversas técnicas de búsqueda, utilizaremos una base de datos de muestra simple con una única tabla llamada products. Esta tabla contiene una columna llamada product_name con varios nombres de productos, también se definen otros campos que utilizaremos en los ejemplos, pero product_name es el principal a utilizar.

Nuestro objetivo es realizar diferentes tipos de búsquedas de cadenas en estos datos.

-- Crear una tabla de muestra con nombres de productos, descripciones y datos en JSON
CREATE TABLE products (
    id serial primary key,
    product_name text,
    description text,
    product_data json
);

-- Insertar datos de muestra
INSERT INTO products (product_name, description, product_data) VALUES
    ('Laptop Acer Aspire E15', 'Potente portátil con pantalla de alta resolución', '{"brand": "Acer", "price": 799.99}'),
    ('Apple iPhone 12 Pro', 'Último smartphone de Apple con características avanzadas de cámara', '{"brand": "Apple", "price": 999.99}'),
    ('Samsung Galaxy Tab S7', 'Tableta para productividad y entretenimiento', '{"brand": "Samsung", "price": 599.99}'),
    ('Sony Playstation 5', 'Consola de juegos de próxima generación con gráficos inmersivos', '{"brand": "Sony", "price": 499.99}'),
    ('Dell XPS 13 Laptop', 'Portátil delgado y ligero para profesionales', '{"brand": "Dell", "price": 899.99}'),
    ('Smartphone Google Pixel 5', 'Smartphone de la serie Google Pixel con cámara excepcional', '{"brand": "Google", "price": 699.99}'),
    ('HP Pavilion Gaming Laptop', 'Portátil para juegos con gráficos de alto rendimiento', '{"brand": "HP", "price": 1099.99}'),
    ('Microsoft Surface Laptop 4', 'Portátil elegante y versátil para trabajo y ocio', '{"brand": "Microsoft", "price": 1199.99}'),
    ('Alienware x16 Gaming Laptop', 'Portátil para juegos premium para jugadores intransigentes', '{"brand": "Alienware", "price": 1499.99}'),
    ('Samsung 4K QLED TV', 'Televisor Ultra HD con impresionante pantalla QLED', '{"brand": "Samsung", "price": 1299.99}'),
    ('Sony Wireless Headphones', 'Auriculares inalámbricos para entusiastas de la música', '{"brand": "Sony", "price": 199.99}');

Ahora que tenemos nuestros datos de muestra, profundicemos en las diversas técnicas de búsqueda.

1. Coincidencia exacta (=)

Para realizar una búsqueda de coincidencia exacta, utiliza el operador de igualdad (=).

  • Pros: Simple y preciso.
  • Contras: Sensible a mayúsculas y minúsculas.
SELECT * FROM products WHERE product_name = 'Apple iPhone 12 Pro';

2. Coincidencia parcial (LIKE)

Para encontrar registros que contengan una subcadena específica, usa el operador LIKE con comodines (% para cualquier número de caracteres, _ para un solo carácter).

  • Pros: Admite coincidencias con comodines.
  • Contras: Distingue entre mayúsculas y minúsculas.

Supongamos que deseas encontrar productos que contengan palabras clave específicas:

-- Encontrar laptops (sin distinción de mayúsculas y minúsculas)
SELECT * FROM products WHERE product_name ILIKE '%laptop%';

-- Encontrar productos que comiencen con "Dell"
SELECT * FROM products WHERE product_name LIKE 'Dell%';

--- Coincidiendo con múltiples subcadenas a la vez en este orden
SELECT * FROM products WHERE product_name LIKE '%gaming%laptop%';

Para aclarar y proporcionar un ejemplo más completo, aquí hay una adición a la sección "Coincidencia Parcial (LIKE)" 

-- Encontrar productos con una coincidencia de comodín de un solo carácter
SELECT * FROM products WHERE product_name LIKE 'Dell_';

En este ejemplo, la consulta LIKE 'Dell_' coincidiría con nombres de productos como "Dell X" o "Dell Y", donde hay un solo carácter después de "Dell."

3. Búsqueda sin distinción de mayúsculas y minúsculas (ILIKE)

Para realizar una búsqueda sin distinción de mayúsculas y minúsculas, usa el operador ILIKE.

  • Pros: No distingue entre mayúsculas y minúsculas.
  • Contras: Limitado a patrones simples.

Cuando no se desea sensibilidad a las mayúsculas:

-- Encontrar laptops (sin distinción de mayúsculas y minúsculas)
SELECT * FROM products WHERE product_name ILIKE '%laptop%';

4. Expresiones regulares (~ y ~*)

Las expresiones regulares, a menudo abreviadas como "regex" o "regexp", son herramientas poderosas para el emparejamiento de patrones dentro del texto. Te permiten definir patrones de búsqueda complejos usando una combinación de caracteres y metacaracteres. Si eres nuevo en las expresiones regulares, puedes aprender más sobre ellas en la documentación de PostgreSQL.

PostgreSQL soporta expresiones regulares para emparejamiento de patrones avanzados. Usa el operador ~ para búsquedas regex sensibles a mayúsculas y minúsculas y ~* para búsquedas insensibles a mayúsculas y minúsculas.

  • Pros: Emparejamiento de patrones poderoso.
  • Contras: Sintaxis compleja.

Para escenarios de emparejamiento de patrones complejos:

-- Encontrar productos que contienen "iPhone" o "iPad"
SELECT * FROM products WHERE product_name ~ 'iPhone|iPad';

-- Encontrar productos que comienzan con "samsung" (insensible a mayúsculas y minúsculas)
SELECT * FROM products WHERE product_name ~* '^samsung';

Las expresiones regulares pueden ser usadas para una amplia gama de patrones, como emparejar fechas en el formato YYYY-MM-DD con el regex \d{4}-\d{2}-\d{2}.

5. Búsqueda por límites de palabras

Para emparejar palabras completas dentro de una columna de texto, usa el escape de límite de palabra \y.

  • Pros: Empareja palabras completas.
  • Contras: Limitado a simples límites de palabras.

Para encontrar palabras completas en el texto:

-- Encontrar productos con la palabra "Laptop"
SELECT * FROM products WHERE product_name ~* '\yLaptop\y';

El escape de límite de palabra \y también puede ser usado para emparejar el comienzo o el final de una cadena. Por ejemplo, el regex ^\ylaptop\y$ solamente emparejaría la palabra "laptop" si es la única palabra en la cadena.

6. Similitud de Trigramas (pg_trgm)

La extensión pg_trgm permite la coincidencia difusa de cadenas basada en trigramas, útil para encontrar palabras similares.

  • Pros: Coincidencia difusa para palabras similares.
  • Contras: Limitado a similitudes de trigramas.

Al buscar palabras que suenan de manera similar:

-- Encuentra productos similares a "Apple iPhone 12"
SELECT * FROM products WHERE similarity(product_name, 'Apple iPhone 12') > 0.3;

Además de este ejemplo, puedes usar pg_trgm para encontrar productos similares a otros nombres ajustando el umbral de similitud.

7. Búsqueda de Full-Text

Para búsquedas avanzadas de texto, PostgreSQL ofrece capacidades de búsqueda de Full-text. Primero, crea una configuración de búsqueda de texto y un índice de texto completo.

  • Pros: Búsqueda de texto avanzada con clasificación.
  • Contras: Requiere configuración; puede no ser adecuado para textos muy cortos.

Para una búsqueda de texto exhaustiva:

-- Crea una configuración de búsqueda de texto
CREATE TEXT SEARCH CONFIGURATION my_search (COPY = simple);
ALTER TEXT SEARCH CONFIGURATION my_search
    ALTER MAPPING FOR hword, hword_part, word
    WITH spanish_stem;

-- Crea un índice de texto completo
CREATE INDEX products_fulltext_idx
ON products USING GIN(to_tsvector('my_search', product_name));

-- Encuentra productos que contengan "laptop" o "notebook"
SELECT * FROM products
WHERE to_tsvector('spanish', product_name) @@ to_tsquery('spanish', 'Samsung | Galaxy');

Este tipo de búsqueda no solo recupera registros coincidentes, sino que también los clasifica según su relevancia. Esta característica es valiosa para aplicaciones como motores de búsqueda y sistemas de recomendación.

8. Uso de Índices para búsquedas más rápidas

Se utiliza cuando tienes un conjunto de datos grande y buscas frecuentemente valores específicos en una columna en particular.

  • Pros: Mejora significativamente el rendimiento de la búsqueda.
  • Contras: Requiere espacio de almacenamiento adicional.

Para búsquedas más rápidas, crea un índice en la columna de búsqueda:

-- Crea un índice en la columna product_name
CREATE INDEX products_product_name_idx ON products (product_name);

-- Mejor rendimiento para coincidencia parcial
SELECT * FROM products WHERE product_name LIKE '%Laptop%';

9. Búsqueda en múltiples columnas usando tsvector

Comúnmente utilizado cuando necesitas buscar de manera eficiente en varias columnas, especialmente para búsquedas de texto completo.

  • Pros: Búsquedas eficientes en múltiples columnas.
  • Contras: Requiere configuración adicional.

Concatena varias columnas en un solo tsvector para búsquedas avanzadas:

-- Combina product_name y description para la búsqueda
SELECT *
FROM products
WHERE to_tsvector('english', product_name || ' ' || description)
    @@ to_tsquery('english', 'laptop | powerful');

10. Combinando múltiples técnicas de búsqueda

  • Pros: Mayor flexibilidad en la búsqueda.
  • Contras: Consultas complejas.

Combina diferentes técnicas de búsqueda para escenarios del mundo real:

-- Encuentra productos que sean coincidencias exactas o tengan un nombre similar
SELECT * FROM products
WHERE product_name = 'Laptop Acer Aspire E15'
    OR similarity(product_name, 'Laptop Acer Aspire E15') > 0.3;

11. Búsqueda en datos JSON usando consultas jsonpath

En caso de que hayas almacenado datos estructurados en formato JSON y necesites consultar atributos específicos dentro de los datos JSON.

  • Pros: Búsqueda eficiente en datos JSON.
  • Contras: Limitado a tipos de datos JSON.
-- Encuentra productos con atributos JSON específicos
SELECT * FROM products
WHERE product_data->>'brand' = 'Samsung';

12. Coincidencia Fuzzy String con fuzzystrmatch

Utiliza esto cuando desees adaptarte a errores tipográficos o coincidencias aproximadas en tu búsqueda.

  • Pros: Coincidencia más difusa para búsquedas tolerantes a errores tipográficos.
  • Contras: Limitado a funciones específicas.

Utiliza las funciones de fuzzystrmatch para búsquedas difusas:

-- Encuentra productos con nombres similares
SELECT * FROM products
WHERE soundex(product_name) = soundex('Dell XPS 13');

Consideraciones de Rendimiento

Al tratar con grandes conjuntos de datos, considera técnicas de optimización de rendimiento, tales como:

  1. Entiende tus datos: Antes de sumergirte en las búsquedas de cadenas, comprende a fondo tus datos. Conocer la estructura, contenido y características de los datos te guiará en la elección del método de búsqueda más adecuado.

  2. Considera el rendimiento: Aunque PostgreSQL puede manejar grandes conjuntos de datos, la optimización del rendimiento es crucial. Utiliza índices, analiza planes de consulta con EXPLAIN y vigila las estadísticas de la base de datos para garantizar búsquedas eficientes.

  3. Clasificación y relevancia: Para aplicaciones como motores de búsqueda y sistemas de recomendación, considera aprovechar las capacidades de búsqueda de texto completo de PostgreSQL. No sólo recuperan registros coincidentes, sino que también los clasifican por relevancia.

  4. Combinando técnicas: No dudes en combinar múltiples técnicas de búsqueda. Para casos de uso complejos, una combinación de coincidencias exactas, coincidencias parciales y expresiones regulares podría dar los mejores resultados.

  5. Consultas de datos JSON: Si tus datos incluyen campos JSON, explora las consultas JSONPath para búsquedas eficientes y precisas dentro de las estructuras JSON.

  6. Indexación: Indexar adecuadamente las columnas utilizadas en las consultas de búsqueda puede mejorar significativamente el rendimiento de la búsqueda.

  7. Vistas Materializadas: Precomputa y almacena los resultados de búsqueda en vistas materializadas para consultas frecuentemente utilizadas.

Conclusión

En esta guía exhaustiva (ya un par de horas escribiendo esto), hemos explorado una amplia variedad de técnicas de búsqueda de cadenas disponibles en PostgreSQL. Desde coincidencias exactas básicas hasta búsquedas avanzadas de texto completo y consultas de datos JSON, PostgreSQL ofrece una gran cantidad de opciones para adaptarse a diversas necesidades, me atrevería a decir que PostgreSQL ofrece muchas más alternativas que MySQL y es un poco mas flexible, aparte de que las búsquedas se pueden extender mediante el uso de extensiones externas, aquí hay más campo para seguir explorando.

Y principalmente no olvides que la documentación de PostgreSQL es un recurso invaluable :)

Happy coding! :D

Photo by Fernando Gomez on Unsplash

Jack Fiallos

Jack Fiallos

Related posts

Te gustó este artículo?