Optimiser DuckDB : de la théorie à une requête de chiffre d'affaires

Pourquoi parler d’optimisation avec DuckDB ?

DuckDB s’est imposé comme un moteur analytique embarqué très performant : stockage colonne, exécution vectorisée, parallélisation automatique, format de fichier compact. Sur un portable moderne, il rivalise souvent avec des entrepôts cloud pour des workloads exploratoires.

Mais, comme pour n’importe quel moteur SQL, la structure des données et la façon d’écrire les requêtes font la différence entre une réponse « instantanée » et un temps d’attente visible pour l’utilisateur.

Dans cet article, on va :

• rappeler les grands principes d’optimisation dans DuckDB ;
• puis les appliquer à un cas concret autour de trois tables : articles, ventes, prix_achat ;
• réécrire et optimiser une requête de chiffre d’affaires annuel par famille.

Schéma de départ :

CREATE TABLE articles (
    id_article    INTEGER,
    code_article  VARCHAR,
    libelle       VARCHAR,
    famille       VARCHAR,
    sous_famille  VARCHAR,
    is_actif      BOOLEAN
);

CREATE TABLE ventes (
    id_vente        BIGINT,
    date_vente      DATE,
    id_article      INTEGER,
    id_client       INTEGER,
    quantite        INTEGER,
    prix_vente_ht   DECIMAL(18,4),
    remise_pct      DECIMAL(5,2),
    id_magasin      INTEGER
);

CREATE TABLE prix_achat (
    id_prix_achat   BIGINT,
    id_article      INTEGER,
    id_fournisseur  INTEGER,
    date_debut      DATE,
    date_fin        DATE,
    prix_achat_ht   DECIMAL(18,4)
);

Requête à optimiser :

SELECT
    date_trunc('year',  date_vente) AS annee,
    date_trunc('month', date_vente) AS mois,
    famille,
    SUM(quantite * prix_vente_ht * (1 - remise_pct / 100.0)) AS ca_ht
FROM ventes
JOIN articles USING(id_article)
WHERE date_vente >= DATE '2024-01-01'
  AND date_vente <  DATE '2024-12-31'
GROUP BY 1, 2, 3
ORDER BY  1, 2, 3;

On va voir comment DuckDB exécute ce type de requête, puis comment l’aider à aller encore plus vite.

Rappels sur l’architecture de DuckDB côté performance

Avant de jouer avec le SQL, c’est utile de comprendre deux ou trois choix d’architecture qui expliquent pourquoi DuckDB est rapide.

Stockage colonne et exécution vectorisée

DuckDB stocke les données en colonnes, par blocs (« row groups »). Pour un workload analytique, le gain est double :

• lecture disque/mémoire réduite : seuls les champs nécessaires sont scannés ;
• meilleure compression sur des colonnes homogènes (dates, booléens, ids, etc.).

L’exécution des requêtes est vectorisée : les opérateurs travaillent sur des « chunks » de quelques milliers de lignes à la fois, adaptés au cache CPU. On évite le mode « row by row » et on exploite au mieux les instructions SIMD.

Conséquence : dès que votre requête est un scan + filtrage + agrégation, DuckDB est dans sa zone de confort.

Index implicites : zonemaps

A chaque table, DuckDB construit automatiquement des min-max indexes (zonemaps) sur chaque colonne de type « général ». Pour chaque chunk de lignes, il stocke les valeurs min et max. Lors d’un WHERE date_vente >= ..., les chunks qui ne peuvent pas matcher sont purement ignorés.

Cela signifie que :

• si vos lignes sont grossièrement ordonnées par date_vente, id_article, etc., DuckDB va sauter de gros morceaux de table ;
• si l’ordre est totalement aléatoire, les zonemaps sont moins efficaces, même si elles ne pénalisent pas le temps de chargement.

Index explicites : ART

DuckDB supporte aussi des index de type Adaptive Radix Tree (ART) via CREATE INDEX, souvent utiles pour des requêtes très sélectives (point lookups, joins sur PK/FK très filtrés).

Ils sont surtout intéressants pour :

• accélérer des recherches sur des identifiants (id_client, id_article) très filtrées ;
• des joins sur des colonnes fortement sélectives.

Sur des scans analytiques larges (comme « toutes les ventes de 2024 »), le facteur déterminant reste la manière dont les données sont organisées et scannées, plus que les indexes explicites.

Bonnes pratiques générales pour optimiser DuckDB

1. Utiliser le bon support de stockage

Quelques règles simples :

• Base DuckDB persistante (.duckdb) plutôt que CSV brut pour les traitements récurrents.
• privilégier Parquet comme format d’échange.
• utiliser CREATE TABLE AS SELECT (CTAS) pour créer des tables optimisées.

Exemple : création d’une table ventes à partir de Parquet, déjà triée pour exploiter les zonemaps :

CREATE TABLE ventes AS SELECT * FROM read_csv('c:/temp/ventes.csv') ORDER BY date_vente, id_article;

2. Soigner les types et le schéma

