Chargement de 1 billion de données météo dans TimescaleDB

 (aliramadhan.me)
3 points par GN⁺ 2024-04-17 | 2 commentaires | Partager sur WhatsApp
  • Pour analyser rapidement les variations météorologiques passées à l’échelle mondiale, il faut charger les données de réanalyse ERA5 dans PostgreSQL/TimescaleDB, ce qui représente environ 754 milliards de lignes selon les variables et la grille choisies
  • ERA5 fournit une résolution horaire depuis 1940 avec une grille latitude/longitude de 0,25 degré, mais dans la structure NetCDF, l’extraction de séries temporelles longues pour un point donné peut encore prendre 20 à 30 minutes
  • Un insert ligne par ligne atteint environ 3 000 lignes par seconde, ce qui demanderait près de 8 ans pour tout charger ; même un insert multi-lignes plafonne à 25 000 à 30 000 lignes par seconde avec psycopg3, soit environ 10 mois
  • PostgreSQL copy et cursor.copy() de psycopg3 diffèrent selon que le CSV est déjà préparé ou non ; en comptant les surcoûts, on est autour de 100 000 lignes par seconde, avec une marge d’amélioration via la parallélisation et des réglages fins
  • Si l’on charge directement depuis un dataframe, la meilleure option est de faire un copy direct vers une hypertable avec psycopg3 ; si le CSV existe déjà, timescaledb-parallel-copy est plus adapté, avec 12 à 16 workers comme point d’équilibre sur ce système

Construire un entrepôt mondial de données météo

  • L’objectif est de créer un entrepôt de données permettant d’interroger les données météo mondiales passées afin d’analyser des signaux de changement climatique déjà observables
  • Les exemples d’analyse portent sur des questions comme : Jakarta est-elle réellement devenue plus chaude ou plus sujette aux tempêtes ? Le Chili dans son ensemble est-il devenu plus chaud ou plus nuageux ? À quoi ressemblent les évolutions selon les régions ?
  • Pour mener des analyses globales rapidement, les requêtes de l’entrepôt doivent être rapides, et la première étape consiste à charger une très grande quantité de données dans la base
  • La pile repose sur PostgreSQL, avec TimescaleDB pour accélérer les requêtes temporelles, et PostGIS apparaît comme un choix prometteur pour accélérer plus tard les requêtes géospatiales

Les données de réanalyse ERA5 et l’échelle des 754 milliards de lignes

  • Les données utilisées ne sont pas des observations météo brutes, mais les données de réanalyse climatique ERA5
    • Les observations historiques peuvent être rares selon les régions et les périodes
    • ERA5 correspond à la sortie d’un modèle climatique contraint par les observations, largement utilisé dans la recherche météo et climat
  • ERA5 couvre toute la planète avec une résolution de 0,25 degré et une résolution horaire depuis 1940
    • Chaque instantané temporel contient 727 080 valeurs par variable
    • La grille comprend 1 038 240 points, avec 1 440 longitudes et 721 latitudes, pôles compris
    • En indexant par temps et par position, on obtient 753 836 544 000 lignes par variable, soit environ 754 milliards de lignes
  • Les variables chargées incluent la température, les composantes est-ouest et nord-sud du vent à 10 m, la nébulosité totale, les précipitations et l’épaisseur de neige
  • La table contient time, location_id, latitude, longitude ainsi qu’une colonne pour chaque variable météo
    • La présence simultanée de location_id et des coordonnées sert à de futurs benchmarks de requêtes et d’index

Là où la structure des fichiers NetCDF devient lente

  • ERA5 est distribué sous forme de fichiers NetCDF, contenant généralement des données par jour, par mois ou par année
  • Une organisation en chunks par temps est rapide et simple pour consulter les données d’un instant précis
  • En revanche, pour observer un motif temporel comme une longue série chronologique en un point, il faut lire de nombreux fichiers, ce qui ralentit fortement l’accès
    • Dans l’exemple donné, l’extraction de la série de températures pour un emplacement prend 20 à 30 minutes
  • Les requêtes géospatiales complexes, en particulier celles qui incluent l’axe temporel, sont lentes et difficiles à exécuter
  • Des outils comme xarray, dask et Pangeo peuvent accélérer le traitement, mais cela reste un processus lent

