Eine Billion Wetterdaten in TimescaleDB laden

 (aliramadhan.me)
3 Punkte von GN⁺ 2024-04-17 | 2 Kommentare | Auf WhatsApp teilen
  • Um historische Wetterveränderungen weltweit schnell zu analysieren, müssen ERA5-Reanalyse-Daten in PostgreSQL/TimescaleDB geladen werden; je nach ausgewählten Variablen und Raster ergibt das etwa 754 Milliarden Zeilen
  • ERA5 bietet seit 1940 eine stündliche Auflösung und ein 0,25-Grad-Längen-/Breitengrad-Raster, doch in der NetCDF-Struktur kann selbst die Extraktion einer langen Zeitreihe für einen einzelnen Punkt 20 bis 30 Minuten dauern
  • Ein einzelnes insert schafft etwa 3.000 Zeilen pro Sekunde, sodass das vollständige Laden rund 8 Jahre dauern würde; auch Multi-Row-insert liegt mit psycopg3 bei nur 25.000 bis 30.000 Zeilen pro Sekunde und damit bei etwa 10 Monaten
  • PostgreSQL copy und psycopg3 cursor.copy() unterscheiden sich je nachdem, ob eine CSV bereits vorbereitet ist; inklusive Overhead liegt die Leistung bei etwa 100.000 Zeilen pro Sekunde, mit Parallelisierung und Tuning auch höher
  • Wenn direkt aus einem DataFrame geladen wird, ist direktes copy in eine Hypertable mit psycopg3 sinnvoll; wenn CSV-Dateien bereits vorliegen, eignet sich timescaledb-parallel-copy, wobei auf diesem System 12 bis 16 Worker der sinnvolle Bereich für Parallelisierung zu sein scheinen

Ein weltweites Wetterdaten-Warehouse aufbauen

  • Ziel ist es, ein Data Warehouse aufzubauen, mit dem sich historische Wetterdaten weltweit abfragen lassen, um bereits eingetretene Signale des Klimawandels zu analysieren
  • Beispielanalysen behandeln Fragen wie: Ist es in Jakarta tatsächlich heißer geworden oder treten Stürme häufiger auf? Ist ganz Chile wärmer geworden oder stärker bewölkt? Wie sehen regionale Veränderungen aus?
  • Für schnelle globale Analysen müssen die Data-Warehouse-Abfragen schnell sein, und der erste Schritt besteht darin, große Datenmengen in die Datenbank zu laden
  • Verwendet wird PostgreSQL als Basis, TimescaleDB ist vielversprechend für die Beschleunigung zeitbasierter Abfragen, und PostGIS ist eine naheliegende Option für spätere Beschleunigung räumlicher Abfragen

ERA5-Reanalyse-Daten und ein Umfang von 754 Milliarden Zeilen

  • Verwendet werden nicht die tatsächlichen Wetterbeobachtungen, sondern ERA5 climate reanalysis-Daten
    • Historische Beobachtungswerte können für bestimmte Regionen und Zeitpunkte lückenhaft sein
    • ERA5 ist die Ausgabe eines Klimamodells, das an Beobachtungen angepasst wurde, und wird in Wetter- und Klimaforschung breit genutzt
  • ERA5 deckt den gesamten Globus mit 0,25-Grad-Auflösung ab und liegt seit 1940 in stündlicher Auflösung vor
    • Zeit-Snapshots enthalten 727.080 Werte pro Variable
    • Das Raster umfasst 1.038.240 Gitterpunkte, bestehend aus 1.440 Längengraden und 721 Breitengraden einschließlich der Pole
    • Bei Indizierung nach Zeit und Ort ergeben sich pro Variable 753.836.544.000 Zeilen, also rund 754 Milliarden Zeilen
  • Zu ladende Variablen sind Temperatur, ost-westliche und nord-südliche Windgeschwindigkeit in 10 m Höhe, Gesamtbewölkung, Niederschlag und Schneefall
  • Die Tabelle enthält time, location_id, latitude, longitude sowie Spalten für die einzelnen Wettervariablen
    • location_id sowie Breiten-/Längengradspalten werden gemeinsam gehalten, um spätere Query- und Index-Benchmarks zu ermöglichen

