Erstellung von Video-Heatmaps mit HyperLogLog in PostgreSQL
Sie möchten Redakteuren genau zeigen, an welcher Stelle Zuschauer aufhören, ein Video anzusehen.
Dazu benötigen Sie eine Heatmap. Diese Heatmap zeigt die Anzahl der eindeutigen Zuschauer für jede Sekunde eines Videos an.
Bei TrendVidStream verarbeiten wir täglich 40 Millionen Events. Wir nutzen einen einfachen Stack: PHP 8.4 und Postgres.
Das Problem ist die Kardinalität. Das Zählen eindeutiger Zuschauer pro Sekunde ist schwierig.
Der naive Weg:
- Eine Tabelle mit Rohdaten (Raw Events) verwenden.
COUNT(DISTINCT viewer_hash)ausführen.- Nach Video und Zeitintervall (Time Bucket) gruppieren.
Dies scheiterte schnell. Für 90 Tage an Daten benötigt man 3,6 Milliarden Zeilen. Die Abfragen dauerten 9 Sekunden. Zudem lassen sich eindeutige Zählungen nicht aggregieren (Rollup). Man kann „eindeutige Werte pro Stunde“ nicht einfach zu „eindeutigen Werten pro Tag“ summieren, da dieselbe Person in beiden Zeiträumen vorkommen kann.
Die Lösung: HyperLogLog (HLL).
HLL erstellt eine kleine „Skizze“ (Sketch) eines Datensatzes. Diese ist winzig, etwa 10 KB groß, unabhängig davon, ob Millionen von Menschen zusehen.
Warum HLL für Heatmaps funktioniert:
- Feste Größe: Sie bleibt klein.
- Zusammenführbar: Man kann zwei Skizzen vereinigen (Union), um eine globale Zählung zu erhalten. Dies löst das Aggregationsproblem.
Unsere Implementierung:
Wir verwenden die postgresql-hll-Erweiterung. Wir nutzen zwei Tabellen.
- Eine Staging-Tabelle: Eine Append-only-Tabelle, in die PHP die rohen Heartbeats schreibt. Wir verwenden hier keine Indizes, um die Schreibvorgänge schnell zu halten.
- Eine Sketch-Tabelle: Diese speichert die HLL-Daten.
Wir nutzen einen Cronjob, um die Daten von der Staging-Tabelle in die Sketch-Tabelle zu verschieben. Wir verwenden diesen Befehl:
INSERT INTO video_heatmap (...)
SELECT ..., hll_add_agg(hll_hash_text(viewer_hash), 14, 5)
FROM beacon_staging
GROUP BY ...
ON CONFLICT (...)
DO UPDATE SET viewers = video_heatmap.viewers || EXCLUDED.viewers;
Der „||“-Operator führt die neuen Daten in die bestehende Skizze zusammen.
Die Ergebnisse:
- Abfragegeschwindigkeit: Unsere Heatmap-Abfragen dauern 15 bis 40 ms. Die alte Methode dauerte 9 Sekunden.
- Speicherplatz: Unser 90-Tage-Datensatz ist 40 GB groß. Die alte Methode hätte 1,2 TB beansprucht.
- Genauigkeit: Wir sehen eine Abweichung von etwa 0,6 % bis 1,4 %. Für eine Heatmap ist das perfekt.
Erkenntnisse:
- Hashen Sie innerhalb der Datenbank. Hashen Sie nicht in Ihrer Applikation.
- Führen Sie Updates in Batches durch. Aktualisieren Sie Skizzen nicht Zeile für Zeile, da Sie sonst Schreib-Hotspots erzeugen.
- Verwenden Sie ein tägliches Salt für Hashes. Dies schützt die Privatsphäre und hält Ihre täglichen Skizzen sauber.
Wenn Ihr Dashboard aufgrund von COUNT(DISTINCT) langsam ist, benötigen Sie kein massives Data Warehouse. Sie benötigen HLL.