PostgreSQL'de HyperLogLog ile Video Isı Haritaları Oluşturmak
Editörlere, izleyicilerin videoyu tam olarak nerede izlemeyi bıraktığını göstermek istersiniz.
Bunu yapmak için bir ısı haritasına ihtiyacınız var. Bu ısı haritası, bir videonun her saniyesi için benzersiz izleyici sayısını gösterir.
TrendVidStream'de her gün 40 milyon olay (event) işliyoruz. Basit bir yığın (stack) kullanıyoruz: PHP 8.4 ve Postgres.
Sorun kardinalite (cardinality). Saniye başına benzersiz izleyici saymak zordur.
Basit (naif) yöntem:
- Ham olaylar tablosu kullanın.
COUNT(DISTINCT viewer_hash)çalıştırın.- Video ve zaman dilimine (time bucket) göre gruplandırın.
Bu yöntem kısa sürede başarısız oldu. 90 günlük veri için 3,6 milyar satıra ihtiyacınız var. Sorgular 9 saniye sürüyordu. Ayrıca, benzersiz sayımlar birleştirilemez (roll up). Aynı kişi her iki grupta da göründüğü için "saatlik benzersizleri" toplayarak "günlük benzersizleri" elde edemezsiniz.
Çözüm: HyperLogLog (HLL).
HLL, bir veri kümesinin küçük bir "taslağını" (sketch) oluşturur. Kaç milyon kişi izlerse izlesin, boyutu yaklaşık 10 KB gibi çok küçüktür.
HLL neden ısı haritaları için uygundur:
- Sabit boyut: Küçük kalır.
- Birleştirilebilir: Küresel bir sayı elde etmek için iki taslağı birleştirebilirsiniz (union). Bu, birleştirme (rollup) sorununu çözer.
Uygulamamız:
postgresql-hll eklentisini kullanıyoruz. İki tablo kullanıyoruz.
- Bir staging (hazırlık) tablosu: PHP'nin ham heartbeat verilerini yazdığı, yalnızca ekleme yapılabilen (append-only) bir tablo. Yazma işlemlerini hızlı tutmak için burada indeks kullanmıyoruz.
- Bir sketch (taslak) tablosu: HLL verilerini burada saklıyoruz.
Verileri staging tablosundan sketch tablosuna taşımak için bir cron işi kullanıyoruz. Şu komutu kullanıyoruz:
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;
"||" operatörü, yeni verileri mevcut taslağa birleştirir.
Sonuçlar:
- Sorgu hızı: Isı haritası sorgularımız 15 ile 40 ms sürüyor. Eski yöntem 9 saniye sürüyordu.
- Depolama: 90 günlük veri kümemiz 40 GB. Eski yöntem 1,2 TB yer kaplayacaktı.
- Doğruluk: Yaklaşık %0,6 ile %1,4 arasında bir hata payı görüyoruz. Bir ısı haritası için bu mükemmeldir.
Çıkarılan dersler:
- Hash işlemini veritabanı içinde yapın. Uygulamanızda hash yapmayın.
- Güncellemelerinizi toplu (batch) yapın. Taslakları satır satır güncellemeyin, aksi takdirde yazma darboğazları (write hotspots) oluşturursunuz.
- Hash'ler için günlük bir salt (salt) kullanın. Bu, gizliliği korur ve günlük taslaklarınızın temiz kalmasını sağlar.
Eğer dashboard'unuz COUNT(DISTINCT) nedeniyle yavaşsa, devasa bir veri ambarına ihtiyacınız yok demektir. İhtiyacınız olan şey HLL'dir.