Dénormalisation dans les bases de données : Quand et comment l'utiliser

J'aibeaucoup écrit sur la normalisation et sur les raisons pour lesquelles elle constitue une base fiable pour l'intégrité des données. Je ne me contredis pas ici. Je continue de croire qu'un schéma bien normalisé constitue le point de départ adéquat pour la plupart des systèmes transactionnels. Cet article traite du choix délibéré d'aller à l'encontre de cette pureté dans des situations très spécifiques où les performances de lecture sont plus importantes que la forme normale stricte.

La dénormalisation ne consiste pas à ignorer la normalisation. Il s'agit d'une optimisation des performances que vous appliquez à un modèle normalisé lorsque des requêtes réelles, des utilisateurs réels et des accords de niveau de service réels indiquent que les jointures et les calculs à la volée sont trop lents ou trop coûteux. En pratique, vous échangez des lectures plus rapides et des requêtes plus simples contre davantage de stockage, des écritures plus complexes et un travail supplémentaire en matière de cohérence.

Dans cet article, je vais vous expliquer dans quels cas la dénormalisation est utile et dans quels cas elle ne l'est pas, ainsi que la manière de la mettre en œuvre en toute sécurité dans les bases de données SQL. L'objectif n'est pas de renoncer à un design de qualité, mais d'y ajouter des raccourcis lorsque la charge de travail le justifie.

Qu'est-ce que la dénormalisation dans les bases de données ?

Si vous êtes familier avec la normalisation et la conception de bases de données, voici une réponse succincte : 

La dénormalisation est l'action délibérée d'ajouter des données redondantes à un schéma précédemment normalisé afin d'accélérer les lectures et de simplifier les requêtes. Il s'agit d'une optimisation ciblée des performances, et non d'une excuse pour négliger une modélisation de qualité.

Si vous êtes novice en matière de conception de bases de données, il pourrait être utile de clarifier les termes « normalisé », « dénormalisé » et « non normalisé ». Ces trois termes sont fréquemment utilisés en ligne, et il est important de ne pas les confondre.

Normalisé : Les données sont réparties dans des tableaux bien structurés qui minimisent la redondance et protègent l'intégrité de l'(voir 3NF/BCNF).

Dénormalisé : Vous réintroduisez une redondance sélective avec des colonnes supplémentaires, des valeurs précalculées ou des tableaux préjoints, en plus de ce modèle normalisé, afin d'accélérer les lectures courantes. Avec la dénormalisation, vous conservez une source unique de vérité (les tableaux normalisés), puis vous maintenez une ou plusieurs représentations plus rapides pour les chemins d'accès fréquents, tels que les tableaux de bord, les listes de produits, la recherche, etc. Vous échangez la complexité du stockage et de l'écriture contre la vitesse de lecture et des requêtes plus simples.

Non normalisé : Données brutes, ponctuelles ou désorganisées, dont la structure et les contraintes n'ont jamais été correctement conçues. Ce n'est pas ce que nous faisons ici.

Exemple en SQL

Voici un petit exemple en SQL pour vous aider à visualiser la différence entre la modélisation normalisée et dénormalisée.

Modélisation normalisée

-- Source of truth
CREATE TABLE customers (
  customer_id   BIGINT PRIMARY KEY,
  name          TEXT NOT NULL,
  tier          TEXT NOT NULL
);

CREATE TABLE orders (
  order_id      BIGINT PRIMARY KEY,
  customer_id   BIGINT NOT NULL REFERENCES customers(customer_id),
  order_total   NUMERIC(12,2) NOT NULL,
  created_at    TIMESTAMP NOT NULL DEFAULT now()
);

-- Typical report needs a join
SELECT c.name, c.tier, SUM(o.order_total) AS revenue
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
GROUP BY c.name, c.tier;

Dénormalisé pour un chemin de rapport

-- Add redundant fields for faster reads
ALTER TABLE orders
  ADD COLUMN customer_name TEXT,
  ADD COLUMN customer_tier TEXT;

-- Now most reports avoid the join
SELECT customer_name, customer_tier, SUM(order_total) AS revenue
FROM orders
GROUP BY customer_name, customer_tier;

Dans cet exemple, vous conserverez les clients comme source de vérité et vous vous assurerez que orders.customer_name / orders.customer_tier, etrestent synchronisés (par exemple, déclencheur, tâche CDC ou remplissage programmé). Même réalité, deux représentations, chacune optimisée pour une tâche différente.

