Votre base de données est 40 fois trop lente

Vos requêtes analytiques s'exécutent probablement beaucoup plus lentement que nécessaire. Prenons une requête d'agrégation sur 100 millions de lignes : avec Postgres, cette opération prend 9,7 secondes, ce qui est considérable. DuckDB, une base de données column-store, termine la même requête en seulement 0,24 seconde, démontrant une amélioration de vitesse de plus de 40x. Cette différence frappante révèle un goulot d'étranglement architectural fondamental inhérent à de nombreuses bases de données traditionnelles.

Les bases de données relationnelles traditionnelles comme Postgres sont des row-stores. Elles organisent les données sur le disque par ligne, ce qui signifie qu'une ligne entière est récupérée du stockage même si une requête n'a besoin que de la valeur d'une seule colonne de cette ligne. Pour les charges de travail analytiques qui agrègent des données sur de nombreuses lignes mais peu de colonnes, cette conception force la base de données à lire de grandes quantités de données non pertinentes, gaspillant une bande passante I/O et des cycles CPU précieux.

Les column-stores comme DuckDB et ClickHouse inversent ce paradigme. Ils stockent les données regroupées par colonne. Lorsqu'une requête d'agrégation cible une colonne spécifique, la base de données ne lit que les données de cette colonne particulière sur le disque, ignorant totalement toutes les autres colonnes de la table. Cela réduit considérablement la quantité de données traitées, conduisant aux gains de performance observés.

Les bases de données colonnaires atteignent leur vitesse stupéfiante en effectuant beaucoup moins de travail. Au lieu de scanner chaque ligne, elles utilisent des stratégies d'indexation et de métadonnées intelligentes pour ignorer les données non pertinentes. Cette efficacité découle de leur stockage orienté colonne, qui regroupe les données par colonne plutôt que par ligne, optimisant ainsi les requêtes analytiques qui agrègent souvent des sous-ensembles de colonnes.

ClickHouse, par exemple, n'indexe pas les lignes individuelles ; il s'appuie sur une sparse primary key définie sur une colonne triée comme un horodatage. Au fur et à mesure que les données sont ingérées, elles sont triées et divisées en blocs de taille fixe d'environ 8 192 lignes, appelés granules. ClickHouse ne stocke que le premier horodatage de chaque granule, créant environ 12 000 petites notes en mémoire pour un jeu de données de 100 millions de lignes.

Lorsqu'une requête demande des données pour une période spécifique, comme mars, ClickHouse scanne rapidement ces notes en mémoire, identifiant et ne lisant que les granules pertinents sur le disque. Dans notre benchmark, cela signifiait ne lire que 1 633 blocs sur 12 208, ignorant plus de 86 % du jeu de données. DuckDB utilise une astuce similaire et très efficace : il stocke des min/max metadata pour chaque bloc de colonne. Cela permet à DuckDB de prouver qu'un bloc n'est pas pertinent pour la requête — par exemple, un bloc couvrant de janvier à février — et d'ignorer entièrement son contenu sans le lire.

Les bases de données colonnaires excellent dans les requêtes d'agrégation, mais leur architecture introduit des compromis importants pour d'autres opérations courantes. Une requête de recherche ponctuelle (point lookup), récupérant une seule ligne par ID, révèle ce contraste saisissant. Avec Postgres, cela ne prend que 2 millisecondes grâce à son binary tree index, qui navigue efficacement vers la page de données exacte contenant la ligne demandée.

ClickHouse, cependant, peine avec de telles requêtes, atteignant 168 millisecondes. Sa sparse primary key, triée par horodatage puis par ID, n'offre aucun chemin direct vers un ID arbitraire sans scanner les 12 208 blocs de données. Même après avoir localisé la ligne, il doit reconstruire l'enregistrement complet en ouvrant et en assemblant les huit fichiers de colonnes, une surcharge considérable pour une récupération apparemment simple.