Wo die NetCDF-Dateistruktur ausbremst

  • ERA5 wird als NetCDF-Datei verteilt, typischerweise in Dateien pro Tag, Monat oder Jahr
  • Die Chunk-Struktur entlang der Zeitachse ist schnell und einfach, wenn Daten für einen bestimmten Zeitpunkt abgefragt werden
  • Wenn jedoch zeitliche Muster wie lange Zeitreihen an einem einzelnen Punkt betrachtet werden sollen, müssen viele Dateien gelesen werden, was langsam ist
    • Im Beispiel dauert das Extrahieren einer Temperatur-Zeitreihe für einen Ort 20 bis 30 Minuten
  • Komplexe räumliche Abfragen, insbesondere mit Zeitachse, sind langsam und schwer auszuführen
  • Werkzeuge wie xarray, dask, Pangeo können die Geschwindigkeit verbessern, dennoch bleibt der Prozess langsam

insert: von Einzelzeilen zu Multi-Row

  • Der einfachste Ansatz ist, Zeilen einzeln per insert einzufügen
  • Ein einzelnes insert bringt mehrere Kostenfaktoren mit sich
    • PostgreSQL muss die Anweisung parsen, Tabellen- und Spaltennamen prüfen und einen Ausführungsplan erstellen
    • Für die Datenintegrität können Locks erforderlich sein
    • Für WAL (write-ahead logging) werden Daten in Puffer geschrieben
    • Die Daten werden in den eigentlichen Speicherbereich der Tabelle eingefügt
    • Beim Commit der Transaktion werden Änderungen dauerhaft gemacht
  • In Python wurden drei Ansätze für Einzelzeilen-Inserts benchmarked
    • pandas df.to_sql() mit chunksize=1
    • parametrisierte Abfragen mit psycopg3
    • parametrisierte Abfragen mit SQLAlchemy
  • Bei Einzelzeilen-Inserts liegt psycopg3 leicht vorn, SQLAlchemy ist am langsamsten
    • Eine TimescaleDB-Hypertable ist etwas langsamer als eine normale PostgreSQL-Tabelle
    • Die beste Leistung liegt bei etwa 3.000 Zeilen pro Sekunde, was für das vollständige Laden rund 8 Jahre bedeuten würde
  • Multi-Row-insert fügt mehrere Zeilen in einer Anweisung ein und reduziert damit Netzwerk-Roundtrips sowie Parse- und Planungskosten
    • psycopg3 ist mit 25.000 bis 30.000 Zeilen pro Sekunde am schnellsten
    • pandas kann langsamer sein, weil die Struktur mit Dictionaries langsamer als Tupel sein kann
    • SQLAlchemy kann zusätzlichen Overhead durch Session-Management und die Abstraktion von SQL-Ausdrücken verursachen
    • Trotzdem wären für das vollständige Laden noch etwa 0,8 Jahre, also fast 10 Monate, nötig

copy: der Bulk-Load-Pfad von PostgreSQL

  • PostgreSQL copy ist eine Bulk-Load-Funktion, die Zeilen aus CSV- oder Binärdateien einliest
  • Weil sie auf Masseneinspielung ausgelegt ist, optimiert sie Parsing, Planung und WAL-Nutzung und ist schneller als Multi-Row-insert
  • Zwei Pfade wurden verglichen
    • NetCDF-Daten zunächst als CSV speichern und dann mit copy laden
    • Ohne CSV-Datei direkt per psycopg3 cursor.copy() nach PostgreSQL streamen
  • Wenn eine CSV-Datei bereits vorliegt, erreicht copy fast 400.000 Zeilen pro Sekunde
  • Rechnet man den Overhead für das Schreiben der CSV-Datei oder die Tupelerzeugung mit ein, liegen sowohl copy als auch psycopg3 bei etwa 100.000 Zeilen pro Sekunde, wobei psycopg3 leicht schneller ist
  • Selbst bei dieser Geschwindigkeit würde das vollständige Laden noch rund 3 Monate dauern

Dauerhafte Laderate und paralleles copy

  • Beim Einfügen vieler Zeilen können Engpässe durch Schreibzugriffe auf den Datenträger, I/O-Konkurrenz zwischen WAL und Tabelleneinfügungen, autovacuum und Checkpoints entstehen
  • In einem Experiment mit rund 772 Millionen Zeilen in 744 Batches war bei einem einzelnen Worker kein starker Leistungseinbruch zu sehen
    • copy csv zeigt häufigere Einbrüche und ist anfälliger für Schwankungen
    • psycopg3 ist im Allgemeinen schneller
    • Der Unterschied zwischen normaler Tabelle und Hypertable ist nicht groß
  • Mit joblib wurden mehrere copy-Jobs oder mehrere psycopg3-Cursor parallel ausgeführt
  • Das Einfügen in eine einzelne Tabelle lässt sich nicht besonders gut parallelisieren; die Leistung stagniert meist nach 16 Workern

