Une vue est une requête enregistrée que vous lisez comme une table : CREATE VIEW name AS SELECT .... Elle ne stocke aucune donnée, donc chaque lecture relance la requête et voit toujours les lignes actuelles. Une vue matérialisée, CREATE MATERIALIZED VIEW name AS SELECT ..., stocke le résultat sur disque et ne change que lorsque vous lancez REFRESH MATERIALIZED VIEW name.
La commande insérée après la création de la vue apparaît dans le résultat (ben avec 90), parce que la requête de la vue s'est exécutée au moment du SELECT. Les vues donnent un nom à une jointure ou à un filtre que vous répétez, cachent des colonnes aux utilisateurs qui ne doivent pas les voir, et gardent les anciennes requêtes fonctionnelles quand les tables changent.
Modifier ou supprimer une vue
CREATE OR REPLACE VIEW remplace la requête sur place. La nouvelle requête doit renvoyer les mêmes colonnes, avec les mêmes noms et types, dans le même ordre ; elle peut seulement ajouter de nouvelles colonnes à la fin. Pour tout autre changement, supprimez la vue et recréez-la.
CREATE OR REPLACE VIEW paid_totals AS
SELECT customer, sum(amount) AS total, count(*) AS orders
FROM orders WHERE paid GROUP BY customer;
DROP VIEW paid_totals;
DROP VIEW IF EXISTS paid_totals CASCADE; -- also drops views built on it
Insérer et mettre à jour via une vue
Une vue sur une seule table est automatiquement modifiable si elle n'a pas de GROUP BY, de DISTINCT, d'agrégats, de window functions, de LIMIT ni d'opérations ensemblistes. Un INSERT, un UPDATE ou un DELETE sur la vue modifie alors la table. WITH CHECK OPTION refuse les lignes que la vue ne pourrait pas afficher ensuite :
Les deux modifications ont atteint products. Insérer (4, 'shelf', false) via la vue échoue avec new row violates check option for view "active_products", parce que la vue ne l'afficherait pas. Pour une vue qui n'est pas modifiable, un trigger INSTEAD OF peut définir ce que fait une écriture.
Créer et rafraîchir une vue matérialisée
Une vue matérialisée exécute sa requête une fois et stocke les lignes. Les lectures sont aussi rapides que sur une table, mais les nouvelles données n'apparaissent qu'après un rafraîchissement :
Avant le rafraîchissement, ana affiche encore 120 ; après, 180. REFRESH relance toute la requête et remplace les lignes stockées. Utilisez une vue matérialisée pour des résultats coûteux à calculer qui peuvent dater de quelques minutes ou quelques heures : tableaux de bord, rapports, résumés de recherche.
CREATE MATERIALIZED VIEW ... WITH NO DATA la crée vide, et sa lecture échoue jusqu'au premier rafraîchissement.
Rafraîchir sans bloquer les lectures
Un REFRESH simple verrouille la vue matérialisée, donc les requêtes qui la lisent attendent la fin du rafraîchissement. REFRESH MATERIALIZED VIEW CONCURRENTLY construit le nouveau résultat à part et n'applique que les différences, donc les lectures continuent. Il faut un index unique sur la vue matérialisée, sur des noms de colonnes simples et sans WHERE :
Sans l'index unique, le rafraîchissement échoue avec un conseil d'en créer un. CONCURRENTLY peut être plus rapide qu'un rafraîchissement simple quand peu de lignes changent, et ne peut pas servir sur une vue matérialisée qui n'a jamais été remplie. Dans tous les cas, un seul rafraîchissement d'une même vue matérialisée s'exécute à la fois. Les autres index fonctionnent comme sur une table, donc indexez les colonnes sur lesquelles vos requêtes filtrent.
Rafraîchir selon un planning
PostgreSQL ne rafraîchit pas les vues matérialisées tout seul. Lancez le rafraîchissement depuis un planificateur, comme cron qui appelle psql, ou l'extension pg_cron là où elle est installée :
# crontab: every 15 minutes
*/15 * * * * psql "$DATABASE_URL" -c 'REFRESH MATERIALIZED VIEW CONCURRENTLY customer_totals'
SELECT cron.schedule('refresh-totals', '*/15 * * * *',
'REFRESH MATERIALIZED VIEW CONCURRENTLY customer_totals');
Vue ou vue matérialisée
| Vue | Vue matérialisée | |
|---|---|---|
| Stocke des lignes | non | oui |
| Données à jour | toujours | au dernier rafraîchissement |
| Vitesse de lecture | celle de la requête | celle d'une table |
| Index | non (utilise les index des tables) | oui |
| Modifiable | les vues simples, oui | non |
La référence de CREATE MATERIALIZED VIEW liste les autres options, comme les paramètres de stockage et les tablespaces.
Questions fréquentes
Une vue matérialisée PostgreSQL se met-elle à jour toute seule ?
Non. Elle garde le résultat de son dernier REFRESH MATERIALIZED VIEW (ou de sa création) jusqu'à ce que vous la rafraîchissiez de nouveau. Planifiez le rafraîchissement avec cron, l'extension pg_cron ou votre application.
Une vue est-elle plus rapide que la requête elle-même ?
Non. Une vue simple ne stocke aucune donnée ; PostgreSQL la développe dans la requête qui la lit, donc elle s'exécute aussi vite que la requête écrite en entier. Seule une vue matérialisée économise le travail, au prix de données moins fraîches.
Peut-on ajouter un index à une vue matérialisée ?
Oui. Une vue matérialisée est stockée comme une table, donc CREATE INDEX fonctionne dessus. Un index unique est d'ailleurs ce qu'exige REFRESH MATERIALIZED VIEW CONCURRENTLY.
Comment lister les vues et les vues matérialisées ?
Dans psql, \dv liste les vues et \dm les vues matérialisées. En SQL, interrogez pg_views et pg_matviews.