La dénormalisation constitue-t-elle une conception inadéquate ?

Il existe une idée fausse courante selon laquelle la dénormalisation est simplement le résultat d'une mauvaise conception. Ce n'est pas le cas si cela est mesuré, évalué et entretenu. Oui, la dénormalisation enfreint souvent les formes normales supérieures (c'est le but recherché), mais elle est intentionnelle et s'appuie sur un plan visant à préserver la cohérence. Une mauvaise conception consiste à ignorer complètement la normalisation ou à introduire des redondances sans stratégie de synchronisation.

Si vous souhaitez approfondir vos connaissances en matière de conception de bases de données , notre cours pour débutants sur lesujet constitue un excellent point de départ.

Normalisation et dénormalisation

Voici la comparaison côte à côte. Cette liste n'est pas exhaustive, mais elle vous aidera à mieux appréhender les avantages et les inconvénients de chaque approche.

Pourquoi et quand dénormaliser

La dénormalisation s'avère particulièrement utile lorsque les utilisateurs réels et les requêtes réelles sont bloqués par des jointures, des agrégations ou des recherches répétées. Une fois que vous avez confirmé que l'indexation, l'optimisation des requêtes et la mise en cache ne suffisent pas, vous pouvez envisager la dénormalisation afin d'optimiser la vitesse de lecture pour les modèles d'accès prévisibles.

Quand la dénormalisation peut être utile

• Charges de travail à forte intensité de lecture: latences p95/p99 dominées par les jointures ou les agrégations, CPU utilisé pour les jointures par hachage/fusion, taux de rotation élevé du cache tampon.
• Formes de requêtes stables: Les mêmes tableaux de bord/points de terminaison fonctionnent toute la journée avec des filtres similaires (par exemple, les ventes d'hier par catégorie).
• Joints en éventail: Un tableau actif est jointe à 3-5 autres uniquement pour afficher une carte ou une liste.
• Points d'agrégation: Vous calculez de manière répétée des totaux, des nombres ou les dernières valeurs sur de grandes plages.
• Pression SLA: Le produit nécessite des réponses inférieures à 200 ms lorsque les plans actuels sont transférés sur le disque ou effectuent trop d'analyses.

Cas d'utilisation classiques

• Tableaux de bord et rapports BI (OLAP/analyse): Précalculer les agrégats quotidiens/mensuels, conserver des vues matérialisées des regroupements coûteux ou stocker des tableaux de faits dénormalisées pour les tranches courantes.
• Commerce électronique/catalogue et pages de recherche/listing: Dupliquer nom_catégorie, nom_de_marque, prix_avec_taxe, ou une projection de produit pré-jointe pour des listes et des filtres rapides.
• CMS/blog/flux d'actualités: Magasin nom_auteur, primary_topicou extrait_rendu dans la table des articles/publications afin d'éviter les jointures ou les transformations d'exécution.
• Flux d'activité et compteurs: Conserver like_count, follower_count, ou latest_comment_at comme attributs dérivés au lieu de les recalculer.
• Analyse d'événements/journaux à grande échelle (OLAP/nosql): Aplatissez les données imbriquées pour les magasins en colonnes et conservez des lignes larges adaptées aux partitions afin de rendre les analyses prévisibles.

Quand ne pas dénormaliser et utiliser d'abord autre chose

• OLTP à forte intensité d'écriture avec cohérence stricte (commandes, paiements, ajustements d'inventaire).
• Le problème réside dans l'absence ou la mauvaise qualité des index, les requêtes N+1 ou un ORM trop bavard. Veuillez d'abord régler ces problèmes.
• Domaines à forte volatilité (par exemple, disponibilité des produits changeant à chaque instant) où la duplication amplifie le taux de désabonnement.
• Équipes sans plan clair en matière de propriété et de synchronisation (déclencheurs, CDC/tâches, politiques d'actualisation, surveillance des dérives). Sans cela, vous risquez de causer plus de dommages que de bienfaits.
• L'ensemble de données est suffisamment petit pour qu'un index couvrant ou un cache le rende déjà rapide.

Il est recommandé d'utiliser la dénormalisation lorsque cela vous offre la vitesse de lecture la plus élevée pour la charge opérationnelle supplémentaire la plus faible, et uniquement après avoir écarté les solutions moins coûteuses.

Si vous avez réalisé que la dénormalisation ne correspond pas tout à fait à vos besoins actuels, veuillez rester à l'écoute. Nous examinerons les alternatives à la dénormalisation dans la section suivante.

Solutions alternatives à la dénormalisation

La dénormalisation ne constitue pas la solution à tous vos problèmes. Avant d'ajouter de la redondance, il est nécessaire d'exploiter au maximum le moteur et votre application. Ces solutions sont plus économiques à entretenir et offrent souvent les mêmes avantages.

1) Indexation

• Indices composites/globaux : Dans vos requêtes SQL, veuillez placer les colonnes de filtre en premier, puis les colonnes GROUP BY /ORDER BY . J'ai inclus des colonnes de liste de sélection afin que le moteur puisse traiter la requête à partir de l'index uniquement.

• Index filtrés/partiels : Indexez uniquement la tranche active (par exemple, status = 'ACTIVE'), en veillant à ce que l'index reste petit et rapide.

• Indices d'expression/fonctionnels : Veuillez indexer sur LOWER(email) ou date_trunc('day', created_at) afin d'éviter les analyses calculées.

2) Optimisation des requêtes et pagination

