09 - Vues, vues materialisees et generated columns

Ce que tu vas apprendre

• Les vues classiques (raccourci de requête)
• Les vues materialisees (cache de requête complexe sur disque)
• REFRESH CONCURRENTLY et stratégies de rafraichissement
• Les colonnes générées (GENERATED ALWAYS AS ... STORED)

Prerequisites

Avoir lu les articles sur l'aggregation et les index.

Les vues : un SELECT sauvegarde

Une vue, c'est une requête nommee. Elle ne stocke pas de donnees, elle exécuté la requête a chaque appel :

sqlCREATE VIEW active_orders AS
  SELECT o.id, o.total, o.status, o.created_at,
         c.name AS customer_name, c.email AS customer_email
  FROM orders o
  JOIN customers c ON o.customer_id = c.id
  WHERE o.status != 'cancelled'
  AND o.deleted_at IS NULL;

Utilisation :

sql-- Comme une table normale
SELECT * FROM active_orders WHERE total > 100 ORDER BY created_at DESC;

-- Avec un JOIN
SELECT ao.*, oi.product_name
FROM active_orders ao
JOIN order_items oi ON oi.order_id = ao.id;

C'est un raccourci. Chaque fois que tu fais SELECT * FROM active_orders, PostgreSQL remplace la vue par la requête sous-jacente et l'exécuté. Pas de cache, pas de stockage supplementaire.

Les avantages :

• Simplifier les requêtes repetitives (tu ne retapes pas le JOIN + WHERE a chaque fois)
• Limiter l'acces aux donnees (une vue peut exposer 3 colonnes d'une table qui en a 20)
• Avoir une "API SQL" stable meme si la structure des tables change

Vues updatable

Les vues simples (un seul FROM, pas de GROUP BY, pas de DISTINCT, pas de JOIN dans certains cas) sont modifiables :

sqlCREATE VIEW pending_orders AS
  SELECT * FROM orders WHERE status = 'pending';

-- Ca marche :
INSERT INTO pending_orders (customer_id, total) VALUES (1, 75.00);
UPDATE pending_orders SET total = 80.00 WHERE id = 10;
DELETE FROM pending_orders WHERE id = 10;

Si la vue est plus complexe (avec des JOINs), il faut un trigger INSTEAD OF pour gerer les modifications (voir l'article 08).

Vues materialisees

La, ca devient interessant pour la performance. Une vue materialisee stocke le résultat sur disque :

sqlCREATE MATERIALIZED VIEW monthly_stats AS
  SELECT
    date_trunc('month', o.created_at)::DATE AS month,
    COUNT(*) AS nb_orders,
    SUM(o.total) AS revenue,
    COUNT(DISTINCT o.customer_id) AS unique_customers,
    AVG(o.total)::NUMERIC(10,2) AS avg_order_value
  FROM orders o
  WHERE o.status = 'completed'
  GROUP BY date_trunc('month', o.created_at)
  ORDER BY month;

sqlSELECT * FROM monthly_stats;

   month    | nb_orders | revenue  | unique_customers | avg_order_value
------------+-----------+----------+------------------+-----------------
 2025-01-01 |       234 | 28560.00 |              189 |          122.05
 2025-02-01 |       312 | 35120.50 |              245 |          112.57
 2025-03-01 |       287 | 31890.00 |              220 |          111.11

Cette requête n'est exécutée qu'une fois (a la création et a chaque REFRESH). Quand tu interroges la vue materialisee, PostgreSQL lit les donnees deja calculees sur disque. C'est instantane, meme si la requête sous-jacente prend 30 secondes.

REFRESH

Les donnees de la vue materialisee ne se mettent pas à jour toutes seules. Il faut un REFRESH :

sql-- Refresh basique (prend un lock exclusif, bloque les lectures)
REFRESH MATERIALIZED VIEW monthly_stats;

-- Refresh concurrent (pas de lock, les lectures continuent pendant le refresh)
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_stats;

CONCURRENTLY a une contrainte : la vue materialisee doit avoir un index unique :

sqlCREATE UNIQUE INDEX idx_monthly_stats_month ON monthly_stats (month);
-- Maintenant CONCURRENTLY fonctionne

Sans cet index unique, PostgreSQL ne peut pas faire le diff entre l'ancien et le nouveau résultat pour un refresh non-bloquant.

Stratégies de refresh

Quand rafraichir la vue materialisee ? Ca depend du cas d'usage :

Cron (pg_cron ou cron système) : le plus simple. Rafraichir toutes les heures, tous les jours a 3h du matin, etc.

sql-- Avec pg_cron (extension PostgreSQL)
SELECT cron.schedule('refresh_monthly_stats', '0 3 * * *',
  'REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_stats');

Apres un événement : rafraichir apres un INSERT massif (import batch, fin de journee).

A la demande : un endpoint API ou un bouton admin qui déclenché le refresh. Parfait pour les dashboards ou l'utilisateur clique "actualiser".

En pratique, le cron nocturne couvre la majorite des cas. Les vues materialisees ne sont pas faites pour des donnees temps réel. Si tu as besoin de donnees a la seconde pres, utilise une requête directe (optimisee avec les bons index).

Quand utiliser une vue materialisee

Bons cas :

• Rapports et dashboards qui agregent beaucoup de donnees
• Statistiques qui n'ont pas besoin d'etre en temps réel
• Requetes complexes avec plusieurs JOINs et aggregations sur des millions de lignes
• Recherche full-text pre-calculee

Mauvais cas :

• Donnees qui changent toutes les secondes et doivent etre fraiches
• Petites tables (une vue normale suffit)
• Donnees spécifiques a un utilisateur (la vue materialisee est partagee)

Generated columns

Depuis PostgreSQL 12, tu peux avoir des colonnes calculees automatiquement :

sqlCREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  price_ht NUMERIC(10,2) NOT NULL,
  tax_rate NUMERIC(5,4) DEFAULT 0.20,
  price_ttc NUMERIC(10,2) GENERATED ALWAYS AS (price_ht * (1 + tax_rate)) STORED
);

