How to properly construct a Leaderboard for 10-50K records in noSQL?-CodePudding

In my upcoming mobile app project, I plan to use the google ecosystem - that means firebase auth, firestore (noSQL), cloud functions. I want to stick to noSQL as it fits better for this project. While scoping the project, the client asked for a leaderboard of top 100 users (application has some kind of xp system), which is not a problem, but then I remembered I'm working in noSQL.

I cannot query over collection, as Google Firebase counts every document read while sorting. (while having 50K users this accounts to 0.06$ per user request)

What is the best approach for this problem? This is a duplicate of this question, but I don't feel satisfied with the approaches that were presented. Is there any better way to do this? I expect rapidly scaling user base in 10's of thousands of users.

I did a quick math considering this answer. The max size of document in Google Firestore is 1MiB, which amounts to maximum of 62,500 users that can fit into the document, considering they have userID that spans to 8 digits and XP that spans to 6 digits. This solution felt like the most peaceful amongst any others.

The problem is, that sorting would have be executed on the client, in Flutter app.

Would this solution be somehow good?

CodePudding user response：

The first link that you've shared mentions that you'll need a query that looks like:

SELECT count(id) FROM players WHERE score > {playerScore}

Firestore now supports aggregation queries and has a new count() function that returns number of documents matching your query. You can try running the following query:

db.collection("users").where("score", isGreaterThanOrEqualTo: userScore).count().then(
      (res) => print(res), // <-- user rank 
      one rror: (e) => print("Error completing: $e"),
    );

This was added recently so make sure you are using latest version of the Firebase SDK.

CodePudding user response：

Answering my own question and locking this thread.

User Dharmaraj pointed out, that while using .limit(100), Firestore does not charge for the whole collection, but only for amount of the limited results. This was unknown for me and now I am presented with a great solution that doesn't screw up your data structure.

Thank you again, mr. Dharmaraj for your help.