insert : de la ligne unitaire au multi-lignes

  • L’approche la plus simple consiste à insérer les données ligne par ligne avec insert
  • Un insert unitaire implique plusieurs coûts
    • PostgreSQL doit parser la requête, valider les noms de table et de colonnes, puis construire un plan d’exécution
    • Des verrous peuvent être nécessaires pour garantir l’intégrité des données
    • Les données sont écrites dans des buffers pour le WAL (write-ahead logging)
    • Les lignes sont ensuite écrites dans l’espace disque réel de la table
    • Le commit de transaction rend finalement les changements durables
  • Trois méthodes d’insertion ligne par ligne ont été benchmarkées en Python
    • utilisation de chunksize=1 avec df.to_sql() de pandas
    • requêtes paramétrées avec psycopg3
    • requêtes paramétrées avec SQLAlchemy
  • Sur les insertions unitaires, psycopg3 est légèrement en tête et SQLAlchemy est le plus lent
    • Une hypertable TimescaleDB est légèrement plus lente qu’une table PostgreSQL classique
    • La meilleure performance atteint environ 3 000 lignes par seconde, ce qui porterait le chargement complet à près de 8 ans
  • L’insert multi-lignes permet de placer plusieurs lignes dans une seule requête afin de réduire les allers-retours réseau, le parsing et le coût de planification
    • psycopg3 est le plus rapide avec 25 000 à 30 000 lignes par seconde
    • pandas peut être plus lent car son mécanisme d’insertion basé sur des dictionnaires est moins efficace qu’une approche par tuples
    • SQLAlchemy peut ajouter du surcoût lié à la gestion de session et à l’abstraction des expressions SQL
    • Malgré cela, le chargement complet nécessiterait encore environ 0,8 an, soit presque 10 mois

copy : la voie de chargement en masse de PostgreSQL

  • PostgreSQL copy est une fonctionnalité de chargement en masse qui lit des lignes depuis des fichiers CSV ou binaires
  • Comme elle est conçue pour le bulk loading, elle optimise le parsing, la planification et l’usage du WAL, ce qui la rend plus rapide que l’insert multi-lignes
  • Deux approches sont comparées
    • écrire d’abord les données NetCDF en CSV puis les charger avec copy
    • streamer directement vers PostgreSQL avec cursor.copy() de psycopg3 sans créer de fichier CSV
  • Quand le fichier CSV est déjà prêt, copy atteint une vitesse d’insertion proche de 400 000 lignes par seconde
  • Si l’on inclut le coût d’écriture du CSV ou de création des tuples, copy et psycopg3 tournent tous deux autour de 100 000 lignes par seconde, avec un léger avantage pour psycopg3
  • Même à ce rythme, le chargement complet prendrait encore environ 3 mois

Débit soutenu et copy en parallèle

  • Lorsqu’on insère de très gros volumes, des goulets d’étranglement peuvent apparaître côté écriture disque, concurrence d’I/O entre WAL et table, autovacuum ou checkpoint
  • Dans une expérience portant sur environ 772 millions de lignes insérées en 744 lots, aucun ralentissement majeur n’est observé avec un seul worker
    • copy csv subit des baisses plus fréquentes et semble plus sensible aux variations
    • psycopg3 est globalement plus rapide
    • La différence entre table classique et hypertable reste limitée
  • Plusieurs tâches copy ou plusieurs curseurs psycopg3 ont été exécutés en parallèle avec joblib
  • L’insertion dans une table unique ne se parallélise pas particulièrement bien, et les performances plafonnent globalement au-delà de 16 workers

