Lowest-Cost Leaderboard system using firebase

Question

My goal is to effectively get a list of children (ordered* and indexed**) with the lowest number of data transfer.

* ordered: ordered by points for each user / database child

** indexed: 2 or less ranks behind/after the current user [A specific child] (further elaborated below)

My database structure is as follows:-

I basically want to get the first 3 users ordered by points (simple):-

val usersRef = FirebaseDatabase.getInstance(DB_LINK).getReference("users").orderByChild("points")
usersRef.limitToFirst(3).addValueEventListener(
    object : ValueEventListener {
        override fun onDataChange(snapshot: DataSnapshot) {
            for (ds in snapshot.children) {
                val points: String = snapshot.child("points").getValue(String::class.java)!!
                val firstName: String = snapshot.child("firstName").getValue(String::class.java) ?: ""
                val uid: String = snapshot.key!!
                // Update View
            }
        }

        override fun onCancelled(error: DatabaseError) {}
    }
)

Then, provided that the currently logged in user isn't one of the first three, I want to get his rank (order according to points in the whole db), 2 users' before him, and 2 users' after him without querying the whole database (it's a user database that can get up to 50K unique users) because querying the whole database is a really expensive client-side task.

I checked firebase data filtering page but found nothing useful about limiting results according to a certain child.

This answer doesn't satisfy my needs, as it loops over the whole database (in my case, 50K records). I need an effective method as I need to really save these firebase bills.

Moreover, I check this answer but it didn't meet my needs because it still queries the whole database, meaning it is not effective and will be billed for each node before the current user. (Maybe he is number 40,000 in the db, so I shouldn't query the whole db each time to get his rank and get billed for 39,999 reads)

I searched for a way to somehow use booleans to filter queries but again found nothing useful. Here is my not-effective code:-

// Gets all children.
usersRef.addValueEventListener(
    object : ValueEventListener {
        override fun onDataChange(snapshot: DataSnapshot) {
            for (ds in snapshot.children) {
                val points: String = snapshot.child("points").getValue(String::class.java)!!
                val firstName: String = snapshot.child("firstName").getValue(String::class.java) ?: ""
                val uid: String = snapshot.key!!
                // Update View only if user is `2 <= usersRank - theirRank <= -2`
            }
        }

        override fun onCancelled(error: DatabaseError) {}
    }
)

I want to achieve something like this:- (Styling already done, logic remaining)

Is there a way to achieve this? Any alternatives?

EDIT: I found out that firestore offers aggregation queries that may help in this situation. Doing more research to further narrow down the costs.

Answer 1

This operation is not available on a Firebase Realtime Database. A better option would be Firestore.

Why?

Well, A fire-store database can give you the count of objects in a certain query. This is a new feature added by firebase. You basically type the query you want, then add .count() before .get(); that way it'll return the count of objects only. This is called aggregation queries. Learn more about them here.

Cloud Functions - Why isn't it appropriate here?

Using a Cloud Function for aggregations avoids some of the issues with client-side transactions, but comes with a different set of limitations:

• Cost - Each rating added will cause a Cloud Function invocation, which may increase your costs. For more information, see the Cloud Functions pricing page.
• Latency - By offloading the aggregation work to a Cloud Function, your app will not see updated data until the Cloud Function has finished executing and the client has been notified of the new data. Depending on the speed of your Cloud Function, this could take longer than executing the transaction locally.
• Write rates - this solution may not work for frequently updated aggregations because Cloud Firestore documents can only be updated at most once per second. Additionally, If a transaction reads a document that was modified outside of the transaction, it retries a finite number of times and then fails.

Combining this with other methods

Now that you're using COUNT() for this system, there is one more method to help further narrow down the costs. That is Periodic Updates.

Periodic Updates

Who would care about a live ranking of all users? You can make the leaderboard update each minute, hour, or day. For example, stack overflow's leaderboard is updated once a day!

This approach would really work for any number of players and any write rate. However, you might need to adjust the frequency though as you grow depending on your willingness to pay.

Costs Side

For each normal read, you are charged for one read. Very simple. However, for one count, you're charged for 0.001 reads (meaning 1000 counts = 1 read). For more information about costs, check this article by firebase.

Final Thoughts

To connect everything up, we shall now apply this on our problem. Firstly, we'll need to keep the first portion of the code as it is. (The portion that grabs the first 3 users), though with some changes to port it to firebase.

NOTICE: Don't forget to setup a composite index because we're ordering by multiple fields at the same time.

val top3 = HashMap<Int, HashMap<String, String>>()

Firebase.firestore.collection("users").orderBy("points", Query.Direction.DESCENDING)
    .orderBy("firstName", Query.Direction.ASCENDING)
    .get().addOnSuccessListener {
    for ((index, doc) in it.documents.withIndex()) {
        val firstName = doc.getString("firstName")!!
        val points = doc.getString("points")!!
        top3[index+1] = hashMapOf("first" to firstName, "points" to points, "uid" to doc.id)
    }
}

• More about ordering and limiting here.

Then, we'll need to implement the COUNT() feature.

Firebase.firestore.collection("users")
    .whereGreaterThan("points", UserProfile.getInstance()!!.getProfile().points)
    .count().get(AggregateSource.SERVER).addOnSuccessListener {
    println("Your rank is: ${it.count+1}")
}

Basically what I did here was:-

1. Selecting the Collection
2. Ordered Ascending by first name so no duplicate ranks.
3. Count them, and pass onto the function.

The final step is just updating the hash map top3 and rank of user each hour/day/minute/...