Búsqueda de Texto Completo en PostgreSQL

Introducción

En este laboratorio, aprenderá a implementar la búsqueda de texto completo (full-text search) en PostgreSQL. La búsqueda de texto completo proporciona la capacidad de buscar en documentos de lenguaje natural palabras o frases específicas. Comenzará creando una tabla articles y añadiendo datos de ejemplo. Luego, agregará una columna search_vector de tipo tsvector, que es un tipo de dato especializado para almacenar texto procesado. Creará un índice para acelerar las búsquedas y luego realizará consultas utilizando tsquery. Finalmente, configurará un trigger (disparador) para actualizar automáticamente los datos de búsqueda cada vez que se modifique el texto de origen, asegurando que su índice de búsqueda se mantenga actualizado.

Crear una Tabla e Insertar Datos de Muestra

En este paso, se conectará a la base de datos PostgreSQL, creará una tabla articles e insertará algunos datos de ejemplo. Esta tabla servirá como base para implementar y probar la búsqueda de texto completo.

Primero, abra una terminal y conéctese al shell interactivo de PostgreSQL (psql) como el usuario postgres. Todos los comandos SQL subsiguientes en este laboratorio se ejecutarán dentro de este shell.

sudo -u postgres psql

Ahora verá el prompt de PostgreSQL, que se ve así: postgres=#.

A continuación, cree la tabla articles. Esta tabla almacenará un ID, un título y el contenido principal de cada artículo.

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT
);

Ahora, inserte tres artículos de ejemplo en la tabla articles. Estos datos se utilizarán para sus consultas de búsqueda en los siguientes pasos.

INSERT INTO articles (title, content) VALUES
('PostgreSQL Tutorial', 'This is a comprehensive tutorial on PostgreSQL.'),
('Full Text Search in PostgreSQL', 'Learn how to implement full text search using TSVECTOR in PostgreSQL.'),
('PostgreSQL Performance Tuning', 'Tips and tricks to improve the performance of your PostgreSQL database.');

Para confirmar que los datos se insertaron correctamente, ejecute una consulta SELECT para ver todos los registros de la tabla.

SELECT * FROM articles;

Debería ver las tres filas que acaba de insertar, similar a la salida a continuación:

 id |             title              |                                       content
----+----------------------------------+-----------------------------------------------------------------------
  1 | PostgreSQL Tutorial            | This is a comprehensive tutorial on PostgreSQL.
  2 | Full Text Search in PostgreSQL | Learn how to implement full text search using TSVECTOR in PostgreSQL.
  3 | PostgreSQL Performance Tuning  | Tips and tricks to improve the performance of your PostgreSQL database.
(3 rows)

Añadir una Columna TSVECTOR e Índice GIN

Para realizar búsquedas de texto completo, necesita una columna de tipo tsvector. Un valor tsvector es una lista ordenada de palabras distintas (lexemas) que han sido normalizadas para fusionar diferentes variantes de la misma palabra. En este paso, agregará una columna tsvector y creará un índice sobre ella para acelerar las consultas de búsqueda.

Primero, agregue una nueva columna llamada search_vector de tipo TSVECTOR a la tabla articles.

ALTER TABLE articles ADD COLUMN search_vector TSVECTOR;

A continuación, rellene esta nueva columna convirtiendo el title y content de cada artículo en un tsvector. La función to_tsvector se utiliza para esta conversión. Procesa el texto eliminando palabras comunes (stop words) y reduciendo las palabras a su forma raíz (stemming).

UPDATE articles
SET search_vector = to_tsvector('english', title || ' ' || content);

En este comando, 'english' especifica la configuración de búsqueda de texto, y || es el operador de concatenación para combinar el título y el contenido.

Para acelerar significativamente las búsquedas de texto completo, cree un GIN (Generalized Inverted Index) en la columna search_vector. Los índices GIN son muy efectivos para indexar valores compuestos como tsvector.

CREATE INDEX articles_search_idx ON articles USING GIN (search_vector);

Puede verificar que la columna y el índice se han agregado inspeccionando la estructura de la tabla con el comando \d.

\d articles

La salida ahora debería incluir la columna search_vector y el índice GIN articles_search_idx.

                                     Table "public.articles"
    Column     |          Type          | Collation | Nullable |                 Default
