Comment exploiter SQLite JSON à pleine vitesse d’indexation : des requêtes rapides avec des colonnes générées

 (dbpro.app)
11 points par GN⁺ 2025-12-13 | 2 commentaires | Partager sur WhatsApp
  • Présentation d’une méthode qui exploite les fonctions JSON de SQLite pour stocker le document JSON brut tel quel, puis extraire et indexer les champs nécessaires via des colonnes générées virtuelles (virtual generated columns)
  • Avec la fonction json_extract, les données internes au JSON peuvent être manipulées comme des colonnes, avec des requêtes exécutées à la vitesse des index B-tree
  • Chaque fois qu’un nouveau modèle de requête est nécessaire, il est possible d’étendre le système en ajoutant des colonnes et des index sans migration de données
  • Cette approche permet d’obtenir à la fois la souplesse des données sans schéma et les performances d’une base de données relationnelle
  • Elle est mise en avant comme un pattern pratique offrant une structure simple et de hautes performances pour les développeurs qui utilisent SQLite

Combiner SQLite et les fonctionnalités JSON

  • SQLite prend en charge les fonctions et opérateurs JSON, ce qui permet de stocker et de manipuler directement des données JSON
    • Le document JSON peut être conservé tel quel dans une colonne, et seules les informations nécessaires sont extraites sous forme de colonnes virtuelles
    • Cette méthode permet de traiter les données avec souplesse, sans définition préalable de schéma
  • L’équipe DB Pro a utilisé SQLite de manière intensive ces derniers mois et a validé ces capacités en conditions réelles
    • Lorsqu’il est correctement configuré, SQLite peut être utilisé de façon fiable en production

Colonnes générées virtuelles (Generated Columns)

  • json_extract permet de définir certaines valeurs du JSON comme colonnes générées virtuelles
    • Ces colonnes ne stockent pas réellement les données : elles sont calculées au moment de la requête et utilisables immédiatement
    • Aucun backfill ni duplication de données n’est nécessaire
  • Il est par exemple possible d’extraire un champ précis d’un document JSON pour le manipuler comme une colonne classique

Ajout d’index et gains de performance

  • En ajoutant un index sur une colonne virtuelle, les données JSON peuvent elles aussi être recherchées à la vitesse des index B-tree, comme des colonnes ordinaires
    • Une colonne virtuelle indexée offre les mêmes performances qu’une colonne d’une base de données relationnelle
  • Cette approche permet d’effectuer des recherches rapides même lorsque les données JSON deviennent volumineuses

Ajouter de nouveaux modèles de requête

  • Si, plus tard, il devient nécessaire de rechercher sur un nouveau champ, il suffit d’ajouter une nouvelle colonne virtuelle et un nouvel index
    • Exemple : extraire le champ user_id et créer un index dessus
    • Il n’est pas nécessaire de modifier les lignes existantes ni d’effectuer une migration
  • Cela permet d’obtenir une extensibilité immédiate des requêtes sans changer la structure des données

Avantages et portée du pattern

  • Ce pattern combine la souplesse du stockage JSON sans schéma et les performances d’indexation d’une base relationnelle
    • Il n’est pas nécessaire de décider de la stratégie d’indexation dès la phase de conception initiale
    • Il est possible d’optimiser au moment voulu en ajoutant colonnes et index selon les besoins
  • Il est présenté aux développeurs utilisant SQLite comme une méthode de traitement des données à la fois simple et puissante
  • DB Pro annonce d’autres articles à venir sur diverses fonctionnalités de SQLite

À lire aussi