• Veuillez éviter l'SELECT *. Veuillez récupérer uniquement ce que vous affichez.

• Remplacer les jointures inutiles par EXISTS/SEMI lorsque vous avez uniquement besoin de vérifier la présence.

• Appliquez les prédicats en aval : filtrez tôt, agrégez tard.

• Utiliser pagination par jeu de touches (WHERE created_at < ? ORDER BY created_at DESC LIMIT 50) pour un défilement stable et rapide.

3) Mise en cache

• Utiliser le cache d'application (par exemple, Redis) pour les requêtes fréquentes et les fragments rendus.
• Utilisation du La mise en cache HTTP (ETag/Last-Modified) pour les pages publiques et les tableaux de bord.
• Les caches de courte durée (30 à 120 secondes) éliminent souvent la nécessité de modifier le schéma.

4) Lire les répliques

• Transférez les lectures lourdes vers les répliques. Idéal pour les tableaux de bord et les exportations.

5) Partitionnement et élagage

• Utilisez l', les tableaux volumineux sont partitionnés par plage/hachage afin que vos analyses ne concernent que les partitions pertinentes (par exemple, les 30 derniers jours). Il ne s'agit pas ici de dénormalisation, mais simplement d'une réduction de la quantité de données analysées.

6) Stockages en colonnes/OLAP

• Transférez les analyses lourdes vers Snowflake/BigQuery/ClickHouse (via ELT/dbt). Maintenez la normalisation OLTP et laissez l'entrepôt gérer les formes larges et faciles à analyser.

7) Hygiène ORM

• Kill requêtes N+1 requêtes (chargement anticipé ou par lots), définissez des listes de sélection raisonnables listes de sélectionet limiter la taille des pages. Une couche ORM propre peut éliminer le besoin de dénormaliser.

8) Colonnes calculées/générées (gérées par la base de données)

• Permettez à la base de données de conserver les valeurs dérivées (par exemple, price_with_tax) en tant que colonnes générées/calculées ou via des index d'expression. Cela vous permettra d'obtenir des lectures rapides sans logique de synchronisation au niveau de l'application.

Techniques de dénormalisation

Voici les méthodes les plus courantes pour ajouter une redondance contrôlée à un modèle normalisé. Pour chacune d'entre elles, je vais vous expliquer son fonctionnement, quand l'utiliser et comment la synchroniser.

Configuration : supposons un noyau normalisé avec clients, commandes, articles_de_commande, produits.

1) Tableaux à « projection » aplatis / pré-assemblés

Quoi : Veuillez créer un tableau qui pré-joint les colonnes nécessaires pour une lecture rapide (par exemple, la liste des produits ou le tableau de bord des commandes).

Quand : Votre chemin d'accès actif relie 3 à 5 tableaux de manière prévisible.

Comment (PostgreSQL) :

-- Read-optimised projection for a typical orders list
CREATE TABLE orders_projection (
  order_id        BIGINT PRIMARY KEY,
  created_day     DATE NOT NULL,
  customer_id     BIGINT NOT NULL,
  customer_name   TEXT  NOT NULL,
  total_amount    NUMERIC(12,2) NOT NULL
);

-- Initial backfill
INSERT INTO orders_projection (order_id, created_day, customer_id, customer_name, total_amount)
SELECT o.order_id,
       o.created_at::date AS created_day,
       c.customer_id,
       c.name AS customer_name,
       SUM(oi.quantity * oi.unit_price) AS total_amount
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY o.order_id, created_day, c.customer_id, c.name;

