Leaderboard Game Jam seperti yang tampil di pengumuman June Solstice Game Jam menggabungkan puluhan ribu skor peserta. Saat data terus bertambah, query ranking penuh bisa menjadi lambat. Artikel ini menjelaskan langsung langkah optimal: temukan query bermasalah, pasang indeks yang tepat, pilih pagination yang sesuai, dan pantau pertumbuhan data supaya leaderboard tetap responsif.

Diagnosa Bottleneck SQL pada Leaderboard

Mulai dengan meniru query yang biasa digunakan untuk menampilkan top 100 peserta, misalnya:

SELECT user_id, score, updated_at
FROM leaderboard_scores
WHERE jam_id = :jam_id
ORDER BY score DESC, updated_at DESC
LIMIT 100;

Gunakan EXPLAIN ANALYZE untuk melihat apakah planner melakukan sequential scan pada jutaan baris. Jika waktu respon melebihi 300 ms, indikatornya adalah biaya tinggi pada tahap sort atau filter.

Identifikasi juga apakah query perlu menyaring berdasarkan status peserta, platform, atau periode waktu. Setiap predicate tambahan perlu diakomodasi dalam desain indeks untuk menghindari bitmap heap scan yang mahal.

Rancang Indeks Efisien untuk Kolom Ranking

Leaderboard mengandalkan nilai skor dan waktu update. Indeks komposit membantu planner menangani ORDER BY dan WHERE sekaligus. Contoh indeks untuk PostgreSQL:

CREATE INDEX idx_leaderboard_jam_score
ON leaderboard_scores (jam_id, score DESC, updated_at DESC);

Indeks ini memungkinkan database membaca baris langsung sesuai urutan ranking tanpa sort tambahan. Jika ada filter tambahan, seperti status peserta, pertimbangkan indeks bertingkat:

CREATE INDEX idx_leaderboard_jam_status_score
ON leaderboard_scores (jam_id, status, score DESC, updated_at DESC);

Perhatikan trade-off: indeks lebih banyak berarti biaya insert/update lebih tinggi. Bandingkan frekuensi penulisan skor (setiap submit) dengan frekuensi query top leaderboard. Pastikan VACUUM rutin dijalankan agar statistik tetap akurat.

Optimalkan Pagination: Offset vs Keyset

Offset pagination (LIMIT x OFFSET y) mudah diimplementasikan tapi lambat saat y besar karena database masih membaca baris sebelumnya. Untuk leaderboard yang menampilkan posisi tertentu, keyset pagination lebih efisien.

Contoh keyset pagination untuk halaman berikutnya:

SELECT user_id, score, updated_at
FROM leaderboard_scores
WHERE jam_id = :jam_id
  AND (score, updated_at) < (:last_score, :last_updated)
ORDER BY score DESC, updated_at DESC
LIMIT 100;

Nilai :last_score dan :last_updated berasal dari entri terakhir pada halaman sebelumnya. Keyset pagination hanya membaca baris yang dibutuhkan, menjaga respons tetap stabil meskipun dataset besar.

Namun, keyset tidak cocok bila pengguna menavigasi ke halaman tertentu (misalnya posisi 501). Dalam kasus itu, kombinasikan: gunakan keyset untuk menggeser seleksi kecil, dan offset hanya saat pengguna memilih halaman numerik, dengan cache hasil set pertama.

Monitoring Pertumbuhan Data dan Kinerja

Siapkan metrik agar tim tahu kapan perlu scale: buat dashboard yang memantau tiga angka utama:

  • Jumlah baris leaderboard per jam/jam_id
  • 99th percentile latency dari query top leaderboard
  • Cache hit ratio pada indeks ranking

Gunakan tooling seperti Prometheus untuk scraping pg_stat_statements atau metrik DB lainnya. Set alert jika latency top leaderboard melebihi ambang (misalnya 500 ms) atau jika pertumbuhan baris melebihi proyeksi.

Selain metrik, rekam statistik EXPLAIN ANALYZE setiap minggu untuk memastikan indeks masih dipakai. Jika plan berubah karena distribusi skor berubah (misalnya banyak tied-score), evaluasi kembali indeks dengan CREATE STATISTICS atau tambahan partial index.

Kesimpulan: Responsif Saat Data Bertambah

Dengan memahami bottleneck query, merancang indeks yang sesuai, memilih pagination yang tepat, dan memantau pertumbuhan data, leaderboard game jam tetap responsif meskipun partisipan meningkat. Fokus pada data reality dari kontes seperti June Solstice Game Jam: prioritaskan indeks komposit untuk ORDER BY utama, gunakan keyset saat memungkinkan, dan manjakan tim operasi dengan metrik proaktif.