Construindo Mapas de Calor de Vídeo com HyperLogLog no Postgres
Você quer mostrar aos editores exatamente onde os espectadores param de assistir a um vídeo.
Para fazer isso, você precisa de um mapa de calor. Este mapa de calor mostra espectadores únicos para cada segundo de um vídeo.
Na TrendVidStream, processamos 40 milhões de eventos todos os dias. Usamos uma stack simples: PHP 8.4 e Postgres.
O problema é a cardinalidade. Contar espectadores únicos por segundo é difícil.
A maneira ingênua:
- Use uma tabela de eventos brutos.
- Execute
COUNT(DISTINCT viewer_hash). - Agrupe por vídeo e bucket de tempo.
Isso falhou rapidamente. Para 90 dias de dados, você precisa de 3,6 bilhões de linhas. As consultas levavam 9 segundos. Além disso, contagens de únicos não permitem rollup. Você não pode somar "únicos por hora" para obter "únicos por dia" porque a mesma pessoa aparece em ambos.
A solução: HyperLogLog (HLL).
O HLL cria um pequeno "sketch" de um conjunto de dados. Ele é minúsculo, cerca de 10 KB, independentemente de quantos milhões de pessoas assistam.
Por que o HLL funciona para mapas de calor:
- Tamanho fixo: Ele permanece pequeno.
- Mesclável: Você pode fazer o union de dois sketches para obter uma contagem global. Isso resolve o problema de rollup.
Nossa Implementação:
Usamos a extensão postgresql-hll. Usamos duas tabelas.
- Uma tabela de staging: Uma tabela apenas de inserção (append-only) onde o PHP escreve os heartbeats brutos. Não usamos índices aqui para manter as escritas rápidas.
- Uma tabela de sketch: Esta armazena os dados HLL.
Usamos um cron job para mover os dados da staging para a tabela de sketch. 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;
O operador "||" mescla os novos dados no sketch existente.
Os Resultados:
- Velocidade de consulta: Nossas consultas de mapa de calor levam de 15 a 40 ms. O método antigo levava 9 segundos.
- Armazenamento: Nosso conjunto de dados de 90 dias tem 40 GB. O método antigo teria ocupado 1,2 TB.
- Precisão: Observamos um erro de cerca de 0,6% a 1,4%. Para um mapa de calor, isso é perfeito.
Lições aprendidas:
- Faça o hash dentro do banco de dados. Não faça o hash no seu app.
- Faça atualizações em lote (batch). Não atualize os sketches linha por linha, ou você criará hotspots de escrita.
- Use um salt diário para os hashes. Isso protege a privacidade e mantém seus sketches diários limpos.
Se o seu dashboard está lento por causa do COUNT(DISTINCT), você não precisa de um data warehouse massivo. Você precisa de HLL.