1 Billionen Wetterdaten in TimescaleDB laden

Aufbau eines Wetterdaten-Warehouse, Teil 1: 1 Billion Zeilen Wetterdaten in TimescaleDB laden

Warum das, was wir tun, wichtig ist

Warum wir ein Wetterdaten-Warehouse aufbauen

• Die Idee ist, historische Wetterdaten aus aller Welt zu sammeln und zu analysieren, um Anzeichen des Klimawandels zu untersuchen
• Mit einem großen Wetterdaten-Warehouse lässt sich regional nachvollziehen, ob Jakarta tatsächlich wärmer geworden ist oder Stürme heftiger wurden, ob es in Chile insgesamt heißer oder wolkiger wird usw.
• So kann man herausfinden, welche Regionen der Erde die stärksten Klimaveränderungen erlebt haben und welche Arten von Veränderungen aufgetreten sind
• Um solche Analysen weltweit durchzuführen, müssen die Abfragen im Data Warehouse schnell sein, und die Datenmenge ist enorm
• Der erste Schritt besteht darin, die Daten in PostgreSQL zu laden. TimescaleDB wirkt vielversprechend, um Time-Series-Abfragen zu beschleunigen, und PostGIS, um Geospatial-Abfragen schneller zu machen

Dateneinführung

• Verwendet werden nicht echte Beobachtungsdaten, sondern Daten aus dem Klimareanalyse-Produkt ERA5
• ERA5 ist das Ergebnis von Klimamodellläufen, die durch Beobachtungsdaten eingeschränkt werden. Wo viele Beobachtungen vorliegen, ähnelt es den Beobachtungen; wo keine vorhanden sind, bleibt es physikalisch konsistent und stimmt mit Klimastatistiken überein
• ERA5 liefert seit 1940 stündliche Daten für den gesamten Globus mit einer Auflösung von 0,25 Grad. Je Variable wie Temperatur, Niederschlag, Bewölkung oder Windgeschwindigkeit fallen mehr als 750 Millionen Zeilen an
• Diese Daten schnell in eine relationale Datenbank einzufügen, ist nicht einfach

Methoden zum Einfügen der Daten

INSERT-Anweisung für einzelne Zeilen

• Die einfachste Methode, aber sehr langsam. Bei 3000 Inserts pro Sekunde würde das Laden aller Daten rund 8 Jahre dauern
• Der Overhead durch Parsing, Prüfung von Tabellen/Spalten, Ausführungsplan, Tabellen-Locks, Schreiben in Buffer, Schreiben auf Disk, Commit usw. ist groß

Multi-Value-INSERT

• Fügt mit einer INSERT-Anweisung mehrere Zeilen ein. Das reduziert den Overhead für Netzwerk, Parsing und Ausführungsplanung
• psycopg3 ist mit 25.000 bis 30.000 Einfügungen pro Sekunde am schnellsten
• Trotzdem würde das Laden aller Daten noch etwa 10 Monate dauern

COPY-Anweisung

• Eine für Massendatenladung optimierte Methode. Sie liest direkt aus CSV- oder Binärdateien und optimiert Parsing, Planung und WAL-Nutzung
• Wenn bereits CSV-Dateien vorliegen, kann man einfach die COPY-Anweisung verwenden
• Mit COPY in psycopg3 sind mehr als 100.000 Einfügungen pro Sekunde möglich. Selbst inklusive Overhead lässt sich der gesamte Datenbestand in unter 3 Monaten laden
• Bei langen Hochgeschwindigkeits-Inserts mit COPY muss man auf mögliche Flaschenhälse achten

Paralleles COPY

• Mehrere COPY-Jobs parallel ausführen, um die Geschwindigkeit zu erhöhen
• Beim Einfügen in eine einzelne Tabelle ist der Parallelisierungseffekt begrenzt; ab mehr als 16 Workern gibt es keine Leistungsverbesserung mehr

Einsatz externer Tools

• Benchmarks mit pg_bulkload und timescaledb-parellel-copy
• pg_bulkload ist schnell, aber standardmäßig unsicher, weil es WAL überspringt
• timescaledb-parallel-copy kann mit mehreren Workern sicher mehr als 300.000 Zeilen pro Sekunde einfügen

Anpassung der PostgreSQL-Einstellungen

• Wenn man fsync und full_page_writes deaktiviert, lassen sich Schreibzugriffe auf die Disk vermeiden und höhere Geschwindigkeiten erreichen, das ist aber riskant
• Auch unlogged-Tabellen verwenden kein WAL und sind daher schneller, werden bei einem Crash jedoch abgeschnitten. Hypertables können nicht unlogged sein

