PostgreSQL : vues matérialisées et rafraîchissement

Une vue matérialisée PostgreSQL conserve le résultat d’une requête sous forme de relation dans la base. Sa lecture utilise ces lignes enregistrées. Les modifications des tables sources deviennent visibles après un rafraîchissement réussi : il faut donc définir explicitement le retard acceptable des données.

Ce guide construit un petit récapitulatif de commandes par région, puis suit les insertions, modifications, suppressions et rafraîchissements ordinaires ou concurrents. Nous avons exécuté 34 vérifications sur une instance isolée de PostgreSQL 18.6, avec des connexions distinctes pour les verrous de lecture et un rôle doté du privilège MAINTAIN.

Les sorties SQL ont été capturées avec psql en mode non aligné, sans en-têtes. Les colonnes sont séparées par | et les booléens apparaissent comme t ou f. Exécutez les exemples dans l’ordre, dans une base jetable. L’exemple de rôle nécessite un administrateur autorisé à créer des rôles et à accorder des privilèges.

Créer et lire un récapitulatif enregistré

CREATE SCHEMA mv_lab;
CREATE TABLE mv_lab.orders (
    id integer PRIMARY KEY,
    region text NOT NULL,
    amount numeric NOT NULL
);
INSERT INTO mv_lab.orders VALUES
    (1, 'east', 10), (2, 'east', 20), (3, 'west', 7);
CREATE MATERIALIZED VIEW mv_lab.region_totals AS
SELECT region, count(*) AS order_count, sum(amount) AS total
FROM mv_lab.orders
GROUP BY region;
SELECT region, order_count, total
FROM mv_lab.region_totals ORDER BY region;

east|2|30
west|1|7

Le groupe east contient deux commandes pour un total de 30 ; west en contient une, d’un montant de 7. Créer la vue matérialisée sans WITH NO DATA exécute sa requête de définition et enregistre le résultat. Les SELECT suivants lisent cette relation matérialisée.

La présentation des vues matérialisées PostgreSQL explique comment la requête conservée permet les rafraîchissements ultérieurs. Nous avons aussi vérifié le catalogue : la relation était de type vue matérialisée et déclarée peuplée.

Distinguez trois notions proches. Une vue ordinaire évalue sa requête lors de sa lecture. Une vue matérialisée conserve un résultat entre les instructions. Une CTE écrite avec WITH ... AS MATERIALIZED concerne l’exécution d’une seule instruction ; elle ne crée pas d’objet persistant à rafraîchir. La documentation des CTE décrit ce fonctionnement limité à l’instruction.

Observer le décalage après les modifications sources

INSERT INTO mv_lab.orders VALUES (4, 'east', 5), (5, 'north', 12);
SELECT 'stored' AS source, region, order_count, total
FROM mv_lab.region_totals
UNION ALL
SELECT 'base' AS source, region, count(*), sum(amount)
FROM mv_lab.orders GROUP BY region
ORDER BY source, region;

base|east|3|35
base|north|1|12
base|west|1|7
stored|east|2|30
stored|west|1|7

La requête sur les données sources inclut maintenant north et une troisième commande east. Le récapitulatif enregistré contient toujours seulement east et west, avec un total de 30 pour east. La somme des groupes enregistrés reste 37, alors que les montants sources totalisent 54.

Ce comportement convient à une application qui peut lire un résultat préparé, à condition que ce décalage soit voulu. Un tableau de bord exigeant les dernières commandes validées n’a pas les mêmes besoins de fraîcheur qu’un rapport pouvant utiliser une photographie périodique des données.

Rafraîchir explicitement le résultat

REFRESH MATERIALIZED VIEW mv_lab.region_totals;
SELECT region, order_count, total
FROM mv_lab.region_totals ORDER BY region;

east|3|35
north|1|12
west|1|7

Le résultat enregistré inclut maintenant north et trois commandes east pour un total de 35. Les lignes sources restent dans leur table. Le rafraîchissement remplace le contenu de la vue matérialisée à partir de sa requête de définition ; cette procédure ne configure pas de maintenance automatique ou incrémentale.

