Video-Analysen mit HyperLogLog in Postgres: So sparen Sie Speicher und Rechenleistung

Mit der richtigen Technologie lassen sich selbst riesige Datenmengen effizient analysieren, ohne die Performance zu opfern. Ein konkretes Beispiel zeigt, wie sich mit HyperLogLog in Postgres Video-Analytics revolutionieren lassen – ohne explodierende Speicherkosten oder langsame Abfragen.

Die Herausforderung: Zu viele Daten, zu wenig Nutzen

Stellen Sie sich vor, ein Nutzer springt in einem neunminütigen Video an die 4:12-Marke, schaut sich 40 Sekunden an, springt zurück zum Anfang und verlässt das Video wieder. Bei Hunderttausenden solcher Sitzungen pro Tag entsteht schnell ein Datenberg: Jede Sekunde, die ein Nutzer aktiv ist, wird als eigene Zeile in einer Tabelle gespeichert. Für ein einziges Video mit 540 Sekunden Laufzeit und einer Million Zuschauern summieren sich diese Zeilen auf Hunderte Millionen pro Tag.

Die eigentliche Frage der Produktteams lautet jedoch: Welche Abschnitte eines Videos werden am häufigsten angesehen – und wie viele einzigartige Zuschauer sehen sich diese an? Doch die Antwort darauf erfordert nur eine einfache Zählung pro Sekunde. Die naive Lösung mit einer watch_events-Tabelle führt zu einem exponentiellen Wachstum der Datenmenge, während die eigentliche Analyse immer nur eine aggregierte Übersicht benötigt.

Warum HyperLogLog die bessere Wahl ist

HyperLogLog ist ein Algorithmus, der die Anzahl einzigartiger Elemente in einer Menge schätzt – und das mit einem festen Speicherbedarf, unabhängig von der Datenmenge. Die Funktionsweise basiert auf der Analyse der längsten Nullfolgen in Hash-Werten. Je länger die Folge, desto mehr einzigartige Elemente müssen durch den Algorithmus geflossen sein, um eine solche Folge zu erzeugen.

Für Video-Analytics bietet HyperLogLog entscheidende Vorteile:

• Konstanter Speicherbedarf: Ein HyperLogLog-Sketch mit 12 Registerbits (4.096 Register) benötigt etwa 2,5 KB Speicherplatz – egal, ob eine oder 50 Millionen Zuschauer gezählt werden. Die Größe der Daten bleibt stabil, selbst bei starkem Traffic.

• Vereinigungsfähigkeit: HyperLogLog-Skizzen lassen sich kombinieren, ohne die ursprünglichen Daten erneut auszuwerten. Das ermöglicht regionale Analysen, die später zu einer globalen Heatmap zusammengeführt werden können.

• Steuerbarer Fehler: Bei einem Register mit 12 Bits liegt der Standardfehler bei etwa 1,6 %. Für Video-Analysen ist diese Ungenauigkeit praktisch unsichtbar und beeinträchtigt die Aussagekraft nicht.

Das PostgreSQL-HLL-Erweiterungsmodul, entwickelt von Citus und Aggregate Knowledge, implementiert diesen Algorithmus effizient. Die Installation erfolgt einmalig pro Datenbank:

CREATE EXTENSION IF NOT EXISTS hll;

Die technische Umsetzung: Eine Tabelle für effiziente Heatmaps

Um die Datenstruktur optimal zu nutzen, wird eine dedizierte Tabelle für Video-Heatmaps angelegt. Jede Zeile repräsentiert einen Zeitabschnitt eines Videos in einer bestimmten Region und enthält einen HyperLogLog-Sketch der Zuschauer.

CREATE TABLE video_heatmap (
    video_id text NOT NULL,
    region text NOT NULL,  -- z. B. 'JP', 'KR', 'GLOBAL'
    bucket_sec integer NOT NULL,  -- Sekunde der Wiedergabe, z. B. 8, 9, 10
    viewers hll NOT NULL,  -- HyperLogLog-Sketch der Zuschauer
    PRIMARY KEY (video_id, region, bucket_sec)
);

Ein zusätzlicher Index beschleunigt häufige Abfragen, etwa wenn nach einem spezifischen Video und einer Region gefiltert wird:

CREATE INDEX video_heatmap_lookup ON video_heatmap (video_id, region, bucket_sec) INCLUDE (viewers);

Das hll-Datentyp speichert den Sketch direkt in der Zeile. PostgreSQL stellt Funktionen bereit, um diese Skizzen zu verarbeiten:

• hll_add_agg und hll_union_agg für Aggregationen
• hll_add zum Hinzufügen von Werten
• hll_cardinality zur Schätzung der Zuschauerzahl
• Der ||-Operator zum Vereinigen von Skizzen

Live-Daten verarbeiten: Keine Rohdaten, nur Skizzen

Anstatt jede Zuschaueraktion als separate Zeile zu speichern, sendet der Videoplayer regelmäßige Heartbeats. Diese enthalten die Sekundenbereiche, die der Nutzer angesehen hat, sowie eine anonyme, gehashte Zuschauer-ID. Der entscheidende Vorteil liegt darin, dass diese Heartbeats nicht als Rohdaten gespeichert, sondern direkt in die HyperLogLog-Skizzen integriert werden.

Ein Python-Skript übernimmt die Verarbeitung der Heartbeats. Es konsumiert die Daten aus einer Warteschlange und wendet sie in einer einzigen SQL-Anweisung pro Heartbeat an. Der Trick: Die Funktion generate_series erweitert den angegebenen Sekundenbereich, sodass jede Sekunde im Bereich mit dem Sketch des Zuschauers aktualisiert wird.

import hashlib
import os
import psycopg

DB = os.environ["ANALYTICS_DSN"]  # z. B. postgresql://app@db/analytics
VIEWER_SALT = os.environ["VIEWER_SALT"].encode()  # 64-Bit-Hash für die Zuschauer-ID

def viewer_hash(viewer_id: str) -> int:
    digest = hashlib.blake2b(viewer_id.encode() + VIEWER_SALT, digest_size=8).digest()
    return int.from_bytes(digest, "big", signed=True)

def record_watch(conn, video_id: str, region: str, start_sec: int, end_sec: int, viewer_id: str) -> None:
    h = viewer_hash(viewer_id)
    with conn.cursor() as cur:
        cur.execute(
            """
            INSERT INTO video_heatmap (video_id, region, bucket_sec, viewers)
            SELECT %(vid)s, %(region)s, g.b, hll_add(hll_empty(), hll_hash_bigint(%(h)s))
            FROM generate_series(%(start)s, %(end)s) AS g(b)
            ON CONFLICT (video_id, region, bucket_sec)
            DO UPDATE SET viewers = video_heatmap.viewers || hll_add(hll_empty(), hll_hash_bigint(%(h)s));
            """,
            {"vid": video_id, "region": region, "h": h, "start": start_sec, "end": end_sec},
        )

if __name__ == "__main__":
    with psycopg.connect(DB, autocommit=False) as conn:
        # Beispielaufruf – in der Praxis kommen die Daten aus Redis oder SQS
        record_watch(conn, "yt_abc123", "JP", start_sec=8, end_sec=47, viewer_id="sess_91f2")
        conn.commit()

Diese Methode reduziert nicht nur den Speicherbedarf drastisch, sondern beschleunigt auch die Abfragen. Statt Millionen von Zeilen zu durchsuchen, werden nur die komprimierten Skizzen kombiniert – ein Vorgang, der in Millisekunden abgeschlossen ist.

Fazit: Skalierbare Analysen für die Zukunft

Die Kombination aus HyperLogLog und PostgreSQL ermöglicht es, Video-Analytics auch bei extrem hohen Nutzerzahlen effizient zu gestalten. Die Lösung ist kostengünstig, skalierbar und liefert Ergebnisse in Echtzeit. Durch die Vereinigungsfähigkeit der Skizzen lassen sich regionale Daten zusammenführen oder Zeitbereiche aggregieren, ohne die Rohdaten erneut auswerten zu müssen. Für Unternehmen, die mit großen Datenmengen arbeiten, ist dieser Ansatz ein Game-Changer – besonders in dynamischen Märkten wie dem asiatisch-pazifischen Raum, wo virale Inhalte innerhalb weniger Stunden Millionen von Zuschauern erreichen können.