Vue matérialisée¶
En SQL, une vue est le résultat d’une requête dont la définition est stockée dans le catalogue système. Elle peut être assimilée à une table virtuelle qui peut être consultée à l’aide d’un SELECT. Elle permet ainsi de masquer la complexité de la requête. Elle peut également être utile pour masquer certaines données à un utilisateur.
Chaque appel à une vue entraîne l’exécution de la requête associée, ce qui peut être coûteux lorsque la requête est complexe. Depuis la version 9.3, il est possible de créer des vues matérialisées. Le résultat de la requête est alors dupliqué dans une table. Les vues matérialisées ne sont spécifiées dans la norme SQL, il s’agit d’une extension.
C’est une forme de dé-normalisation puisque la lecture de la vue entraîne juste la lecture de la table sans exécuter la requête. Il n’y a ainsi aucune garantie que les données qu’elle contient sont bien à jour par rapport aux données réellement présentes dans les objets de la base.
Cette technique de mise en cache est utilisée principalement à des fins d’optimisation et de recherche de performance, lorsque l’on doit faire face à des requêtes particulièrement longues et complexes. Comme tout système de mise en cache, il faut prévoir un système d’invalidation du cache afin de le mettre à jour.
Avantages¶
Les vues matérialisées sont utiles :
- pour apporter un gain notable en performance pour des requêtes spécifiques ;
- lorsque les “données réelles” sont peu ou rarement modifiées ;
- lorsque l’on peut travailler sur des données légèrement désynchronisées.
Inconvénients¶
Voici toutefois des objections majeures à l’utilisation des vues matérialisées dans PostgreSQL :
- elles ne simplifient pas les problèmes, au contraire elles complexifient la gestion des données ;
- il faut prévoir un moyen de les mettre à jour (contention possible).
Mise en oeuvre¶
Création¶
Pour créer une vue matérialisée, il faut utiliser l’ordre CREATE MATERIALIZED VIEW mymatview AS
Créons un jeu de données :
CREATE TABLE t1 (c1 INT);
INSERT INTO t1 (c1)
SELECT *
FROM generate_series(1, 100);
CREATE TABLE t2 (c1 INT, c2 TEXT);
INSERT INTO t2 (c1, c2)
SELECT i, md5(i::TEXT)
FROM generate_series(1, 100) i(i);
La requête suivante fait la joINTure entre t1 et t2 sur la colonne c1, puis applique un filtre sur la colonne c2 afin de ne sélectionner que les enregistrements se terminant par un chiffre pair :
Voici son plan d’exécution :
EXPLAIN VERBOSE SELECT t1.c1, t2.c2 FROM t1 JOIN t2 ON t1.c1 = t2.c1 WHERE t2.c2 ~ '[0|2|4|6|8]$';
QUERY PLAN
-----------------------------------------------------------------------
Hash Join (cost=2.64..5.32 rows=31 width=37)
Output: t1.c1, t2.c2
Hash Cond: (t1.c1 = t2.c1)
-> Seq Scan on public.t1 (cost=0.00..2.00 rows=100 width=4)
Output: t1.c1
-> Hash (cost=2.25..2.25 rows=31 width=37)
Output: t2.c2, t2.c1
-> Seq Scan on public.t2 (cost=0.00..2.25 rows=31 width=37)
Output: t2.c2, t2.c1
Filter: (t2.c2 ~ '[0|2|4|6|8]$'::text)
(10 rows)
Créons une vue non matérialisée :
CREATE VIEW vuefresh
AS
SELECT t1.c1, t2.c2
FROM t1
JOIN t2 ON t1.c1 = t2.c1
WHERE t2.c2 ~ '[0|2|4|6|8]$';
Voici le plan d’exécution lors d’une lecture :
EXPLAIN VERBOSE SELECT * FROM vuefresh;
QUERY PLAN
-----------------------------------------------------------------------
Hash Join (cost=2.64..5.32 rows=31 width=37)
Output: t1.c1, t2.c2
Hash Cond: (t1.c1 = t2.c1)
-> Seq Scan on public.t1 (cost=0.00..2.00 rows=100 width=4)
Output: t1.c1
-> Hash (cost=2.25..2.25 rows=31 width=37)
Output: t2.c2, t2.c1
-> Seq Scan on public.t2 (cost=0.00..2.25 rows=31 width=37)
Output: t2.c2, t2.c1
Filter: (t2.c2 ~ '[0|2|4|6|8]$'::text)
(10 rows)
Une lecture de la vue entraîne l’exécution de la requête.
Pour éviter cela, on peut créer la vue matérialisée :
CREATE MATERIALIZED VIEW vuemat AS SELECT t1.c1,t2.c2 FROM t1 join t2 ON t1.c1=t2.c1 WHERE t2.c2 ~ '[0|2|4|6|8]$';
Voici le plan d’exécution lors de la lecture :
EXPLAIN VERBOSE SELECT * FROM vuemat ;
QUERY PLAN
------------------------------------------------------------------
Seq Scan on public.vuemat (cost=0.00..22.70 rows=1270 width=36)
Output: c1, c2
(2 rows)
Le plan est bien moins coûteux puisqu’il consiste en une simple lecture de la table. L’utilisation d’une vue matérialisée évite d’exécuter la requête à chaque lecture.
Rafraîchissement¶
Supprimons le contenu de la table t1 et comparons les deux vues:
DELETE FROM t1;
SELECT * FROM vuefresh;
c1 | c2
----+----
(0 rows)
SELECT * FROM vuemat;
c1 | c2
----+----------------------------------
9 | 45c48cce2e2d7fbdea1afc51c7c6ad26
10 | d3d9446802a44259755d38e6d163e820
12 | c20ad4d76fe97759aa27a0c99bff6710
14 | aab3238922bcc25a6f606eb525ffdc56
19 | 1f0e3dad99908345f7439f8ffabdffc4
20 | 98f13708210194c475687be6106a3b84
27 | 02e74f10e0327ad868d138f2b4fdd6f0
35 | 1c383cd30b7c298ab50293adfecb7b18
36 | 19ca14e7ea6328a42e0eb13d585e4c22
40 | d645920e395fedad7bbbed0eca3fe2e0
42 | a1d0c6e83f027327d8461063f4ac58a6
44 | f7177163c833dff4b38fc8d2872f1ec6
48 | 642e92efb79421734881b53e1e1b18b6
49 | f457c545a9ded88f18ecee47145a72c0
51 | 2838023a778dfaecdc212708f721b788
52 | 9a1158154dfa42caddbd0694a4e9bdc8
53 | d82c8d1619ad8176d665453cfb2e55f0
54 | a684eceee76fc522773286a895bc8436
56 | 9f61408e3afb633e50cdf1b20de6f466
59 | 093f65e080a295f8076b1c5722a46aa2
60 | 072b030ba126b2f4b2374f342be9ed44
63 | 03afdbd66e7929b125f8597834fa83a4
67 | 735b90b4568125ed6c3f678819b6e058
69 | 14bfa6bb14875e45bba028a21ed38046
71 | e2c420d928d4bf8ce0ff2ec19b371514
76 | fbd7939d674997cdb4692d34de8633c4
88 | 2a38a4a9316c49e5a833517c45d31070
89 | 7647966b7343c29048673252e490f736
91 | 54229abfcfa5649e7003b83dd4755294
92 | 92cc227532d17e56e07902b254dfad10
96 | 26657d5ff9020d2abefe558796b99584
(31 rows)
La vue matérialisée vuemat contient des données périmées. Il est possible de mettre à jour la vue grâce à l’ordre REFRESH MATERIALIZED VIEW vuemat; :
La vue matérialisée vuemat ne contient plus des données périmées :
Note
La mise à jour d’une vue matérialisée entraîne la pose d’un verrou de type ACCESS EXCLUSIVE sur la vue, ce qui empêche toute opération sur celle-ci (même une simple lecture).
La version 9.4 introduit la possibilité de rafraîchir une vue matérialisée sans blocage grâce à la clause CONCURRENTLY. Dès lors, le verrou utilisé est de type EXCLUSIVE ce qui autorise les lectures. Cependant, il n’est possible d’utiliser cette clause que s’il existe un index unique portant seulement sur des noms de colonne et sur l’intégralité des lignes de la vue :
REFRESH MATERIALIZED VIEW CONCURRENTLY vuemat ;
ERROR: cannot refresh materialized view "public.vuemat" concurrently
HINT: Create a unique index with no WHERE clause on one or more columns of the materialized view.
Le `REFRESH MATERIALIZED VIEW CONCURRENTLY` est impossible car il manque un index unique :
```sql
CREATE UNIQUE INDEX ON vuemat (c1);
CREATE INDEX
Avec la création du précédent index unique, il est alors maintenant possible de lancer un REFRESH MATERIALIZED VIEW CONCURRENTLY :
Attention
Rien n’empêche d’insérer des lignes en doublon dans la table t1 qui pourrait rentrer en conflit avec l’index unique sur la vue matérialisée. L’erreur ne sera relevée que lors de la mise à jour de la vue :
INSERT INTO t1(c1) VALUES ('9');
REFRESH MATERIALIZED VIEW CONCURRENTLY vuemat ;
INSERT INTO t1(c1) VALUES ('9');
REFRESH MATERIALIZED VIEW CONCURRENTLY vuemat ;
Le deuxième rafraîchissement est alors impossible car l’index unique sur vuemat n’est plus respecté :
ERROR: new data for materialized view "vuemat" contains duplicate rows without any null columns
DETAIL: Row: (9,45c48cce2e2d7fbdea1afc51c7c6ad26)
Ainsi les deux vues peuvent différer :
Écrire dans les vues matérialisées ?¶
Contrairement aux vues classiques, il n’est pas possible de modifier directement le contenu d’une vue matérialisée :
Conclusion¶
À l’exception de cas très précis et maîtrisés, les vues matérialisées sont à déconseiller car elles sont complexes à mettre en place et à maintenir.
Elles peuvent être utiles pour des infocentres où il est nécessaire de manipuler de gros volumes de données. Elles permettent ainsi d’effectuer un calcul intermédiaire afin d’obtenir un résultat plus rapidement.
Info
Postgres n’effectue pas de réécriture transparente des vues matérialisées. Il faut explicitement faire une requête sur la vue pour l’exploiter.