Was ist die beste Methode?

• Am besten ist es, mit psycopg3 direkt per COPY in eine Hypertable zu schreiben. Für CSV-Dateien sollte timescaledb-parallel-copy verwendet werden
• Für die Parallelisierung sind 12 bis 16 Worker angemessen
• Wenn man Schutzmechanismen deaktiviert, sind bis zu 460.000 Zeilen pro Sekunde möglich, allerdings mit Risiken
• Mit besserer Hardware sind noch höhere Geschwindigkeiten möglich
• ClickHouse könnte schneller sein, aber der Autor entschied sich für TimescaleDB, weil er PostgreSQL lernen wollte
• Bei 460.000 Zeilen pro Sekunde lässt sich der gesamte Datenbestand in weniger als 20 Tagen laden

Meinung von GN⁺

• Der Versuch, ERA5-Daten zur Analyse in eine relationale Datenbank zu laden, ist spannend. Bisher war es üblich, NetCDF-Daten direkt mit xarray oder dask zu analysieren, aber mit einem Data Warehouse lassen sich komplexere Abfragen durchführen.
• Beeindruckend ist, dass der Autor mit fünf Jahre alter Hardware 460.000 Zeilen pro Sekunde einfügen kann. Mit aktueller Hardware wären vielleicht sogar 1 Million pro Sekunde möglich. Das Deaktivieren von fsync und full_page_writes kann allerdings die Datenbankintegrität gefährden und erfordert Vorsicht.
• Die Parallelverarbeitung von PostgreSQL scheint bei einer einzelnen Tabelle nicht besonders hilfreich zu sein. In Kombination mit Parallelisierung und Partitionierung wäre womöglich noch mehr Leistung möglich. Auch horizontale Skalierungslösungen für Postgres wie Citus wären einen Blick wert.
• Interessant ist auch, dass sich ERA5-Daten für die Analyse des Klimawandels nutzen lassen. So kann man vergangene Klimabedingungen in Regionen mit wenigen Beobachtungsdaten untersuchen. Man sollte jedoch berücksichtigen, dass ERA5 letztlich ein Modellergebnis ist. Es wurde zwar mit Beobachtungsdaten abgeglichen, bleibt aber mit Unsicherheiten behaftet.
• Für Analyseplattformen sind Cloud-Data-Warehouses wie Snowflake oder BigQuery üblich. Aber wie der Autor zu lernen und eigene Hardware zu betreiben, hat ebenfalls großen Wert. Gerade Klimadaten sind so umfangreich, dass sich ein Umzug in die Cloud nicht leicht bewerkstelligen lässt. Die tatsächlichen Analyseergebnisse werden spannend sein.

[jangsc0000]

Ist die GN+-Meinung in Höflichkeitsform geschrieben ..?

[GN⁺]

Hacker-News-Kommentare

Zusammengefasst ergibt sich Folgendes:

• Bei der Analyse georäumlicher Daten ist es wichtig, Koordinatenreferenzsysteme (CRS) und Kartenprojektionen zu verstehen. Für groß angelegte Geodaten-Workloads ist Google BigQuery am leistungsfähigsten.

• Ob relationale Datenbanken für rasterbasierte Wetterdaten geeignet sind, lässt sich nur durch Experimente klären.

• Der Grund, warum Hypertables in Timescale langsam sind, könnte am standardmäßig erzeugten Index auf der timestamp-Spalte liegen. Es ist sinnvoll, die Indexerstellung mit der Option create_default_indexes=>false zu überspringen oder die Indizes erst nach dem Laden der Daten anzulegen.

• Es fehlt an einer Analyse, welche Vorteile die Überführung von Wetterdaten in ein RDBMS überhaupt bringt. Auch mit Serverless + Objektspeicher lassen sich sehr schnelle Antwortzeiten erzielen.

• Die meisten Wetter-/Klimadatensätze wie ERA5 bestehen aus regelmäßigen Breiten-/Längengrad-Rastern, daher ist es keine gute Idee, diese Struktur vollständig aufzubrechen. Besser ist es, cloudoptimierte Versionen wie ARCO-ERA5 zu nutzen.

• Wenn man in PostgreSQL WAL deaktiviert und den Befehl VACUUM FREEZE regelmäßig ausführt, lässt sich die Performance beim Laden sehr großer Datenmengen weiter steigern.

• Falls COPY nicht verwendet werden kann, ist es ebenfalls eine gute Methode, Zeilen als JSON-Strings zu kodieren, sie als einzelnen Query-Parameter zu senden und json_to_recordset zu verwenden.