Recherche en texte intégral PostgreSQL

Introduction

Dans ce laboratoire, vous apprendrez à implémenter la recherche en texte intégral (full-text search) dans PostgreSQL. La recherche en texte intégral offre la capacité de rechercher dans des documents en langage naturel des mots ou des phrases spécifiques. Vous commencerez par créer une table articles et ajouter des données d'exemple. Ensuite, vous ajouterez une colonne search_vector de type tsvector, qui est un type de données spécialisé pour stocker du texte traité. Vous créerez un index pour accélérer les recherches, puis effectuerez des requêtes en utilisant tsquery. Enfin, vous configurerez un déclencheur (trigger) pour mettre à jour automatiquement les données de recherche chaque fois que le texte source est modifié, garantissant ainsi que votre index de recherche reste à jour.

Créer une table et insérer des données d'exemple

Dans cette étape, vous allez vous connecter à la base de données PostgreSQL, créer une table articles et insérer quelques données d'exemple. Cette table servira de base pour implémenter et tester la recherche en texte intégral.

Tout d'abord, ouvrez un terminal et connectez-vous à l'interpréteur interactif de PostgreSQL (psql) en tant qu'utilisateur postgres. Toutes les commandes SQL suivantes dans ce laboratoire seront exécutées à l'intérieur de cet interpréteur.

sudo -u postgres psql

Vous verrez maintenant l'invite de PostgreSQL, qui ressemble à postgres=#.

Ensuite, créez la table articles. Cette table stockera un ID, un titre et le contenu principal de chaque article.

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

Insérez maintenant trois articles d'exemple dans la table articles. Ces données seront utilisées pour vos requêtes de recherche dans les étapes suivantes.

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

Pour confirmer que les données ont été insérées correctement, exécutez une requête SELECT pour afficher tous les enregistrements de la table.

SELECT * FROM articles;

Vous devriez voir les trois lignes que vous venez d'insérer, similaires à la sortie ci-dessous :

 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)

Ajouter une colonne TSVECTOR et un index GIN

Pour effectuer une recherche en texte intégral, vous avez besoin d'une colonne de type tsvector. Une valeur tsvector est une liste triée de mots distincts (lexèmes) qui ont été normalisés pour fusionner différentes variantes du même mot. Dans cette étape, vous allez ajouter une colonne tsvector et créer un index sur celle-ci pour accélérer les requêtes de recherche.

Tout d'abord, ajoutez une nouvelle colonne nommée search_vector de type TSVECTOR à la table articles.

ALTER TABLE articles ADD COLUMN search_vector TSVECTOR;

Ensuite, peuplez cette nouvelle colonne en convertissant le title et le content de chaque article en tsvector. La fonction to_tsvector est utilisée pour cette conversion. Elle traite le texte en supprimant les mots courants (stop words) et en réduisant les mots à leur forme racine (stemming).

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

Dans cette commande, 'english' spécifie la configuration de recherche textuelle, et || est l'opérateur de concaténation pour combiner le titre et le contenu.

Pour accélérer considérablement les recherches en texte intégral, créez un index GIN (Generalized Inverted Index) sur la colonne search_vector. Les index GIN sont très efficaces pour indexer des valeurs composites comme tsvector.

CREATE INDEX articles_search_idx ON articles USING GIN (search_vector);

Vous pouvez vérifier que la colonne et l'index ont été ajoutés en inspectant la structure de la table avec la commande \d.

\d articles

La sortie devrait maintenant inclure la colonne search_vector et l'index 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)

Effectuer des recherches en texte intégral

Avec la colonne tsvector et l'index GIN en place, vous pouvez maintenant effectuer des recherches en texte intégral efficaces. Les requêtes sont construites à l'aide du type de données tsquery, qui représente une requête textuelle. L'opérateur @@ est utilisé pour faire correspondre un tsquery à un tsvector.

Tout d'abord, recherchons les articles contenant le mot search. La fonction to_tsquery convertit la chaîne de recherche en tsquery.

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

Cette requête retournera l'article sur "Full Text Search".

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

Vous pouvez également utiliser des opérateurs dans votre requête. L'opérateur & (AND) trouve les documents contenant tous les termes spécifiés. Recherchons les articles qui contiennent à la fois PostgreSQL et performance.

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

Ceci retourne l'article sur l'optimisation des performances.

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

L'opérateur | (OR) trouve les documents contenant au moins l'un des termes spécifiés. Recherchons les articles contenant soit tutorial, soit tuning.

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

Cette requête retournera deux articles.

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

Automatiser les mises à jour avec un déclencheur (trigger)

Actuellement, la colonne search_vector est statique. Si vous mettez à jour le titre ou le contenu d'un article, le search_vector ne reflétera pas automatiquement le changement. Pour résoudre ce problème, vous pouvez créer un déclencheur qui met à jour automatiquement la colonne search_vector avant toute opération INSERT ou UPDATE sur la table articles.

Tout d'abord, définissez une fonction qui sera exécutée par le déclencheur. Cette fonction met à jour le search_vector pour la ligne nouvelle ou mise à jour.

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 dans la fonction fait référence à la ligne en cours d'insertion ou de mise à jour.

Ensuite, créez le déclencheur lui-même. Ce déclencheur appellera la fonction articles_tsvector_update avant chaque insertion ou mise à jour de ligne.

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

Maintenant, testons le déclencheur. Tout d'abord, insérez un nouvel article sur l'indexation de base de données.

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

En raison du déclencheur, le search_vector de ce nouvel article a été automatiquement renseigné. Vous pouvez le vérifier en recherchant le mot indexes.

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

Le nouvel article devrait apparaître dans les résultats.

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

Cela confirme que votre déclencheur fonctionne correctement, garantissant que votre index de recherche en texte intégral reste synchronisé avec vos données.

Résumé

Dans ce laboratoire, vous avez appris les bases de la mise en œuvre de la recherche en texte intégral dans PostgreSQL. Vous avez réussi à créer une table, à la peupler de données et à la configurer pour la recherche en texte intégral en ajoutant une colonne tsvector. Vous avez appris à utiliser la fonction to_tsvector pour traiter le texte et un index GIN pour optimiser les performances de recherche. Vous vous êtes également exercé à effectuer des recherches avec tsquery en utilisant différents opérateurs. Enfin, vous avez implémenté un déclencheur pour automatiser le processus de synchronisation de l'index de recherche avec vos données, ce qui est une étape cruciale pour maintenir un système de recherche fiable.