Time-Series-Schema auf TigerData: Hypertables, Continuous Aggregates, Compression, Retention

Eine Zeitreihendatenbank nimmt Ihnen die Partitionierung ab, nicht das Nachdenken. Ob Ihre Plattform im dritten Jahr noch in Millisekunden antwortet oder ob jede Jahresauswertung zum Kaffeeholen einlädt, entscheidet sich im Schema – lange bevor die erste Zeile geschrieben wird.

Dieser Artikel zeigt das Schema-Design für industrielle Messdaten auf TigerData, der Plattform hinter TimescaleDB. Die Daten kommen dabei aus einem HiveMQ-Broker und werden über einen geplanten Job in der Datenbank transformiert – wie in ELT statt ETL beschrieben. Hier geht es um das, was danach passiert: wie Fakten und Identitäten getrennt werden, wie Hypertables, Continuous Aggregates, Compression und Retention zusammenspielen – und welche Fallen wir in realen Projekten am häufigsten sehen.

Schmale Fakten, Identität in einer Dimension

Der erste Reflex ist, alles in eine Tabelle zu schreiben: den vollen Topic-Pfad, den Anlagennamen, die Einheit, den Wert. Bei einer Million Zeilen pro Tag wiederholen Sie damit dieselbe Zeichenkette eine Million Mal.

Der Star-Schema-Instinkt zahlt sich aus. Identität – also alles, was ein Signal ausmacht – wandert in eine Dimensionstabelle mit einem ganzzahligen Surrogatschlüssel. Die Faktentabelle trägt nur noch drei Spalten:

CREATE TABLE measurement (
  ts       timestamptz NOT NULL,
  tag_id   integer     NOT NULL REFERENCES tag(id),
  value    double precision
);

SELECT create_hypertable('measurement', 'ts',
  chunk_time_interval => INTERVAL '1 day');

Schmal, dicht, hervorragend komprimierbar. Alles Beschreibende – Topic-Pfad, Anlage, Einheit, Datentyp – steht einmal in tag. Diese Trennung ist der grösste einzelne Hebel für Speicherplatz und Abfragegeschwindigkeit.

Hypertables: Partitionierung, die Sie nicht verwalten

Eine Hypertable sieht aus wie eine normale Tabelle und ist intern in Chunks nach Zeit zerlegt. Inserts landen fast immer im jüngsten Chunk, der klein genug ist, um im Speicher zu bleiben. Abfragen mit Zeitfilter berühren nur die betroffenen Chunks.

Die Chunk-Grösse ist die einzige Stellschraube, die wirklich zählt. Faustregel: Ein Chunk sollte in den Arbeitsspeicher passen. Zu grosse Chunks verlieren den Vorteil, zu kleine erzeugen tausende Objekte, die der Planer durchgehen muss. Bei industriellen Volumen ist ein Tag ein guter Startwert.

Continuous Aggregates: Rollups, die sich selbst pflegen

Ein Dashboard, das ein Jahr Sensordaten aus Rohwerten aggregiert, scannt Milliarden Zeilen. Ein Continuous Aggregate ist eine materialisierte Sicht, die TigerData inkrementell fortschreibt – nur neue Zeitfenster werden berechnet.

Hierarchisch rollen

Der Kunstgriff: Bauen Sie die Ebenen aufeinander auf, statt jede aus den Rohdaten zu berechnen. Der Minutenwert kommt aus den Rohdaten, der Stundenwert aus dem Minutenwert, der Tageswert aus dem Stundenwert. So bleibt der Aufwand pro Ebene konstant klein, statt sich zu vervielfachen.

Man kann keinen Durchschnitt mitteln

Die Falle, die in fast jedem ersten Entwurf steckt. Wenn Sie in der Minutenebene avg(value) materialisieren und in der Stundenebene den Durchschnitt dieser Durchschnitte bilden, ist das Ergebnis falsch, sobald die Minuten unterschiedlich viele Messwerte enthalten – und das tun sie immer.

Die Lösung: Tragen Sie sum und count mit und teilen Sie erst beim Lesen.

CREATE MATERIALIZED VIEW measurement_1min
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 minute', ts) AS bucket,
       tag_id,
       sum(value)   AS value_sum,
       count(value) AS value_count,
       min(value)   AS value_min,
       max(value)   AS value_max
FROM measurement
GROUP BY bucket, tag_id;

Die Stundenebene summiert dann value_sum und value_count weiter, die Lese-View bildet value_sum / value_count. Min und Max sind unproblematisch, weil sie sich sauber verschachteln lassen – anders als der Mittelwert.

Compression: der Grund, warum Historie bezahlbar bleibt

Ältere Chunks lassen sich spaltenweise komprimieren. Entscheidend sind zwei Parameter: segmentby gruppiert nach dem Identitätsschlüssel, orderby sortiert innerhalb des Segments – in der Regel nach Zeit absteigend.

