Performances, coûts et reproductibilité

Les tables blockchain couvrant tout l’historique comptent des milliards de lignes, et une même question peut coûter quelques centimes et s’exécuter en quelques secondes, ou coûter réellement cher et prendre une heure, selon la manière dont la requête est écrite. Les bonnes habitudes qui permettent de garder les requêtes rapides et peu coûteuses (périmètres restreints, plages fixes, versions explicites) sont aussi celles qui rendent les résultats reproductibles, ce qui transforme un résultat de requête en élément de preuve. Cette leçon traite de ces deux aspects et se termine par la manière de vérifier un résultat à l’aide d’un nœud.

Comment les moteurs analytiques lisent les données

Les entrepôts de données et les moteurs de lac de données stockent les données par colonne, et non par ligne. Une requête ne lit que les colonnes qu’elle nomme. Ainsi, SELECT * sur une table de transactions lit également la volumineuse colonne input contenant les données d’appel, même si vous n’avez besoin que de trois colonnes étroites. Dans les services facturés à l’analyse, la facture est proportionnelle au nombre d’octets lus ; nommer les colonnes est donc la première mesure de maîtrise des coûts.

La deuxième mesure est le partitionnement. Les grandes tables sont divisées en partitions, généralement par jour d’horodatage de bloc ou par plage de blocs, stockées dans des fichiers ou des segments distincts. Lorsqu’un filtre sur la colonne de partition exclut une partition, le moteur l’ignore entièrement : c’est l’élagage des partitions. À l’intérieur des partitions, les données peuvent être regroupées (triées) selon une colonne comme le numéro de bloc ou l’adresse, et les moteurs conservent les valeurs minimales et maximales de chaque bloc de données afin d’ignorer ceux qui ne peuvent pas correspondre.

Quelques idées reçues à rectifier :

• LIMIT 10 ne réduit pas le coût d’une analyse dans la plupart des entrepôts ; le filtrage et l’analyse ont lieu avant la limite.
• Un filtre sur une colonne qui n’est pas une colonne de partition (comme to_address) n’élague pas les partitions, même si le regroupement selon cette colonne peut être utile.
• La plupart des services proposent une simulation ou une commande EXPLAIN qui indique le nombre d’octets ou de partitions que l’analyse lira. Utilisez-la avant d’exécuter une requête sur l’historique complet.

L’ordre de grandeur aide à estimer le coût. Ethereum produit un bloc par intervalle de 12 secondes, soit environ 7 200 blocs par jour ; Bitcoin, environ 144. Une plage d’une semaine sur Ethereum représente environ 50 000 blocs, soit une petite fraction des plus de 20 millions de blocs de l’historique de la chaîne.

Filtres sur les partitions et les plages de blocs

Les analystes raisonnent en hauteur de bloc ; les tables sont partitionnées par date. La méthode fiable consiste à convertir d’abord la plage de blocs en fenêtre temporelle, puis à filtrer selon les deux critères.

-- Étape 1 : trouver la fenêtre temporelle couvrant la plage de blocs
SELECT MIN(block_timestamp) AS t0, MAX(block_timestamp) AS t1
FROM blocks
WHERE number BETWEEN 21100000 AND 21107199;

-- Étape 2 : filtrer sur la colonne de partition avec des valeurs littérales, puis sur les blocs
SELECT transaction_hash, from_address, to_address, amount_raw
FROM token_transfers
WHERE block_timestamp >= TIMESTAMP '2024-11-01 00:00:00'  -- étape 1, fenêtre élargie
  AND block_timestamp <  TIMESTAMP '2024-11-05 00:00:00'  -- à des journées entières
  AND block_number BETWEEN 21100000 AND 21107199           -- périmètre exact
  AND token_address = '0x7a11…0001';

L’étape 1 lit la petite table blocks. L’étape 2 utilise le filtre temporel afin que le moteur ne lise que quelques partitions journalières, et le filtre sur les blocs afin de renvoyer exactement les lignes visées. Élargir la fenêtre à des journées entières coûte peu et protège contre les cas limites liés aux horodatages.

Deux précautions. De nombreux moteurs n’élaguent les partitions que lorsque le filtre de partition compare la colonne à des constantes. Une jointure ou une sous-requête qui fournit la fenêtre peut donc entraîner l’analyse de toutes les données ; transmettez les valeurs sous forme de littéraux ou de paramètres de requête. De plus, envelopper la colonne de partition dans une fonction, par exemple CAST(block_timestamp AS DATE) = …, peut désactiver l’élagage sur certains moteurs ; comparez plutôt la colonne brute à une plage.