pg_bulkload et timescaledb-parallel-copy

  • En plus de PostgreSQL copy, les outils pg_bulkload et timescaledb-parallel-copy ont été benchmarkés
  • pg_bulkload semble plus rapide avec sa configuration par défaut, mais il contourne par défaut les shared buffers et saute l’écriture WAL, ce qui peut rendre impossible la récupération des données après un crash
  • Dans les mêmes conditions avec fsync désactivé, timescaledb-parallel-copy avec plusieurs workers fait mieux que pg_bulkload
  • timescaledb-parallel-copy permet de définir explicitement le nombre de workers pour les insertions parallèles
    • Les performances initiales sont bonnes, mais sur ce système un goulet d’étranglement apparaît avant 100 millions de lignes, faisant chuter brutalement le débit avant une reprise en vagues
    • Le débit soutenu se situe autour de 600 000 à 700 000 lignes par seconde sur une table classique, et autour de 300 000 lignes par seconde sur une hypertable
  • pg_bulkload ne propose pas de réglage direct du nombre de workers, mais l’option writer=parallel permet d’utiliser plusieurs threads pour la lecture, le parsing et l’écriture

Réglages PostgreSQL et compromis sur la durabilité

  • Des gains supplémentaires peuvent être obtenus en ajustant des paramètres non durables de PostgreSQL
  • Les principaux réglages consistent à désactiver fsync pour éviter les flush disque, et full_page_writes pour éviter la protection contre les écritures partielles de pages
  • Ces réglages peuvent compromettre l’intégrité de la base en cas de crash
  • Une unlogged table n’écrit pas dans le WAL, ce qui accélère les écritures, mais elle peut être tronquée lors de la récupération après incident
    • Il faut ensuite la convertir en table logged, ce qui peut être lent et mono-thread
    • Une hypertable ne peut pas être unlogged, donc si l’on a besoin d’une hypertable, des étapes supplémentaires de conversion ou de migration sont nécessaires

Choix final : charger directement dans une hypertable

  • Si la cible finale est une hypertable, il est plus rapide de la remplir directement que de charger d’abord une table classique puis de la convertir
  • Dans un test simple portant sur environ 772 millions de lignes insérées avec copy de psycopg3 et 16 workers, l’insertion directe dans l’hypertable prend moins de temps que l’insertion dans une table classique suivie d’une conversion
    • Dans ce cas, l’insertion directe dans l’hypertable représente environ 80 % du temps nécessaire à l’autre méthode
    • Le processus de conversion ou de migration n’est pas rapide et semble mono-thread
  • La méthode recommandée est la suivante
    • pour charger directement depuis un dataframe, utiliser copy de psycopg3 directement vers l’hypertable
    • si le fichier CSV existe déjà, utiliser timescaledb-parallel-copy
    • la parallélisation semble trouver son point d’équilibre entre 12 et 16 workers sur ce système

Conclusion générale des benchmarks et temps estimé

  • Avec les paramètres de protection activés et un seul worker, le plafond de débit soutenu, surcoûts inclus, semble être d’environ 140 000 lignes par seconde sur ce matériel
  • Avec plusieurs workers, il est possible de monter jusqu’à environ 250 000 lignes par seconde en débit soutenu avec le curseur copy de psycopg3 tout en conservant les protections
  • Le processus d’insertion se parallélise assez peu efficacement, et la zone optimale semble se situer entre 4 et 16 workers
  • En acceptant le risque et en désactivant fsync, psycopg3 peut maintenir environ 462 000 lignes par seconde
  • pg_bulkload désactive fsync par défaut, ce qui impose de l’utiliser avec prudence
  • À un rythme soutenu d’environ 462 000 lignes par seconde, le chargement d’environ 754 milliards de lignes prendrait environ 20 jours

