data / sql

Vues matérialisées avancées : rafraîchissement et dépendances

Leçon 231 exercice

Explication

Ce que vous allez apprendre

Chaîner plusieurs vues matérialisées dépendantes en respectant l'ordre de rafraîchissement
Utiliser REFRESH MATERIALIZED VIEW CONCURRENTLY pour ne pas bloquer les lecteurs
Planifier un rafraîchissement automatique périodique avec pg_cron
Construire une alternative incrémentale par trigger, toujours à jour à coût constant
Choisir entre recalcul périodique et mise à jour incrémentale selon le besoin métier

Dans quel contexte ?

Un tableau de bord commercial affiche des ventes agrégées par jour puis par mois, calculées à partir de deux vues matérialisées chaînées (ventes_par_jour puis ventes_par_mois). Une équipe a eu la mauvaise surprise de voir des chiffres mensuels incohérents après un rafraîchissement fait dans le désordre. Cette leçon explique comment automatiser correctement ce rafraîchissement, sans bloquer les utilisateurs qui consultent le tableau de bord en même temps.

D'abord, rappel de la situation de départ

La leçon sur les vues a présenté la vue matérialisée comme un résultat "figé dans le temps", à rafraîchir manuellement. En production réelle, cette simplicité pose vite deux questions concrètes : que faire quand une vue matérialisée dépend d'une AUTRE vue matérialisée ? Et comment rafraîchir sans bloquer les utilisateurs qui consultent le rapport pile à ce moment-là ?

Étape 1 : des vues en chaîne, comme des dominos

Une vue "ventes par mois" peut être calculée à partir d'une vue "ventes par jour" déjà existante, plutôt que de repartir des données brutes à chaque fois. Mais cette chaîne impose une règle stricte : toujours rafraîchir dans l'ordre des dépendances, d'abord "par jour", ensuite "par mois" — sinon on obtient des chiffres calculés sur des données déjà obsolètes.

Il reste un problème : le blocage pendant le calcul

Un REFRESH classique verrouille la vue pendant tout le recalcul : personne ne peut la lire à ce moment-là, ce qui peut gêner de vrais utilisateurs.

Étape 2 : rafraîchir sans bloquer personne

REFRESH MATERIALIZED VIEW CONCURRENTLY évite ce blocage, au prix d'une contrainte technique précise : il exige un index UNIQUE sur la vue. C'est un compromis à connaître avant de se retrouver bloqué en production sans savoir pourquoi la commande échoue.

Stratégie	Toujours à jour ?	Bloque les lecteurs ?	Coût
`REFRESH MATERIALIZED VIEW`	Non, jusqu'au prochain refresh	Oui, pendant le calcul	Recalcul complet
`REFRESH ... CONCURRENTLY`	Non, jusqu'au prochain refresh	Non (nécessite un index UNIQUE)	Recalcul complet
Trigger incrémental	Oui, instantanément	Non	O(1) par écriture, logique dédiée par cas

Piège fréquent

Lancer REFRESH MATERIALIZED VIEW CONCURRENTLY sur une vue sans index UNIQUE échoue avec une erreur explicite ("cannot refresh materialized view concurrently"). Il faut créer l'index unique correspondant avant de pouvoir utiliser cette option.

Étape 3 : automatiser, plutôt que rafraîchir à la main

Une fois qu'on sait rafraîchir proprement, l'étape suivante est de ne plus avoir à y penser : pg_cron permet de planifier ce rafraîchissement à intervalle régulier, directement depuis SQL.

Pour aller plus loin : ne jamais tout recalculer

Une dernière approche change complètement de stratégie : plutôt que de recalculer l'intégralité d'un total à chaque rafraîchissement, un trigger peut mettre à jour une table de résumé au fil de l'eau, à chaque ligne insérée. Le résultat reste alors toujours à jour instantanément, à coût constant par écriture — mais demande d'écrire une logique dédiée pour chaque type de modification (INSERT, UPDATE, DELETE), un vrai compromis entre simplicité et performance.

Bonne pratique

Réserve le rafraîchissement incrémental par trigger aux métriques vraiment critiques en temps réel ; pour un tableau de bord rafraîchi toutes les 15 minutes, pg_cron + REFRESH CONCURRENTLY reste largement plus simple à maintenir.

Vers la suite

Cette idée de distribuer le travail plutôt que de le centraliser annonce directement le sujet de la leçon suivante : le sharding, qui distribue cette fois les données elles-mêmes sur plusieurs serveurs.

Commandes & code