Une autre économie consiste à extraire une seule fois et à itérer localement. Une enquête implique généralement des dizaines de variantes d’une même question portant sur les mêmes adresses et les mêmes blocs. Au lieu d’analyser chaque fois les tables couvrant tout l’historique, exécutez une requête bien filtrée qui récupère tous les transferts concernant les adresses du dossier dans la plage de blocs du dossier, enregistrez-les dans une petite table ou un fichier Parquet, puis explorez ces données, par exemple dans un moteur embarqué sur un ordinateur portable. L’analyse coûteuse n’a lieu qu’une fois, les itérations sont presque gratuites, et l’extrait enregistré constitue également un instantané fixe des éléments de preuve, ce qui favorise autant la reproductibilité que la maîtrise des coûts.

Échantillonnage

L’échantillonnage est utile pour explorer une table inconnue, tester la logique d’une requête et estimer des distributions comme les niveaux de frais typiques. Il ne convient pas aux totaux, au traçage ni à tout ce qui est rare. La valeur sur chaîne est fortement concentrée : une poignée de transferts peut représenter la majeure partie du volume d’une journée, et un échantillon de 1 % les manquera généralement.

Il existe deux types d’échantillons aux effets différents :

• Échantillonnage du stockage. TABLESAMPLE SYSTEM (1 PERCENT) (pris en charge sous une forme ou une autre par BigQuery, PostgreSQL, Trino et DuckDB) lit un sous-ensemble aléatoire des blocs de stockage. Il réduit le nombre d’octets lus, mais les lignes sont regroupées plutôt qu’indépendantes, et une nouvelle exécution produit un échantillon différent sur certains moteurs.
• Échantillonnage déterministe. Un filtre sur une valeur dérivée d’un hachage renvoie toujours les mêmes lignes.

-- Échantillon reproductible : les txids sont des hachages uniformes, donc '%00' en conserve ~1 sur 256
SELECT COUNT(*) AS sampled
FROM transactions
WHERE block_timestamp >= TIMESTAMP '2024-11-01 00:00:00'
  AND block_timestamp <  TIMESTAMP '2024-12-01 00:00:00'
  AND hash LIKE '%00';

Comme les hachages de transaction sont effectivement aléatoires, conserver ceux qui se terminent par 00 sélectionne environ 1 transaction sur 256 (0,39 %), les mêmes sur chaque moteur et à chaque exécution. Le filtre lit tout de même la colonne hash pour l’ensemble de la plage : il réduit la taille des résultats et les rend reproductibles, mais ne diminue pas le coût de l’analyse. Combinez-le avec un filtre de partition bien délimité.

Modèles incrémentiels

Les tâches de surveillance (une liste de suivi d’adresses, les flux quotidiens vers des plateformes d’échange) ne doivent pas recalculer l’historique à chaque exécution. Un modèle incrémentiel traite uniquement les nouveaux blocs et les ajoute à une table dérivée. Le piège est la tête de chaîne : les blocs récents peuvent être réorganisés, de sorte qu’une tâche qui ne fait qu’ajouter conservera les lignes de blocs qui n’existent plus (voir la leçon Les blocs et les en-têtes comme données).

-- Actualisation incrémentielle avec fenêtre de retour arrière en cas de réorganisation ; exécution répétée sans risque
-- :from_block = bloc le plus élevé déjà chargé moins 64
-- :to_block   = dernier bloc finalisé
DELETE FROM watched_transfers
WHERE block_number >= :from_block;

INSERT INTO watched_transfers
SELECT tt.*
FROM token_transfers AS tt
WHERE tt.block_number BETWEEN :from_block AND :to_block
  AND EXISTS (SELECT 1 FROM watchlist AS w
              WHERE w.address IN (tt.from_address, tt.to_address));

À chaque exécution, la plage de retour arrière est supprimée puis rechargée ; l’exécution de la tâche deux fois produit donc la même table : la tâche est idempotente. Charger uniquement jusqu’au bloc finalisé (sur Ethereum, la finalité intervient normalement après environ deux époques, soit à peu près 13 minutes) évite la plupart des problèmes de réorganisation ; sur Bitcoin, une fenêtre de retour arrière d’au moins six blocs est une convention courante. EXISTS est utilisé à la place d’une jointure afin qu’un transfert entre deux adresses suivies ne soit inséré qu’une seule fois. Exécutez les deux instructions dans une transaction de base de données afin que les lecteurs ne voient jamais une plage partiellement chargée. La syntaxe des paramètres varie : :name dans de nombreux clients, @name dans BigQuery, $1 dans PostgreSQL, ? dans JDBC. Les outils libres comme dbt mettent en œuvre ce modèle sous la forme de « modèles incrémentiels », et une reconstruction complète périodique permet de détecter les écarts que les incréments n’ont pas captés.