Code et environnement de benchmark

  • Le code pour télécharger ERA5, créer les tables, effectuer les insertions et copy, lancer les benchmarks et générer les graphiques est disponible dans timescaledb-insert-benchmarks
  • Un nouveau conteneur Docker est lancé pour chaque benchmark afin de garantir un environnement cohérent
    • Le stockage n’est pas conservé entre les conteneurs Docker
    • Les fichiers NetCDF et CSV sont lus depuis un HDD
    • La base de données est stockée sur un SSD NVMe
  • Configuration matérielle
    • CPU: 2x 12-core Intel Xeon Silver 4214
    • RAM: 16x 16 GiB Samsung ECC DDR4 2666 MT/s
    • SSD: Intel 2 TB NVMe
    • HDD: Seagate Exos X16 14TB 7200 RPM
  • Configuration logicielle
    • Ubuntu 20.04, Linux kernel 5.15
    • PostgreSQL 15.5
    • TimescaleDB 2.13.0
    • pg_bulkload 3.1.20
  • La configuration PostgreSQL utilise les valeurs recommandées par timescaledb-tune pour 250.57GB de mémoire et 48 CPU, avec un ajustement spécifique de la taille du WAL pendant les benchmarks

À lire aussi

2 commentaires

 
jangsc0000 2024-04-18

Les avis GN+ sont rédigés au vouvoiement.. ?
 
