Zeitreihendaten - Teil 2.

Einleitung

Im ersten Teil haben wir untersucht, ob PostgreSQL als Speicher für Zeitreihendaten verwendet werden kann. Unsere Ergebnisse waren nicht schlecht, aber die Leistung war für unseren Anwendungsfall zu langsam.

Eine Möglichkeit, die Leistung zu steigern, ist die Verwendung spezialisierter Zeitreihendatenbanken. Eine dieser Datenbanken ist TimescaleDB. Das Coole daran ist, dass es eine Erweiterung innerhalb von PostgreSQL ist. Sie können weiterhin PostgreSQL und all Ihr Wissen nutzen - und gleichzeitig bekommen Sie viele coole Funktionen für Zeitreihendaten.

Klingt zu gut, um wahr zu sein. Schauen wir es uns an.

Das Testszenario für TimescaleDB

Wir erstellen das gleiche Szenario wie im ersten Teil des Beitrags. Kurz gesagt: 5 Benutzer, 10 Millionen Ereignisse über 5 Jahre und dann eine Aggregationsabfrage darüber.

Los geht’s…

CREATE DATABASE timescale_test;

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "timescaledb"; --  Running version 2.11.2

Die letzte Zeile macht die Magie und lädt Timescale als Erweiterung in Ihre Datenbank. Sie haben nun alle Vorzüge von Timescale zur Verfügung. Timescale hat eine ziemlich gute Dokumentation dazu. Der einfachste Weg zum Einstieg ist deren Docker-Image.

Ein anderer Weg ist die Installation über brew und das Laden in Postgres.app - das habe ich als bescheidener Mac-Benutzer getan. Ich habe auch den brew tap so modifiziert, dass er mit PostgreSQL 15 kompatibel ist.

Aber fahren wir mit dem Testaufbau fort. Natürlich brauchen wir eine Tabelle…

CREATE TABLE events (
  user_id UUID NOT NULL,
  timestamp TIMESTAMPTZ NOT NULL,
  payload TEXT NOT NULL,
  source TEXT NOT NULL,
  target TEXT NOT NULL
);

Und jetzt kommt die Magie - wir können die Tabelle in eine Hypertable verwandeln. Die Hypertable ist im Grunde eine partitionierte Tabelle mit einem Standard-Index auf der Zeit, erstellt und gepflegt von TimescaleDB:

SELECT create_hypertable('events', 'timestamp');

Magie. Jetzt erstellen wir die Testdaten…

DO $$
DECLARE
  user_id UUID;
BEGIN
  FOR i IN 1..10 LOOP
    user_id = gen_random_uuid();
    INSERT INTO events (
      user_id,
      timestamp,
      payload,
      source,
      target
    )
    SELECT
      user_id,
      NOW() - interval '1 second' * trunc(random() * 60 * 60 * 24 * 365 * 5),
      'dummypayload-.' || md5(random()::text),
      'dummysource-' || md5(random()::text),
      'dummytarget-' || md5(random()::text)
    FROM generate_series(1, 10000000);
  END LOOP;
END $$;

Und schalten \timing ein und führen unsere erste Abfrage aus:

EXPLAIN
SELECT
    time_bucket('1 day', timestamp) AS day,
    COUNT(*) AS count
FROM
    events
WHERE user_id = '6a7f240e-44fa-455e-b97a-a9d7dcc74b29'
    AND timestamp >= NOW() - INTERVAL '5 years'
GROUP BY
    day
ORDER BY
    day;

Das dauerte in der Datenbank 8 Sekunden. Das ist bereits 50% schneller als die nicht-optimierte PostgreSQL-Version. Cool.

Aber natürlich können wir hier auch einen spezialisierten Index hinzufügen:

CREATE INDEX on events(user_id, timestamp);

Jetzt sind wir bei 700 Millisekunden. Das ist fast viermal schneller als die reine PostgreSQL-Version. Dies ist ebenfalls ein Index-Only-Scan, aber der Query-Plan sieht anders aus. Eine Hypertable ist eine schöne Art zu sagen “eine Tabelle, die automatisch nach Zeit partitioniert wird”. Daher kann die Abfrage offenbar etwas besser parallelisieren als ohne Partitionierung. Interessant.

Das ist cool und lässt sich ohne zu viel Magie erreichen. Einfach eine Erweiterung hinzufügen. Eine Hypertable erstellen und Sie erhalten den Leistungsschub. Schön!

Mehr coole Sachen

Ich bin kein TimescaleDB-Experte, aber es gibt noch mehr coole Sachen. Timescale hat Continuous Aggregates. Das sind Materialized Views, die automatisch aktualisiert werden. Und deren Abfrage beinhaltet auch die neuesten Daten aus der Quelltabelle, falls sie noch nicht im Materialized View enthalten sind. Ziemlich cool.

Sie bekommen auch viele Funktionen, die bei Zeitreihendaten helfen. time_bucket_gapfill kann automatisch “fehlende” Werte durch Interpolation oder Setzen auf 0 füllen. Sehr nützlich.

Und das ist nur ein Einblick in das, was TimescaleDB kann.

Zusammenfassung

Unser Vergleich sieht jetzt so aus:

TimescaleDB scheint für unser Testszenario schneller zu sein. Wenn Sie nicht so viele Daten haben, können Sie weiterhin PostgreSQL verwenden. Sobald Ihre Daten wachsen, können Sie jederzeit auf TimescaleDB umsteigen und die Vorteile nutzen.

Mehr

• Tolles Foto oben von Jon Tyson: https://unsplash.com/photos/FlHdnPO6dlw