pg_bulkload und timescaledb-parallel-copy

  • Neben PostgreSQL copy wurden auch pg_bulkload und timescaledb-parallel-copy benchmarked
  • pg_bulkload wirkt mit den Standardeinstellungen schneller, umgeht jedoch standardmäßig die shared buffers und überspringt WAL-Logging, sodass nach einem Crash eine Wiederherstellung der Daten möglicherweise nicht möglich ist
  • Unter denselben Bedingungen mit deaktiviertem fsync schneidet timescaledb-parallel-copy mit mehreren Workern besser ab als pg_bulkload
  • Mit timescaledb-parallel-copy lässt sich die Zahl der Worker für parallele Inserts festlegen
    • Die Anfangsleistung ist gut, doch auf diesem System wird schon vor 100 Millionen Zeilen ein Flaschenhals erreicht, worauf die Insert-Rate stark abfällt und sich dann wellenförmig erholt
    • Die nachhaltige Laderate liegt bei normalen Tabellen bei etwa 600.000 bis 700.000 Zeilen pro Sekunde und bei Hypertables bei etwa 300.000 Zeilen pro Sekunde
  • pg_bulkload hat keine direkte Worker-Anzahl, nutzt aber mit der Option writer=parallel mehrere Threads für Lesen, Parsen und Schreiben

PostgreSQL-Tuning und der Kompromiss bei der Haltbarkeit

  • Zusätzliche Leistung lässt sich durch Anpassung nicht-dauerhafter Einstellungen in PostgreSQL erzielen
  • Zentrale Einstellungen sind das Deaktivieren von fsync, um Disk-Flushes zu vermeiden, und das Deaktivieren von full_page_writes, um den Schutz vor partiellen Page-Writes zu umgehen
  • Diese Einstellungen können bei einem Crash die Integrität der Datenbank gefährden
  • Eine unlogged table erzeugt kein WAL und schreibt daher schneller, kann bei Crash-Recovery aber abgeschnitten werden
    • Anschließend muss sie in eine normale logged table umgewandelt werden; dieser Prozess kann langsam sein und möglicherweise nur single-threaded laufen
    • Hypertables können nicht unlogged sein; wenn eine Hypertable benötigt wird, sind zusätzliche Konvertierungs- und Migrationsschritte nötig

Die endgültige Wahl: direkt in die Hypertable laden

  • Wenn das Ziel eine Hypertable ist, ist direktes Laden in die Hypertable schneller als erst in eine normale Tabelle zu laden und anschließend zu konvertieren
  • In einem einfachen Test mit rund 772 Millionen Zeilen via psycopg3 copy und 16 Workern benötigte das direkte Einfügen in die Hypertable weniger Zeit als das Einfügen in eine normale Tabelle plus anschließende Konvertierung
    • In diesem Fall dauerte das direkte Laden in die Hypertable ungefähr 80 % der Zeit
    • Der Konvertierungs- und Migrationsprozess ist nicht schnell und wirkt wie ein Single-Thread-Prozess
  • Die empfohlene Vorgehensweise ist daher
    • Wenn direkt aus einem DataFrame geladen wird: mit psycopg3 direkt per copy in die Hypertable
    • Wenn CSV-Dateien bereits vorhanden sind: timescaledb-parallel-copy verwenden
    • Für die Parallelisierung scheinen auf diesem System 12 bis 16 Worker der sinnvolle Bereich zu sein

Fazit der Benchmarks und benötigte Zeit

  • Bei einem einzelnen Worker mit aktivierten Schutzmechanismen scheint die Obergrenze der nachhaltigen Laderate inklusive Overhead auf dieser Hardware bei etwa 140.000 Zeilen pro Sekunde zu liegen
  • Mit mehreren Workern lässt sich die nachhaltige Laderate mit psycopg3 copy cursor unter Beibehaltung der Schutzmechanismen auf etwa 250.000 Zeilen pro Sekunde steigern
  • Der Insert-Prozess skaliert bei der Parallelisierung nur begrenzt; 4 bis 16 Worker scheinen der sinnvolle Bereich zu sein
  • Wer das Risiko eingeht und fsync deaktiviert, kann mit psycopg3 etwa 462.000 Zeilen pro Sekunde nachhaltig erreichen
  • pg_bulkload deaktiviert standardmäßig fsync, daher ist bei der Nutzung Vorsicht geboten
  • Bei dauerhaft etwa 462.000 Zeilen pro Sekunde dauert das Laden von rund 754 Milliarden Zeilen ungefähr 20 Tage

