Stocker des données dérivées dans PostgreSQL : colonnes générées contre vues matérialisées

Ce que c'est

La documentation dit d'une colonne générée qu'elle est « pour les colonnes ce qu'une vue est pour les tables » : GENERATED ALWAYS AS (expr) avec VIRTUAL (calculée à la lecture, sans stockage ; le comportement par défaut depuis PostgreSQL 18) ou STORED (calculée lors de l'insertion et de la mise à jour, et écrite sur disque). L'expression ne peut utiliser que des fonctions immuables, ne peut pas référencer une autre colonne générée, une sous-requête ou une autre table, et la colonne ne peut pas être écrite directement. Une vue matérialisée stocke la sortie d'une requête arbitraire ; REFRESH MATERIALIZED VIEW remplace entièrement son contenu, et la vue ne peut pas être mise à jour directement.

Pourquoi c'est important

Les deux remplacent un recalcul dans le code applicatif, et les deux peuvent dériver de l'intention : une colonne générée stockée coûte du temps d'écriture et de l'espace à chaque mise à jour, une vue matérialisée renvoie des lignes périmées jusqu'à son prochain rafraîchissement. Choisir la mauvaise option produit soit des écritures lentes, soit des lectures fausses.

Comment l'appliquer

• Normalisation par ligne (lower(email), un tsvector construit à partir du titre et du corps, un horodatage regroupé par intervalle) : une colonne générée STORED, indexée comme n'importe quelle autre colonne ; la valeur est toujours à jour. Un index d'expression est l'alternative quand aucune colonne n'est nécessaire, mais la requête doit alors répéter l'expression exactement.
• Valeurs par ligne bon marché et rarement filtrées (conversions d'unités, un nom d'affichage concaténé à partir de plusieurs parties) : une colonne générée virtuelle, qui ne coûte rien à l'écriture.
• Agrégats sur de nombreuses lignes (totaux journaliers, classements, jointures dénormalisées pour un tableau de bord) : une vue matérialisée avec un index unique sur sa clé, rafraîchie par un job planifié avec REFRESH MATERIALIZED VIEW CONCURRENTLY. La documentation exige un index unique sur des noms de colonnes, sans expression ni WHERE, pour CONCURRENTLY, et note qu'un rafraîchissement ordinaire peut bloquer les lecteurs.
• Afficher l'heure du rafraîchissement à côté des données servies depuis une vue matérialisée, et alerter lorsque le job de rafraîchissement ne s'est pas exécuté.
• Lorsqu'une synthèse doit être à jour alors que les tables de base changent constamment, maintenir une table de synthèse dans la même transaction que l'écriture ou via des triggers ; une vue matérialisée rafraîchie chaque minute est un recalcul complet chaque minute.

Pièges

WITH NO DATA laisse une vue matérialisée dans un état non interrogeable jusqu'au premier rafraîchissement, et CONCURRENTLY ne peut pas être utilisé pour ce premier remplissage ; un seul rafraîchissement à la fois peut s'exécuter par vue. Les colonnes générées virtuelles ne peuvent pas utiliser de types ou de fonctions définis par l'utilisateur. Une colonne générée ne peut pas faire partie d'une clé de partition et ne peut pas avoir de valeur par défaut ni de définition d'identité.