-- Index for fast filtering by day/customer
CREATE INDEX ON orders_projection (created_day, customer_id);

Options de synchronisation :

• Tâche post-écriture (mise en file d'attente « order.updated » → recalcul de la ligne)
• Tâche batch nocturne de 15 minutes (dbt/cron)

2) Colonnes redondantes (copier quelques attributs)

Quoi : Dupliquez quelques attributs fréquemment consultés dans une autre table afin d'éviter les jointures (par exemple, orders.customer_name).

Quand : Vous n'avez besoin que d'une ou deux valeurs et ne souhaitez pas obtenir une projection complète.

Comment (exemple de déclencheur PostgreSQL) :

ALTER TABLE orders
  ADD COLUMN customer_name TEXT,
  ADD COLUMN customer_tier TEXT;

-- Keep the redundant fields correct on insert/update
CREATE OR REPLACE FUNCTION sync_order_customer_fields()
RETURNS TRIGGER AS $
BEGIN
  SELECT name, tier INTO NEW.customer_name, NEW.customer_tier
  FROM customers WHERE customer_id = NEW.customer_id;
  RETURN NEW;
END;
$ LANGUAGE plpgsql;

CREATE TRIGGER trg_orders_sync_customer
BEFORE INSERT OR UPDATE OF customer_id ON orders
FOR EACH ROW EXECUTE FUNCTION sync_order_customer_fields();

-- Propagate customer name/tier changes to existing orders
CREATE OR REPLACE FUNCTION propagate_customer_changes()
RETURNS TRIGGER AS $
BEGIN
  UPDATE orders
  SET customer_name = NEW.name,
      customer_tier = NEW.tier
  WHERE customer_id = NEW.customer_id;
  RETURN NEW;
END;
$ LANGUAGE plpgsql;

CREATE TRIGGER trg_customer_propagate
AFTER UPDATE OF name, tier ON customers
FOR EACH ROW EXECUTE FUNCTION propagate_customer_changes();

Compromis : La lecture est simple, mais l'écriture se disperse lorsqu'un client effectue une mise à jour.

3) Attributs dérivés (stocker les valeurs calculées)

Quoi : Conservez les valeurs que vous calculez habituellement à la volée (par exemple, price_with_tax , latest_comment_at, item_count).

Quand : Le calcul est effectué de manière continue et est déterministe.

Deux modèles courants :

• Colonne générée (la base de données recalcule lors de l'écriture : vous avez moins de contrôle, pas de références entre tableaux)
• Colonne stockée + déclencheur/tâche (vous contrôlez quand et comment elle change)

-- Generated column example (Postgres 12+; same-table expressions)
ALTER TABLE products
  ADD COLUMN price_with_tax NUMERIC(12,2) GENERATED ALWAYS AS (price * 1.20) STORED;

-- Counter maintained by triggers (likes per post)
ALTER TABLE posts ADD COLUMN like_count INTEGER NOT NULL DEFAULT 0;

CREATE OR REPLACE FUNCTION inc_like_count() RETURNS TRIGGER AS $
BEGIN
  UPDATE posts SET like_count = like_count + 1 WHERE post_id = NEW.post_id;
  RETURN NEW;
END; $ LANGUAGE plpgsql;

CREATE TRIGGER trg_like_insert
AFTER INSERT ON post_likes
FOR EACH ROW EXECUTE FUNCTION inc_like_count();

CREATE OR REPLACE FUNCTION dec_like_count() RETURNS TRIGGER AS $
BEGIN
  UPDATE posts SET like_count = GREATEST(like_count - 1, 0) WHERE post_id = OLD.post_id;
  RETURN OLD;
END; $ LANGUAGE plpgsql;

CREATE TRIGGER trg_like_delete
AFTER DELETE ON post_likes
FOR EACH ROW EXECUTE FUNCTION dec_like_count();

4) Tableaux récapitulatifs / synthétiques

Quoi : Précalculer les agrégats tels que le chiffre d'affaires quotidien, le nombre d'utilisateurs actifs par jour, les commandes par catégorie.

Quand : Les tableaux de bord reproduisent les mêmes regroupements et les analyses brutes sont coûteuses.