Code und Benchmark-Umgebung

  • Code für ERA5-Download, Tabellenerstellung, Insert/copy, Benchmarks und Diagrammerzeugung steht unter timescaledb-insert-benchmarks bereit
  • Für jeden Benchmark wurde ein frischer Docker-Container gestartet, um eine konsistente Umgebung sicherzustellen
    • Storage wurde nicht zwischen Docker-Containern beibehalten
    • NetCDF- und CSV-Dateien wurden von einer HDD gelesen
    • Die Datenbank wurde auf einer NVMe-SSD gespeichert
  • Hardware-Konfiguration
    • 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
  • Software-Konfiguration
    • Ubuntu 20.04, Linux kernel 5.15
    • PostgreSQL 15.5
    • TimescaleDB 2.13.0
    • pg_bulkload 3.1.20
  • Für PostgreSQL wurden die von timescaledb-tune für 250,57 GB RAM und 48 CPUs empfohlenen Werte verwendet; in den Benchmarks wurde die WAL-Größe zusätzlich separat angepasst

Verwandte Beiträge

2 Kommentare

 
jangsc0000 2024-04-18

Ist die GN+-Meinung in Höflichkeitsform geschrieben ..?
 
GN⁺ 2024-04-17
Hacker-News-Kommentare

  • Ich habe beruflich ziemlich viel Geodatenanalyse gemacht, und Geodaten sind viel subtiler, als man denkt, weil man Koordinatenreferenzsysteme (CRS) und Darstellungsprojektionen genau verstehen muss
    Ohne eine Infrastruktur, die Metadaten korrekt zusammen mit den Daten transportiert, fühlt sich CRS ständig unsicher an
    Ich habe die entsprechenden Funktionen von AWS, Postgres/PostGIS, Spark/Databricks, Snowflake, Trino und ArcGIS alle benutzt, aber für groß angelegte Geodatenarbeit halte ich Google BigQuery für mit Abstand am besten
    Eine Query, die auf einem riesigen m6a-EC2 mit PostGIS Stunden dauerte und viel kostete, lief im kostenlosen Tier von BigQuery in unter 5 Sekunden
    Ich habe öffentliche Daten von FEMA verwendet, aber Snowflake und AWS-Dienste scheiterten daran, dass die geometry-Spalte die maximale Byte-Größe überschritt, Spark hat keinen Geodatentyp und die Open-Source-Erweiterungen waren ebenfalls enttäuschend
    On-Premises kann die Lage anders sein, aber bei 20 TB liegen die BigQuery-Speicherkosten wahrscheinlich unter $100 pro Monat, daher würde ich das nicht unbedingt selbst betreiben wollen

    • Ich habe beim Verarbeiten globaler OSM- und Whosonfirst-Datenpipelines etwas Ähnliches erlebt und die Google-Kosten stiegen mit Airflow + BigQuery auf $7k pro Monat, sodass ich das durch einen einmaligen Hardwarekauf von $7k ersetzt habe
      Anfangs habe ich H3-Indizes verwendet, und weil alle Zwischendatensätze in den Speicher passten, war das möglich
      Das Setup besteht aus einem Mac Studio mit 128 GB + Asahi Linux + mmap-Parquet-Dateien + DuckDB; außerdem läuft darauf Airflow, und mit Nix beschleunige ich Entwickler-Builds und führe auch Airflow-Jobs des Datenteams aus
      GCP ist großartig, wenn es kostenlos oder billig ist, aber irgendwann kann eine höhere Rechnung überraschen, auch wenn die Nutzung gar nicht steigt
    • Ich würde gern mehr dazu hören, was genau damit gemeint ist, dass Spark keinen Geodatentyp hat und die Open-Source-Pakete enttäuschend sind
      Hast du es mit Apache Sedona verglichen, und was genau hat dir gefehlt?
      Ich arbeite bei Wherobots, gegründet von den Machern von Apache Sedona, und würde gern Feedback hören
      https://sedona.apache.org/latest/
      https://wherobots.com/
    • Es wäre gut, wenn du auf den konkreten Datensatz verlinken könntest
      Ich stimme zu, dass extrem breite Spalten viele Werkzeuge kaputtmachen, aber andere spaltenorientierte Postgres-Alternativen sollten das wohl problemlos unterstützen können
      Ich finde es überraschend, dass Redshift, das direkt mit BigQuery konkurriert, offenbar nicht verwendet wurde, und die super-Spalte in Redshift kann sogar noch größer werden als das Maximum von BigQuery
      Ich sehe oft, wie Leute auf die harte Tour lernen, dass PostGIS schwer zu schlagen ist
      Dass Trino/Presto und Spark in diesem Bereich stagniert haben, ist ebenfalls besonders aufschlussreich
    • Ich habe ein ca. 500-GB-Data-Warehouse von einem 8-Core-Postgres-RDS nach BigQuery migriert; die Rebuild-Zeit sank von 5 Stunden auf 11 Minuten, und die Kosten waren ähnlich oder sogar niedriger
      In Postgres wurden Teile großer Tabellen gecacht, in BigQuery wurde trotzdem alles von Grund auf neu aufgebaut
      Für selbst betriebene Werkzeuge mag ich Postgres lieber, aber es ist schwer, dagegen zu argumentieren, wenn die Performance um mehr als einstellige Faktoren besser wird
    • Ich höre wirklich sehr viel Gutes über BigTable und BigQuery, hatte aber bisher noch keine Gelegenheit, sie selbst auszuprobieren

  • Wirklich ein großartiger Artikel
    Ich bin bei Timescale für DevRel zuständig, und es freut mich, solche gut geschriebenen Beiträge aus der Community zu sehen
    Einer der Gründe, warum die hypertable langsamer war, ist ziemlich sicher, dass standardmäßig ein Index auf der timestamp-Spalte angelegt wird
    Die normale Tabelle war wahrscheinlich schneller, weil sie keinen Index hatte
    Mit create_hypertable und create_default_indexes=>false kann man das Anlegen der Indizes überspringen, oder man löscht die Indizes vor dem Einfügen der Daten
    Am Ende braucht man diesen Index natürlich, aber bei solchen Bulk-Loads ist es besser, ihn nach dem Laden in einem Schritt zu erstellen
    Mich würde außerdem interessieren, wie gut die HDD beim Lesen der Daten in einer stark parallelen Konfiguration mitgehalten hat

    • Ich wusste nicht, dass create_default_indexes=>false existiert und dass hypertable standardmäßig einen Zeitindex anlegt; ich werde dazu einen erklärenden Kommentar ergänzen
      Ich möchte auch einen Benchmark ausprobieren, bei dem ich ohne Zeitindex einfüge und den Index anschließend manuell erstelle
      Selbst mit 32 Workern schien die HDD gut mitzukommen
      Als ich die Datenträgerauslastung mit btop angesehen habe, wirkte die SSD, auf der Postgres lief, eher wie der Flaschenhals als die HDD; deshalb bin ich zu dem Schluss gekommen, dass es sinnvoller ist, die SSD für Postgres durch eine schnellere zu ersetzen, als die Daten von der HDD auf die SSD zu verschieben

  • Ich verstehe nicht, warum man das unbedingt so machen sollte
    Die meisten Wetter- und Klimadatensätze einschließlich ERA5 sind auf einem regelmäßigen Breiten-/Längengrad-Raster hochgradig strukturiert.
    Selbst wenn man nur Zeitreihen für bestimmte Orte herauszieht, liegt die Stärke solcher Datensätze in ihrer inhärenten räumlich-zeitlichen Struktur und ihrem Kontext; wenn es nicht ausschließlich darum geht, Punkt-Zeitreihen zu extrahieren, ergibt es wenig Sinn, diese Struktur vollständig zu zerstören.
    Selbst wenn man nur Punkt-Zeitreihen herauszieht, wird man Daten wahrscheinlich recht aggressiv reduzieren wollen, da man beispielsweise eine Zeitreihe der Oberflächentemperatur mitten im Ozean kaum je braucht.
    Für die meisten Forschungs- und Betriebsanwendungen von Datensätzen wie ERA5 scheinen Cloud-optimierte Replikate, die die ursprüngliche Struktur bewahren, wie ARCO-ERA5 aus den Google Public Datasets besser geeignet zu sein.
    Solche Versionen behalten die Originalstruktur bei und teilen die Daten so in Chunks auf, dass sie für großflächigen Parallelzugriff aus dem Cloud-Speicher geeignet sind.
    In fast allen Fällen, die ich in meiner Laufbahn gesehen habe, ist ein Zarr-basiertes Archiv mit allgemeinem Chunking allein schon schnell genug für die meisten interessanten Anwendungsfälle.
    https://cloud.google.com/storage/docs/public-datasets/era5

    • Der Hauptgrund war, dass es ein persönliches Projekt ist und ich alles auf meinem Heimserver ausprobieren wollte, ohne für Cloud-Ressourcen zu zahlen, und außerdem Postgres, TimescaleDB und später auch PostGIS lernen wollte.
      Wie rabernat sagte, ist es allerdings auch langsam, lange Zeitreihen aus Cloud-Replikaten herauszuziehen.
      Letztlich möchte ich auch komplexe räumlich-zeitliche Abfragen machen, etwa das 99. Perzentil der sommerlichen Temperaturen in Chile von 1940 bis 1980 berechnen.
      Ich bezweifle nicht, dass das Cloud-Replikat schneller sein könnte, aber das passt nicht zu einem Budget von 0 $.
    • Stimmt, aber tatsächlich hat auch der öffentliche Google-ERA5-Datensatz genau dasselbe Chunking-Problem, das im Artikel beschrieben wird.
      Er ist für räumliche Abfragen optimiert und nicht für Zeitreihenabfragen.
      Ich habe gerade einen Benchmark laufen lassen, und es dauerte 20 Minuten, um die Zeitreihe einer einzelnen Variablen an einem Punkt abzurufen.
      Das zeigt gut, dass man bei einem erwarteten Nutzungsprofil mit Zeitreihen ein auf Zeitreihen optimiertes Chunking braucht.
    • Es wäre schön, wenn das jemand umsetzen und anderen auch beibringen würde, wie man es nutzt.
      Manche Forschungslabore haben bereits RDBMS-basierte Pipelines, in denen veröffentlichte Algorithmen und Daten miteinander verflochten sind; niemand will sie neu implementieren, und es gibt auch kein Budget dafür.
      Die beste Verbesserung, die wir erreichen konnten, war ungefähr der Umstieg von altem MySQL auf Postgres + PostGIS.
      Timescale hätte vermutlich ebenfalls geholfen.
      Es gab auch Gründe, alles lokal laufen zu lassen, etwa Datenschutz, Cluster-Zugang und Budget.

  • Guter Artikel
    Was hier fehlt, ist meiner Meinung nach eine Analyse, was man eigentlich gewinnt, wenn man Wetterdaten in eine relationale Datenbank überführt.
    Die Motivation ist, die Abfragegeschwindigkeit zu erhöhen, aber dafür braucht man eine Baseline.
    Als Maintainer von Xarray und Zarr und Gründer von https://earthmover.io/ bin ich mit dieser technischen Landschaft ziemlich vertraut, und wenn man Daten in Zarr passend chunked, lassen sich Zeitreihenabfragen für Wetterdaten mit einer serverlosen Lösung plus Objektspeicher mit einer Latenz von unter einer Sekunde bedienen.
    Das ist viel schneller als die im Artikel genannten 30 Minuten.
    Wenn man bedenkt, wie aufwendig das Laden der Daten in diesem Artikel ist, lohnt es sich, solche Lösungen ernsthaft zu prüfen, bevor man den RDBMS-Weg einschlägt.

    • Ich stimme zu, dass das Speichern in passend gechunkten Zarr-Dateien fast sicher schneller wäre, einfacher einzurichten und weniger Speicherplatz brauchen würde.
      Man könnte auch eine API davorsetzen, damit es sich wie eine Abfrage anfühlt.
      Es stimmt auch, dass ich den RDBMS-Ansatz nicht ausreichend gerechtfertigt habe.
      Der Hauptgrund für Postgres + Timescale war, dass ich diese Systeme lernen wollte und es am spannendsten fand, mit ERA5-Daten herumzuspielen.
      Wetterdaten haben wohl auch den Reiz, groß genug zu sein, um eine echte Herausforderung darzustellen.
      Ohne Belege frage ich mich, ob ein gut abgestimmtes und indiziertes TimescaleDB + PostGIS bei komplexen räumlich-zeitlichen Abfragen wie dem 99. Perzentil der sommerlichen Temperaturen in Chile von 1940 bis 1980 helfen könnte.
      In diesem Fall müsste man möglicherweise mehrere Zarr-Chunks lesen.
      Die Idee einer separaten Tabelle zum Cachen solcher Statistiken gefällt mir auch, aber mit Zarr ist das ebenfalls nicht besonders schwierig.
      Als Nächstes will ich Abfragen und Indizes benchmarken, daher werde ich wohl mehr darüber erfahren.
    • Das führt etwas vom Thema weg, aber ich interessiere mich für denselben Bereich.
      Es scheint eine grundlegende Spannung zu geben: große Chunks sind gut für die Visualisierung großer Gebiete und große Abfragen, kleine Chunks dagegen für punktbasierte oder Zeitreihenabfragen.
      Es ist zwar möglich, solche großen Geodatenbestände in getrennten, unterschiedlich gechunkten Versionen vorzuhalten, aber kosteneffizient ist das nicht besonders.
      Ich habe gehört, dass kerchunk genutzt wird, um gewissermaßen das Beste aus beiden Welten zu bekommen, aber dann verliert man anscheinend die Optionen bei der Datenkompression, und die Komplexität steigt ziemlich stark.
      Ich frage mich, wie man am besten ein Gleichgewicht zwischen mehreren Anwendungsfällen findet.

  • Ich habe an der hier referenzierten Postgres-Dokumentation zum Bulk Loading mitgearbeitet; der Artikel gibt einen guten Überblick über verschiedene Techniken.
    Ich habe einiges davon ausprobiert, um das Laden der OpenStreetMap-Datenbank zu beschleunigen, und das letzte öffentliche Update dazu gibt es unter https://www.youtube.com/watch?v=BCMnu7xay2Y.
    Seitdem ist die Ladezeit für den Planet-Datensatz dank besserer Hardware, GIS-Verbesserungen in PG15 und der Übernahme der middle-way-node-index-id-shift-Technik in osm2pgsql auf unter 4 Stunden gefallen.
    Als Vorschlag an den Autor: Einige Experimente vermeiden indirekt WAL-Schreibvorgänge über pg_bulkload und COPY.
    Als Craig Ringer den verlinkten SO-Beitrag schrieb, war das noch nicht dokumentiert, aber man kann in der Konfiguration WAL einfach abschalten.
    Natürlich verliert man bei einem Absturz die Tabelle, die gerade verarbeitet wird, und wenn der Job mehrere Wochen dauert, kann das passieren.
    Bei Zeitreihendaten ist es aber nicht schwer, die Ladestruktur so aufzubauen, dass man höchstens den letzten Chunk verliert.
    Für Bulk Loads braucht man WAL-Daten praktisch nicht.
    Bei einem Absturz räumt man einfach das rechte Ende der geladenen Daten auf und startet erneut.
    Die postgresql.conf-Einstellungen, die ich verwende, um WAL und anderen Overhead abzuschalten, sind:
    wal_level = minimal
    max_wal_senders = 0
    synchronous_commit = off
    fsync = off
    full_page_writes = off
    autovacuum = off
    checkpoint_timeout = 60min
    Und schließlich schalte ich beim Laden großer Chunks normalerweise wie oben autovacuum aus, um die Vacuum-Arbeit zu verringern, und führe hinter der aktuell geladenen Datumspartition regelmäßig VACUUM FREEZE aus.
    Das gilt für normales PG und erlaubt der Datenbank, einen Teil der Arbeit zu überspringen, bei der sie sich um Zwischenzustände kümmert, in denen neue Transaktionen geschrieben wurden, aber noch nicht für alle sichtbar sind.

    • Ich werde versuchen, Einstellungen zum Abschalten von WAL und anderem Overhead zu verwenden und sehen, ob Inserts dadurch schneller werden.
      Gerade bei chunkweisem Laden ist es gut, von einem Experten zu hören, dass WAL-Daten für Bulk Loads nicht wirklich nötig sind.
      Ich habe noch keine USV, hoffe aber, den rund 20 Tage dauernden Ladevorgang ohne Stromausfall zu überstehen, und selbst im schlimmsten Fall sollte ich wohl einfach weitermachen können.
    • Ich würde gern mehr Material zu den GIS-Verbesserungen in PG15 sehen.

  • Falls der OP der Autor ist, könnten auch einige ähnliche Experimente interessant sein, die ich vor etwa 4 Jahren gemacht habe.
    Gleicher Datensatz, gleiches Ziel, ähnliches Vorhaben.
    https://rdrn.me/optimising-sql/
    Die Untersuchung verlief ähnlich, aber ich habe normales Postgres statt Timescale verwendet, und in meinem Setup war direktes Kopieren binärer Daten unter der Annahme, dass die Daten bereits im Speicher liegen, etwa 3-mal schneller als COPY.

    • Das hätte ich gern vor dem Start gesehen.
      Ich habe in einer Fußnote vermerkt, warum ich Binary COPY nicht ausprobiert habe; im Grunde, weil jemand anderes sagte, die Leistung sei enttäuschend.
      Trotzdem sollte ich es wohl selbst ausprobieren.
      https://aliramadhan.me/2024/03/31/trillion-rows.html#fn:copy-binary-note
    • Ich habe Teil 1 und 2 gelesen, und der Artikel hat mir gefallen.
      Auch das Format mit Randnotizen ist gut.
      Es war hilfreich, dass du eine Funktion bereitgestellt hast, um strukturierte NumPy-Arrays als Postgres-Binärformat zu schreiben; das hatte ich vorher nicht herausfinden können.

  • „Ist eine relationale Datenbank für gitterförmige Wetterdaten überhaupt geeignet? Keine Ahnung, aber wir werden es herausfinden.“
    Diese Haltung gefällt mir.
    Sie ist das genaue Gegenteil des „also, in Wahrheit“-Stils vieler anderer Mainstream-Techartikel, und genau das mag ich daran.
    Mir gefällt auch, dass der Artikel die Leser auf der ganzen Reise bis zum Ende dabeihält.

    • Vielleicht ist es sogar ein Vorteil, als Anfänger keine Interessenkonflikte zu haben.
      Ich habe viel gesucht, aber keine eindeutige Antwort für meinen Anwendungsfall gefunden, also habe ich beschlossen, selbst Benchmarks zu fahren.

  • Interessanter Artikel.
    Die Stelle „Mit dauerhaft etwa 462k Inserts pro Sekunde würde das für rund 754 Milliarden Zeilen ungefähr 20 Tage dauern, was gar nicht so schlecht klingt. Kürzer als das Schreiben dieses Artikels.“ fand ich lustig.
    Ich neige auch zu längeren, tiefergehenden Blogposts und kann nachvollziehen, dass das mehr Aufwand ist, als man denkt.

    • Einige der Benchmarks dauerten mehrere Stunden, und manche musste ich mehrfach neu laufen lassen; dabei habe ich auch viel gelernt.

  • Wenn du Zeitreihencharts oder mehrere Diagramme direkt aus SQL-Abfragen zeichnen möchtest, ist qStudio eine kostenlose SQL-IDE und funktioniert mit TimescaleDB sowie vielen anderen Datenbanken.
    https://www.timestored.com/qstudio/database/timescale
    Hinweis: Das ist mein Tool.

    • Mich würde interessieren, wie der Prozess aussieht, um qStudio um Unterstützung für weitere Datenbanken zu erweitern.
      Ich denke, man könnte vielleicht Unterstützung für Timeplus hinzufügen.
      Timeplus ist eine auf ClickHouse basierende, Streaming-first-Datenbank, und die zentrale DB-Engine Timeplus Proton ist Open Source.
      qStudio ist ebenfalls Open Source und in Java geschrieben, daher scheint für die Unterstützung eines neuen RDBMS ein JDBC-Treiber nötig zu sein.
      Falls das stimmt: Für Timeplus Proton gibt es einen Open-Source-JDBC-Treiber, der auf dem ClickHouse-Treiber basiert und zusätzlich für Streaming-Anwendungsfälle angepasst wurde.
      https://www.timeplus.com/
      https://github.com/timeplus-io/proton
      https://github.com/timeseries/qstudio
      https://github.com/timeplus-io/proton-java-driver
    • Ich habe bisher meist TablePlus und matplotlib über psycopg3 verwendet, um Abfrageergebnisse zu visualisieren, aber das hier scheint schneller einsetzbar zu sein.
      Bisher habe ich nur Daten eingefügt, aber bald werde ich auch Abfragen und Plotting ausprobieren.

  • Ich verwende ebenfalls ERA5-Reanalyse-Daten und brauche schnelle Zeitreihen
    Die Daten kommen als [lat, lon]-Gitter, aufgestapelt nach dem gewählten Zeitraum, zum Beispiel in der Form [stündliche Daten für einen Monat, lat, lon], sodass alles über mehr als 20 Jahre zu einem riesigen Matrix-Transponierungsproblem wird
    Meine Vorgehensweise ist, jede netCDF-Datei herunterzuladen, zu transponieren und dann in eine riesige 3D-HDF-Datei in der Form [lat, lon, hour] zu packen
    Auf meiner Workstation dauert es etwa 30 Minuten, ein Jahr für eine Variable zu erzeugen, aber danach lässt sich eine einzelne (lat, lon)-Position in Millisekunden abrufen
    Das ist ein Ansatz mit anfänglichem Aufwand und langfristigem Gewinn
    Simpel, aber ich bin kein Datenbankexperte, sondern nur Klimaforscher

    • Simpel, aber vermutlich schneller und platzsparender als eine relationale Datenbank
      Es scheint, als würden rabernat in den Kommentaren hier und open-meteo etwas Ähnliches machen und es ebenfalls für schnell halten