Vues matérialisées dans PostgreSQL

Si vous travaillez régulièrement en SQL (idéalement avec PostgreSQL), vous avez sans doute rédigé un bon nombre de requêtes complexes pour répondre à des questions clés. Il arrive aussi que vous ayez besoin d’utiliser très souvent les résultats de ces requêtes. Exécuter une requête complexe à répétition peut s’avérer coûteux. Et s’il existait un moyen de mettre en cache les résultats d’une requête complexe pour gagner en performance ? Les vues matérialisées sont faites pour cela.

Vous vous dites peut‑être qu’il existe aussi de simples vues qui peuvent exposer le résultat d’une requête. Mais une vue simple diffère d’une vue matérialisée à plusieurs égards. Voici ce que dit la documentation officielle de PostgreSQL à propos des vues simples :

La vue n’est pas matérialisée physiquement. À la place, la requête est exécutée à chaque fois que la vue est référencée dans une requête.

Autrement dit, les vues simples ne sont pas stockées physiquement dans la base. Chaque fois qu’une vue est utilisée, la requête ayant servi à la créer est réexécutée. C’est ce qui peut rendre les vues simples moins efficaces en termes de temps d’accès.

À l’inverse, les vues matérialisées offrent de la souplesse en vous permettant de persister une vue physiquement dans la base de données. Vous pouvez ensuite les interroger comme des vues simples (avec un temps d’accès réduit). Dans la suite de ce tutoriel, vous allez découvrir les vues matérialisées dans PostgreSQL. Dans l’un de ses webinaires, le Chief Data Scientist de DataCamp, David Robinson, montre comment il utilise les vues matérialisées pour stocker efficacement les résultats de requêtes complexes.

Remarque : pour suivre ce tutoriel, vous devez être à l’aise avec les notions abordées dans le cours Joining Data in SQL.

Configuration initiale de la base de données

Il est préférable de préparer une base PostgreSQL et d’y créer une table de base afin de pouvoir l’interroger efficacement. Cela vous aidera dans les sections suivantes de ce tutoriel. Vous pouvez restaurer cette sauvegarde qui contient la table utilisée ici. Si vous souhaitez apprendre à restaurer une sauvegarde dans PostgreSQL, reportez‑vous à la première section de ce tutoriel.

Si vous avez pu restaurer la sauvegarde, vous devriez voir une table nommée international_debt dans la base (vous devrez créer une base au préalable si vous n’en avez pas). Jetons un œil aux premières lignes de la table (une simple requête select suffit) :

La table contient des informations sur les statistiques d’endettement de différents pays dans le monde pour l’année en cours et selon plusieurs catégories (voir les colonnes indicator_name, indicator_code). La colonne debt indique le montant de la dette (en USD) d’un pays donné pour une catégorie donnée. Ces données économiques servent souvent à analyser la situation des pays. Elles proviennent de la Banque mondiale.

Maintenant que votre base est prête, vous pouvez passer à la suite.

Créer des vues matérialisées

Créons une vue matérialisée à partir de la requête suivante :

select country_name,
sum(debt) as total_debt
from international_debt
group by country_name order by country_name;

La requête renvoie les pays avec leur dette totale (par ordre alphabétique). Le résultat devrait ressembler à ceci :

Voici la vue matérialisée :

CREATE MATERIALIZED VIEW country_total_debt
as
  select country_name,
  sum(debt) as total_debt
  from international_debt
  group by country_name order by country_name;

Vous devriez obtenir un message de réussite du type : Query returned successfully: 124 rows affected, 242 ms execution time.

Actualiser une vue matérialisée

Vous pouvez aussi indiquer à PostgreSQL de ne charger aucune donnée dans la vue matérialisée lors de sa création en spécifiant l’option WITH NO DATA :

CREATE MATERIALIZED VIEW country_total_debt_2
as
  select country_name,
  sum(debt) as total_debt
  from international_debt
  group by country_name order by country_name
WITH NO DATA;

Vous obtiendrez un message du type : Query returned successfully with no result in 41 ms. Notez la différence entre les deux messages. Avec WITH NO DATA, une vue matérialisée vide est créée. Vous ne pouvez pas l’interroger immédiatement. En revanche, vous pouvez la peupler en exécutant :

REFRESH MATERIALIZED VIEW country_total_debt_2;

Interroger une vue matérialisée

À ce stade, vous devriez avoir deux vues matérialisées (country_total_debt, country_total_debt_2). Exécutons une requête select simple sur l’une d’elles :

select * from country_total_debt_2;

La requête devrait renvoyer quelque chose comme :

La sortie est identique à celle de la requête encapsulée dans la vue matérialisée. Vous pouvez également exécuter des agrégations sur des vues matérialisées :

select max(total_debt) from country_total_debt_2;

Vous devriez obtenir :

Supprimer une vue matérialisée

Les vues matérialisées se suppriment comme les tables dans PostgreSQL. Il suffit de préciser Materialized View après Drop :

Drop Materialized View country_total_debt_2;

Pour comparer MySQL à son principal concurrent, consultez notre analyse détaillée PostgreSQL vs. MySQL.

Conclusion

Voilà pour ce tutoriel, et merci de votre lecture. Vous avez découvert les vues matérialisées dans PostgreSQL et la façon de les utiliser. Grâce à leur efficacité, elles sont largement utilisées en entrepôt de données. Si vous souhaitez aller plus loin avec des requêtes plus avancées, consultez ces cours DataCamp :

• Intermediate SQL
• Reporting in SQL