CREATE TABLE daily_sales (
  sales_day    DATE PRIMARY KEY,
  gross_amount NUMERIC(14,2) NOT NULL,
  order_count  INTEGER NOT NULL
);

-- Incremental upsert for "yesterday" (run hourly/15-min)
INSERT INTO daily_sales (sales_day, gross_amount, order_count)
SELECT (o.created_at AT TIME ZONE 'UTC')::date AS sales_day,
       SUM(oi.quantity * oi.unit_price)       AS gross_amount,
       COUNT(DISTINCT o.order_id)             AS order_count
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.created_at >= date_trunc('day', now() - interval '1 day')
  AND o.created_at <  date_trunc('day', now())
GROUP BY sales_day
ON CONFLICT (sales_day) DO UPDATE
  SET gross_amount = EXCLUDED.gross_amount,
      order_count  = EXCLUDED.order_count;

Options de synchronisation : tâche planifiée (cron/dbt), mises à jour en continu (CDC) ou agrégateurs événementiels.

5) Vues matérialisées (résultats de requêtes persistants)

Quoi : Veuillez stocker le résultat d'une requête complexe sous forme de tableau physique que vous pouvez indexer.

Quand : Le jeu de résultats est coûteux, relativement stable, et vous pouvez tolérer des fenêtres de rafraîchissement.

-- Expensive report
CREATE MATERIALIZED VIEW mv_category_sales AS
SELECT p.category_id,
       date_trunc('day', o.created_at) AS day,
       SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
GROUP BY p.category_id, date_trunc('day', o.created_at);

-- Make it fast to query
CREATE INDEX ON mv_category_sales (category_id, day);

-- Refresh patterns
-- Full, blocking:
REFRESH MATERIALIZED VIEW mv_category_sales;

-- Non-blocking reads (requires unique index on the MV):
-- 1) ensure a unique index exists (e.g., (category_id, day))
-- 2) then:
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_category_sales;

Compromis : Vous devrez gérer la fréquence d'actualisation, car l'actualisation incrémentielle n'est pas native dans Postgres. Cela dit, cela est relativement simple lorsque vous utilisez dbt ou des modèles personnalisés « append-only + windowed rebuild » (ajout uniquement + reconstruction par fenêtres).

6) Projections JSON « document » précalculées

Quoi : Stockez un blob JSON dénormalisé qui correspond aux besoins de votre API/interface utilisateur (par exemple, une fiche produit), ainsi que des index JSON ciblés.

Quand : Votre API fournit la même forme à plusieurs reprises, et vous souhaitez une seule lecture.

CREATE TABLE product_cards (
  product_id BIGINT PRIMARY KEY,
  card_json  JSONB NOT NULL
);

-- Build or rebuild card documents
INSERT INTO product_cards (product_id, card_json)
SELECT p.product_id,
       jsonb_build_object(
         'id', p.product_id,
         'name', p.name,
         'brand', b.name,
         'price_with_tax', p.price * 1.20,
         'category', c.name
       )
FROM products p
JOIN brands b    ON b.brand_id = p.brand_id
JOIN categories c ON c.category_id = p.category_id
ON CONFLICT (product_id) DO UPDATE SET card_json = EXCLUDED.card_json;

-- GIN index for JSON queries if needed
CREATE INDEX idx_product_cards_gin ON product_cards USING GIN (card_json);

Options de synchronisation : reconstruction en fonction des événements lors de modifications apportées aux produits/marques/catégories, ou actualisation fréquente par lots pour les produits récemment mis à jour.

Choisir une stratégie de synchronisation (guide rapide)

• Déclencheurs : Ils sont immédiats et garantissent la cohérence des transactions. Ils sont généralement très efficaces pour les petits fan-out et les faibles volumes d'écriture, mais ils peuvent nuire à la latence d'écriture s'ils sont utilisés de manière excessive.
• Application à double écriture : L'application gère à la fois la source et la dénormalisation. C'est simple, mais plus risqué. Vous pouvez atténuer ce problème grâce à des tentatives idempotentes et des modèles de boîte d'envoi/CDC.
• CDC → employé : Cette stratégie syn est fiable et évolutive, car elle propage les modifications de manière asynchrone. Il est idéal lorsque la cohérence finale est acceptable.
• Tâches planifiées : Option la plus simple pour les agrégats et les vues matérialisées. Veuillez sélectionner les fenêtres de rafraîchissement qui correspondent à votre niveau de tolérance en matière d'expérience utilisateur.