INSERT INTO products (name, price_ht) VALUES ('Clavier', 89.99);
SELECT name, price_ht, tax_rate, price_ttc FROM products;

   name    | price_ht | tax_rate | price_ttc
-----------+----------+----------+-----------
 Clavier   |    89.99 |   0.2000 |   107.988

La colonne price_ttc se recalcule automatiquement a chaque INSERT et UPDATE. Tu ne peux pas la modifier directement (PostgreSQL refuse).

STORED signifie que la valeur est stockee sur disque (PostgreSQL ne supporte pas VIRTUAL pour l'instant, contrairement a MySQL). Le calcul ne se fait qu'a l'écriture, pas a la lecture.

On peut aussi ajouter une generated column a une table existante :

sqlALTER TABLE orders ADD COLUMN total_with_tax NUMERIC(10,2)
  GENERATED ALWAYS AS (total * 1.20) STORED;

Et on peut indexer une generated column comme n'importe quelle autre colonne :

sqlCREATE INDEX idx_price_ttc ON products (price_ttc);

Comparaison des approches

Les trois sont complémentaires. Sur un projet typique, j'utilise des vues pour simplifier les requêtes, des vues materialisees pour les dashboards, et des generated columns pour les prix TTC et les champs de recherche normalises (LOWER(email) en generated column au lieu d'un index sur expression).

Sur paltemps.fr, les vues materialisees servent aux pages de statistiques publiques. Elles se rafraichissent toutes les nuits, et les pages se chargent en moins de 50ms malgre des agregations sur des tables de plusieurs centaines de milliers de lignes.

Résumé

• Les vues sont des requêtes nommees, exécutées a chaque appel (pas de cache)
• Les vues materialisees stockent le résultat sur disque (REFRESH pour mettre à jour)
• REFRESH CONCURRENTLY évité de bloquer les lectures (besoin d'un index unique)
• Les generated columns calculent une valeur automatiquement a chaque écriture
• Vue = raccourci, vue materialisee = cache, generated column = calcul par ligne

Article précédent : 08 - Les triggers Article suivant : 10 - JSON et JSONB

Sources

• PostgreSQL Documentation - CREATE VIEW
• PostgreSQL Documentation - Materialized Views
• PostgreSQL Documentation - Generated Columns
• Citus Data - Materialized Views Tips