---------------+------------------------+-----------+----------+-----------------------------------------
 id            | integer                |           | not null | nextval('articles_id_seq'::regclass)
 title         | character varying(255) |           | not null |
 content       | text                   |           |          |
 search_vector | tsvector               |           |          |
Indexes:
    "articles_pkey" PRIMARY KEY, btree (id)
    "articles_search_idx" gin (search_vector)

Realizar Búsquedas de Texto Completo

Con la columna tsvector y el índice GIN en su lugar, ahora puede realizar búsquedas de texto completo eficientes. Las consultas se construyen utilizando el tipo de datos tsquery, que representa una consulta de texto. El operador @@ se utiliza para hacer coincidir un tsquery con un tsvector.

Primero, busquemos artículos que contengan la palabra search. La función to_tsquery convierte la cadena de búsqueda en un tsquery.

SELECT title, content FROM articles WHERE search_vector @@ to_tsquery('english', 'search');

Esta consulta devolverá el artículo sobre "Full Text Search".

             title              |                                       content
----------------------------------+-----------------------------------------------------------------------
 Full Text Search in PostgreSQL | Learn how to implement full text search using TSVECTOR in PostgreSQL.
(1 row)

También puede usar operadores dentro de su consulta. El operador & (AND) encuentra documentos que contienen todos los términos especificados. Busquemos artículos que contengan tanto PostgreSQL como performance.

SELECT title FROM articles WHERE search_vector @@ to_tsquery('english', 'PostgreSQL & performance');

Esto devuelve el artículo sobre optimización de rendimiento.

             title
-------------------------------
 PostgreSQL Performance Tuning
(1 row)

El operador | (OR) encuentra documentos que contienen al menos uno de los términos especificados. Busquemos artículos que contengan tutorial o tuning.

SELECT title FROM articles WHERE search_vector @@ to_tsquery('english', 'tutorial | tuning');

Esta consulta devolverá dos artículos.

             title
-------------------------------
 PostgreSQL Tutorial
 PostgreSQL Performance Tuning
(2 rows)

Automatizar Actualizaciones con un Trigger

Actualmente, la columna search_vector es estática. Si actualiza el título o el contenido de un artículo, el search_vector no reflejará automáticamente el cambio. Para solucionar esto, puede crear un trigger que actualice automáticamente la columna search_vector antes de cualquier operación de INSERT o UPDATE en la tabla articles.

Primero, defina una función que será ejecutada por el trigger. Esta función actualiza el search_vector para la fila nueva o actualizada.

CREATE OR REPLACE FUNCTION articles_tsvector_update() RETURNS TRIGGER AS $$
BEGIN
    NEW.search_vector :=
        to_tsvector('pg_catalog.english', NEW.title || ' ' || NEW.content);
    RETURN NEW;
END
$$ LANGUAGE plpgsql;

La variable NEW en la función se refiere a la fila que se está insertando o actualizando.

A continuación, cree el trigger en sí. Este trigger llamará a la función articles_tsvector_update antes de que cada fila sea insertada o actualizada.

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

Ahora, probemos el trigger. Primero, inserte un nuevo artículo sobre indexación de bases de datos.

INSERT INTO articles (title, content) VALUES ('Database Indexing', 'Learn about B-Tree and GIN indexes.');

Debido al trigger, el search_vector para este nuevo artículo se pobló automáticamente. Puede verificar esto buscando la palabra indexes.

SELECT title FROM articles WHERE search_vector @@ to_tsquery('indexes');

El nuevo artículo debería aparecer en los resultados.

        title
---------------------
 Database Indexing
(1 row)

Esto confirma que su trigger está funcionando correctamente, asegurando que su índice de búsqueda de texto completo permanezca sincronizado con sus datos.

Resumen

En este laboratorio, ha aprendido los fundamentos de la implementación de búsqueda de texto completo en PostgreSQL. Ha creado con éxito una tabla, la ha poblado con datos y la ha configurado para la búsqueda de texto completo añadiendo una columna tsvector. Aprendió a utilizar la función to_tsvector para procesar texto y un índice GIN para optimizar el rendimiento de la búsqueda. También practicó la realización de búsquedas con tsquery utilizando diferentes operadores. Finalmente, implementó un trigger para automatizar el proceso de mantener el índice de búsqueda sincronizado con sus datos, lo cual es un paso crítico para mantener un sistema de búsqueda fiable.