2 commentaires

 
GN⁺ 2025-12-13
Commentaires sur Hacker News

  • Il est possible d’encoder directement des documents JSON sous forme de B-tree sérialisé
    Cela permet d’interroger immédiatement les champs internes à la vitesse de l’index, et comme le document lui-même est déjà indexé, il n’y a plus besoin de parsing
    Ce format s’appelle Lite³. C’est un projet sur lequel je travaille moi-même
    Lien GitHub

    • Vraiment génial ! J’aime bien Rkyv, mais il nécessite Rust, ce qui était un peu lourd pour de petits projets
      J’apprécie particulièrement que Lite³ prenne en charge les données binaires avec lite3_val_bytes
    • Je me demandais en quoi Lite³ diffère du JSONB de PostgreSQL
      JSONB encode la longueur des tableaux et les offsets ensemble, ce qui permet d’ajuster le compromis entre efficacité de compression et vitesse
      Lite³ permet les mises à jour in-place, mais il faut exécuter périodiquement un “vacuum” pour éviter que des données sensibles ne restent présentes
      JSONB est difficile à mettre à jour sans réencodage, alors que Lite³ peut être nettoyé assez simplement en parcourant la structure
      La compressibilité semble probablement meilleure avec JSONB, mais je trouve que la conception de Lite³ est une approche très astucieuse
      Je maintiens moi aussi un compilateur ASN.1, donc ce genre de format de sérialisation m’intéresse beaucoup. Lite³ m’a donné de nouvelles idées
    • Une implémentation en version Rust serait vraiment bienvenue

  • J’aime beaucoup SQLite, mais pour l’analytique, j’utilise plus souvent DuckDB
    DuckDB utilise un fichier unique comme SQLite, tout en traitant de très gros jeux de données extrêmement rapidement
    Même sur un MacBook M2, il est très rapide avec 20 millions d’enregistrements
    Par exemple, on peut lire directement des fichiers JSON avec la requête suivante

    SELECT avg(sale_price), count(DISTINCT customer_id)
FROM '/my-data-lake/sales/2024/*.json';

    On peut aussi charger une colonne de type JSON et utiliser une syntaxe à la Postgres comme col->>'$.key'

    • Je me demande si la première requête indexe à la volée les fichiers JSON du système de fichiers
    • DuckDB, utilisé avec l’outil de visualisation pygwalker, permet d’analyser des millions d’enregistrements en quelques secondes
      Cela dit, la comparaison avec SQLite est un peu injuste. SQLite sert à construire des systèmes, tandis que DuckDB est destiné à l’analyse
      DuckDB est un peu délicat à déployer sur plusieurs plateformes
    • Il ne faut absolument pas stocker un jeu de données sans compression. DuckDB prend en charge divers formats de compression

  • Je pensais que l’usage de Generated Column pour les performances JSON était une méthode courante
    Je l’ai déjà utilisé aussi dans Postgres pour conserver comme clé étrangère une clé située dans une colonne JSON. C’était une approche un peu “maudite”, mais elle fonctionnait bien

    • Avec Postgres, on ne peut pas mettre un index directement sur un champ interne de JSONB ?
      Par exemple 
      CREATE INDEX idx_status_gin