La mise à jour d'une seule ligne expose davantage la divergence architecturale. Avec Postgres, une mise à jour s'effectue en 5 millisecondes, réécrivant une ligne et mettant à jour une entrée d'index. ClickHouse, à l'inverse, prend 5,8 secondes pour la même opération. Cela est dû à ses immutable data files ; la modification d'une seule valeur nécessite la réécriture complète du fichier de colonne de revenus pour ce bloc de données affecté.

Ces caractéristiques de performance ne sont pas des défauts de conception mais des spécialisations inhérentes. Les bases de données orientées lignes comme Postgres sont optimisées pour les transactional workloads (OLTP), où les insertions, mises à jour et recherches fréquentes sur une seule ligne sont primordiales. Les bases de données colonnaires, par conception, privilégient les analytical workloads (OLAP), où les requêtes d'agrégation sur de vastes ensembles de données dominent, rendant leurs forces et faiblesses distinctes selon le cas d'utilisation.

Deux options colonnaires distinctes existent pour accélérer les requêtes analytiques : ClickHouse et DuckDB. Bien que les deux offrent des gains de performance impressionnants par rapport à Postgres pour les opérations d'agrégation, leurs philosophies architecturales divergent considérablement, dictant des applications idéales disparates.

ClickHouse fonctionne comme un système complet basé sur un serveur, conçu spécifiquement pour les entrepôts de données de production. Il fonctionne de manière similaire à Postgres, nécessitant une instance de serveur en cours d'exécution et prenant en charge l'accès multi-utilisateur simultané pour des environnements d'analyse partagés robustes.

DuckDB, à l'inverse, offre une expérience de embedded library, similaire à SQLite pour les charges de travail analytiques. Il s'exécute en cours de processus avec votre application, stockant toute sa base de données dans un seul fichier sur le disque, ce qui le rend idéal pour la manipulation de données locale côté client.

Pour un backend d'analyse partagé et évolutif prenant en charge plusieurs utilisateurs et de grands ensembles de données, ClickHouse est l'arme de choix. Il gère l'ingestion à haut débit et les requêtes d'agrégation complexes sur des pétaoctets de données dans un environnement de production.

Choisissez DuckDB pour booster l'analyse de données locale, alimenter des applications à nœud unique ou accélérer des tâches ETL ultra-rapides. Sa nature en cours de processus et sa base de données en fichier unique simplifient le déploiement pour les data scientists individuels ou les outils internes à plus petite échelle.

Si vous avez besoin d'une plateforme d'analyse multi-utilisateur robuste, ignorez DuckDB. Si vos besoins se limitent au traitement local mono-utilisateur, la surcharge serveur de ClickHouse est inutile.

Quelle est la principale différence entre une base de données colonnaire et une base de données orientée lignes ?

Une base de données orientée lignes (comme Postgres) stocke toutes les données d'un seul enregistrement ensemble. Une base de données colonnaire (comme ClickHouse) stocke toutes les valeurs d'une seule colonne ensemble, ce qui est beaucoup plus efficace pour les requêtes analytiques.

Quand dois-je utiliser une base de données colonnaire ?

Utilisez une base de données colonnaire pour les charges de travail analytiques (OLAP) qui impliquent l'agrégation ou le filtrage de quelques colonnes sur des millions ou des milliards de lignes. Elles sont idéales pour les tableaux de bord, la business intelligence et les plateformes d'analyse.

Pourquoi les bases de données colonnaires sont-elles lentes pour les mises à jour d'une seule ligne ?

Leurs fichiers de données sont optimisés pour l'ingestion en masse et sont souvent immuables. La mise à jour d'une seule valeur peut nécessiter la réécriture d'un bloc entier important d'un fichier de colonne, ce qui le rend beaucoup plus lent que les bases de données transactionnelles.

DuckDB est-il un remplaçant pour Postgres ?

Non, ils servent des objectifs principaux différents. DuckDB est une base de données analytique embarquée (comme SQLite pour l'analyse), idéale pour l'analyse de données en cours de processus. Postgres est une base de données transactionnelle à usage général (OLTP) conçue pour être un système d'enregistrement.