Requêtes versionnées et reproductibles

Un résultat est reproductible si une autre personne, ou vous-même un an plus tard, peut exécuter la même requête sur les mêmes données et obtenir les mêmes lignes. Pour cela, il faut :

• Périmètre fixe. Utilisez des plages de blocs, jamais CURRENT_DATE ou « les 30 derniers jours ».
• Contrôle de version. Conservez le SQL dans un dépôt et indiquez le commit dans le rapport ; pas de copies modifiées manuellement.
• Résultat déterministe. Triez selon une clé complète (numéro de bloc, puis index de journal) ; sans ORDER BY, l’ordre des lignes peut changer entre les exécutions. Évitez les fonctions approximatives dans les chiffres présentés.
• Versions des données. Consignez le jeu de données, son instantané ou sa date d’extraction, ainsi que la version des entrées modifiables comme les tables d’étiquettes et de prix.
• Un enregistrement d’exécution sauvegardé à côté du résultat :

{"query": "flows_to_exchanges.sql", "commit": "9c41e2a",
 "dataset": "eth_transfers_parquet", "snapshot": "2026-10-01",
 "blocks": [21100000, 21107199], "labels_version": "v14",
 "engine": "duckdb 1.x", "run_utc": "2026-10-28T14:05:00Z",
 "rows": 1832, "result_sha256": "5be1…a7d0"}

Le hachage du résultat exporté permet à un réviseur de confirmer qu’il a reproduit exactement les mêmes lignes. La leçon Rédiger le rapport analytique explique comment inclure ces enregistrements dans l’annexe.

Valider les résultats à l’aide d’un nœud

Les jeux de données sont construits par des logiciels qui peuvent échouer. Avant d’inclure un chiffre dans un rapport, vérifiez-en un échantillon par rapport à la source (voir la leçon Qualité, normalisation et provenance des données) :

• Soldes. Comparez un solde calculé en SQL avec eth_getBalance pour l’adresse au même bloc, ou avec balanceOf (sélecteur 0x70a08231) appelé via eth_call à ce bloc.
• Blocs. Comparez les hachages de bloc enregistrés à ceux du nœud (getblockhash sur Bitcoin, eth_getBlockByNumber sur les chaînes EVM) afin de détecter les lignes provenant de blocs orphelins.
• Décomptes. Comparez le transaction_count de chaque bloc au nombre de lignes effectivement chargées dans transactions.
• Continuité. Vérifiez que les numéros de bloc ne présentent aucune lacune et que le hachage parent de chaque bloc correspond au hachage du bloc précédent.

-- Vérification de continuité : lacunes dans les numéros de bloc et liens parents rompus
SELECT number, prev_number, parent_hash, prev_hash
FROM (SELECT number, parent_hash,
             LAG(number) OVER (ORDER BY number) AS prev_number,
             LAG(hash)   OVER (ORDER BY number) AS prev_hash
      FROM blocks
      WHERE number BETWEEN 21100000 AND 21107199) AS s
WHERE number <> prev_number + 1          -- bloc manquant
   OR parent_hash <> prev_hash;          -- bloc provenant d’une autre branche

Toute ligne renvoyée signale un problème : un bloc manquant ou un bloc enregistré qui n’est pas lié à son prédécesseur. Un résultat vide sur la plage de blocs du dossier, associé à des vérifications ponctuelles concordantes, permet d’affirmer que les données ont été validées.

Points clés pour l’examen : pourquoi les moteurs en colonnes facturent les colonnes lues et pourquoi SELECT * et LIMIT sont trompeurs ; l’élagage des partitions et la raison pour laquelle le filtre de partition doit utiliser des plages littérales sur la colonne brute ; la conversion de plages de blocs en fenêtres temporelles ; les cas où l’échantillonnage est acceptable et la différence entre l’échantillonnage du stockage et l’échantillonnage déterministe par hachage ; les modèles incrémentiels avec une fenêtre de retour arrière en cas de réorganisation, des têtes finalisées et une suppression-insertion idempotente ; les éléments d’une requête reproductible (plage de blocs fixe, contrôle de version, ordre déterministe, versions des données, enregistrement d’exécution) ; la validation à l’aide d’un nœud par des vérifications de solde, de hachage de bloc, de décompte et de continuité.