Vues matérialisées avancées

Stratégies de rafraîchissement, chaînage de vues dépendantes et alternative incrémentale par trigger.

-- Lister les vues matérialisées existantes et leur état
SELECT schemaname, matviewname, ispopulated FROM pg_matviews;

-- Vue matérialisée de base, avec index unique requis pour le rafraîchissement concurrent
CREATE MATERIALIZED VIEW ventes_par_jour AS
SELECT DATE_TRUNC('day', cree_le) AS jour, SUM(montant) AS total
FROM commandes
GROUP BY DATE_TRUNC('day', cree_le);

CREATE UNIQUE INDEX ON ventes_par_jour (jour);

-- Vue matérialisée DÉPENDANTE d'une autre vue matérialisée (chaînage)
CREATE MATERIALIZED VIEW ventes_par_mois AS
SELECT DATE_TRUNC('month', jour) AS mois, SUM(total) AS total
FROM ventes_par_jour
GROUP BY DATE_TRUNC('month', jour);

-- REFRESH CONCURRENTLY : ne bloque pas les lecteurs (exige l'index unique ci-dessus)
REFRESH MATERIALIZED VIEW CONCURRENTLY ventes_par_jour;
REFRESH MATERIALIZED VIEW CONCURRENTLY ventes_par_mois;  -- toujours rafraîchir dans l'ordre des dépendances

-- Planifier un rafraîchissement automatique périodique
CREATE EXTENSION IF NOT EXISTS pg_cron;

SELECT cron.schedule(
    'refresh-ventes-par-jour',
    '*/15 * * * *',                 -- toutes les 15 minutes
    $$REFRESH MATERIALIZED VIEW CONCURRENTLY ventes_par_jour$$
);

-- Alternative : rafraîchissement incrémental piloté par trigger (jamais de recalcul complet)
CREATE TABLE ventes_par_jour_incr (
    jour  DATE PRIMARY KEY,
    total NUMERIC NOT NULL DEFAULT 0
);

CREATE OR REPLACE FUNCTION maj_ventes_par_jour_incr()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO ventes_par_jour_incr (jour, total)
    VALUES (DATE_TRUNC('day', NEW.cree_le)::DATE, NEW.montant)
    ON CONFLICT (jour) DO UPDATE SET total = ventes_par_jour_incr.total + NEW.montant;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_maj_ventes_incr
AFTER INSERT ON commandes
FOR EACH ROW EXECUTE FUNCTION maj_ventes_par_jour_incr();
-- Avantage : toujours à jour, coût O(1) par insertion, pas de recalcul global
-- Inconvénient : ne gère pas nativement les UPDATE/DELETE sans triggers supplémentaires

-- Taille de chaque vue matérialisée, utile pour arbitrer coût de stockage vs coût de recalcul
SELECT matviewname, pg_size_pretty(pg_total_relation_size('public.' || matviewname)) AS taille
FROM pg_matviews;

-- Supprimer proprement une chaîne de vues matérialisées dépendantes (ordre inverse de création)
DROP MATERIALIZED VIEW IF EXISTS ventes_par_mois;
DROP MATERIALIZED VIEW IF EXISTS ventes_par_jour;

Résumé

Une vue matérialisée peut dépendre d'une autre : rafraîchir dans l'ordre des dépendances, jamais en parallèle non coordonné.
REFRESH MATERIALIZED VIEW CONCURRENTLY exige un index UNIQUE et évite de bloquer les lecteurs.
pg_cron permet de planifier des rafraîchissements périodiques directement en base.
Un rafraîchissement incrémental par trigger reste toujours à jour à coût O(1) par écriture, au prix d'une logique applicative dédiée par type de mise à jour.

Exercices pratiques

1 disponible

Mission : corriger un tableau de bord aux chiffres mensuels incohérents

Objectif : Diagnostiquer un rafraîchissement de vues matérialisées chaînées fait dans le mauvais ordre, puis automatiser un rafraîchissement sans blocage.

Contexte

Le tableau de bord commercial affiche ventes_par_jour puis ventes_par_mois, cette dernière calculée à partir de la première. Cette nuit, un script de maintenance a lancé REFRESH MATERIALIZED VIEW ventes_par_mois; avant REFRESH MATERIALIZED VIEW ventes_par_jour;, et les commerciaux se plaignent que le total du mois en cours ne correspond pas à la somme des jours affichés. Il faut aussi que ce rafraîchissement ne bloque plus les commerciaux qui consultent le dashboard au même moment.

Résoudre l’exercice →