Nous avons aussi tenté un UPDATE direct sur la vue matérialisée, que PostgreSQL a rejeté. Modifiez les données sources, puis rafraîchissez le résultat dérivé. Si une lecture doit présenter un ordre stable, ajoutez ORDER BY à son SELECT, comme dans toutes les lectures affichées ici.

Respecter les conditions du rafraîchissement concurrent

CREATE UNIQUE INDEX region_totals_region_key
ON mv_lab.region_totals (region);
UPDATE mv_lab.orders SET amount = 9 WHERE id = 3;
DELETE FROM mv_lab.orders WHERE region = 'north';
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_lab.region_totals;
SELECT region, order_count, total
FROM mv_lab.region_totals ORDER BY region;

east|3|35
west|1|9

Cette séquence crée un index unique sur region, porte le montant de west à 9, supprime la ligne source north et rafraîchit la vue de façon concurrente. Le résultat contient west à 9 et aucun groupe north. Nous avons comparé ce résultat à l’agrégat source dans les deux sens pour détecter aussi bien les lignes manquantes que les lignes en trop.

La référence de REFRESH MATERIALIZED VIEW précise les conditions : la vue doit déjà être peuplée et un index unique admissible doit porter sur des noms de colonnes et inclure toutes les lignes. Un index d’expression ou un index partiel ne satisfait pas cette exigence.

Nos tests d’erreur confirment les trois cas. Le rafraîchissement concurrent échoue sans index admissible, avec seulement un index unique sur lower(region), puis avec seulement un index unique partiel. Ce dernier échoue même si toutes les lignes de l’exemple satisfont son prédicat. L’index unique ordinaire sur la colonne permet le rafraîchissement.

Choisissez des colonnes qui identifient une ligne du résultat. Ici, GROUP BY produit une ligne par région non NULL. Un autre récapitulatif nécessite un index adapté à la structure réelle de son résultat ; ajouter un index unique arbitraire ne définit pas cette structure.

Comprendre l’effet sur les lecteurs

Nous avons testé deux connexions à la même vue. La première termine un rafraîchissement dans une transaction, puis garde volontairement celle-ci ouverte pendant huit secondes. Alors que son verrou de relation reste détenu, la seconde tente un SELECT avec un délai maximal d’attente du verrou de 500 millisecondes.

Rafraîchissement dans la transaction ouverte	Verrou observé	Lecteur distinct
Ordinaire	AccessExclusiveLock	Atteint le délai d’attente du verrou
CONCURRENTLY	ExclusiveLock	Renvoie le récapitulatif enregistré

Un second rafraîchissement concurrent atteint lui aussi le délai d’attente tant que la première transaction de rafraîchissement concurrent reste ouverte. La réussite du SELECT ne signifie donc pas que plusieurs rafraîchissements peuvent se chevaucher librement sur la même vue.

L’exercice isole la compatibilité des verrous et la durée de vie de la transaction. Il ne mesure ni la durée du rafraîchissement ni le débit en production. Les transactions maintenues ouvertes sont annulées après les vérifications. Gardez les transactions de rafraîchissement courtes et surveillez leurs échecs : CONCURRENTLY ne supprime pas toute possibilité d’attente.

Peupler une vue WITH NO DATA avant le mode concurrent

CREATE MATERIALIZED VIEW mv_lab.delayed_totals AS
SELECT region, sum(amount) AS total
FROM mv_lab.orders GROUP BY region
WITH NO DATA;
CREATE UNIQUE INDEX delayed_totals_region_key
ON mv_lab.delayed_totals (region);
SELECT matviewname, ispopulated FROM pg_matviews
WHERE schemaname = 'mv_lab' AND matviewname = 'delayed_totals';
REFRESH MATERIALIZED VIEW mv_lab.delayed_totals;
SELECT matviewname, ispopulated FROM pg_matviews
WHERE schemaname = 'mv_lab' AND matviewname = 'delayed_totals';

delayed_totals|f
delayed_totals|t

