How to architecture efficiently a leaderboard for a game which target 100k to 10M users where there can be several filters?
We have to display top 20 + user ranking.
For context this is a educational game which have around 100 games with ranking for specific games but also a general leaderboard. The db will probably be Postgres.
Filters are some characteristics that users have, allowing to restrict the leaderboard to some types of users. These filters are :
- state / region
- school (very long list)
- school grade (8 differents)
Sorted sets from redis seem to be the go-to solution for simple effective leaderboard but it doesn't work for lots of filters if I well understood what I have read.
I explored lot of possibilities. Simple leaderboard with sql queries, which can eventually be updates each hour to not overload server, but the performance is bad with a lot of users. Stochastic leaderboard with exact top 20 but inexact rank for user, and sorted set with redis. These 2 last ones are ok for simple leaderboards but not with filters.
I hope to find a way to have cost-effective leaderboard with filtering abilities and if possible exact and realtime.
Thanks you all!