Postgresql或其他数据库:如何动态、高效地自动排序行和存储位置?

In my current schema, I have the scores of users in a mini-game (that not necessarily everyone is playing) with multiple types of scores. For sake of argument, let's say the two scores are hits and accuracy.

在我目前的模式中，我在一个小型游戏(不一定每个人都在玩)中有很多的用户，有多种类型的分数。为了讨论起见，我们假设这两个分数是命中的和准确的。

I need to keep a scoreboard and ranking of all of my users (2.8 mil) who are currently playing this game (only 248k). I know already anyone with hits = 0 is not considered a player.

我需要保留一个计分板和我的所有用户的排名(2.8英里)，目前正在玩这个游戏(只有248k)。我知道任何命中= 0的人都不被认为是球员。

So let's say a player somewhere in the middle gets their 200th hit, getting an accuracy increase from .58 to .6, displacing other users who currently have 199 hits and accuracy between .58 and .6. I want to store every ranking change dynamically and efficiently in each row in any method possible. Essentially giving me the ability to run the following query:

假设一个玩家在中间的某个地方得到了200次命中，从。58到。6的精度增加了，取代了其他的用户，他们现在的命中率和准确度在。58和。6之间。我想用任何可能的方法在每一行中动态有效地存储每个排名变化。本质上给了我运行以下查询的能力:

SELECT hit_ranking, accuracy_ranking FROM score WHERE user_id=100;

选择hit_ranking, accuracy_ranking FROM score WHERE user_id=100;

I have tried or explored the following approaches:

我尝试或探索了以下方法:

Trigger on update to update all rows' ranking (this idea was deemed to be far too computationally expensive at 1/10 of our current userbase)

在更新时触发，以更新所有行的排名(这个想法被认为在计算上太过昂贵，只有当前用户群的1/10)
Cron job to sort all records in python and update them in the database incrementally (this took ~40 minutes with 60k users, and will not result in a good user experience)

Cron job对python中的所有记录进行排序并在数据库中增量地更新它们(这在60k用户中花费了大约40分钟，不会产生良好的用户体验)
Calculate each user's ranking on performing an action by storing a job in SQS (queueing service) and calculating ranking for that user

通过在SQS(排队服务)中存储作业并计算该用户的排名，计算每个用户在执行操作时的排名

We have 3 running right now which worked at a smaller scale, but as the dataset grows it can take some time to calculate each ranking, and a lack of action on the user's end means they're never updated.

我们现在有3个运行在小范围内，但是随着数据集的增长，计算每个排名可能需要一些时间，而用户端缺乏操作意味着它们永远不会更新。

If postgresql or another database can store this data sorted dynamically (similar to the concept of a sorted heap), it would be a huge help. I am willing to make this database its own instance as this mini-game is considered to be one of our growing features.

如果postgresql或其他数据库可以动态地存储这些数据(类似于排序堆的概念)，这将是一个巨大的帮助。我愿意将这个数据库作为它自己的实例，因为这个迷你游戏被认为是我们日益增长的特性之一。

1 个解决方案

#1

Consider the CREATE INDEX syntax in Postgres, namely the bit where you can combine multiple columns as an expression, and sort them descending.

考虑Postgres中的创建索引语法，即可以将多个列合并为表达式并对它们进行降序排序的位。

So, something like

所以,类似

CREATE INDEX index_hitrank ON scoreboard (hits DESC);
CREATE INDEX index_accuracyrank ON scoreboard (accuracy DESC);

Even simple maths is possible

即使是简单的数学也是可能的

CREATE INDEX index_bothrank ON scoreboard (hits + accuracy DESC);

SELECT * from scoreboard order by hits DESC should then be nice and fast :)

选择*从记分牌顺序按点击DESC应该是不错和快速:)

#1