La première lecture du catalogue indique false ; le rafraîchissement ordinaire fait passer la valeur à true. WITH NO DATA crée une vue non peuplée, dont les lignes ne peuvent pas être lues avant son peuplement. La référence de CREATE MATERIALIZED VIEW décrit cette option.

Nos vérifications supplémentaires remettent la vue dans cet état avec REFRESH ... WITH NO DATA. Un SELECT échoue alors, tout comme un rafraîchissement concurrent malgré l’index unique existant. Un rafraîchissement ordinaire rétablit le peuplement, après quoi le mode concurrent fonctionne. Nous avons aussi vérifié l’interdiction de combiner CONCURRENTLY et WITH NO DATA.

Pour définir l’objet avant d’exécuter sa requête, créez-le sans données, ajoutez l’index requis, peuplez-le par un rafraîchissement ordinaire, puis utilisez le mode concurrent lorsque cela convient. Prévoyez le peuplement initial séparément des rafraîchissements suivants.

Accorder le privilège nécessaire au rôle de rafraîchissement

CREATE ROLE mv_refresh NOLOGIN;
GRANT USAGE ON SCHEMA mv_lab TO mv_refresh;
GRANT MAINTAIN ON TABLE mv_lab.region_totals TO mv_refresh;
SET ROLE mv_refresh;
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_lab.region_totals;
RESET ROLE;
SELECT region, order_count, total
FROM mv_lab.region_totals ORDER BY region;

east|3|35
west|1|9

Dans PostgreSQL 18, MAINTAIN sur la vue matérialisée autorise son rafraîchissement. Le rôle de test reçoit aussi USAGE sur le schéma pour pouvoir désigner l’objet. NOLOGIN en fait un rôle d’autorisation pour cet exercice ; l’administrateur l’adopte avec SET ROLE.

Un autre rôle sans MAINTAIN s’est vu refuser le rafraîchissement. Le rôle autorisé a réussi sans droit SELECT direct sur la table orders, et sa tentative de lecture directe de cette table a été refusée. Dans cet exemple isolé, la vue matérialisée reste la propriété de l’administrateur.

Ces résultats concernent ces droits, ce propriétaire et PostgreSQL 18. Ils ne configurent pas l’authentification de l’application ni les autorisations de toutes les fonctions qu’une requête de définition pourrait appeler. Choisissez explicitement l’identité et le propriétaire utilisés en production, puis vérifiez la requête complète dans cette configuration.

Évaluer l’intérêt de maintenir le résultat

Mesurez à la fois les requêtes servies par la vue matérialisée et le travail nécessaire à son rafraîchissement. Ce petit jeu de données vérifie la justesse des résultats, pas un gain de vitesse. Notre guide PostgreSQL EXPLAIN ANALYZE, en anglais, aide à examiner les plans réels ; EXPLAIN ANALYZE exécute l’instruction analysée. Le guide pg_stat_statements, en anglais, aide à repérer les requêtes répétées à examiner.

Avant de planifier les rafraîchissements, définissez l’âge maximal acceptable des données, le responsable de l’opération, une alerte d’échec et le comportement des lecteurs si le dernier rafraîchissement devient trop ancien. Enregistrez la réussite du rafraîchissement séparément des horodatages des données sources. L’indicateur de peuplement dit que l’objet peut être lu ; il ne garantit pas que son contenu respecte un objectif de fraîcheur.

Supprimer les objets de l’exercice

REVOKE MAINTAIN ON TABLE mv_lab.region_totals FROM mv_refresh;
REVOKE USAGE ON SCHEMA mv_lab FROM mv_refresh;
DROP ROLE mv_refresh;
DROP MATERIALIZED VIEW mv_lab.delayed_totals;
DROP MATERIALIZED VIEW mv_lab.region_totals;
DROP TABLE mv_lab.orders;
DROP SCHEMA mv_lab;

Les vérifications confirment que le rafraîchissement a préservé les quatre commandes sources restantes, pour un total de 44. Le nettoyage supprime le rôle de test, les vues matérialisées, la table et le schéma, puis le serveur isolé est arrêté. Les vérifications téléchargeables incluent l’expérience à deux sessions et les cas d’erreur attendus.