Je vous recommande d'intégrer systématiquement l'observabilité dans votre solution. Il n'y a rien de plus préoccupant que de ne pas être certain que les modifications ont été traitées ou répliquées correctement. Il peut également vous aider à prendre en compte vos vérifications de dérive et vos scripts de remplissage.

Comment mettre en œuvre la dénormalisation étape par étape

Comme toujours, il est important de noter que la modification de votre base de données comporte des risques. Je vous recommande vivement de répliquer votre base de données et d'essayer d'abord de mettre en œuvre la dénormalisation sur la réplique, afin de vous assurer que tout fonctionne comme prévu. Si vous disposez d'un environnement de développement, c'est encore mieux.

Le processus est assez simple : mesurer → modifier le moins possible → veiller à l'exactitude → vérifier → surveiller → répéter.

1) Définir et établir un objectif de réussite

• Veuillez enregistrer la requête exacte (texte + paramètres) et sa fréquence.
• Enregistrer une référence : EXPLAIN (ANALYZE, BUFFERS) plan, latence p95, CPU/E/S, lignes analysées.
• Veuillez convenir d'un critère de réussite (par exemple « p95 < 120 ms avec un coût d'écriture ≤ 1,2 × »).
• Veuillez confirmer la tolérance de cohérence (par exemple, « les analyses peuvent avoir un décalage de 5 minutes »).

2) Sélectionnez la dénormalisation la plus petite qui fonctionne

• Idéalement, il est recommandé d'essayer une colonne redondante ou un tableau récapitulatif pour une projection de grande envergure.
• Esquissez la nouvelle forme :
• Colonnes redondantes ? (lesquelles, pourquoi)
• Tableau agrégé ou vue matérialisée ? (grain, clés, fenêtre de rafraîchissement)
• Déterminez le modèle de cohérence :
• Fort (déclencheur/transactionnel) par opposition à éventuel (CDC/tâche/actualisation de vue matérialisée).
• Veuillez noter le budget d'amplification d'écriture (nombre d'écritures supplémentaires acceptables par événement).

3) Concevoir le chemin de synchronisation

• Déclencheurs (cohérence forte, faible fan-out).
• CDC/boîte d'envoi → employé (évolutif, cohérence finale) :
• L'application effectue des écritures dans les tableaux sources et dans une ligne de la boîte d'envoi au cours de la même transaction.
• Un employé consulte la boîte d'envoi et met à jour la cible dénormalisée de manière idempotente.
• Tâches planifiées / Actualisation MV (idéale pour les agrégats) :
• Définissez la fréquence d'actualisation, le fenêtrage et la stratégie de remplissage.

L'idempotence est non négociable. Les mises à jour doivent pouvoir être répétées en toute sécurité (par exemple, UPSERT avec recalcul déterministe). Faites-moi confiance, vous vous en féliciterez plus tard.

4) Créer, compléter et valider

1. Veuillez créer des structures (tableaux, colonnes, index, déclencheurs/tâches) tout en conservant l'application inchangée.
2. Remplissage à partir de la source de vérité.
3. Vérifier la parité avec des invariants :

-- Example parity check: orders_projection vs live join
SELECT COUNT(*) AS mismatches
FROM orders_projection p
JOIN orders o   ON o.order_id = p.order_id
JOIN customers c ON c.customer_id = o.customer_id
WHERE p.customer_name <> c.name
   OR p.total_amount <> (
        SELECT SUM(oi.quantity * oi.unit_price)
        FROM order_items oi WHERE oi.order_id = o.order_id
      );

4. Corrigez les incohérences ; relancez jusqu'à ce que le résultat soit nul (ou dans les limites d'une marge d'erreur convenue).

5) Effectuez la coupe en toute sécurité

• Déployez la modification de l'application derrière un indicateur de fonctionnalité ou un canary (par exemple, 10 % du trafic lit à partir du chemin dénormalisé).
• Exécutez des lectures fantômes : calculez l'ancien résultat en arrière-plan et comparez les hachages/agrégats pour un sous-ensemble de requêtes.
• Conservez une restauration rapide (revenez instantanément au chemin normalisé).

6) Surveillez les éléments pertinents

Veuillez créer un petit tableau de bord dans l'outil d'observabilité de votre choix :

