Currently, whenever a request to /api/v1/players/ranking comes in, the server computes the ranking from scratch by essentially iterating over all records in the database and summing up the scores they would yield (https://github.com/stadust/pointercrate/blob/fcbddfd9af7a042135bf2b28636d81a7730add97/migrations/20210419002933_nationalityupdate.up.sql#L374-L428). While this was fine 7 years ago, it is clearly not scalable (today's database has over 100 000 records!), and its starting to become a bottleneck. Instead, we should cache the score in a new score column of the players table. This sadly introduces statefulness (the avoidance of which was the reason I originally implemented the on-demand computation). The following events will produce a need for the score column to be updated:
Record newly approved
Record that was previously approved gets unapproved
Player gets banned
Demon gets added or moved around
(formula update - rare, can be ignored and instead considered as part of migration that does the update)
I do not want us to move to a world where the stats viewer is only updated on a fixed interval (like the in-game leaderboards), so we will need to adjust the scores immediately when handling the above events.
Trying to compute the exact changes that each event would entail will be difficult, particularly for demon movements, but I think we the following slightly heavy-handed approach can get us quite a ways:
When records get approved/rejected or players get banned (first 3 points above), recompute the score for the affected player from scratch
When demons get added or moved, recompute the scores of all players from scratch
The latter means that we will still have to do an iteration over all records, however instead of doing it on every request (thousands of times a day), we only do it when demons get added/moved, which happens rarely (maybe a handful of times a month).
stadust
commented
2 months ago
Currently, whenever a request to
/api/v1/players/rankingcomes in, the server computes the ranking from scratch by essentially iterating over all records in the database and summing up the scores they would yield (https://github.com/stadust/pointercrate/blob/fcbddfd9af7a042135bf2b28636d81a7730add97/migrations/20210419002933_nationalityupdate.up.sql#L374-L428). While this was fine 7 years ago, it is clearly not scalable (today's database has over 100 000 records!), and its starting to become a bottleneck. Instead, we should cache the score in a newscorecolumn of theplayerstable. This sadly introduces statefulness (the avoidance of which was the reason I originally implemented the on-demand computation). The following events will produce a need for thescorecolumn to be updated:I do not want us to move to a world where the stats viewer is only updated on a fixed interval (like the in-game leaderboards), so we will need to adjust the scores immediately when handling the above events.
Trying to compute the exact changes that each event would entail will be difficult, particularly for demon movements, but I think we the following slightly heavy-handed approach can get us quite a ways:
The latter means that we will still have to do an iteration over all records, however instead of doing it on every request (thousands of times a day), we only do it when demons get added/moved, which happens rarely (maybe a handful of times a month).