GN⁺ 2024-04-17
Commentaires de Hacker News

  • J’ai pas mal fait d’analyse géospatiale dans mon travail, et les données géospatiales sont bien plus subtiles qu’on ne l’imagine, car il faut comprendre précisément les systèmes de référence de coordonnées (CRS) et les projections utilisées pour la visualisation.
    Sans infrastructure capable de transporter correctement les métadonnées avec les données, les CRS finissent par rendre constamment nerveux.
    J’ai utilisé les fonctionnalités AWS liées au sujet, Postgres/PostGIS, Spark/Databricks, Snowflake, Trino et ArcGIS, mais pour les traitements géospatiaux à grande échelle, je trouve que Google BigQuery est de très loin le meilleur.
    Une requête qui prenait plusieurs heures et coûtait cher sur PostGIS avec une énorme instance m6a EC2 s’exécutait en moins de 5 secondes sur le free tier de BigQuery.
    J’utilisais des données publiques de la FEMA ; Snowflake et les services AWS bloquaient parce que la colonne geometry dépassait la taille maximale en octets, Spark n’a même pas de type géospatial, et les extensions open source m’ont déçu.
    La situation peut être différente en on-premise, mais pour 20 To, le coût de stockage BigQuery a de fortes chances d’être inférieur à 100 $ par mois, donc je n’ai pas vraiment envie de l’exploiter moi-même.

    • J’ai traversé un parcours similaire en traitant des pipelines avec OSM mondial et Whosonfirst ; les coûts Google sont montés jusqu’à 7 k$ par mois avec Airflow + BigQuery, et nous avons remplacé ça par un achat unique de matériel à 7 k$.
      C’était possible parce qu’on utilisait H3 comme index au départ et que tous les jeux de données intermédiaires tenaient en mémoire.
      La configuration : Mac Studio 128 Go + Asahi Linux + fichiers parquet mmap + DuckDB ; on y fait aussi tourner Airflow, et on utilise Nix pour accélérer les builds développeur et exécuter les tâches Airflow de l’équipe data.
      GCP est très bien quand c’est gratuit ou peu cher, mais il peut vous surprendre à un moment donné avec une facture plus élevée, même sans hausse d’usage.
    • J’aimerais en savoir plus sur le fait que Spark n’a pas de type géospatial et que les packages open source sont décevants.
      As-tu comparé avec Apache Sedona, et qu’est-ce qui manquait concrètement ?
      Je travaille chez Wherobots, fondée par les créateurs d’Apache Sedona, donc j’aimerais beaucoup avoir ce retour.
      https://sedona.apache.org/latest/
      https://wherobots.com/
    • Ce serait bien de pouvoir obtenir un lien vers le jeu de données précis.
      Je suis d’accord pour dire que des outils avec des colonnes très larges peuvent casser, mais d’autres systèmes orientés colonnes de la famille Postgres devraient pouvoir les prendre en charge sans problème.
      Je suis surpris que Redshift, qui concurrence directement BigQuery, n’ait apparemment pas été utilisé ; la colonne super de Redshift peut même être configurée avec une limite supérieure à celle de BigQuery.
      Je vois souvent des gens découvrir à leurs dépens qu’il est difficile de battre PostGIS.
      Le fait que Trino/Presto et Spark stagnent dans ce domaine est aussi particulièrement révélateur.
    • Nous avons migré un data warehouse d’environ 500 Go d’un Postgres RDS 8 cœurs vers BigQuery : le temps de reconstruction est passé de 5 heures à 11 minutes, avec un coût similaire voire inférieur.
      Dans Postgres, nous mettions en cache une partie des grosses tables, alors que dans BigQuery nous reconstruisions tout depuis zéro, et c’était quand même le cas.
      Comme outil auto-hébergeable, je préfère Postgres, mais il est difficile de contester une amélioration des performances d’un ordre de grandeur.
    • J’entends vraiment énormément de bonnes choses sur BigTable et BigQuery, et je regrette de ne pas avoir encore eu l’occasion de les essayer.

  • Vraiment un excellent article.
    Je m’occupe du DevRel chez Timescale, et c’est agréable de voir la communauté produire un article aussi bien écrit.
    L’une des raisons pour lesquelles l’hypertable était plus lente est presque certainement qu’elle crée par défaut un index sur la colonne timestamp.
    La table classique n’avait pas d’index, ce qui a pu la rendre plus rapide.
    Avec create_hypertable, on peut utiliser create_default_indexes=>false pour ignorer la création d’index, ou bien supprimer les index avant de charger les données.
    Au final, cet index sera nécessaire, mais pour ce type de chargement par lots, il vaut mieux le créer en une seule fois après l’import.
    Je suis aussi curieux de savoir jusqu’où le HDD qui lit les données tient le coup dans une configuration fortement parallèle.

    • Je ne savais pas que create_default_indexes=>false existait ni que les hypertables créaient par défaut un index temporel, et j’ajouterai une note pour expliquer ce point.
      J’aimerais aussi faire un benchmark où l’on insère sans index temporel puis où l’on crée l’index manuellement.
      Même avec 32 workers, le HDD semblait s’en sortir.
      En observant l’utilisation disque avec btop, le SSD sur lequel tournait Postgres semblait plus proche du goulot d’étranglement que le HDD ; j’en ai donc conclu qu’investir dans un SSD plus rapide pour Postgres serait plus rentable que de déplacer les données du HDD vers le SSD.

  • Je ne vois pas pourquoi faire ça ainsi.
    La plupart des jeux de données météo et climatiques, y compris ERA5, sont très structurés sur une grille régulière latitude-longitude.
    Même si l’on n’extrait que la série temporelle d’un lieu précis, la force de ces jeux de données réside dans leur structure spatio-temporelle et leur contexte intrinsèques ; à moins que l’objectif soit uniquement d’extraire des séries temporelles ponctuelles, casser complètement cette structure n’a pas beaucoup de sens.
    Même dans le cas où l’on n’extrait que des séries temporelles ponctuelles, par exemple une série de température de surface au milieu de l’océan a très peu de chances d’être utile, donc on voudrait sans doute réduire les données assez radicalement.
    Pour la plupart des usages en recherche et en production de jeux de données comme ERA5, il me semble plus approprié d’utiliser des répliques optimisées pour le cloud qui préservent la structure d’origine, comme ARCO-ERA5 dans Google Public Datasets.
    Ces versions conservent la structure originale tout en découpant les données en chunks adaptés à l’accès massivement parallèle depuis le stockage cloud.
    Dans presque tous les cas que j’ai vus dans ma carrière, une archive basée sur Zarr avec un chunking standard suffit à être assez rapide pour la majorité des usages intéressants.
    https://cloud.google.com/storage/docs/public-datasets/era5

    • La raison principale, c’est que c’est un projet personnel : je voulais tout essayer sur mon serveur à la maison sans payer de ressources cloud, et apprendre Postgres, TimescaleDB, puis plus tard PostGIS.
      Cela dit, comme rabernat l’a mentionné, extraire de longues séries temporelles depuis une réplique cloud est aussi lent.
      À terme, je veux aussi faire des requêtes spatio-temporelles complexes, par exemple calculer le 99e percentile des températures estivales au Chili entre 1940 et 1980.
      Je ne doute pas qu’une réplique cloud puisse être plus rapide, mais cela entre en conflit avec un budget de 0 $.
    • C’est vrai, mais en pratique les données publiques Google ERA5 souffrent exactement du même problème de chunking que celui décrit dans l’article.
      Elles sont optimisées pour les requêtes spatiales, pas pour les requêtes de séries temporelles.
      Je viens de lancer un benchmark : récupérer la série temporelle d’une seule variable en un point a pris 20 minutes.
      Cela montre bien que si le schéma d’usage prévu est la série temporelle, il faut un chunking optimisé pour les séries temporelles.
    • Ce serait bien que quelqu’un le fasse et apprenne aussi aux autres à s’en servir.
      Certains labos ont déjà des pipelines basés sur un RDBMS mêlant algorithmes et données publiés, et personne ne veut les réimplémenter, ni n’a le budget pour le faire.
      La meilleure amélioration que nous ayons obtenue a été de passer d’un vieux MySQL à Postgres + PostGIS.
      Timescale aurait probablement aidé aussi.
      Il y avait aussi des raisons de faire tourner ça en local, comme la confidentialité, l’accès au cluster et le budget.

  • Bon article.
    Ce qui manque ici, à mon avis, c’est une analyse de ce que l’on gagne à déplacer des données météo vers une base de données relationnelle.
    La motivation est d’accélérer les requêtes, mais il faut un point de comparaison.
    En tant que mainteneur de Xarray et Zarr, et fondateur de https://earthmover.io/, je connais assez bien ce paysage technologique : avec un chunking approprié des données dans Zarr, on peut traiter des requêtes de séries temporelles sur des données météo avec une latence inférieure à la seconde, en utilisant uniquement une solution serverless + du stockage objet.
    C’est bien plus rapide que les 30 minutes mentionnées dans l’article.
    Vu la difficulté de chargement des données décrite dans cet article, ce genre de solution mérite d’être évalué sérieusement avant de partir sur la voie RDBMS.

    • Je suis d’accord : stocker dans des fichiers Zarr correctement chunkés serait presque certainement plus rapide, plus simple à configurer et moins gourmand en espace.
      On peut aussi mettre une API devant pour que cela ressemble à des requêtes.
      C’est vrai aussi que l’approche RDBMS n’a pas été suffisamment justifiée.
      La principale raison du choix Postgres + Timescale était que je voulais les apprendre, et que jouer avec les données ERA5 semblait le plus amusant.
      Le fait que les données météo soient assez volumineuses pour constituer un défi avait aussi son attrait.
      Sans preuve à l’appui, je me demande si un TimescaleDB + PostGIS bien réglé et bien indexé pourrait aider pour des requêtes spatio-temporelles complexes, comme le 99e percentile des températures estivales au Chili entre 1940 et 1980.
      Dans ce cas, il faudrait peut-être lire plusieurs chunks Zarr.
      J’aime aussi l’idée d’avoir une table séparée qui mettrait en cache ce type de statistiques, même si ce n’est pas si difficile à faire avec Zarr.
      Je prévois ensuite de benchmarker les requêtes et les index, donc j’en saurai sans doute davantage.
    • C’est un peu hors sujet, mais je m’intéresse au même domaine.
      Il me semble qu’il existe une tension fondamentale : les gros chunks sont bons pour la visualisation de vastes zones et les grosses requêtes, tandis que les petits chunks sont bons pour les requêtes ponctuelles ou de séries temporelles.
      Maintenir séparément plusieurs versions de ces grands jeux de données géospatiales avec des chunkings différents est possible, mais pas très rentable.
      J’ai entendu dire que kerchunk était utilisé pour essayer d’obtenir le meilleur des deux mondes, mais il me semble qu’on perd alors des options de compression des données et que la complexité augmente pas mal.
      Je me demande quelle est la meilleure façon de trouver un équilibre entre ces différents cas d’usage.

  • J’ai contribué à la documentation sur les chargements massifs dans Postgres citée ici, et c’est un bon tour d’horizon de plusieurs techniques.
    J’ai pas mal travaillé sur ce sujet pour accélérer le chargement des bases OpenStreetMap, et la dernière présentation publique de mise à jour se trouve ici : https://www.youtube.com/watch?v=BCMnu7xay2Y
    Depuis, grâce aux progrès du matériel, aux améliorations GIS de PG15 et à l’adoption par osm2pgsql de la technique middle-way-node-index-id-shift, le temps de chargement d’un jeu planet est passé sous les 4 heures.
    Une suggestion pour l’auteur : certaines expériences suppriment indirectement l’écriture dans le WAL via pg_bulkload et COPY.
    À l’époque où Craig Ringer a écrit le billet SO lié, ce n’était pas documenté, mais on peut tout simplement désactiver le WAL dans la configuration.
    Bien sûr, en cas de crash, on perd la table en cours, et si le traitement dure plusieurs semaines, cela peut arriver.
    Mais pour des données de séries temporelles, avec une bonne structure de chargement, il n’est pas difficile de faire en sorte de ne perdre que le dernier chunk.
    Les données WAL ne sont pratiquement pas nécessaires pour un chargement massif.
    En cas de crash, il suffit de nettoyer l’extrémité droite des données chargées et de reprendre.
    Les paramètres postgresql.conf que j’utilise pour désactiver le WAL et autres surcoûts sont les suivants :
    wal_level = minimal
    max_wal_senders = 0
    synchronous_commit = off
    fsync = off
    full_page_writes = off
    autovacuum = off
    checkpoint_timeout = 60min
    Enfin, pour réduire le travail de vacuum lors de chargements par gros chunks, je désactive généralement autovacuum comme ci-dessus, puis j’exécute périodiquement VACUUM FREEZE derrière la partition de date en cours de chargement.
    C’est pour du PG standard, et cela permet de sauter une partie du travail où la base se préoccupe d’états intermédiaires dans lesquels de nouvelles transactions ont été écrites mais ne sont pas encore visibles par tout le monde.

    • Je vais essayer les paramètres qui désactivent le WAL et les autres surcoûts pour voir si les insertions deviennent plus rapides.
      En particulier pour un chargement par chunks, c’est rassurant d’entendre d’un expert que les données WAL ne sont pas vraiment nécessaires aux chargements massifs.
      Je n’ai pas encore d’UPS, mais j’espère pouvoir tenir les quelque 20 jours nécessaires au chargement des données sans coupure de courant ; et dans le pire des cas, il me semble que je pourrai simplement reprendre.
    • Je me demande s’il existe des ressources pour en savoir plus sur les améliorations GIS de PG15.

  • Si l’OP est l’auteur, une expérience similaire que j’ai menée il y a environ 4 ans pourrait aussi l’intéresser.
    Même jeu de données, même cible, objectifs similaires.
    https://rdrn.me/optimising-sql/
    La démarche d’investigation est similaire, mais j’ai utilisé Postgres standard au lieu de Timescale ; dans ma configuration, en supposant que les données soient déjà en mémoire, la copie directe de données binaires était environ 3 fois plus rapide que COPY.

    • J’aurais aimé voir ça avant de commencer.
      J’ai laissé en note de bas de page la raison pour laquelle je n’ai pas essayé le COPY binaire : essentiellement parce que quelqu’un d’autre disait que les performances étaient décevantes.
      Mais je devrais quand même tester moi-même.
      https://aliramadhan.me/2024/03/31/trillion-rows.html#fn:copy-binary-note
    • J’ai lu les parties 1 et 2, et l’article était intéressant.
      J’aime aussi le format avec les notes dans la marge.
      La fonction qui écrit des tableaux structurés numpy en binaire Postgres m’a été utile ; je n’avais pas réussi à comprendre comment faire auparavant.

  • « Une base de données relationnelle est-elle vraiment adaptée aux données météo maillées ? Je ne sais pas, mais on le saura en essayant. »
    J’aime cette attitude.
    C’est tout l’inverse du style « en réalité, voyez-vous » de beaucoup d’articles tech grand public, et ça me plaît.
    J’aime aussi la façon dont le récit garde le lecteur accroché jusqu’au bout du parcours.

    • Le fait d’être débutant et de ne pas avoir d’enjeu particulier peut être un avantage.
      J’ai beaucoup cherché, mais je n’ai pas trouvé de réponse décisive pour mon cas d’usage, alors j’ai décidé de lancer moi-même des benchmarks.

  • Article intéressant.
    Le passage « à environ 462 k insertions par seconde en continu, il faudrait environ 20 jours pour quelque 754 milliards de lignes, ce qui semble pas mal. C’est moins que le temps qu’il m’a fallu pour écrire cet article » m’a fait rire.
    Comme je tends moi aussi vers des billets de blog plus longs et plus approfondis, je comprends que cela demande plus d’efforts qu’on ne l’imagine.

    • Certaines parties du benchmark ont pris plusieurs heures, et j’ai dû en relancer quelques-unes ; j’ai aussi beaucoup appris pendant le processus.

  • Si vous voulez tracer directement des graphiques de séries temporelles, ou plusieurs graphiques, à partir de requêtes SQL, qStudio est un IDE SQL gratuit qui fonctionne avec plusieurs bases, dont TimescaleDB.
    https://www.timestored.com/qstudio/database/timescale
    Disclaimer : c’est un outil que j’ai créé.

    • Je me demande quelle est la procédure pour ajouter la prise en charge d’autres bases de données à qStudio.
      Je me dis qu’on pourrait peut-être ajouter la prise en charge de Timeplus.
      Timeplus est une base de données orientée streaming basée sur ClickHouse, et son moteur de base de données principal, Timeplus Proton, est open source.
      Comme qStudio est aussi open source et écrit en Java, il semble qu’un pilote JDBC soit nécessaire pour prendre en charge un nouveau SGBDR.
      Si c’est bien le cas, Timeplus Proton dispose d’un pilote JDBC open source basé sur le pilote ClickHouse, avec des modifications pour les usages en streaming.
      https://www.timeplus.com/
      https://github.com/timeplus-io/proton
      https://github.com/timeseries/qstudio
      https://github.com/timeplus-io/proton-java-driver
    • J’utilisais surtout TablePlus et matplotlib via psycopg3 pour tracer les résultats de requêtes, mais ça a l’air plus rapide à utiliser.
      Pour l’instant je n’ai fait que l’insertion des données, mais je compte bientôt essayer les requêtes et le plotting.

  • Moi aussi, j’utilise les données de réanalyse ERA5 et j’ai besoin de séries temporelles rapides
    Comme les données arrivent sous forme de grille [lat, lon] empilée par période choisie, par exemple [données horaires sur un mois, lat, lon], si l’on veut plus de 20 ans, cela devient un énorme problème de transposition de matrice
    Ma méthode consiste à télécharger chaque fichier netCDF, à le transposer, puis à l’insérer dans un énorme fichier HDF 3D structuré en [lat, lon, hour]
    Sur ma station de travail, produire un an de données pour une variable prend environ 30 minutes, mais récupérer ensuite un seul emplacement (lat, lon) se fait en quelques millisecondes
    C’est une approche où l’on souffre au début pour en tirer profit à long terme
    C’est simple, mais je ne suis pas expert en bases de données, seulement climatologue

    • C’est simple, mais il y a de fortes chances que ce soit plus rapide et plus efficace en espace qu’une base de données relationnelle
      rabernat, qui a commenté ici, et open-meteo semblent aussi utiliser une approche similaire et la trouver rapide