• Veuillez noter: latence p50/p95, lignes lues, accès au tampon, stabilité du plan de requête.
• Écriture: temps d'écriture supplémentaire, erreurs de déclenchement/tâche, retard de file d'attente (CDC), horodatage de la dernière actualisation MV.
• Qualité des données: compteurs de dérive (vérifications quotidiennes de parité), nombre de lignes remplies, pourcentage de discordances.
• Coût/empreintetaille des tables, gonflement de l'index, augmentation de la taille des MV.

7) Exploiter et itérer

• Veuillez attribuer la responsabilité (équipe/système d'astreinte) et un manuel d'intervention (comment reconstruire, remplacer, réparer). Ce point est souvent négligé, mais il est important de pouvoir réagir le plus rapidement possible lorsqu'un incident survient. 
• Réévaluer tous les trimestres : est-ce toujours nécessaire ? La forme est-elle toujours adéquate ? Pourrions-nous simplifier ? Veuillez documenter la décision et ajouter un lien vers les tableaux de bord.

Avantages et inconvénients de la dénormalisation

Nous avons abordé les détails dans la section tableau Normalisation vs Dénormalisation dans une section précédente, voici donc un bref récapitulatif.

Ce que vous gagnez

• Lectures plus rapides et latences p95/p99 plus prévisibles
• Requêtes simplifiées sur les chemins fréquents (moins de jointures/agrégations)
• Réduction de la charge CPU/E/S par lecture et optimisation des coûts des requêtes analytiques

Quel est le coût ?

• Amplification d'écriture (mises à jour/insertions supplémentaires)
• Risque de cohérence (données obsolètes/dérivantes si la synchronisation n'est pas effectuée correctement)
• Augmentation du stockage (colonnes/tableaux/vues en double)
• Frais généraux opérationnels (actualisations, remplacements, surveillance)
• Modification du fan-out (les mises à jour du schéma/de la logique doivent être répliquées)

Outils et technologies qui favorisent la dénormalisation

Toutes les bases de données ne facilitent pas la dénormalisation de la même manière. Certains vous offrent des fonctionnalités de premier ordre pour conserver les résultats de requêtes coûteuses, tandis que d'autres vous demandent de créer vos propres tâches, déclencheurs ou pipelines.

PostgreSQL

Postgres vous offre plusieurs méthodes pour matérialiser des formes optimisées pour la lecture. 

Les vues matérialisées conservent le résultat d'une requête, ce qui permet une lecture instantanée. Vous choisissez quand effectuer l'actualisation (selon un calendrier ou de manière ponctuelle), et vous pouvez même l'effectuer simultanément afin que les lecteurs ne soient pas bloqués.

Pour une duplication allégée, les colonnes générées et les index d'expression permettent à la base de données de conserver les valeurs dérivées et d'accélérer les filtres courants sans avoir à écrire de logique de synchronisation supplémentaire. 

Lorsque vous avez besoin d'une forte cohérence entre la source et une copie dénormalisée, vous pouvez utiliser des déclencheurs pour synchroniser les éléments (mais n'oubliez pas qu'ils ajoutent du travail à votre chemin d'écriture). Pour les actualisations déclenchées par des événements ou par lots, il est possible d'associer Postgres à un planificateur (par exemple, pg_cron ou un exécuteur de tâches externe) ou à une réplication logique/CDC afin de transmettre les modifications à un module qui met à jour vos tables dénormalisées de manière asynchrone.

Si vous souhaitez vous familiariser avec la création de vos propres bases de données et tester la dénormalisation, nous vous invitons à consulternotre cours PostgreSQL.

Serveur SQL

SQL Server s'appuie fortement sur les vues indexées, qui sont en quelque sorte des vues matérialisées toujours actives, où le moteur synchronise la vue à chaque insertion/mise à jour/suppression, ce qui rend les lectures extrêmement rapides. En revanche, les écritures gèrent désormais à la fois les tableaux de base et la vue, ce qui peut ralentir les charges de travail OLTP importantes. 

En outre, vous pouvez utiliser des colonnes calculées persistantes pour couvrir les attributs dérivés à ligne unique. 

Pour les rapports à grande échelle, les index Columnstore constituent une alternative à la dénormalisation : ils compressent et analysent très efficacement les grands ensembles de données, ce qui peut éviter d'avoir à dupliquer les données.

Oracle

Les vues matérialisées d'Oracle constituent une option éprouvée avec une actualisation par l FAST, qui utilise les journaux de modifications pour mettre à jour uniquement ce qui a changé, ou une actualisation par l'COMPLETE, lorsque vous acceptez de tout reconstruire. 

Grâce à la réécriture des requêtes, l'optimiseur peut utiliser de manière transparente votre vue matérialisée lorsque quelqu'un interroge les tables sous-jacentes, ce qui permet aux équipes de bénéficier d'une accélération sans modifier le SQL de l'application. Comme toujours, une lecture plus rapide implique un surcroît de travail ailleurs : la gestion des journaux et l'actualisation des vues augmentent la charge de travail liée à l'écriture et aux opérations.

MySQL / MariaDB

MySQL ne dispose pas de vues matérialisées natives, de sorte que la dénormalisation est généralement réalisée à l'aide de tableaux physiques et de tâches planifiées ou de déclencheurs afin de les maintenir à jour. 

Les colonnes générées facilitent le traitement des valeurs dérivées simples. Cette approche est efficace pour les tableaux de bord et les résumés prévisibles, mais il est important de prêter attention à la fréquence d'actualisation et à la complexité des déclencheurs afin de ne pas surcharger accidentellement les chemins d'écriture critiques. De nombreuses équipes associent cette approche à des répliques en lecture afin de décharger entièrement les requêtes de reporting.

Snowflake / BigQuery (couche analytique)

Les entrepôts de données en colonnes sont conçus pour les données dénormalisées, larges et faciles à analyser. En général, vous modélisez des tableaux de faits larges avec des attributs utiles intégrés, puis vous vous appuyez sur le partitionnement/clustering pour élaguer ce qui est analysé. 

Les deux plateformes prennent en charge les vues matérialisées et les tâches/requêtes planifiées afin de maintenir les agrégats à jour sans interférer avec le système OLTP. Vous échangez les coûts de stockage et de rafraîchissement contre des lectures à grande échelle très prévisibles et peu coûteuses. Il est idéal pour les tableaux de bord et la BI.

Ce cours est un peu plus avancé que notre cours PostgreSQL. Si vous maîtrisez déjà les bases de données, nous vous invitons à essayer notre introduction à la modélisation de données avec Snowflake.

dbt (modélisation et orchestration)

dbt est reconnu comme la couche « infrastructure en tant que code » pour l'analyse dénormalisée. Vous définissez les modèles une seule fois, sélectionnez une matérialisation (tableau, vue, incrémentielle) et laissez dbt gérer les builds, les dépendances et les tests. Les modèles incrémentiels sont particulièrement utiles pour les tableaux récapitulatifs qui nécessitent uniquement l'ajout et la fusion de nouvelles données. C'est sans conteste l'un de mes outils préférés.

CDC et pipelines de données

Lorsque vous souhaitez que les modifications apportées aux applications soient intégrées de manière fiable dans des structures dénormalisées, la capture des données modifiées est la solution idéale. Des outils tels que Debezium (auto-hébergé) ou des connecteurs gérés (Fivetran, Airbyte) transmettent les modifications au niveau des lignes depuis l'OLTP vers des workers ou des entrepôts qui mettent à jour les tableaux de projection, les compteurs ou les agrégats. Ceci est généralement cohérent à terme (idéal pour les tableaux de bord et les flux) et s'adapte bien mieux que de tout regrouper dans des déclencheurs. Rendez les mises à jour idempotentes, surveillez les retards et conservez un chemin de remplissage pour les événements tardifs ou manqués.

Points clés à retenir et réflexions finales

La dénormalisation est une optimisation pragmatique qui s'ajoute à une conception normalisée, et non un rejet de celle-ci. Vous échangez la complexité du stockage et de l'écriture contre des lectures plus rapides et plus simples sur des chemins spécifiques et bien compris.

Veuillez noter :

• Veuillez commencer par normaliser. Veuillez utiliser la dénormalisation uniquement lorsque cela est nécessaire pour une requête nommée et mesurée.
• Commencez par épuiser les solutions les moins coûteuses (index, réécriture des requêtes, mise en cache, répliques, OLAP).
• Optez pour la dénormalisation la plus modeste qui soit rentable (une colonne redondante, un agrégat minime ou une vue matérialisée).
• Soyez explicite sur la fraîcheur (cohérence forte ou éventuelle) et mettez en place une synchronisation idempotente.
• Veuillez mesurer avant et après, surveiller les écarts et garder un interrupteur de retour en arrière à portée de main.
• Considérez les tableaux normalisés comme la source de vérité. Les éléments dénormalisés sont des copies optimisées pour la lecture que vous pouvez reconstruire.