Dans DuckDB, types et nullabilité ont un impact sur la taille et la vitesse :

• préférer DATE/TIMESTAMP aux VARCHAR pour les dates ;
• utiliser DECIMAL(p,s) pour les montants pour éviter les erreurs de flottant ;
• éviter les VARCHAR inutilement larges si des codes peuvent être normalisés (tables de dimensions).

Dans notre cas, le schéma proposé est déjà sain : DATE pour les dates, DECIMAL pour les montants.

3. Ecrire des requêtes « friendly » pour l’optimiseur

Quelques patterns utiles avec DuckDB :

• Projeter seulement les colonnes utiles (ici c’est déjà le cas) ;
• éviter les expressions inutiles dans le GROUP BY ;
• utiliser WHERE plutôt que HAVING pour les filtres non agrégés ;
• si possible, limiter le nombre de fonctions lourdes dans la projection.

Sur notre requête, le calcul du CA est un peu lourd :

quantite * prix_vente_ht * (1 - remise_pct / 100.0)

Si ce calcul est systématique, on peut le factoriser via une colonne générée.

4. Exploiter les colonnes générées

DuckDB supporte les colonnes générées (GENERATED ALWAYS AS), très pratiques pour centraliser des calculs métiers.

Exemple : calculer le montant de ligne dans ventes :

ALTER TABLE ventes
ADD COLUMN montant_ligne_ht AS (
  quantite * prix_vente_ht * (1 - remise_pct / 100.0)
);

C’est une colonne virtuelle : pas de stockage supplémentaire, mais une expression centralisée, réutilisable dans toutes les requêtes.

5. Mesurer avec EXPLAIN et EXPLAIN ANALYZE

Pour ne pas optimiser à l’aveugle :

EXPLAIN SELECT ...;          -- plan logique + physique
EXPLAIN ANALYZE SELECT ...;  -- plan + temps par opérateur

DuckDB propose aussi le profiling via pragma :

PRAGMA enable_profiling = json;
PRAGMA profiling_output = 'profiling/ca_ventes_2024.json';

SELECT ...; -- votre requête

PRAGMA disable_profiling;

Cela vous permet d’identifier où se focalise le temps : scan, join, agrégation, tri, etc.

Cas pratique : optimiser une requête de chiffre d’affaires par famille

Revenons à notre requête de départ :

SELECT
    date_trunc('year',  date_vente) AS annee,
    date_trunc('month', date_vente) AS mois,
    famille,
    SUM(quantite * prix_vente_ht * (1 - remise_pct / 100.0)) AS ca_ht
FROM ventes
JOIN articles USING(id_article)
WHERE date_vente >= DATE '2024-01-01'
  AND date_vente <  DATE '2024-12-31'
GROUP BY 1, 2, 3
ORDER BY  1, 2, 3;

Problème classique : lorsqu’il y a plusieurs années de ventes, la table ventes peut devenir volumineuse et le scan complet devient coûteux. On veut :

• limiter la quantité de données scannées ;
• exploiter les zonemaps sur la date ;
• simplifier le calcul du CA.

Etape 1 : spécialiser la table ventes sur 2024

Si l’entreprise interroge très souvent l’année 2024, il est pertinent de matérialiser une table dédiée, triée par date pour optimiser les zonemaps.

CREATE TABLE ventes_2024 AS
SELECT *
FROM ventes
WHERE date_vente >= DATE '2024-01-01'
  AND date_vente <  DATE '2025-01-01'
ORDER BY date_vente, id_article;

Avantages :

• toutes les requêtes sur 2024 travaillent sur un volume réduit ;
• la colonne date_vente est ordonnée, donc les zonemaps permettent de sauter des blocs entiers si vous filtrez sur des mois ou des périodes plus courtes.

Etape 2 : centraliser le calcul du montant de ligne

On crée une colonne générée pour le montant HT de chaque ligne de vente :

ALTER TABLE ventes_2024 ADD COLUMN montant_ligne_ht DECIMAL;
UPDATE ventes_2024 set montant_ligne_ht =quantite * prix_vente_ht * (1 - remise_pct / 100.0);

La requête devient plus lisible :

SELECT
    date_trunc('year',  date_vente) AS annee,
    date_trunc('month', date_vente) AS mois,
    a.famille,
    SUM(v.montant_ligne_ht) AS ca_ht
FROM ventes_2024 v
JOIN articles a USING(id_article)
WHERE a.is_actif
GROUP BY ALL
ORDER BY annee, mois, famille;

Améliorations apportées :

• calcul métier factorisé et réutilisable (montant_ligne_ht) ;
• utilisation de GROUP BY ALL propre à DuckDB pour éviter les positions de colonne (plus lisible et moins fragile) ;
• filtrage sur is_actif pour exclure les articles inactifs, ce qui réduit l’agrégat si la dimension produits est volumineuse.

Etape 3 : pré-agréger par article et par mois

Si la requête d’analyse par famille est très fréquente, on peut pousser l’optimisation plus loin en pré-agrégeant les ventes au niveau article/mois, puis en joignant avec articles pour remonter à la famille.

