Membangun Heatmap Video Dengan HyperLogLog Di PostgreSQL
Anda ingin menunjukkan kepada editor dengan tepat di bagian mana penonton berhenti menonton video.
Untuk melakukan ini, Anda memerlukan heatmap. Heatmap ini menunjukkan jumlah penonton unik untuk setiap detik video.
Di TrendVidStream, kami memproses 40 juta event setiap hari. Kami menggunakan stack sederhana: PHP 8.4 dan Postgres.
Masalahnya adalah kardinalitas. Menghitung penonton unik per detik itu sulit.
Cara naif:
- Gunakan tabel event mentah.
- Jalankan
COUNT(DISTINCT viewer_hash). - Kelompokkan berdasarkan video dan bucket waktu.
Cara ini gagal dengan cepat. Untuk data selama 90 hari, Anda membutuhkan 3,6 miliar baris. Query memakan waktu 9 detik. Selain itu, hitungan unik tidak dapat diakumulasikan (roll up). Anda tidak bisa menjumlahkan "uniques per jam" untuk mendapatkan "uniques per hari" karena orang yang sama bisa muncul di keduanya.
Solusinya: HyperLogLog (HLL).
HLL membuat "sketch" kecil dari sebuah dataset. Ukurannya sangat kecil, sekitar 10 KB, tidak peduli berapa juta orang yang menonton.
Mengapa HLL bekerja untuk heatmap:
- Ukuran tetap: Tetap kecil.
- Dapat digabungkan (mergeable): Anda dapat melakukan union pada dua sketch untuk mendapatkan hitungan global. Ini menyelesaikan masalah akumulasi (rollup).
Implementasi Kami:
Kami menggunakan ekstensi postgresql-hll. Kami menggunakan dua tabel.
- Tabel staging: Sebuah tabel append-only di mana PHP menulis heartbeat mentah. Kami tidak menggunakan indeks di sini agar proses penulisan tetap cepat.
- Tabel sketch: Tabel ini menyimpan data HLL.
Kami menggunakan cron job untuk memindahkan data dari staging ke tabel sketch. Kami menggunakan perintah ini:
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;
Operator "||" menggabungkan data baru ke dalam sketch yang sudah ada.
Hasilnya:
- Kecepatan query: Query heatmap kami memakan waktu 15 hingga 40 ms. Cara lama memakan waktu 9 detik.
- Penyimpanan: Dataset 90 hari kami berukuran 40 GB. Cara lama akan memakan tempat sebesar 1,2 TB.
- Akurasi: Kami melihat error sekitar 0,6% hingga 1,4%. Untuk sebuah heatmap, ini sudah sempurna.
Pelajaran yang dipetik:
- Lakukan hashing di dalam database. Jangan lakukan hashing di aplikasi Anda.
- Lakukan pembaruan secara batch. Jangan memperbarui sketch satu per satu baris atau Anda akan menciptakan write hotspots.
- Gunakan salt harian untuk hash. Ini melindungi privasi dan menjaga sketch harian Anda tetap bersih.
Jika dashboard Anda lambat karena COUNT(DISTINCT), Anda tidak membutuhkan data warehouse yang masif. Anda membutuhkan HLL.