Búsqueda de Texto Completo en PostgreSQL

Introducción

En este laboratorio, aprenderás cómo implementar la búsqueda de texto completo en PostgreSQL utilizando TSVECTOR. Comenzarás creando una tabla articles y agregando datos de ejemplo. Luego, agregarás una columna search_vector y crearás un índice TSVECTOR en ella. Finalmente, configurarás un trigger (disparador) para actualizar automáticamente la columna search_vector cada vez que se modifiquen las columnas title o content. Esto te permitirá realizar búsquedas de texto completo eficientes.

Skills Graph

Configuración de la tabla articles

En este paso, crearás la tabla articles e insertarás algunos datos de ejemplo. Esta tabla se utilizará para demostrar las capacidades de búsqueda de texto completo en PostgreSQL.

Primero, conéctate a la base de datos PostgreSQL como el usuario postgres utilizando el siguiente comando en tu terminal:

sudo -u postgres psql

Ahora, crea la tabla articles con el siguiente comando SQL:

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

Este comando crea una tabla llamada articles con tres columnas: id, title y content. La columna id es una clave primaria (primary key) autoincrementable.

A continuación, inserta algunos datos de ejemplo en la tabla articles:

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.');

Este comando inserta tres filas en la tabla articles, cada una con un título y contenido diferentes.

Puedes verificar que los datos se hayan insertado correctamente ejecutando la siguiente consulta:

SELECT * FROM articles;

Deberías ver las tres filas que acabas de insertar.

 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)

Agregar la columna search_vector y crear un índice GIN

En este paso, agregarás la columna search_vector a la tabla articles y crearás un índice GIN en ella. La columna search_vector almacenará la representación TSVECTOR de las columnas title y content, y el índice GIN acelerará las búsquedas de texto completo.

Primero, agrega la columna search_vector a la tabla articles:

ALTER TABLE articles ADD COLUMN search_vector TSVECTOR;

A continuación, actualiza la columna search_vector con la representación TSVECTOR de las columnas title y content. Puedes usar la función to_tsvector para convertir texto a un TSVECTOR.

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

En el comando anterior, 'english' especifica la configuración de idioma (language configuration) que se utilizará para el procesamiento de texto. El operador || concatena las columnas title y content con un espacio en medio.

Ahora, crea un índice GIN en la columna search_vector:

CREATE INDEX articles_search_vector_idx ON articles USING GIN (search_vector);

GIN (Índice Invertido Generalizado - Generalized Inverted Index) es un tipo de índice que es muy adecuado para indexar columnas TSVECTOR. Este índice permitirá a PostgreSQL encontrar rápidamente documentos que coincidan con una consulta de búsqueda dada.

Puedes verificar que el índice se haya creado ejecutando el siguiente comando:

\d articles

Deberías ver el índice articles_search_vector_idx listado en la salida.

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

Realización de búsquedas de texto completo con TSQUERY

Ahora que tienes la columna search_vector y el índice GIN configurados, puedes realizar búsquedas de texto completo utilizando los operadores TSQUERY.

PostgreSQL proporciona la función to_tsquery para convertir una cadena de texto en un objeto TSQUERY. Luego, puedes usar el operador @@ para comparar el TSQUERY con la columna search_vector.

Por ejemplo, para buscar artículos que contengan tanto "PostgreSQL" como "tutorial", puedes usar la siguiente consulta:

SELECT * FROM articles WHERE search_vector @@ to_tsquery('english', 'PostgreSQL & tutorial');

El operador & en la función to_tsquery especifica que ambos términos deben estar presentes en el search_vector.

También puedes usar el operador | para buscar artículos que contengan "PostgreSQL" o "tutorial":

SELECT * FROM articles WHERE search_vector @@ to_tsquery('english', 'PostgreSQL | tutorial');

Para buscar artículos que contengan la frase "full text search" (búsqueda de texto completo), puedes usar la siguiente consulta:

SELECT * FROM articles WHERE search_vector @@ to_tsquery('english', '''full text search''');

Encerrar la frase entre comillas simples le indica a to_tsquery que la trate como una sola frase.

Experimenta con diferentes consultas de búsqueda para ver cómo funcionan los operadores TSQUERY.

Clasificación de resultados por relevancia

PostgreSQL proporciona la función ts_rank para clasificar los resultados de búsqueda por relevancia. La función ts_rank toma el search_vector y el TSQUERY como entrada y devuelve un valor de clasificación (rank value).

Para clasificar los resultados de búsqueda por relevancia, puedes usar la siguiente consulta:

SELECT title, ts_rank(search_vector, to_tsquery('english', 'PostgreSQL')) AS rank
FROM articles
WHERE search_vector @@ to_tsquery('english', 'PostgreSQL')
ORDER BY rank DESC;

Esta consulta busca artículos que contengan "PostgreSQL", calcula la clasificación de cada artículo utilizando la función ts_rank y luego ordena los resultados por clasificación en orden descendente.

La columna rank en la salida muestra la puntuación de relevancia (relevance score) para cada artículo. Los artículos con puntuaciones de relevancia más altas se enumeran primero.

Mantener la columna search_vector actualizada con un Trigger (disparador)

Para asegurar que la columna search_vector esté siempre actualizada, puedes crear un trigger (disparador) que actualice automáticamente la columna cada vez que se modifiquen las columnas title o content.

Primero, crea una función de trigger (trigger function) que actualice la columna search_vector:

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

Esta función toma los nuevos valores de las columnas title y content y actualiza la columna search_vector con la representación TSVECTOR de los valores concatenados.

Luego, crea un trigger (disparador) que llame a la función update_search_vector cada vez que se inserte o actualice una fila en la tabla articles:

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

Este trigger (disparador) actualizará automáticamente la columna search_vector cada vez que se modifiquen las columnas title o content.

Para probar el trigger (disparador), actualiza el contenido de un artículo existente:

UPDATE articles SET content = 'This is an updated comprehensive tutorial on PostgreSQL with advanced features.' WHERE id = 1;

Ahora, busca artículos que contengan la palabra "features":

SELECT * FROM articles WHERE search_vector @@ to_tsquery('english', 'features');

Deberías ver el artículo actualizado en los resultados de búsqueda.

Resumen

En este laboratorio, has aprendido cómo implementar la búsqueda de texto completo (full-text search) en PostgreSQL utilizando TSVECTOR. Creaste una tabla articles, agregaste una columna search_vector, creaste un índice GIN en la columna search_vector y configuraste un trigger (disparador) para actualizar automáticamente la columna search_vector cada vez que se modifican las columnas title o content. También aprendiste cómo realizar búsquedas de texto completo utilizando los operadores TSQUERY y cómo clasificar los resultados de búsqueda por relevancia.