8 Février, 2024 par Robert Gravelle

Les vues matérialisées de PostgreSQL offrent un mécanisme puissant pour améliorer les performances des requêtes en pré-calculant et en stockant le jeu de résultats d'une requête sous forme de table physique. Ce tutoriel vous guidera à travers la création de vues matérialisées dans PostgreSQL, en utilisant la DVD Rental Database comme exemple pratique.

Comprendre les Vues Matérialisées

Une vue matérialisée est une capture instantanée du jeu de résultats d'une requête qui est stockée sous forme de table physique. Contrairement aux vues normales, qui sont virtuelles et exécutent la requête sous-jacente à chaque fois qu'elles sont référencées, les vues matérialisées persistent les données, permettant ainsi des performances de requête plus rapides au coût de rafraîchissements périodiques.

Les vues matérialisées sont particulièrement utiles dans les scénarios où les données sous-jacentes changent rarement par rapport à la fréquence des exécutions de requêtes. Cela les rend idéales pour des situations telles que le reporting, l'entreposage de données, et les cas où les données en temps réel ne sont pas des requêtes strictes.

Configuration de la Base de Données DVD Rental

Avant de plonger dans les vues matérialisées, configurons la base de données DVD Rental. C'est la version PostgreSQL de la populaire base de données d'exemple Sakila pour MySQL. Vous pouvez télécharger la base de données DVD Rental depuis la page de tutoriels officielle de PostgreSQL (PostgreSQL Sample Database).

Le fichier de la base de données est au format ZIP (dvdrental.zip), vous devez donc l'extraire en dvdrental.tar avant de charger la base de données d'exemple dans le serveur de base de données PostgreSQL. Une fois que vous avez extrait le fichier .tar, créez une nouvelle base de données appelée "dvdrental" et exécutez la commande pg_restore pour peupler la base de données dvdrental à partir du contenu du fichier .tar :

        pg_restore -U postgres -d dvdrental D:\sampledb\postgres\dvdrental.tar
    

Remplacez le chemin ci-dessus par celui qui pointe vers le fichier dvdrental.tar extrait sur votre système.

Vous pouvez trouver les instructions d'installation détaillées ici.

Créer une Vue Matérialisée

Supposons que nous voulions créer une vue matérialisée qui montre le revenu total généré par chaque catégorie de film. Voici un guide étape par étape :

• Connectez-vous à votre base de données PostgreSQL
• Créez la vue matérialisée en utilisant l'instruction DML suivante :

CREATE MATERIALIZED VIEW mv_category_revenue AS
SELECT
    c.name AS category,
    SUM(p.amount) AS total_revenue
FROM
    category c
    JOIN film_category fc ON c.category_id = fc.category_id
    JOIN film f ON fc.film_id = f.film_id
    JOIN inventory i ON f.film_id = i.film_id
    JOIN rental r ON i.inventory_id = r.inventory_id
    JOIN payment p ON r.rental_id = p.rental_id
GROUP BY
    c.name;

Dans cet exemple, nous joignons plusieurs tables de la base de données DVD Rental pour calculer le revenu total pour chaque catégorie de film.

Pour Navicat For PostgreSQL (ou Navicat Premium) 16:

• Cliquez sur le bouton "Materialized View" pour afficher la liste des objets de vues matérialisées, puis cliquez sur "+ New Materialized View" dans la barre d'outils des objets pour ouvrir le view designer :

  

• Entrez la portion SELECT de l'instruction ci-dessus dans l'éditeur de définition :

  

• Nous pouvons cliquer sur le bouton Preview pour vérifier que notre instruction fonctionne comme prévu :

  

• Pour créer la nouvelle vue matérialisée, cliquez sur le bouton Save. Une boîte de dialogue apparaîtra demandant le nom de la vue matérialisée. Appelons-la "mv_category_revenue" comme nous l'avons fait dans l'instruction CREATE MATERIALIZED VIEW ci-dessus :

  

• En cliquant sur le bouton Save de la boîte de dialogue, Navicat changera le nom de la nouvelle vue matérialisée de "untitled" à celui que nous avons fourni. Il ajoutera également notre nouvelle vue matérialisée aux vues matérialisées dans le volet de navigation de gauche :

  

Conclusion

Les vues matérialisées de PostgreSQL sont un outil précieux pour optimiser les performances des requêtes dans des scénarios où les données en temps réel ne sont pas critiques. En pré-calculant et en stockant les résultats de requêtes complexes, les vues matérialisées peuvent améliorer considérablement les temps de réponse pour les tâches analytiques et de reporting. Dans ce tutoriel, nous avons appris comment créer une vue matérialisée pour la base de données DVD Rental, montrant leur application pratique dans un scénario réel.