Construcción de mapas de calor de video con HyperLogLog en PostgresQ
Quieres mostrar a los editores exactamente dónde dejan de ver un video los espectadores.
Para hacer esto, necesitas un mapa de calor. Este mapa de calor muestra los espectadores únicos para cada segundo de un video.
En TrendVidStream, procesamos 40 millones de eventos cada día. Utilizamos un stack sencillo: PHP 8.4 y Postgres.
El problema es la cardinalidad. Contar espectadores únicos por segundo es difícil.
El enfoque ingenuo:
- Usar una tabla de eventos sin procesar.
- Ejecutar COUNT(DISTINCT viewer_hash).
- Agrupar por video y bucket de tiempo.
Esto falló rápidamente. Para 90 días de datos, necesitas 3.6 mil millones de filas. Las consultas tardaban 9 segundos. Además, los conteos únicos no se pueden consolidar (roll up). No puedes sumar los "únicos por hora" para obtener los "únicos por día" porque la misma persona aparece en ambos.
La solución: HyperLogLog (HLL).
HLL crea un pequeño "sketch" de un conjunto de datos. Es diminuto, alrededor de 10 KB, independientemente de cuántos millones de personas lo vean.
Por qué HLL funciona para los mapas de calor:
- Tamaño fijo: Se mantiene pequeño.
- Combinable: Puedes unir dos sketches para obtener un conteo global. Esto resuelve el problema de la consolidación.
Nuestra implementación:
Utilizamos la extensión postgresql-hll. Usamos dos tablas.
- Una tabla de staging: Una tabla de solo inserción (append-only) donde PHP escribe los heartbeats sin procesar. No usamos índices aquí para mantener las escrituras rápidas.
- Una tabla de sketches: Esta almacena los datos HLL.
Utilizamos un cron job para mover los datos de la tabla de staging a la tabla de sketches. Usamos este comando:
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;
El operador "||" combina los nuevos datos con el sketch existente.
Los resultados:
- Velocidad de consulta: Nuestras consultas de mapas de calor tardan entre 15 y 40 ms. El método anterior tardaba 9 segundos.
- Almacenamiento: Nuestro conjunto de datos de 90 días ocupa 40 GB. El método anterior habría ocupado 1.2 TB.
- Precisión: Observamos un error de entre el 0.6% y el 1.4%. Para un mapa de calor, esto es perfecto.
Lecciones aprendidas:
- Realiza el hash dentro de la base de datos. No realices el hash en tu aplicación.
- Realiza actualizaciones por lotes (batch). No actualices los sketches fila por fila o crearás puntos calientes de escritura (write hotspots).
- Usa un salt diario para los hashes. Esto protege la privacidad y mantiene limpios tus sketches diarios.
Si tu dashboard es lento debido a COUNT(DISTINCT), no necesitas un almacén de datos (data warehouse) masivo. Necesitas HLL.