ALTER TABLE measurement SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'tag_id',
  timescaledb.compress_orderby   = 'ts DESC'
);

Richtig segmentiert erreichen Sensordaten Kompressionsfaktoren im hohen einstelligen bis zweistelligen Bereich – und Abfragen nach einem einzelnen Signal werden sogar schneller, weil weniger Blöcke gelesen werden müssen. Falsch segmentiert verlieren Sie beides.

Retention: verschiedene Tabellen, verschiedene Lebensdauern

Nicht alles muss ewig leben. Ein bewährtes Muster:

• Verbatim-Archiv der Rohnachrichten – wenige Tage bis Wochen. Es dient dem erneuten Verarbeiten, nicht der Analyse.
• Rohmesswerte – Monate. Fein genug für Fehlersuche und Modelltraining.
• Minuten- und Stunden-Rollups – Jahre. Klein, komprimiert, das Arbeitspferd der Dashboards.
• Tages-Rollups – unbegrenzt. Sie kosten praktisch nichts.

Alles davon läuft als Policy, nicht als Skript, das jemand pflegen muss. Wer diese Lebenszyklen erst nachträglich einführt, zahlt sie mit einer Migration – planen Sie sie mit dem Schema.

Zwei Fallen beim Auflösen von Identitäten

Verbrannte Sequenznummern

Der naheliegende Upsert lautet INSERT … ON CONFLICT DO NOTHING. Der Haken: PostgreSQL wertet die Zeile vollständig aus – inklusive nextval() auf dem Surrogatschlüssel – bevor der Konflikt erkannt wird. Jede Dublette erhöht die Sequenz still. Bei hoher Kardinalität und Millionen Nachrichten pro Tag bläht das Ihre IDs um Grössenordnungen auf.

Der Ausweg ist ein SELECT-first-Guard: Nur für Zeilen, die ein NOT EXISTS als wirklich neu ausweist, wird der Insert überhaupt versucht. Das ON CONFLICT bleibt als Absicherung gegen Race Conditions bestehen, aber der Normalfall verbrennt keine ID mehr.

Optionale Identitätsteile brauchen NULL-sichere Vergleiche

Manchmal identifiziert der Topic-Pfad ein Signal nicht vollständig – es gibt einen optionalen Zusatz, der mal da ist und mal nicht. Damit enthält Ihr Identitätsschlüssel eine nullbare Spalte, und «nicht vorhanden» muss gleich «nicht vorhanden» sein.

Standard-SQL sieht das anders: NULL ist nie gleich NULL. Sie brauchen deshalb UNIQUE NULLS NOT DISTINCT auf dem Constraint (ab PostgreSQL 15) und IS NOT DISTINCT FROM in den Join-Bedingungen. Wer das übersieht, dupliziert entweder Tags oder dedupliziert sie nie.

Betrieb: Hintergrundjobs brauchen ein Budget

Compression, Retention, Rollup-Refresh und Ihr eigener Transformationsjob laufen alle als Hintergrundjobs – und teilen sich denselben Arbeitsspeicher und dieselbe Worker-Anzahl. Drei Regeln aus der Praxis: Starten Sie schwere Policies zeitlich versetzt statt alle zur vollen Stunde. Begrenzen Sie die Laufzeit jedes einzelnen Laufs. Und zählen Sie Ihre geplanten Jobs gegen die verfügbaren Worker – wer die Rechnung nicht macht, erlebt Jobs, die still keinen Worker bekommen.

Welche Schicht beantwortet welche Frage

Die Regel für die Darstellungsschicht: Dashboards lesen Rollups für weite Zeiträume und Rohdaten nur für enge. Wer das umdreht, erzeugt genau die Ladezeiten, die Anwender wieder zu Excel greifen lassen.

Wie wir Sie unterstützen

Schema-Design ist der Teil eines Datenplattform-Projekts, der sich am schlechtesten nachträglich korrigieren lässt – und derjenige mit dem grössten Hebel. Wir entwerfen und betreiben solche Schemata für Schweizer Unternehmen als Teil unserer Arbeit an operativen Datensystemen und Datenplattformen, unter anderem für Industrie und Logistik und Verkehrsbetriebe.

Sie planen ein Time-Series-Schema oder haben eines, das langsam wird? → Kontakt aufnehmen – ein Review dauert typischerweise einen halben Tag und spart oft Monate.

Weiterführende Glossar-Artikel

• Industrielle Time-Series-Datenplattform – die Architektur, in der dieses Schema steht
• ELT statt ETL – wie die Daten überhaupt hierher kommen
• Schema-on-Read für IoT-Payloads – was mit den Nachrichten passiert, die nicht ins Schema passen
• Dead-Lettering und Reconciliation – sicherstellen, dass nichts fehlt
• Was ist ein Data Warehouse? – Abgrenzung zur klassischen Analysewelt
• OLAP vs. OLTP – warum Schreib- und Lesemuster verschiedene Schemata verlangen