Pré-agrégation :

CREATE TABLE ca_mensuel_article_2024 AS
SELECT
    date_trunc('month', date_vente) AS mois,
    id_article,
    SUM(montant_ligne_ht) AS ca_ht
FROM ventes_2024
GROUP BY mois, id_article;

Requête finale :

SELECT
    date_trunc('year',  mois)    AS annee,
    mois,
    a.famille,
    SUM(c.ca_ht) AS ca_ht
FROM ca_mensuel_article_2024 c
JOIN articles a USING(id_article)
WHERE a.is_actif
GROUP BY ALL
ORDER BY annee, mois, famille;

Bénéfices :

• vous ne scannez plus ventes_2024 à chaque fois : seulement la table pré-agrégée, beaucoup plus petite ;
• les calculs de montants sont déjà effectués ;
• la complexité se déplace dans un job de préparation (batch), dont vous maîtrisez la fréquence.

Dans un contexte EloNeva, ce job peut être orchestré dans une pipeline CI/CD ou un scheduler (GitLab CI, Argo, etc.), et la table ca_mensuel_article_2024 est consommée par les dashboards.

Ajouter la notion de marge avec prix_achat

La table prix_achat permet de calculer un coût moyen et une marge. On considère que la ligne prix_achat active pour une vente est celle dont l’intervalle de validité couvre date_vente :

SELECT
    date_trunc('month', v.date_vente) AS mois,
    a.famille,
    SUM(v.montant_ligne_ht) AS ca_ht,
    SUM(v.quantite * pa.prix_achat_ht) AS cout_ht,
    SUM(v.montant_ligne_ht) - SUM(v.quantite * pa.prix_achat_ht) AS marge_ht
FROM ventes_2024 v
JOIN articles a USING(id_article)
JOIN prix_achat pa
  ON pa.id_article = v.id_article
 AND v.date_vente >= pa.date_debut
 AND v.date_vente <  COALESCE(pa.date_fin, DATE '9999-12-31')
WHERE a.is_actif
GROUP BY mois, a.famille
ORDER BY mois, a.famille;

Optimisations spécifiques pour ce join

1. Ordre physique de prix_achat On peut organiser prix_achat pour qu’elle soit triée par id_article, date_debut :

   CREATE TABLE prix_achat_optim AS
   SELECT *
   FROM prix_achat
   ORDER BY id_article, date_debut;
   

   Cela améliore les zonemaps sur id_article et date_debut, et facilite l’utilisation d’un sort-merge join par l’optimiseur.

2. Index ART sur id_article si les accès sont très sélectifs

   Si vous faites souvent des requêtes très ciblées sur un article ou un petit groupe d’articles, un index peut aider :

   CREATE INDEX idx_prix_achat_article
     ON prix_achat_optim(id_article);
   

   Attention : les index ART doivent tenir en mémoire. Ils sont donc à réserver aux cas de figure où le bénéfice est réel (à vérifier avec EXPLAIN ANALYZE).

3. Pré-agréger le coût comme pour le CA

   On peut également pré-agréger le coût pour les mêmes raisons que le CA :

   CREATE TABLE cout_mensuel_article_2024 AS
   SELECT
       date_trunc('month', v.date_vente) AS mois,
       v.id_article,
       SUM(v.quantite * pa.prix_achat_ht) AS cout_ht
   FROM ventes_2024 v
   JOIN prix_achat_optim pa
     ON pa.id_article = v.id_article
    AND v.date_vente >= pa.date_debut
    AND v.date_vente <  COALESCE(pa.date_fin, DATE '9999-12-31')
   GROUP BY mois, v.id_article;
   

   Puis la marge finale se calcule en joignant CA et coût pré-agrégés.

Temps de réponse et gains obtenus

| Requête | Sans optimisation | Avec optimisation | Gains | Résultats |

Conclusion : de la requête SQL au produit analytique

Dans cet article, on est parti d’une requête de chiffre d’affaires mensuel par famille et on l’a progressivement optimisée en exploitant les points forts de DuckDB :

• structuration physique des données (CTAS, tri par date) pour aider les zonemaps ;
• centralisation des calculs métier dans des colonnes générées ;
• pré-agrégations ciblées (mensuel par article) pour accélérer les requêtes récurrentes ;
• optimisation du join avec prix_achat grâce à l’ordre des données et, si nécessaire, des index.

Dans un contexte d’entreprise comme EloNeva, ces patterns permettent de :

• réduire la complexité de la couche BI (les requêtes de reporting deviennent simples) ;
• maîtriser les temps de réponse pour les utilisateurs finaux ;
• industrialiser progressivement une stack analytique moderne, en combinant DuckDB, Docker, un orchestrateur et un data lake.

L’idée clé : ne pas s’arrêter à la première requête qui fonctionne. Avec DuckDB, quelques décisions de modélisation et d’architecture bien pensées transforment un POC en brique analytique robuste et performante.