ON my_table
USING gin ((data->'status'));
      Article de référence
    • Mais dans ce genre de cas, il vaut souvent mieux finir par séparer cela en table clé/valeur
      Si le schéma JSON change, le parsing ou les migrations peuvent échouer
    • En fait, ce n’est pas une approche si “maudite” que ça. On peut utiliser une structure relationnelle normalisée là où c’est nécessaire, et gérer le reste en jsonb
    • Je me demandais si on pouvait utiliser une colonne VIRTUAL au lieu de STORED, et cet exemple utilise bien VIRTUAL

  • J’ai découvert cette technique récemment grâce à un exemple d’optimisation proposé par Claude Code
    C’était un point que j’avais raté parce que je ne connaissais pas cette fonctionnalité récente de SQLite, et le gain de performances était assez important
    La leçon, c’est que même avec des outils familiers, il est bon de relire périodiquement la documentation

    • Relire le manuel apporte parfois des déclics étonnants

  • J’ai écrit l’article de blog après avoir vu en 2023 sur HN un commentaire de bambax
    Lien vers le commentaire d’origine

  • On peut créer un index sans projeter directement le JSON, mais une colonne calculée (computed column) simplifie les requêtes
    Avant MS-SQL 2025(v17), la prise en charge de JSON était limitée, donc cette méthode était indispensable

    • Si l’on n’utilise que des colonnes calculées sans interroger directement le JSON, on ne risque pas d’écrire par erreur des requêtes non indexées
    • J’avais entendu parler de cette fonctionnalité lors d’une conférence locale de DBA, mais à l’époque cela ne m’avait pas semblé être un grand changement

  • J’ai ouvert l’article sur HN et j’ai trouvé amusant que mon commentaire y soit cité, et qu’en plus ce commentaire soit précisément le sujet du billet
    Voir la phrase “Merci, bambax !” m’a fait sourire. SQLite est vraiment un super outil

    • Le commentaire qui a réellement inspiré l’article se trouve à ce lien

  • Intéressant, mais on ne pourrait pas simplement utiliser "Index On Expression" de SQLite ?
    Par exemple CREATE INDEX idx_events_type ON events(json_extract(data, '$.type'))
    Cela dit, si la syntaxe du chemin JSON diffère ne serait-ce qu’un peu, l’index peut ne plus être utilisé. En revanche, une Virtual Generated Column garantit toujours l’index

    • En utilisant ensemble un index d’expression et une vue, on peut garantir la correspondance de l’expression
      Exemple : projet recordlite
    • Si la syntaxe du chemin JSON change même légèrement, l’index peut devenir inutilisable
      Par exemple, json_extract(data, "$.type") et data -> '$.type' sont interprétés différemment
      Autrement dit, si l’expression de la clause WHERE change, l’index devient sans effet
    • C’est une solution simple et rapide. Le fait que la requête et l’index doivent correspondre est de toute façon toujours vrai
    • Les index d’expression sont une fonctionnalité relativement récente de SQLite, ajoutée à partir de SQLite 3.9.0 (2015-10-14)

  • J’aimerais que les développeurs évitent de mettre toutes leurs données dans des colonnes JSON(B) alors qu’ils disposent d’un schéma cohérent
    Cela complique la mise en place des index, la gestion des contraintes, et ajoute beaucoup d’overhead à l’usage réel

    • Les colonnes JSON brillent lorsqu’il s’agit de données difficiles à représenter en tables, comme des structures en arbre
      Par exemple, dans un environnement Haskell+TypeScript, il est bien plus pratique de sérialiser en JSON des structures imbriquées complexes
    • JSON(B) est utile quand on veut stocker dans une même collection des données de types différents
      Exemple : stocker dans une seule table les résultats de plusieurs processeurs de paiement, ou gérer dans un site de petites annonces des attributs différents selon les articles
      En C# ou en JS/TS aussi, la gestion devient plus simple si l’on utilise en complément des outils de validation de types (Zod, OpenAPI, etc.)
    • Pour du JSON simple, il vaut mieux normaliser, mais des réponses d’API complexes deviennent un enfer de JOIN si on les éclate en tables
      Au final, c’est une question d’équilibre entre maintenabilité et performances. L’idée centrale de cet article est qu’on peut aussi indexer facilement du JSON
    • Quand on manipule d’un bloc toute une structure arborescente, comme avec des données de capteurs, une colonne JSON est bien plus simple
      Les performances en lecture peuvent être suffisamment compensées par les index
    • Une normalisation complète est souvent inefficace
      Par exemple, dans un système de tarification produit, représenter en JSON des règles de remise atypiques propres à certains clients offre bien plus de souplesse

  • Si l’on utilisait XML au lieu de JSON, ce serait le même modèle que les bases documentaires (document store) des années 90–2000
    On parsait à l’insertion et à la mise à jour, puis on n’accédait plus qu’aux index lors des requêtes
    Le fait que SQLite propose ce genre de fonctionnalité en standard est vraiment fascinant
 
iolothebard 2025-12-14

À la fin du XXe siècle… il y avait ce qu’on appelait une universal database… (aujourd’hui c’est vrai, mais à l’époque c’était faux.)